Array question.

[jasonb75]

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

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

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

 

[Weaver]

try:

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

 

[gardnertoo]

How 'bout something like this:

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

 

[Weaver]

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

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

 

[jasonb75]

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.

 

[mikerickson]

When putting values in a discontinous range

Range("B38,B74,B110,B146,B182,B218,B254").Value = someArray

acts like

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?

With Range("B38,B74,B110,B146,B182,B218,B254")
    .Formula = "=$C$2+(Row(A1)-1)/36"
End With

 

[jasonb75]

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

 

[Weaver]

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.

 

[jasonb75]

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.