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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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???
 
Upvote 0
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?
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top