Offset in VBA

EdE

Board Regular
Joined
Apr 8, 2002
Messages
241
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
You can use LOOPS or the OFFSET command. Loops are usually preferrably because unlike, the OFFSET function, it does not require you to select any cells, which slows down your code.

If you need help, post exactly what you are trying to do, in a bit more detail.
 

EdE

Board Regular
Joined
Apr 8, 2002
Messages
241
I am trying to name lots of cells.
Starting with a1, copy, move to a2, insert name, enter.
Move to a3, copy, move to a4, insert name, enter.

The problem is these things are not lined up. Dont ask.
I just have about 200 of these to do, and doing them by copy, tab, insert name, enter, tab, and then repeat process will take forever.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
Let's say that you simply have seven cells you want to populate, A1 through A7. Via a loop, ait would look something like:
Code:
Dim i as Long
For i = 1 to 7
   Cells(i, "A") = YourValueHere
Next i
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
Joe,

Whacha got against a For Each?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foof()
    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Set</SPAN> r = Union([A1:A5], [c4:d7], [f22])
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> r.Cells
        c.Interior.ColorIndex = c.Row
    <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
Whacha got against a For Each?
Nothing at all! As you know, there are numerous different ways of doing loops, and I just picked one that I use a lot.

I was kind of hoping for more details on the structure of their spreadhseet and what they are trying to accomplish, as that often dicates if certains ways are better than others.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
Actually, after reading this again, we may be looking at both. I was thinking the OP wanted to perform identical actions on discontiguous cells. It looks like he want to perform an identical series of actions on several areas. Maybe a <pre>For Each a in r.areas
for i = 1 to x
next i
next a</pre>
kinda thing???
 

EdE

Board Regular
Joined
Apr 8, 2002
Messages
241
Ok I am not following. This is what I have.
In cells a1, a3, a5, a7, a9, a11, a13 I have text listed.
I want to use these texts as cells names for a2,a4,a6,a8,a10,a12,a14 respectively. I have 7 columns by 6 rows and 7 sections of this to do. I was hoping that I could use a loop that would in essence do the following:
select a1, copy, tab to a2, paste insert name, enter, tab to a3, copy, tab to a4, paste insert name, enter, etc.

Does that make sense?
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
Looks like Joe called it correctly...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foofoo()
    <SPAN style="color:#00007F">Dim</SPAN> c%, r%
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">For</SPAN> c = 1 <SPAN style="color:#00007F">To</SPAN> 7
        <SPAN style="color:#00007F">For</SPAN> r = 1 <SPAN style="color:#00007F">To</SPAN> 13 <SPAN style="color:#00007F">Step</SPAN> 2
            Cells(r + 1, c).Name = Cells(r, c).Value
        <SPAN style="color:#00007F">Next</SPAN> r
    <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
Try this:
Code:
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
 

Forum statistics

Threads
1,077,783
Messages
5,336,303
Members
399,074
Latest member
rlong98

Some videos you may like

This Week's Hot Topics

Top