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?

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
--
HTH...

Jim Thomlinson


"davegb" wrote:

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?

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


--
HTH...

Jim Thomlinson


"davegb" wrote:

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?

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

"davegb" wrote:
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?