Using the below code i m trying to loop column and rows. When cells in green import the range in the formula string. At row 7 i want to import a formula which sum up the green cells but i want the formula to be shown. the loop works fine, the formula create fine but is imported as string instead of a formula.
Any help will appreciated.
Sub test()
Dim Row As Long
Dim Col As Long
Dim strFormula As String
With ThisWorkbook.Worksheets("Sheet1")
'Loop columns
For Col = 1 To 3
'Clear strFormula variable
strFormula = ""
'Loop rows
For Row = 1 To 5
'Check if cell is green
If .Cells(Row, Col).Interior.Color = 9359529 Then
'Create formula
If strFormula = "" Then
strFormula = """=SUM(.cells(" & Row & "," & Col & ")"
Else
strFormula = strFormula & ",.cells(" & Row & "," & Col & ")"
End If
Else
End If
Next Row
'Finalize formula
If strFormula <> "" Then
strFormula = strFormula & ")"""
'Import formula
.Cells(7, Col).Formula = strFormula
Else
End If
Next Col
End With
End Sub
Please do not name your variable as Row
. Row
is a property and this will interfere with that.
I would do this slightly different. I would identify the range which has the relevant color and construct a range object which will have only those cells. In the end I will use it with an apostrophe '
to enter the formula as text.
Is this what you are trying?
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim rw As Long
Dim cl As Long
Dim rngGreen As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
With ws
For cl = 1 To 3
Set rngGreen = Nothing
For rw = 1 To 5
If .Cells(rw, cl).Interior.Color = 9359529 Then
If rngGreen Is Nothing Then
Set rngGreen = .Cells(rw, cl)
Else
Set rngGreen = Union(.Cells(rw, cl), rngGreen)
End If
End If
Next rw
If Not rngGreen Is Nothing Then .Cells(7, cl).Formula = _
"'=Sum(" & rngGreen.Address & ")"
Next cl
End With
End Sub
Note: To get the sum, you can use "=Sum(" & rngGreen.Address & ")"
without the apostrophe.