Code ran, now it doesn't

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 =3D ActiveSheet.Range("A2")
iOrigCityNo =3D Left(strOrig, 2)
iEndRow =3D ActiveSheet.Range.Cells(Rows.C=ADount, "B").End(xlUp).Row
Set rTOCtyLst =3D 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 =3D ActiveSheet.Range("A2")
lOrigCityNo =3D Left(strOrig, 2)
lEndRow =3D 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=3D 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.=20
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?