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]

Nope the edit was changing "For c = 1 to 6" to "For c = 1 to 7" to match Ede's information on # of columns involved.

 

[Joe4]

Nope the edit was changing "For c = 1 to 6" to "For c = 1 to 7" to match Ede's information on # of columns involved.

I must be losing it! It is time to go home!

EdE, Greg's code should do what you want.

 

[dtaylor]

Greg,

Sub foofoo()
    Dim c%, r%
    On Error Resume Next
    For c = 1 To 7
        For r = 1 To 13 Step 2
            Cells(r + 1, c).Name = Cells(r, c).Value
        Next r
    Next c
End Sub

What does the % symbol (c%, r%) do in your posted code?

Thank You,

Dan

 

[EdE]

It isnt working. I step through the code, but nothing happens.

I have this code

Sub nametest()
Dim c
Dim r
On Error Resume Next
For c = 1 to 13 step 2
Cells(r, c + 1).Name = Cells (r,c).Value
Next c
End c

None of the cells have names that were in that range. What am I doing wrong?

 

[Datsmart]

EdE

Check again, it worked for me.
Click on Cell A2 and look in the Name Box.
It will show the value of A1.

Datsmart

 

[EdE]

I tried starting new workbook. it still doesnt work for me. Could this have anything to do with excel 2003?
Do I need to define a starting spot? When I step through, and can see the spreadsheet, I dont even see the cursor move. This is driving me crazy.

 

[Datsmart]

The cursor is not going to move. Nothing is being selected. That is why it runs so fast. The Named Ranges are assigned by the VBA code.
Go to Insert-> Name-> Define
You will see all your named ranges.

 

[EdE]

Not there either. I dont know what I am missing.....

 

[Datsmart]

What do you have in cells A1, A3, etc?
Text, numbers, formulae?

 

[EdE]

text

WHen I record a macro, i get this:
ActiveWorkbook.Names.Add Name:="CellName", RefersToR1C1:= _
"=Details!R637C69"

Only if I copy the contents of R637C68 and paste into the name field.