# Array question.

#### jasonb75

##### Well-known Member
Hi all, does anyone know if it's possible to make this work without using an obscene amount of code to do so?

Code:
``````ar = Range("C2").Value
Range("B38", "B74", "B110", "B146", "B182", "B218", "B254") = Array(ar, ar + 1, ar + 2, ar + 3, ar + 4, ar + 5, ar + 6)``````

I could use
Code:
``````Range("B38") = ar
Range("B74") = ar + 1
etc...``````

but the code module is starting to get a bit heavy so I'm trying to shorten things where possible.

Thanks

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
try:

Code:
``````Sub test()
ar = Range("C2").Value
For Each cel In Range("B38,B74,B110,B146,B182,B218,B254")
cel.Value = ar
ar = ar + 1
Next
End Sub``````

How 'bout something like this:

Code:
``````Sub test()
ar = Range("C2").Value
For i = 0 To 6
Cells(38 + i * 36, 2).Value = ar + i
Next i
End Sub``````

Actually, your original suggestion using the array would probably work too

Code:
``````Sub test2()
ar = Range("C2").Value
Range("B38,B74,B110,B146,B182,B218,B254").Value = Array(ar, ar + 1, ar + 2, ar + 3, ar + 4, ar + 5, ar + 6)
End Sub``````

It's just a matter of how you express the range in the Range() argument

So I guess my theory was correct, that the line I tried can only be used with a continuous horizontal range.

Thanks guys, that gives me some options to work with.

edit: excelR8R, I just tried the alternative range expression, it runs without error now, but the value doesn't increase, it always returns the 1st argument of the array.

Last edited:
When putting values in a discontinous range
Code:
``Range("B38,B74,B110,B146,B182,B218,B254").Value = someArray``
acts like
Code:
``````For Each oneArea in Range("B38,B74,B110,B146,B182,B218,B254").Areas
oneArea.Value = someArray
Next``````
That (plus Array(ar ,ar+1, ar+2,...) is a row type array) is why the numbers don't increase.

Would putting a function in your cells work?
Code:
``````With Range("B38,B74,B110,B146,B182,B218,B254")
.Formula = "=\$C\$2+(Row(A1)-1)/36"
End With``````

Thanks for clearing that up Mike, I've gone with excelR8R's original idea, the loop allows me to apply formats, etc as it goes so it condenses the code quite well.

I've already tried in cell functions, but I need actual entry in the cell, I only posted a small part of the code, the value in C2 will actually be sheet1 C2, which is formula variable., where as the cells that the code populates need to be fixed value.

Suggestions taken in though, I'm sure a time will come when that solves a problem

My bad for not checking the output on my second idea properly. Worst part is, I think I've come across this issue, as described by mik, before!

I dunno of this is just me, but sometimes, contributing on here can be very competetive and if you can't be quick, there's always shortest/smartest/sneakiest to go for.

Haha, I guess you forgot to read your sig before posting too

I dunno of this is just me, but sometimes, contributing on here can be very competetive and if you can't be quick, there's always shortest/smartest/sneakiest to go for.

Replies
2
Views
191
Replies
17
Views
3K
Replies
4
Views
572
Replies
7
Views
299
Replies
11
Views
1K

1,202,987
Messages
6,052,936
Members
444,616
Latest member
novit19089

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back