PDA

View Full Version : Solved: Error when referencing 3 ranges with a variable



Digita
01-20-2009, 06:11 PM
Hi,

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


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


Thanks in advance & regards


kp

Ischyros
01-20-2009, 06:42 PM
I tried your code with 1, 2, and finally 3 range arguments. Worked for 1 and 2. This should work

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


Sorry I can't explain why....:dunno

Kenneth Hobs
01-20-2009, 07:19 PM
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:
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

Digita
01-20-2009, 07:46 PM
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?

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

Kenneth Hobs
01-20-2009, 07:54 PM
"A2:A30,C2:C30,L2:L30"

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
01-20-2009, 09:08 PM
Thanks Ken.