<Method 'Range' of object '_Global' failed> error

[Ken Loomis]

When I run this macro,

Sub addLeadType(leadType As Integer)
Dim totalrows As Integer
Dim typeCol As Integer

typeCol = 11

' The following line generates the error
Range(Cells(1, typeCol)).Value = "Lead Type"


totalrows = ActiveSheet.UsedRange.Rows.count
Range(Cells(2, typeCol), Cells(totalrows, typeCol)).Value = leadType
End Sub

I get a "<Method 'Range' of object '_Global' failed> error at the line
indicated above.

I tried hard coding the "11" in place of typeCol and get the same error.

Can someone tell me what I am doing wrong here?

Thanks,
Ken Loomis

 

[Tom Ogilvy]

Invalid syntax

Range(Cells(1, typeCol)).Value = "Lead Type"

should be

Cells(1, typeCol).Value = "Lead Type"


Range(rng reference, rng reference) works, but Range(rng reference)
doesn't (and is redundant).

 

[Paul Robinson]

Hi
You can use Cells without Range like this

Cells(1, typeCol).Value = "Lead Type"

This refers to cells on the active sheet, so to be safe you might want
to use

ActiveSheet.Cells(1, typeCol).Value = "Lead Type"

or if your sheet is not active use

Worksheets("MySheetName").Cells(1, typeCol).Value = "Lead Type"

You can also use Cells to reference a location in a Range like this

MyRange.Cells(1, typeCol).Value = "Lead Type"

this will now refer to the (1, typeCol) entry in MyRange.

Your further syntax is OK

Range(Cells(2, typeCol), Cells(totalrows, typeCol)).Value = leadType

which again refers to a Range on the Active Sheet. Qualification is
then most efficiently done with With...End With
With ActiveSheet
Range(.Cells(2, typeCol), .Cells(totalrows, typeCol)).Value =
leadType
End With

regards
Paul