VBA Variables in Formulas

J

Jameo

Active Member
Joined
Apr 14, 2011
Messages
270
Hi all,

Maybe you can help with the following?

If I had a simple formula referencing another sheet, and I wanted VBA to fill it across I could use something like this (with different row values in each sheet).


Code: 
Public sub Test()
dim TPA as Integer
dim TPB as Integer
 
TPA = 1
TPB = 2
 
Do
 
Sheets("sheet1").cells (1, TPA).Formula = "=sheet2!B " & TPB & ""
 
TPA = TPA + 1
TPB = TPB + 4
Loop Until TPA = 50
 
End sub

My question is how would I go about referencing a change to the column rather than the row?

i.e. I need a way to add a set number of characters to the formula. so rather than referencing B2, then B6, then B10 as in the example above, I instead need to reference B2, F2, J2, N2.

Thanks in advance for any help.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Look at the FormulaR1C1 property, which you can then set up with formulas in the R1C1 reference style.
 
Upvote 0
Code: 
Sheets("Sheet1").Cells (1, TPA).FormulaR1C1 = "=Sheet2!R2C" & TPB
 
Upvote 0
Use the FormulaR1C1 and and R1C1 style formula. Example:

Code: 
Sheets("sheet1").cells (1, TPA).FormulaR1C1 = "=Sheet2!RC" & TPB
 
Upvote 0
Try using Cells(row, column).Address:

Code: 
Public Sub Test()
Dim TPA As Integer
Dim TPB As Integer
 
TPA = 1
TPB = 2
 
Do
 
Sheets("sheet1").Cells(1, TPA).Formula = "=sheet2!" & Cells(TPB, TPA).Address(False, False)
 
TPA = TPA + 1
TPB = TPB + 4
Loop Until TPA = 50
 
End Sub

The (False, False) after .Address means it refer to the relative cell reference (A1) rather than the absolute cell reference ($A$1).
 
Upvote 0
Cheers for the replies all.

The R1C1 formula structure does not work for me, due to the way my data is arranged.

MrKowZ - I tried the .address with (0,0) but that didn't seem to work, I haven't tried it with false, false.

Saying all this I now have it working. I had to write the following function:

Code: 
Public Function GetRange(intColumn As Integer, intRow As Integer) As String
    GetRange = Replace(Replace(Cells(intRow, intColumn).Address, "$", ""), ":", "")
End Function

with
Code: 
Sheets("CriticalOTPressures").Cells(3, TpAP).Formula = "=AllOTCalc!" & GetRange(FtV, 2) & ""

Seems to work well.

Thanks again all
 
Upvote 0
Next question ;)

Any idea how I can now autofill the formulas down (bearing in mind i will again need the column as the variable) I will try your method MrKowz, will post back if it works.

Normally i would use something like:

cells(3, TpAp).AutoFill Destination:=Range("C2:C" & LRow), Type:=xlFillDefault

My function will work fine for the first C2, but not sure how it will work for the :C
 
Last edited:
Upvote 0
Yeah,

Cells(3, TpAP).AutoFill Destination:=Range(GetRange(FtV, 2) & LRow), Type:=xlFillDefault

doesn't work unfortunately. Any ideas?

Cheers
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Direct Referencing Non Contiguous Columns
Replies
2
Views
96
Fuji
F
S
  • Question Question
What's the best/most efficient way to set Variables for Columns
Replies
9
Views
239
dmt32
D
Keebler
  • Solved
excel function from another cell
Replies
6
Views
149
smozgur
smozgur
A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
110
Ali M
A
V
  • Solved
Need VBA to split data into multiple sheets paste formulas, formatting AND data validation
Replies
2
Views
86
vanessavalentino83
V

Forum statistics

Threads
1,224,620
Messages
6,179,927
Members
452,949
Latest member
beartooth91

Share this page

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