Loop Macro to add Formula only on rows with data
 

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.

Loop Macro to add Formula only on rows with data
 

On Jan 27, 1:29*pm, Lucas B wrote:
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.

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

Loop Macro to add Formula only on rows with data
 

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.

"mcescher" wrote:

On Jan 27, 1:29 pm, Lucas B wrote:
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.

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
.