Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
column letters and numbers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
column letters and numbers
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) "Matt S" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
column letters and numbers
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) "Matt S" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
no row numbers or column letters DISPLAYED | Excel Discussion (Misc queries) | |||
How can I change column numbers back to column letters? | Excel Worksheet Functions | |||
Covert Column Numbers to Column Letters in Excel VB | Excel Programming | |||
row numbers & column letters | Excel Discussion (Misc queries) | |||
Row/Column letters/numbers - how do you hide? | Excel Programming |