VBA to loop through cells in a row and perform an action when value is found

[justme101]

Hello all,

I have a file in the following format where a rows has some headers, let's say Name, Age and Gender. Now every time the code comes across the word "Gender", it fills up a certain formula in the 11 rows below the sub-header "1":

I have the following code, but doesn't work at all:

Sub Macro4()
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
 
ThisWorkbook.Worksheets("CSD Trend").Activate

Dim x As Integer

For Each Row In Range("M12:SJ12")
        
'Update formulas
  If ActiveCell.Value = "Station" Then
  ActiveCell.Offset(2, 0).Select
 
  Range(Selection, Selection.Offset(10, 0)).FormulaR1C1 = "formula here"
        
End If
      Next



Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True


          
End Sub

 

[I_know_nuffin]

Change your code loop to the below

Dim x As Integer
Range("M12").Select

For Each Column In Range("M12:SJ12")


        
'Update formulas
  If ActiveCell.Value = "Station" Then
  ActiveCell.Offset(2, 0).Select
 
  Range(Selection, Selection.Offset(10, 0)).FormulaR1C1 = "formula here"
  ActiveCell.Offset(-2, 0).Select
  ActiveCell.Offset(0, 1).Select
  Else
  ActiveCell.Offset(0, 1).Select
        
End If
      Next

 

[Fluff]

How about

Sub Macro4()
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
 
ThisWorkbook.Worksheets("CSD Trend").Activate

Dim Cl As Range

For Each Cl In Range("M12:SJ12")
        
'Update formulas
  If Cl.Value = "Station" Then
   Cl.Offset(2, 0).Resize(11).FormulaR1C1 = "formula here"
  End If
Next Cl



Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True


          
End Sub