Offset in VBA

[EdE]

How do I skip cells in VBA?
Say, I am starting at A1, perform task,then want to move to a2, perform task, then move to a3. I want to do this without using absolutes. I need to do this in various areas 7 times before I move to the next area. Any ideas?
Thanks!
Ed

 

[Greg Truby]

OK - you can see that Joe and I are confused. Are we copying values from one cell to the cell below? Or are we using the value of the cell in the top bunk as the name of the cell in the bottom bunk?

 

[EdE]

Try this:

Sub MyCopy()

    Dim i As Long, j As Long
'   Loop through columns
    For j = 1 To 7
'   Loop through rows
        For i = 1 To 13 Step 2
            Cells(i + 1, j) = Cells(i, j)
        Next i
    Next j
    
End Sub

How do I get the copy, paste insert name in this loop? I am really confused on this one? I normally dont have this much difficulty grasping something.....

 

[Greg Truby]

Did mine not do what you want?

 

[Joe4]

How do I get the copy, paste insert name in this loop? I am really confused on this one? I normally dont have this much difficulty grasping something.....

You don't need it. Try it out.

You don't need to copy & paste, you can set the values directly equal to another cell. For example, what it essentially doing is saying set the value of A2 equal to whatever is in cell A1, etc.

 

[EdE]

OK - you can see that Joe and I are confused. Are we copying values from one cell to the cell below? Or are we using the value of the cell in the top bunk as the name of the cell in the bottom bunk?

Close, I want to use the text in A1 and use it to name the cell A2, use the text in cell a3 and use it to name A4. Therefore cells a2,a4,a6,a8,a10,a12,a14 will be named and those names come from a1,a3,a5,a7,a9,a11,a13. Disregard the row thing. I dont need to set the value of the cell I need to NAME it. I can just restart the macro for each new row. Do i have everyone totally confused now?

 

[Greg Truby]

I want to use the text in A1 and use it to name the cell A2, use the text in cell a3 and use it to name A4. Therefore cells a2,a4,a6,a8,a10,a12,a14 will be named and those names come from a1,a3,a5,a7,a9,a11,a13.

That's supposed to be what mine does. Did your results differ?

 

[Joe4]

I want to use the text in A1 and use it to name the cell A2, use the text in cell a3 and use it to name A4

Do you mean name as in "naming ranges" for reference purposes, and that you are not actually trying to copy the cells values over to the cell below?

Did mine not do what you want?

Greg, our code was essentially identical, so I don't think we need to worry about that (if yours doesn't do what he wants, neither does mine). I think we just haven't figured out exactly what they are looking for yet.

 

[Greg Truby]

Joe,

Mine: Cells(r + 1, c).Name = Cells(r, c).Value

Yours: Cells(i + 1, j) = Cells(i, j)

...subtle but rather key difference there...

 

[Joe4]

Mine: Cells(r + 1, c).Name = Cells(r, c).Value

Yours: Cells(i + 1, j) = Cells(i, j)

Ahh, I must've have missed that the first time around (or was that the "Edit"?)

 

[EdE]

I want to use the text in A1 and use it to name the cell A2, use the text in cell a3 and use it to name A4

Do you mean name as in "naming ranges" for reference purposes, and that you are not actually trying to copy the cells values over to the cell below?

Correct. I want to use the value of a2 as the name reference for A1, as in a cell reference.