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

S

Stephen

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

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
 
S

Stephen

Cool, Thanks!

Dave Peterson said:
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
 
R

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
 
G

Gary Keramidas

rick:

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

--


Gary


Rick Rothstein (MVP - VB) said:
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
 
R

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




Gary Keramidas said:
rick:

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top