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?

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]



On Mon, 25 Jan 2010 14:43:01 -0800, Lucas B
wrote:

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,

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.

"Chip Pearson" wrote:

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]



On Mon, 25 Jan 2010 14:43:01 -0800, Lucas B
wrote:

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?
.

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

Thanks Chip!

"Lucas B" wrote:

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.

"Chip Pearson" wrote:

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]



On Mon, 25 Jan 2010 14:43:01 -0800, Lucas B
wrote:

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?
.