there's got to be a better way...
Cool, Thanks!
"Dave Peterson" wrote:
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
|