Loop Macro to add Formula only on rows with data

[Lucas B]

I am writing a macro that will loop to add formulas to all rows that have
data to four columns, some with data and some without. With previous help
this macro adds a formula successfully to each row.
Dim R As Range

Set R = Range("F4")
Do Until R.EntireRow.Cells(1, "A").Value = vbNullString
With R
.FormulaR1C1 = "=IF(LEN(RC1)<LEN(RC5),
CONCATENATE(RC1,RC5),CONCATENATE(RC5,RC1))"
With .Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlColorIndexAutomatic
End With
.Locked = True
End With
Set R = R(2, 1)
Loop

However, I need this loop macro to only add the formula to rows in column G
that has data, typically every other row. I tried changing the Do Until
statement to column G, but then it ends too soon as I need it to loop based
on column A but only add the formula to rows with data in column G. I tried
an In Then statement but that did not work.

Dim T As Range
Set T = Range("I4")
Do Until T.EntireRow.Cells(1, "A").Value = vbNullString
With T
.FormulaR1C1 = "=RC7-RC8"
.NumberFormat = "h:mm;@"
With .Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
.Locked = True
End With
Set T = T(2, 1)
Loop

Any assistance is appreciated.

 

[mcescher]

I am writing a macro that will loop to add formulas to all rows that have
data to four columns, some with data and some without.  With previous help
this macro adds a formula successfully to each row.
Dim R As Range

Set R = Range("F4")
Do Until R.EntireRow.Cells(1, "A").Value = vbNullString
    With R
        .FormulaR1C1 = "=IF(LEN(RC1)<LEN(RC5),
CONCATENATE(RC1,RC5),CONCATENATE(RC5,RC1))"
        With .Interior
            .ColorIndex = 38
            .Pattern = xlSolid
            .PatternColorIndex = xlColorIndexAutomatic
        End With
        .Locked = True
    End With
    Set R = R(2, 1)
Loop

However, I need this loop macro to only add the formula to rows in columnG
that has data, typically every other row.  I tried changing the Do Until
statement to column G, but then it ends too soon as I need it to loop based
on column A but only add the formula to rows with data in column G.  I tried
an In Then statement but that did not work.

Dim T As Range
Set T = Range("I4")
Do Until T.EntireRow.Cells(1, "A").Value = vbNullString
        With T
        .FormulaR1C1 = "=RC7-RC8"
        .NumberFormat = "h:mm;@"
        With .Interior
            .ColorIndex = 38
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
    .Locked = True
    End With
    Set T = T(2, 1)
Loop

Any assistance is appreciated.

You're on the right track with the IF statement.

Set R = Range("F4")
Set G = Range("G4")

Do Until R.EntireRow.Cells(1, "A").Value = vbNullString
If Len(G) > 0 Then
With R
.FormulaR1C1 = "=IF(LEN(RC1)<LEN(RC5),
CONCATENATE(RC1,RC5),CONCATENATE(RC5,RC1))"
With .Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlColorIndexAutomatic
End With
.Locked = True
End With
End If
Set R = R(2, 1)
Set G = G(2, 1)
Loop

 

[Lucas B]

Thanks mcesher,

That works as long as I put it into a new sub. The way I see it I will have
four macros, one to concatenate Columns A & E, one to subtract Column H from
G, one to subtract column L from J and one to subtract column P from O.

Once I have all four written, I'll see if I can combine to one so the user
can just push one button to run the whole macro instead of four.

Thanks again.