Using variables in a vba formula

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126
Hi I was given this formula on this board by a very kind person yesterday, it works perfectly when intorduced straight into the spreadsheet, however I want this formula to be added to a cell when a macro is run, this is easily achieved but I cannot find a way to substitute the row values in this formula to variables, does anybody know what syntax I need? thank you.


Basically I want to change this:

=IF(B2<>C2,SUMIF($B$2:B2,B2,$B$1:B1),"")

Into a working version of something like this:

For z = 6 To LastCol

Cells(X3, z).Formula = "=IF(FX2<>GX2,SUMIf($F$X2:FX2,FX2,$F$X1:FX1),"")"

Next z

Where X3 is the cell into which the formula goes, X2 and X1 are variables of row number.


Thanks.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
Do you mean something like this?

myFormula= "=IF(B" & var2 & "<>C" & var2 & ",SUMIF($B$" & row2 & ":B" & var2 & ",B" & var2 & ",$B$"& var1 & ":B" & var1 & "),"""""")"
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
Hi,

Something like:
Code:
Dim sFormula As String
Dim Var1 As Long, Var2 As Long
Var1 = 1
Var2 = 2
sFormula = "=IF(B" & Var2 & "<>C" & Var2 & _
           ",SUMIF($B$" & Var2 & ":B" & Var2 & ",B" & _
           Var2 & ",$B$" & Var1 & ":B" & Var1 & ")," & _
           Chr(34) & Chr(34) & ")"
Selection.Formula = sFormula
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126
Thank you both for a quick reply, both look great and should work, I've been a little bit daft and realise that the columns should also be variables, I will try to correct this using your sample syntax, thanks a bunch for your help, cheers both.
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126

ADVERTISEMENT

The new formula compiles, however I'm getting an object-defined error on:

Cells(X3, z).Formula = sFormula

any ideas?

I have also noticed my new formula is inputting the col variable as an integer rather than a char even though it is declared as long, any suggestions here? thanks again for your help.
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
Hi,

I suspect that either X3 or z variables are invalid (e.g. not numeric, zero etc)
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126

ADVERTISEMENT

Hi, They are both declared as integers and both have values assigned (Values are displayed on mouse over), cheers, have the col values set to string, formula looking good, just can't assign it to these cells for some reason?

could it be to do with the loop?
 

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126
For z = 6 To LastCol

'change column values to letters
If z > 26 Then
ColumnLetter = Chr(Int((z - 1) / 26) + 64) & _
Chr(((z - 1) Mod 26) + 65)
Else
ColumnLetter = Chr(z + 64)
End If

z1 = z + 1

If z1 > 26 Then

ColumnLetter2 = Chr(Int((z1 - 1) / 26) + 64) & _
Chr(((z1 - 1) Mod 26) + 65)
Else
ColumnLetter2 = Chr(z1 + 64)
End If

a = ColumnLetter
a1 = ColumnLetter2

sFormula = "=IF(" & a & "" & X2 & "<>" & a1 & "" & X2 & ",SUMIF($" & a & "$" & X2 & ":" & a & "" & X2 & "," & a & "" & X2 & ",$" & a & "$" & X1 & ":" & a & "" & X1 & "),"""""")"

Cells(X3, z).Formula = sFormula

Next z


Also tried Cells(X3, a).Formula = sFormula - No Luck :confused:
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
Hi,

Just tried this, seems to work ok:
Code:
Sub qqq()
Dim sFormula As String
Dim Var1 As Long, Var2 As Long
Var1 = 1
Var2 = 2
sFormula = "=IF(B" & Var2 & "<>C" & Var2 & _
           ",SUMIF($B$" & Var2 & ":B" & Var2 & ",B" & _
           Var2 & ",$B$" & Var1 & ":B" & Var1 & ")," & _
           Chr(34) & Chr(34) & ")"
Cells(2, 5).Formula = sFormula
End Sub

Can you post your code?
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
Hi,

Why are you changing the column number to a letter when the Cells statement happily acceptds a number for the column?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,998
Messages
5,834,808
Members
430,322
Latest member
excelnoobnoob

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