As long as there's variations in each the formulas, I don't see anything wrong
with your technique:

But I'd drop the i stuff and add a variable for the lastrow. And I'd convert to
values after all the work.

Option Explicit
Sub testme()

Dim LastRow As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("AJ2:AJ" & LastRow)
.Formula = "=IF(F2=0,0,B2&F2)"
End With

With .Range("AK2:AK" & LastRow)
.Formula = "=IF(I2=0,0,B2&I2)"
End With

With .Range("AL2:AL" & LastRow)
.Formula = "=IF(L2=0,0,B2&L2)"
End With

With .Range("AM2:AM" & LastRow)
.Formula = "=IF(O2=0,0,B2&O2)"
End With

With .Range("AN2:AN" & LastRow)
.Formula = "=IF(R2=0,0,B2&R2)"
End With

With .Range("AO2:AO" & LastRow)
.Formula = "=IF(V2=0,0,B2&V2)"
End With

With .Range("AP2:AP" & LastRow)
.Formula = "=IF(X2=0,0,B2&X2)"
End With

With .Range("AQ2:AQ" & LastRow)
.Formula = "=IF(AA2=0,0,B2&AA2)"
End With

With .Range("AR2:AR" & LastRow)
.Formula = "=IF(AD2=0,0,B2&AD2)"
End With

With .Range("AS2:AS" & LastRow)
.Formula = "=IF(AG2=0,0,B2&AG2)"
End With

With .Range("aj2:As" & LastRow)
.Value = .Value
End With

End With

End Sub



Stephen wrote:

Set i = Range("AJ2:AJ" & Range("A65536").End(xlUp).Row)
With i
.Formula = "=IF(F2=0,0,B2&F2)"
.Formula = .Value
End With
Set i = Range("AK2:AK" & Range("A65536").End(xlUp).Row)
With i
.Formula = "=IF(I2=0,0,B2&I2)"
.Formula = .Value
End With
Set i = Range("AL2:AL" & Range("A65536").End(xlUp).Row)
With i
.Formula = "=IF(L2=0,0,B2&L2)"
.Formula = .Value
End With
Set i = Range("AM2:AM" & Range("A65536").End(xlUp).Row)
With i
.Formula = "=IF(O2=0,0,B2&O2)"
.Formula = .Value
End With
Set i = Range("AN2:AN" & Range("A65536").End(xlUp).Row)
With i
.Formula = "=IF(R2=0,0,B2&R2)"
.Formula = .Value
End With
Set i = Range("AO2:AO" & Range("A65536").End(xlUp).Row)
With i
.Formula = "=IF(V2=0,0,B2&V2)"
.Formula = .Value
End With
Set i = Range("AP2:AP" & Range("A65536").End(xlUp).Row)
With i
.Formula = "=IF(X2=0,0,B2&X2)"
.Formula = .Value
End With
Set i = Range("AQ2:AQ" & Range("A65536").End(xlUp).Row)
With i
.Formula = "=IF(AA2=0,0,B2&AA2)"
.Formula = .Value
End With
Set i = Range("AR2:AR" & Range("A65536").End(xlUp).Row)
With i
.Formula = "=IF(AD2=0,0,B2&AD2)"
.Formula = .Value
End With
Set i = Range("AS2:AS" & Range("A65536").End(xlUp).Row)
With i
.Formula = "=IF(AG2=0,0,B2&AG2)"
.Formula = .Value
End With

gives me what I want but it just seems so manual...

Anyone know a better way?

--

Dave Peterson