Loop Macros

[Lucas B]

I am trying to write a macro that will do the following as long as there is
data in column A.

Range("F4").Select
ActiveCell.FormulaR1C1 _
= "=IF(LEN(R4C1)<LEN(R4C5),
CONCATENATE(R4C1,R4C5),CONCATENATE(R1C5,R1C1))"
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
Selection.Locked = True
End With

How do I write a Loop that will keep doing this on each successive row until
the cell in column A is blank?

 

[Chip Pearson]

Try

Dim R As Range
Set R = Range("F4")
Do Until R.EntireRow.Cells(1, "A").Value = vbNullString
With R
.FormulaR1C1 = "your formula here"
With .Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlColorIndexAutomatic
End With
.Locked = True
End With
Set R = R(2, 1)
Loop


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Lucas B]

Chip,

Thanks, that worked to fill out the column, but it's placing the exact
formula in each cell, I need it to concatenate the info in each row. Is
there a way to have the formula update to the new row? This is the formula
that I input but I need the row to change to the next row as it fills out
down the range.

=IF(LEN(R4C1)<LEN(R4C5), CONCATENATE(R4C1,R4C5),CONCATENATE(R1C5,R1C1))

I appreciate your help on the loop, that's one more step in the right
direction for me.

Try

Dim R As Range
Set R = Range("F4")
Do Until R.EntireRow.Cells(1, "A").Value = vbNullString
With R
.FormulaR1C1 = "your formula here"
With .Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlColorIndexAutomatic
End With
.Locked = True
End With
Set R = R(2, 1)
Loop


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

I am trying to write a macro that will do the following as long as there is
data in column A.

Range("F4").Select
ActiveCell.FormulaR1C1 _
= "=IF(LEN(R4C1)<LEN(R4C5),
CONCATENATE(R4C1,R4C5),CONCATENATE(R1C5,R1C1))"
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
Selection.Locked = True
End With

How do I write a Loop that will keep doing this on each successive row until
the cell in column A is blank?

.

 

[Lucas B]

Got it solved, eliminate the row # in the formula and it works perfectly.

Thanks Chip!

Chip,

Thanks, that worked to fill out the column, but it's placing the exact
formula in each cell, I need it to concatenate the info in each row. Is
there a way to have the formula update to the new row? This is the formula
that I input but I need the row to change to the next row as it fills out
down the range.

=IF(LEN(R4C1)<LEN(R4C5), CONCATENATE(R4C1,R4C5),CONCATENATE(R1C5,R1C1))

I appreciate your help on the loop, that's one more step in the right
direction for me.

Try

Dim R As Range
Set R = Range("F4")
Do Until R.EntireRow.Cells(1, "A").Value = vbNullString
With R
.FormulaR1C1 = "your formula here"
With .Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlColorIndexAutomatic
End With
.Locked = True
End With
Set R = R(2, 1)
Loop


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

I am trying to write a macro that will do the following as long as there is
data in column A.

Range("F4").Select
ActiveCell.FormulaR1C1 _
= "=IF(LEN(R4C1)<LEN(R4C5),
CONCATENATE(R4C1,R4C5),CONCATENATE(R1C5,R1C1))"
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
Selection.Locked = True
End With

How do I write a Loop that will keep doing this on each successive row until
the cell in column A is blank?

.