there's got to be a better way...

[Guest]

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]

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

 

[Guest]

Cool, Thanks!

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

 

[Rick Rothstein \(MVP - VB\)]

Stephen... and here is why Dave is suggesting that you leave your code
structure as is (the following is Dave's modification of your code condensed
into a For-Next loop)...

Sub TestMe()
Dim X As Long
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 0 To 9
With .Range("A" & Chr$(74 + X) & "2:A" & Chr$(74 + X) & LastRow)
.Formula = "=IF(" & String$(Abs(X > 6), "A") & _
Chr$(65 + ((5 + 3 * X) Mod 26)) & _
"2=0,0, B2&" & String$(Abs(X > 6), "A") & _
Chr$(65 + ((5 + 3 * X) Mod 26)) & "2)"
End With
Next
End With
End Sub

Shorter than yours? Yes. Easy to figure out what is going on in the code
(especially if you come back in 6 months to maintain, or make changes to,
it? Not by a long shot.

Rick

 

[Gary Keramidas]

rick:

there you go again, letting your math background get in the way.<g>

--


Gary

Stephen... and here is why Dave is suggesting that you leave your code
structure as is (the following is Dave's modification of your code condensed
into a For-Next loop)...

Sub TestMe()
Dim X As Long
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 0 To 9
With .Range("A" & Chr$(74 + X) & "2:A" & Chr$(74 + X) & LastRow)
.Formula = "=IF(" & String$(Abs(X > 6), "A") & _
Chr$(65 + ((5 + 3 * X) Mod 26)) & _
"2=0,0, B2&" & String$(Abs(X > 6), "A") & _
Chr$(65 + ((5 + 3 * X) Mod 26)) & "2)"
End With
Next
End With
End Sub

Shorter than yours? Yes. Easy to figure out what is going on in the code
(especially if you come back in 6 months to maintain, or make changes to, it?
Not by a long shot.

Rick

 

[Rick Rothstein \(MVP - VB\)]

LOL... Yeah, I was curious what the condensed code would look like... then,
once I finished it, it occurred to me that it would make a good lesson of
what not to do.<g>

Rick

rick:

there you go again, letting your math background get in the way.<g>

 

[Dave Peterson]

If you have to modify this when the user comes back, that's ok with me.

But if you left this for me to update, I'd be yelling at you--between my cries
of pain <vbg>.