How to integrate a formula within a loop
Category: [Excel] Demo Available
How to integrate a formula within a loop
Requirement:
The user would like to integrate a formula within a loop based on the result of the first condition.
In other words, if the condition is NOT met, a formula is inserted and the loop continues onward.
The code below should serve to illustrate...
The problem has been the format or syntax on how to get the formula in there following the "Else" clause.
Code:
Sub Loop_Snippet()
'This is where the LOOP BEGINS / STARTS - there is HEADER DATA in ROW 1.
For Loop_Thru = 2 To Last_Row_ColA
'THIS IS THE RANGE YOU ARE TESTING !!!
Tested_Column = Range("A" & Loop_Thru)
'Check Tested_Column and FOLLOW THROUGH accordingly.
If Tested_Column Like "t*" Or Tested_Column Like "T*" Then
Result = "There is a ""T"" in this column!"
Else
Result = "THE FORMULA GOES HERE..."
End If
'Display the Result column B
Range("B" & Loop_Thru) = Result
'CONTINUE the LOOP.
Next
'EXIT...
End Sub
Solution:
You can do something like this. You don't say what your formula is so can't give precise advice.
Sub Loop_Snippet()
Dim Loop_Thru As Long, Last_Row_ColA As Long, Tested_Column, Result As String
For Loop_Thru = 2 To Last_Row_ColA
Tested_Column = Range("A" & Loop_Thru)
If LCase(Tested_Column) Like "t*" Then
Result = "There is a ""T"" in this column!"
Else
Result = "=A1+A2"
End If
Range("B" & Loop_Thru) = Result
Next
End Sub
OR
Well, that's probably better practice so you could do it like this (also shorter as don't really need all those variables):
Sub Loop_Snippet()
Dim Loop_Thru As Long, Last_Row_ColA As Long, Tested_Column, Result As String
For Loop_Thru = 2 To Last_Row_ColA
If LCase(Range("A" & Loop_Thru)) Like "t*" Then
Range("B" & Loop_Thru).Value = "There is a ""T"" in this column!"
Else
Range("B" & Loop_Thru).Formula = "=A1+A2"
End If
Next
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by StephenR.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to loop through different ranges |
| How to cut, copy and insert on a loop |
| How to turn one operation into a loop in VBA |
| How to use looping to delete cells of similar value |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.