Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > there's got to be a better way...
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 83
Default there's got to be a better way...
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?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default there's got to be a better way...
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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 83
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,202
Default there's got to be a better way...
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



"Dave Peterson" wrote in message
...
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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default there's got to be a better way...
rick:

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

--


Gary


"Rick Rothstein (MVP - VB)" wrote in message
...
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



"Dave Peterson" wrote in message
...
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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,202
Default there's got to be a better way...
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" <GKeramidasATmsn.com wrote in message
...
rick:

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

--


Gary


"Rick Rothstein (MVP - VB)" wrote in
message ...
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



"Dave Peterson" wrote in message
...
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




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default there's got to be a better way...
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.

"Rick Rothstein (MVP - VB)" wrote:

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

"Dave Peterson" wrote in message
...
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

--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


All times are GMT +1. The time now is 03:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement