Excel VBA: Using .Cells() to dynamically reference cell in cell formula

0
votes

I want to create a cell formula which references cells like B1, e.g. ActiveSheet.Cells(1,x).Formula = "=B1*" & x. However, I want to use the .Cells function rather than a Range reference to reference cells.

Given:

Dim x As Integer 
Dim y As Integer 
x = 5
y = 10

I want this: 

ActiveSheet.Cells(1,x).Formula = "=" & ActiveSheet.Cells(y,1) & "*" & x

To provide the same output as:

ActiveSheet.Cells(1,x).Formula = "=B" & y & "*" & x

Ideally, I want to be able to dynamically change both the row and column of the cell I am referencing in the formula. However, I still want the cell to show up as a cell in the formula and not simply the value of the referenced cell.

1
excelvbadynamicrangeformula
What's the problem you're coming across? – StoriKnow
You can get the address of a cell rather than its formula by using .Address - e.g., ActiveSheet.Cells(y,1).Address(False, False). – Chel
Okay, I see how .Address works in regards to avoiding getting a formula, but if I use .Address, will this: ActiveSheet.Cells(1,x).Formula = "=" & ActiveSheet.Cells(y,1).Address(False, False) give me the same as this: ActiveSheet.Cells(1,x).Formula = "=B" & y & "*" & x, provided x = 5 and y = 10? – CSAW
^After testing it out, it seems to work! – CSAW
@rdhs How do you set ActiveSheet.Cells(y,1).Address(False, False) equal to a variable. What object type is it? A range? (I tried to set it to a range and it did not work) – CSAW

1 Answers

0
votes

If I see that correctly then your problem is that you want map the cell numbers to their cell names; similar to this:

ActiveSheet.Cells(1,x).Formula = "=" & Chr(Ord("A") + 1) & y & "*" & x

Keep in mind that this will only work for the first 26 columns, after that you'll need to find a better solution.