[Excel] Macro Mixing Relative and Fixed References

[tcss]

Hi again everyone,

Sorry to have so many questions!

I'm trying to create my first macro but I need it to have both relative and fixed references. Basically what I need is:

1. Copy value from a location that moves down a list one item at a time.
2. Paste this value into the same cell every time.
3. Copy a second value from a different location that never changes.
4. Paste this second value into a location that moves down a list one item at a time.

I made a diagram that sort of shows what I mean...




So to accomplish this I tried creating two macros, one that was with fixed references and one with relative references, and splicing them together to get the following:

Sub Copy1()
'
' Copy1 Macro
'

'
ActiveCell.Offset(-4, -1).Range("A1").Select
Selection.Copy
Range("C2").Select
ActiveSheet.Paste
Range("H20:I20").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(-18, 3).Range("A1:B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub


But not surprisingly I got an error: "Run-time error '1004': Application-defined or object-defined error." When I click debug, it highlights the line ActiveCell.Offset(-4, -1).Range("A1").Select in yellow.

Can anyone help? I would appreciate it very much.

Thank you!

[tcss]

Ok, so I figured out that the "Use Relative References" button is a toggle button, and that the relativity is in relation to the cell you have selected when you start recording. So I recorded a new macro based on this knowledge and got the following:

ActiveCell.Offset(0, -1).Range("A1").Select
Selection.Copy
Range("C2").Select
ActiveSheet.Paste
Range("H1:I1").Select
Selection.Copy
ActiveCell.Offset(1, 3).Range("A1:B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


The first step works great, it copies progressively down the line and pastes in the same cell each time. However the steps 3 and 4 aren't working. It kind of goes bonkers at that part. I did move my step 3 values to H1 and I1 instead of H20 and I20.

The problem seems to be that step 4 is relative to step 3...but I'm not sure how to make it relative to the very first cell when I started. Maybe?

[westconn1]

It kind of goes bonkers at that part.

a paste target must be a single cell

the code to do this is a simple loop, but i am not sure of where you get all your values

do you want all the values in K & L to be = to H & I 20?

for each c in range("b2:b99")
  c.offset(, 1) = c
  c.offset(, 9) = range("h20")
  c.offset(, 10) = range("i20")
next

change b99 to suit, i doubt this is totally right, but should give some better idea, try it on some copy sheet first

[tcss]

Thanks for your response westconn1,

Basically what I want is this:
1. Update the web query I have in the middle with the data on the left.
2. Copy the resulting median and max values in H & I 20 (now H & I 1) over to the right as a value instead of a formula.
3. Repeat, moving down one cell each time.

After much trial and error, I've narrowed the problem down and I think I know what it is. Basically, the last step, where I copy from H & I 20 and paste into a new position (moving down one cell each time), is relative to the place I copied it from, which means it never moves because H & I 20 never move, so it can't move down one cell every time I run the macro.

I want it to be relative to the very first cell I'm positioned on when I start recording the macro, because then I only have to move the mouse once and run. I can just go down the line. However, I've noticed that the web query doesn't update fast enough for this to even work...so I'm not sure the concept would work even if I was able to get the macro to cooperate.

Thoughts?

[westconn1]

did you try the code i posted at all, to see if it did something close to what you want?

I've noticed that the web query doesn't update fast enough

i must have missed the part about web query in the original post