Using variables in a vba formula

[mantis_sg]

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.

 

[mantis_sg]

here you go ..

Public Sub SumTotalMonth()

Dim x As Integer
Dim X1 As Integer
Dim X2 As Integer
Dim X3 As Integer
Dim X4 As Integer
Dim Y1 As Long
Dim LastCol As Integer
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range

Dim sFormula As String
Dim z As Integer
Dim z1 As Integer
Dim a As String
Dim a1 As String


Sheets("Production hours required").Select

'find last column
Set rng = Range("F3").SpecialCells(xlCellTypeLastCell)
LastCol = rng.Column

' Find the last row of data
FinalRow = Range("E100").End(xlUp).Row

For x = 4 To FinalRow
Next x

X1 = FinalRow + 2
X2 = FinalRow + 3
X3 = FinalRow + 4
X4 = FinalRow + 5

Y1 = LastCol

'inserts the month value into the row between totals
For i = 6 To LastCol

Cells(X2, i).FormulaR1C1 = "=MONTH(R[-54]C)"
Cells(X2, i).Font.ColorIndex = 2
Next i

'Convert month formula tp values

Set rng1 = Range(Cells(X2, "F"), Cells(X2, Y1))
Set rng2 = Range(Cells(X4, "F"), Cells(X4, Y1))

rng1.Copy
rng2.PasteSpecial

rng2.Copy
rng1.PasteSpecial Paste:=xlPasteValues

rng2.Clear

'Insert Formula to calculate month end totals

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, a).Select

Selection.Formula = sFormula

Next z

End Sub

 

[al_b_cnu]

Hi,

Wasnt able to test this, but here's your code slightly cleaned up, calling a UDF 'GetColLetter '

Public Sub SumTotalMonth()

Dim x As Integer
Dim X1 As Integer
Dim X2 As Integer
Dim X3 As Integer
Dim X4 As Integer
Dim Y1 As Long
Dim LastCol As Integer
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range

Dim sFormula As String
Dim z As Integer
Dim z1 As Integer
Dim a As String
Dim a1 As String


Sheets("Production hours required").Select

'find last column
Set rng = Range("F3").SpecialCells(xlCellTypeLastCell)
LastCol = rng.Column

' Find the last row of data
FinalRow = Range("E100").End(xlUp).Row

For x = 4 To FinalRow
Next x

X1 = FinalRow + 2
X2 = FinalRow + 3
X3 = FinalRow + 4
X4 = FinalRow + 5

Y1 = LastCol

'inserts the month value into the row between totals
For i = 6 To LastCol

Cells(X2, i).FormulaR1C1 = "=MONTH(R[-54]C)"
Cells(X2, i).Font.ColorIndex = 2
Next i

'Convert month formula tp values

Set rng1 = Range(Cells(X2, "F"), Cells(X2, Y1))
Set rng2 = Range(Cells(X4, "F"), Cells(X4, Y1))

rng1.Copy
rng2.PasteSpecial

rng2.Copy
rng1.PasteSpecial Paste:=xlPasteValues

rng2.Clear

'Insert Formula to calculate month end totals

For z = 6 To LastCol

'change column values to letters
ColumnLetter = GetColLetter(z)

ColumnLetter2 = GetColLetter(z + 1)

a = ColumnLetter
a1 = ColumnLetter2

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

Cells(X3, a).Formula = sFormula

Next z

End Sub
Function GetColLetter(ByVal Col As Integer, _
                      Optional ToCol As Integer = 0) As String
Dim sCol As String, sCol1 As String

sCol = Cells(1, Col).Address(True, False)
sCol = Left$(sCol, InStr(sCol, "$") - 1)
If ToCol > 0 Then
    sCol1 = Cells(1, ToCol).Address(True, False)
    sCol = sCol & ":" & Left$(sCol1, InStr(sCol1, "$") - 1)
End If
GetColLetter = sCol
End Function