MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Variables in a range


Posted by Duane Kennerson on November 14, 2001 6:29 PM

Here is the code I am using:
Range("ab" & y, "ab" & m) = Range("q3")

I want this to be a range where "ab" & y = ab3
"ab" & m = ab5
With the current code, the value of q3 is put in cell
ab3 and cell ab5 but not ab4. I tried using a colon (:)
instead of a comma but I get a list separator error.

Any suggestions???

Thanks in advance


Posted by George Eastham on November 15, 2001 4:08 AM


EITHER :-

Range(Range("ab" & y), Range("ab" & m)) = Range("q3")

OR

Range("ab" & y & ":" & "ab" & m) = Range("q3")


Posted by Duane Kennerson on November 15, 2001 1:40 PM

Good suggestions however they yeild the same results as my origional formula. I need excel to put the contents of q3 in all three cells, not just ab3 and ab5. I want excel to treat this as a range instead of just two cell references.

Any other suggestions???

Thanks for your help so far.


Posted by Duane Kennerson on November 15, 2001 1:40 PM

Good suggestions however they yeild the same results as my origional formula. I need excel to put the contents of q3 in all three cells, not just ab3 and ab5. I want excel to treat this as a range instead of just two cell references.

Any other suggestions???

Thanks for your help so far.


Posted by George Eastham on November 15, 2001 3:26 PM

I was afraid of that! Thank you for your help!


Posted by George Eastham on November 15, 2001 3:29 PM


When I try it, all three methods (yours and my two) do what you want - the value in Q3 is put in each of the cells AB3,AB4, and AB5.

Posted by Duane Kennerson on November 15, 2001 5:57 PM

How strange, mine will only put it in the two cells ab3 and ab5, I tried both methods. I should tell you, although I'm not sure if it makes a difference, "y" and "m" are references to a number in another cell that will constantly change. In my example, the range spands over a 3 cell range but depending on the values in y an m, the range could be much larger. Uggggg, it will only put the value from q3 into ab3 and ab5 and leave the middle cell(s) blank. Perhaps there is a problem in my code, here is the whole procedure, its actually two procedures but here they are:

Sub movetolist()
'move to collection area of filter sheet

Dim x As Integer
x = Range("aa2") + 2
Range("AA2").Select
Application.CutCopyMode = False
Selection.Copy
Range("AA3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("s3:x1000").Select
Selection.Copy
Range("ab" & x) = Range("q3")
Range("ac" & x).Select
ActiveSheet.Paste
dates

End Sub

Sub dates()
'put the date in blank cells

Dim y As Integer
Dim m As Integer
y = Range("aa5") - 1
m = Range("aa2") + 1
Range("ab" & y, "ab" & m) = Range("q3")

End Sub

Posted by George Eastham on November 15, 2001 7:14 PM


I've just run your code and it works for me.
Can't see why it doesn't work for you.
Do you have any rows hidden or any any filters on?

Posted by Duane Kennerson on November 15, 2001 7:36 PM

I went back and did a little more testing. It is working like you said when I have 3 cells in my range. It is not working when I have just one or any other number that 3 cells in my range. I need a little tweaking I guess.

Thank you for your help, I thought I was losing my mind. I guess I have to figure out how to make it work for multiple sized ranges.

Any thoughts on where my code may be wrong for that aspect??? The size of the range can vary, thats my problem I guess.

Posted by George Eastham on November 15, 2001 8:16 PM


Your code works for me whatever the size of the range.

Posted by Duane Kennerson on November 16, 2001 12:45 PM


of q3 is put in cell


Mine is working for every size range except for if it is a one cell range. If the range will only contain one cell, it puts the contents of q3 in the appropriate cell but then it also puts the contents of q3 in the next row below the first cell. Must be something in my variables.