VBA Help required

chappelg

New Member
Joined
Nov 5, 2002
Messages
33
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Apr 15, 2002
Messages
18
You can access cell data using code like:
Code:
x = Cells(1, 1).Value

This code will change the formula in your desired cell based on the active cell:
Code:
activerow = ActiveCell.Row
activecol = ActiveCell.Column
Cells(1, 1).Formula = "=Sheet!R" & activerow & "C" & activecol
 

chappelg

New Member
Joined
Nov 5, 2002
Messages
33
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
 

Forum statistics

Threads
1,144,312
Messages
5,723,654
Members
422,508
Latest member
Lordkit1

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
Top