Solved: Referencing A Variable + An Integer To Show Cell Value?

[Simon Lloyd]

Hello all the title of this may be misleading but i didnt know what to call it!, if i have set varibles like this: P1 = Range("AE56").Value, P2 = Range("O29").Value, P3 = Range("Z17").Value etc how then could i do this:
For I = 1 To 50
ActiveCell.Offset(0, I).Value = P & I
Next
All that shows here is the Integer that I stands for rather than the value of of the cell (P1 should show the value of AE56), i have tried several different ways.

Any ideas?

Regards,
SImon

[moa]

You might have to use an array instead.

[lucas]

Not sure I'm following but shouldn't your variable match what you have set?

ActiveCell.Offset(0, I).Value = P1 & I 'or
ActiveCell.Offset(0, I).Value = P2 & I

[moa]

It looks like he's trying to use a variable by building it's name on the fly; P1 to P50 from "P" and the variable I.

[Simon Lloyd]

Yes Glen thats right, having typed in 50 different "P"'s and ranges i didn't fancy doing that just to put them all in row once collected!, so i thought while setting the Activecell.Offset to (0, I) i would use the I again to increment the "P"'s killing 2 birds with one stone but i cant get it to come together!

Regards,
Simon

[moa]

Yeah, I've managed it in another language but not VBA. An array of all those values would definitely be your best bet.

[Simon Lloyd]

How would i do that then Glen?, do i put each of my earlier variants e.g P1, P2 in an array in the order i want and then something like For Each P & I in Array ActiveCell.Offset(0,I).....even when i typed this it seemed wrong so i know that wont work.....a shove in the right direction maybe?

Regards,
Simon

[lucas]

can you not put the values from the cells into an array in the code? Hard coded in or do the cell values change too?

[moa]

I kind of meant starting from scratch by scrapping the "P & I" idea by putting all your values from the sheet into an array then looping through the array
For I = 1 To 50
ActiveCell.Offset(0, I).Value = P(I)
Next

P being an array.

[Simon Lloyd]

Thanks for posting back both, i have sorted it, Malcom posted a little earlier on today to a Newbie he posted this as help for using Offset for entering values:

Dim Arr, i As Long
Arr = Array(100, 200, 300, 400, 500)
For i = 0 To UBound(Arr)
ActiveCell.Offset(0, 1 + i) = Arr(i)
Next

So i nicked it and all is well!

Thanks Malcom!

Thanks all,
regards,
Simon

[lucas]

Hi Glen,
I'm with you but how can he load an array from this disjointed range without hard coding it?
P1 = Range("AE56").Value, P2 = Range("O29").Value, P3 = Range("Z17").Value etc

[lucas]

ah, hard coded array....I was concerned that the range AE56, etc. values might be changing and you needed to load your array from the changed values.

[moa]

yep. Probably just as much coding work putting it into an array now as doing what he originally had to do with all those separate variables, but think how much prettier his code will be :)

[Simon Lloyd]

I dont know about pretty but took some time!, as a side ca you help with this please Arr1 = Array(Range("D4", "D8", "D12", "D16"))this snippet is from a very long line but the problem is when using this i get an Error Wrong Number Of Arguments!....do i really have to type in Range"D4" etc for each value? i am using the array to check see if any of the cells are blank before assigning Values to them like this

Arr1 = Array(Range("D4", "D8", "D12", "D16"))
For Each cell In Arr1
If cell = "" Then
GoTo E '''this is to error handling
End If
Next
Regards,
Simon

[lucas]

Simon,
It's trying to load the array from the disjointed(not in line with each other)range that's causing the problem I think but I don't know of a way to do this.....

[Simon Lloyd]

Arr1 = Array(Range("D4"), Range("D8"), Range("D12"), Range("D16"))
For Each cell In Arr1
If cell = "" Then
GoTo E
End If
Next
this doesnt give a problem but doesnt recognise the empty cells either!

[mdmackillop]

Sub MyArray()
Dim Arr1, Cel
Arr1 = Array("D4", "D8", "D12", "D16")
For Each Cel In Arr1
If Range(Cel) = "" Then
MsgBox Cel & " is empty"
End If
Next
End Sub

[Simon Lloyd]

Malcom thanks for that, it's ironic that you should fix both of my problems in this post....you even fixed one without knowing it!

Regards,
SImon

[mdmackillop]

That's why it's worth posting alternative solutions, even where a question is marked solved.
BTW, the resize approach should be quicker than the loop, and it just cropped up again here (http://vbaexpress.com/forum/showpost.php?p=91716&postcount=6), with a transpose function thrown in for good measure.

[Bob Phillips]

What am I missing. Why aren't you just using



For I = 1 To 50
ActiveCell.Offset(0, I).Value = Range("P" & I).Value
Next

[Simon Lloyd]

Bob, the "P" wasn't column P it was because earlier i had used the value of a cell to be "P1" and another to be "P2" etc up to 50 in the end i used a posting by malcom for using an array for all of my "P"'s....or have i got the wrong end of the meaning for your post....it looks like you mean column P and I as the value for the rows.

There again what do i know?, i seem to have made a few schoolboy posts lately!

Regards,
Simon