Code ran, now it doesn't

[davegb]

In an earlier post, I posted this code, which didnt' run.

Sub CtyMatch()
Dim strOrig, strOutcomes As String
Dim rCell, rTOCtyLst As Range
Dim iOrigCityNo, iEndRow As Integer


strOrig = ActiveSheet.Range("A2")
iOrigCityNo = Left(strOrig, 2)
iEndRow = ActiveSheet.Range.Cells(Rows.C­ount, "B").End(xlUp).Row
Set rTOCtyLst = Range(.Cells(1, 1), .Cells(1, iEndRow))


I was getting an error on the Set line.
I was also told not to use integer variable type anymore, so I changed
it to long.

Sub CtyMatch()
Dim strOrig, strOutcomes As String
Dim rCell, rTOCtyLst As Range
Dim lOrigCityNo, lEndRow As Long

strOrig = ActiveSheet.Range("A2")
lOrigCityNo = Left(strOrig, 2)
lEndRow = ActiveSheet.Range.Cells(Rows.Count, "B").End(xlUp).Row

Now I'm getting a "wrong number of assignments or invalid property
assignment" error on the lEndRow= line! The only thing thats changed is
the variable type, and I was told that I didn't need to use integer any
more, just to use long. I tried changing them back to integer, but it
still gives me the error! Can anyone explain to me why code that ran
fine a week ago doesn't run at all today? Ozone layer depletion maybe?
I haven't changed the location of the code or the sheet it's supposed
to execute on.
Any ideas?

 

[Jim Thomlinson]

You cna either use range of cells but not both. In your case you want Cells

Change
lEndRow = ActiveSheet.Range.Cells(Rows.Count, "B").End(xlUp).Row
To
lEndRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

 

[Jim Thomlinson]

Looking at your variable declarations you should probably give Chips site a
quick look. Some of your declarations are not working out as you intend them
I suspect...

http://www.cpearson.com/excel/variables.htm

Dim X, Y, Z As Single

is not the same as declaration

Dim X As Single, Y As Single, Z As Single

It is the same as

Dim X As Variant, Y As Variant, Z As Single

or, more explicitly, as

Dim X As Variant
Dim Y As Variant
Dim Z As Single

 

[Dave Patrick]

FYI; when you;

Dim iOrigCityNo, iEndRow As Integer

iOrigCityNo ends up dimensioned as Variant. If you want it as integer then
do something like;

Dim iOrigCityNo As Integer, iEndRow As Integer

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
In an earlier post, I posted this code, which didnt' run.

Sub CtyMatch()
Dim strOrig, strOutcomes As String
Dim rCell, rTOCtyLst As Range
Dim iOrigCityNo, iEndRow As Integer


strOrig = ActiveSheet.Range("A2")
iOrigCityNo = Left(strOrig, 2)
iEndRow = ActiveSheet.Range.Cells(Rows.C­ount, "B").End(xlUp).Row
Set rTOCtyLst = Range(.Cells(1, 1), .Cells(1, iEndRow))


I was getting an error on the Set line.
I was also told not to use integer variable type anymore, so I changed
it to long.

Sub CtyMatch()
Dim strOrig, strOutcomes As String
Dim rCell, rTOCtyLst As Range
Dim lOrigCityNo, lEndRow As Long

strOrig = ActiveSheet.Range("A2")
lOrigCityNo = Left(strOrig, 2)
lEndRow = ActiveSheet.Range.Cells(Rows.Count, "B").End(xlUp).Row

Now I'm getting a "wrong number of assignments or invalid property
assignment" error on the lEndRow= line! The only thing thats changed is
the variable type, and I was told that I didn't need to use integer any
more, just to use long. I tried changing them back to integer, but it
still gives me the error! Can anyone explain to me why code that ran
fine a week ago doesn't run at all today? Ozone layer depletion maybe?
I haven't changed the location of the code or the sheet it's supposed
to execute on.
Any ideas?