VBA Help required

[chappelg]

Greetings,

I'm new to using VBA and need some help. I need to be able to access cell data in a form that allows me to change the cells being used in a loop so that I can count through them.
At present the only way I know how to access cell data is through the Range command:
e.g. Var1 = Range("`sheet`!DA6").Value
In addition I need to be able to have the starting reference of the formula change with the active cell in the worksheet. So ideally what I want is something like:

Var1 = ActiveCell("`sheet`!R[n]C[y]")

Where I can set the values of n and y and change them in a For-Next loop. Unfortunately the above structure doesn't work. Can anyone help? I appologise if my explaination is not very clear.

Regards,
Gerald Chappell

 

[DonkeyOte]

In a For Each Loop I often use a counter such as CROW

say my DATA that I am looping through starts in row 2 then CROW = 2 at the beginning of the loop...

then just before NEXT ... I add one to the CROW value so

CROW = CROW + 1

Next ...

Then I can reference a cell using CROW
e.g
Sheets("sheet1").Range("A" & CROW) = "x"

etc...

 

[Andrew Poulsom]

You can use the OFFSET method with the active cell like this. The syntax is:

Offset(Row,Column)

So:

ActiveCell.Offset(1,0) ' one cell down
ActiveCell.Offset(0,1) ' one cell to the right
ActiveCell.Offset(1,1) ' one cell down and one cell to the right.

Use negatives to go up and left.

 

[Visions_Fugitive]

You can access cell data using code like:

x = Cells(1, 1).Value

This code will change the formula in your desired cell based on the active cell:

activerow = ActiveCell.Row
activecol = ActiveCell.Column
Cells(1, 1).Formula = "=Sheet!R" & activerow & "C" & activecol

 

[chappelg]

Thanks for the speedy replies, I'm able to start working on the problem again now. No doubt I'll be back later with more problems though

Once again, thanks.

Regards,
Gerald