VBA Help - For Each Statement to Build a Formula based on Cell Values

[Johnny Thunder]

Hello All,

I am working on a dropdown list that will create a formula thru vba to do a validation based on a dropdown list value true or false, true being the range needs to be added to my sum formula.

Here is the dropdown list below, just need help with writing code to do this kind of thing. Any help is appreciated.

Excel Workbook
	E	F	G
1	Periods to Validate	True or False	Range
2	January	FALSE	D154
3	February	FALSE	E154
4	March	FALSE	F154
5	April	FALSE	G154
6	May	FALSE	H154
7	June	FALSE	I154
8	July	FALSE	J154
9	August	FALSE	K154
10	September	FALSE	L154
11	October	TRUE	M154
12	November	TRUE	N154
13	December	TRUE	O154
ValidateTBT

So what the code will need to do is

Loop thru each Month Jan - Dec and if the offset value =True then add the range value to a sum formula.

So with the example above my formula should look something like =Sum(M154,N154,O154) based on the months that have the value True.

this formula needs to be dynamic enough to know how many cell ranges to add based on what a user selects as true.

 

[Trebor76]

Hi Johnny Thunder,

Try this while on the sheet in question:

Option Explicit
Sub Macro1()

    Dim rngMyCell  As Range
    Dim lngLastRow As Long
    Dim strFormulaRange As String
    
    Application.ScreenUpdating = False
    
    lngLastRow = Cells(Rows.Count, "E").End(xlUp).Row
    
    For Each rngMyCell In Range("E2:E" & lngLastRow)
        If CStr(rngMyCell.Offset(0, 1)) = "True" Then
            If strFormulaRange = "" Then
                strFormulaRange = rngMyCell.Offset(0, 2)
            Else
                strFormulaRange = strFormulaRange & "," & rngMyCell.Offset(0, 2)
            End If
        End If
    Next rngMyCell
    
    'Puts the formula in cell H2. Change to suit.
    Range("H2").Formula = "=SUM(" & strFormulaRange & ")"
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Osvaldo Palmeiro]

Try:

Sub ElaborateFormula()
 Dim f As String, m As Range
  For Each m In Range("F2:F13")
   If m.Value = True Then f = f & m.Offset(, 1).Value & ","
  Next m
 f = Left(f, Len(f) - 1)
 [K2] = "=SUM(" & f & ")"
End Sub

 

[Johnny Thunder]

thank you @Trebor76 for the help on this!

I did however use @Osvaldo Palmeiro version since it was a much more condensed variation of the code.

Osvaldo, I wanted to ask, with your line "SUM(" & f & ")" is there a way to add a string to the results?

Current Results: "SUM(M154,N154,O154)

Updated Results: "Sum('" & NameofSheet & "'!" & M154, '" & NameofSheet & "'!" & N154, '" & NameofSheet & "'!" & O154)

NameofSheet = Sheet Name defined by a variable.

 

[Osvaldo Palmeiro]

Hi, Johnny.
Is there a criteria to associate the NameofSheet to the cell address which is in column G ?
For example, assuming that May is True, then what NameofSheet would be associated to H154? Another case, if November is True, what NameofSheet would be associate to cell N154?

Maybe an easy way to associate the NameofSheet to the cell's address would be to place the names of the respective sheets right side to the cell's address, that means in column H. Is this feasible?

 

[Norie]

Do you need code for this?

=MMULT(Sheet2!D154:O154, --(F2:F13))

 

[Johnny Thunder]

Osvaldo, similar to the loop you provided I have another drop down list that looks into a folder to validate if a specific file has been saved within the folder.

On my dropdown list, if the cell adjacent to the file name is blank it means that the file has not yet been saved, if the cell has a time stamp, it means the file has been saved down to my folder and to run your piece of code to get a sum of the months in questions.

Hopefully that explains what I am doing. So, the NameofSheet variable continues to change as the loop runs.

Hopefully there is a way to parse in the NameofSheet into what you have provided.

Hi, Johnny.
Is there a criteria to associate the NameofSheet to the cell address which is in column G ?
For example, assuming that May is True, then what NameofSheet would be associated to H154? Another case, if November is True, what NameofSheet would be associate to cell N154?

Maybe an easy way to associate the NameofSheet to the cell's address would be to place the names of the respective sheets right side to the cell's address, that means in column H. Is this feasible?

 

[Norie]

If you want to add a sheet name try this.

Dim cl As Range
Dim arrRefs()
Dim cnt As Long
Dim strSheetName As String

    ReDim arrRefs(1 To 12)

    strSheetName = "Your Sheet Name"

    For Each cl In Range("F2:F13")
        If cl.Value = True Then
            cnt = cnt + 1
            arrRefs(cnt) = "'" & strSheetName & "'!" & cl.Offset(, 1).Value
        End If
    Next cl

    ReDim Preserve arrRefs(1 To cnt)

    Range("K2").Formula = "=SUM(" & Join(arrRefs, ",") & ")"

 

[Johnny Thunder]

That did it @Norie

My code is working as needed!


Thanks to all for the help on this!