VBA "Left" not working
 

I have a macro that I've been using for about a year and just recently I'm
getting a "compile error: wrong number of arguments or invalid property
assignment" on the Left function (Visual Basic for Applications).

As far as I know, nothing has changed and I'm still using Excel 2003 with SP2.

VBA "Left" not working
 

As ALWAYS, post your macro for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"prs16001" wrote in message
...
I have a macro that I've been using for about a year and just recently I'm
getting a "compile error: wrong number of arguments or invalid property
assignment" on the Left function (Visual Basic for Applications).

As far as I know, nothing has changed and I'm still using Excel 2003 with
SP2.

VBA "Left" not working
 

could you provide the code?

VBA "Left" not working
 

The full code is several hundred lines and this is the line where it gets the
compile error. This is also the first use of the Left function. As I stated
before, I've been using this with no errors for the past year and now I'm
getting a compile error. lot_ed and lotidLen are dimensioned as variant.


lot_ed = Left(RegWB.Worksheets("NSR" & StepNum & "ED").Cells(j, 3).Value,
lotidLen)

VBA "Left" not working
 

It sounds like you may have lost one (or more) references. Try clicking
Tools/References on the VB editor menu bar and put a check mark next to any
that are marked as missing.

Rick


"prs16001" wrote in message
...
I have a macro that I've been using for about a year and just recently I'm
getting a "compile error: wrong number of arguments or invalid property
assignment" on the Left function (Visual Basic for Applications).

As far as I know, nothing has changed and I'm still using Excel 2003 with
SP2.

VBA "Left" not working
 

The error is probably in the

RegWB.Worksheets("NSR" & StepNum & "ED").Cells(j, 3).Value

Make sure object RegWB is still set, examine the values of StepNum and j,
and make sure the worksheet "NSR" & StepNum & "ED" still exists, etc.

"prs16001" wrote:

The full code is several hundred lines and this is the line where it gets the
compile error. This is also the first use of the Left function. As I stated
before, I've been using this with no errors for the past year and now I'm
getting a compile error. lot_ed and lotidLen are dimensioned as variant.


lot_ed = Left(RegWB.Worksheets("NSR" & StepNum & "ED").Cells(j, 3).Value,
lotidLen)

VBA "Left" not working
 

Just had something similar so thought I'd post this for others.

This is almost certainly a 'reference' issue.

Just goto Tools - References

You need to tick a reference that has 'Left' within it. 'lookup' definitely does... there are probably others.

The reason your code is not working is because a previously used reference is now 'missing' or has been unchecked for some reason... perhaps by another user of the spreadsheet.

On Tuesday, July 15, 2008 8:40 AM prs1600 wrote:

I have a macro that I've been using for about a year and just recently I'm
getting a "compile error: wrong number of arguments or invalid property
assignment" on the Left function (Visual Basic for Applications).

As far as I know, nothing has changed and I'm still using Excel 2003 with SP2.


On Tuesday, July 15, 2008 8:49 AM Don Guillett wrote:

As ALWAYS, post your macro for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software



On Tuesday, July 15, 2008 9:45 AM prs1600 wrote:

The full code is several hundred lines and this is the line where it gets the
compile error. This is also the first use of the Left function. As I stated
before, I've been using this with no errors for the past year and now I'm
getting a compile error. lot_ed and lotidLen are dimensioned as variant.


lot_ed = Left(RegWB.Worksheets("NSR" & StepNum & "ED").Cells(j, 3).Value,
lotidLen)


On Tuesday, July 15, 2008 9:58 AM Rick Rothstein \(MVP - VB\) wrote:

It sounds like you may have lost one (or more) references. Try clicking
Tools/References on the VB editor menu bar and put a check mark next to any
that are marked as missing.

Rick


On Tuesday, July 15, 2008 10:04 AM Charli wrote:

The error is probably in the

RegWB.Worksheets("NSR" & StepNum & "ED").Cells(j, 3).Value

Make sure object RegWB is still set, examine the values of StepNum and j,
and make sure the worksheet "NSR" & StepNum & "ED" still exists, etc.

"prs16001" wrote:


On Thursday, July 17, 2008 11:01 PM Jarek Kujawa wrote:

could you provide the code?


Submitted via EggHeadCafe - Software Developer Portal of Choice
Review of DevExpress DXperience Control Suite
http://www.eggheadcafe.com/tutorials...rol-suite.aspx

VBA "Left" not working
 

Tim

Are 2 year old posts just now showing up at EggHead?


Gord Dibben MS Excel MVP

On Thu, 21 Oct 2010 21:48:40 GMT, tim bryden
wrote:

Just had something similar so thought I'd post this for others.

This is almost certainly a 'reference' issue.

Just goto Tools - References

You need to tick a reference that has 'Left' within it. 'lookup' definitely does... there are probably others.

The reason your code is not working is because a previously used reference is now 'missing' or has been unchecked for some reason... perhaps by another user of the spreadsheet.

On Tuesday, July 15, 2008 8:40 AM prs1600 wrote:

I have a macro that I've been using for about a year and just recently I'm
getting a "compile error: wrong number of arguments or invalid property
assignment" on the Left function (Visual Basic for Applications).

As far as I know, nothing has changed and I'm still using Excel 2003 with SP2.


On Tuesday, July 15, 2008 8:49 AM Don Guillett wrote:

As ALWAYS, post your macro for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software



On Tuesday, July 15, 2008 9:45 AM prs1600 wrote:

The full code is several hundred lines and this is the line where it gets the
compile error. This is also the first use of the Left function. As I stated
before, I've been using this with no errors for the past year and now I'm
getting a compile error. lot_ed and lotidLen are dimensioned as variant.


lot_ed = Left(RegWB.Worksheets("NSR" & StepNum & "ED").Cells(j, 3).Value,
lotidLen)


On Tuesday, July 15, 2008 9:58 AM Rick Rothstein \(MVP - VB\) wrote:

It sounds like you may have lost one (or more) references. Try clicking
Tools/References on the VB editor menu bar and put a check mark next to any
that are marked as missing.

Rick


On Tuesday, July 15, 2008 10:04 AM Charli wrote:

The error is probably in the

RegWB.Worksheets("NSR" & StepNum & "ED").Cells(j, 3).Value

Make sure object RegWB is still set, examine the values of StepNum and j,
and make sure the worksheet "NSR" & StepNum & "ED" still exists, etc.

"prs16001" wrote:


On Thursday, July 17, 2008 11:01 PM Jarek Kujawa wrote:

could you provide the code?


Submitted via EggHeadCafe - Software Developer Portal of Choice
Review of DevExpress DXperience Control Suite
http://www.eggheadcafe.com/tutorials...rol-suite.aspx