Solved: VBA Code for large number of cells in a range

[tammyl]

Hi,

I wish to refer to a large number of individual cells in a range.
So far i have

CalRng = Range("A4", "C4", "E4", "G4", "K4", "M4", "O4", _
"A10", "C10", "E10", "G10", "K10", "M10", "O10", _
"A16", "C16", "E16", "G16", "K16", "M16", "O16", _
"A22", "C22", "E22", "G22", "K22", "M22", "O22", _
"A28", "C28", "E28", "G28", "K28", "M28", "O28", _
"A34", "C34", "E34", "G34", "K34", "M34", "O34")

vba calling code is
For each c in CalRng.....

But I get a Compile error that states:
Wrong number of arguments or invalid property assignment.
I do not wish to check every cell in the full range("A4:O34")

Hope someone can assist.
Much appreciated.
Cheers tammyl

[GTO]

Greetings Tammy,

When delineating the range, regardless of whether contiguous or not, it's all one string. Also, you must Set the range as an object. Due to the error you stated, I imagine you just missed the "Set" in your example, so the formatting of the string should be the problem. Try:

Sub test()
Dim CalRng As Range
Dim c As Range
Dim l As Long

Set CalRng = Range("A4, C4, E4, G4, K4, M4, O4," & _
"A10, C10, E10, G10, K10, M10, O10," & _
"A16, C16, E16, G16, K16, M16, O16," & _
"A22, C22, E22, G22, K22, M22, O22," & _
"A28, C28, E28, G28, K28, M28, O28," & _
"A34, C34, E34, G34, K34, M34, O34")
For Each c In CalRng
l = l + 1

c = l

Next
End Sub

I would mention that if the sub or function can be run while another than intended sheet is active, or for that matter, a generally good practice is to define what sheet the range belongs to, such as:

Set CalRng = Worksheets("Sheet1").Range("A4, C4, E4, G4, K4, M4, O4," & _
"A10, C10, E10, G10, K10, M10, O10," & _
"A16, C16, E16, G16, K16, M16, O16," & _
"A22, C22, E22, G22, K22, M22, O22," & _
"A28, C28, E28, G28, K28, M28, O28," & _
"A34, C34, E34, G34, K34, M34, O34")

...or, better yet, use the sheet's object or codename, such as:

Set CalRng = Sheet1.Range("A4, C4, E4, G4, K4, M4, O4," & _
"A10, C10, E10, G10, K10, M10, O10," & _
"A16, C16, E16, G16, K16, M16, O16," & _
"A22, C22, E22, G22, K22, M22, O22," & _
"A28, C28, E28, G28, K28, M28, O28," & _
"A34, C34, E34, G34, K34, M34, O34")

Hope this helps:) ,

Mark

[tammyl]

Mark,
Solved. Worked perfectly. Thankyou so much for your help.
Cheers tammyl :thumb