Solved: Error when referencing 3 ranges with a variable

[Digita]

Hi,

Below is an extract of a script which errors out on the last line in XL 2k. Not sure why . Any idea?

[vba]
Lr = 30
Range("A2:A" & Lr, "C2:C" & Lr, "L2:L" & Lr).NumberFormat = "#,##0,"
[/vba]

Thanks in advance & regards


kp

[Ischyros]

I tried your code with 1, 2, and finally 3 range arguments. Worked for 1 and 2. This should work
[VBA]
Lr = 30
Range("A2:A" & Lr, "C2:C" & Lr).NumberFormat = "#,##0,"
Range("L2:L" & Lr).NumberFormat = "#,##0,"
[/VBA]

Sorry I can't explain why....

[Kenneth Hobs]

When you press the Compile button it tells you why. You used 3 parameters for Range. Put your cursor on Range and press F1 to get help. It uses 1-2 parameters.

Use Union:
[VBA]Sub test()
Dim lr As Long
lr = 30
Union(Range("A2:A" & lr), Range("C2:C" & lr), Range("L2:L" & lr)).NumberFormat = "#,##0,"
End Sub[/VBA]

[Digita]

Hi Ken & Ischyros,

Thanks both for your responses. If range only accepts 2 parameters max, then why does the line below work with no problem?

[VBA]Range("A2:A30,C2:C30,L2:L30").NumberFormat = "#,##0,"[/VBA]

[Kenneth Hobs]

[VBA]"A2:A30,C2:C30,L2:L30"[/VBA]

That is one parameter. Of course that way works as well since it is one parameter. If you are going to do that, then you need to concatenate the ","'s to build your parameter.

[Digita]

Thanks Ken.