column letters and numbers

[Matt S]

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

 

[joel]

Hre are a few improvements. When using nummerical columns use CELLS instead
of RANGE.

'refinement 1
ActiveCell.Offset(12, 2).Range(Range("A1"), Cells(LastRow - 12),
UBound(arrLeanConc)).Value = _
Application.Transpose(arrLeanConc)

'refinement 2
Range(Range("A1"), Cells(LastRow - 12), UBound(arrLeanConc)).Offset(12,
2).Value = _
Application.Transpose(arrLeanConc)

'refinement 3
RowOff = 12
ColOff = 2
Range(Cells(1 + RowOff, 1 + ColOff), Cells(LastRow), UBound(arrLeanConc) +
ColOff).Value = _
Application.Transpose(arrLeanConc)

 

[Jeff]

Hi Matt, You haven't given us a whole lot to work with but if i understand
your problem correctly a For...Next loop will do the trick

For i = 0 To UBound(ArrLeanConc)
ActiveSheet.Cells(1, i).Value = ArrLeanConc(i)
Next

In place of

ActiveCell.Offset(12, 2).Range("A1:" & strdummy & LastRow - 12).Value =
Application.Transpose(arrLeanConc)

 

[Rick Rothstein]

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.