Thread
:
column letters and numbers
View Single Post
#
4
Posted to microsoft.public.excel.programming
Rick Rothstein
external usenet poster
Posts: 5,934
column letters and numbers
You can replace all of your If..Then..Else blocks with one of these single
lines of code depending on one condition.
If the UBound of your arrLeanConc is never going to be greater than 26....
strdummy = Chr(64 + UBound(arrLeanConc))
Otherwise...
strdummy = Split(Cells(1, UBound(arrLeanConc)).Address(, 0), "$")(0)
If the only place you are using the strdummy variable is in the one line you
showed us, then you can eliminate the strdummy variable and just use its
equivalent (the right side of the appropriate statement above) directly in
the line in place of the strdummy variable.
--
Rick (MVP - Excel)
"Matt S" <Matt
wrote in message
...
All,
Can someone please optimize this code?
If UBound(arrLeanConc) = 1 Then
strdummy = "A"
ElseIf UBound(arrLeanConc) = 2 Then
strdummy = "B"
ElseIf UBound(arrLeanConc) = 3 Then
strdummy = "C"
ElseIf UBound(arrLeanConc) = 4 Then
strdummy = "D"
Else
End If
ActiveCell.Offset(12, 2).Range("A1:" & strdummy & LastRow - 12).Value =
Application.Transpose(arrLeanConc)
my upperbound on the array arrLeanConc changes based on the number of Lean
gases I have specified. I also have to use the reference style and not
absolute. I would like to post the results of the arrLeanConc in my excel
file. There must be a better way to do this.
Any help would be appreciated!
Thanks!
Matt S
Reply With Quote
Rick Rothstein
View Public Profile
Find all posts by Rick Rothstein