Need to use VBA select all the blank cells, one column to the right of
a column which has a variable range.
example:
Have values in a column, B3:B10. Need to select C3:C10.

The column B range size changes all the time, but always starts in
cell B3, and can range from 50-500 cells.. I'll be entering formulas
in column C, and don't want to enter any formulas where there isn't a
value in column B, so, it necessitates chosing only the blanks that
has a value preceeding it, in column B.

Have located lots of VBA relative navigation codes, but none that will
select this column.
TIA for any ideas.
Pete

No selection necessary, just fill in the blanks.

Adjust "My Formula" to suit.

Sub Insert_Formula()
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 3 Step -1
If Cells(X, 2).Value < "" Then
Cells(X, 2).Offset(0, 1).Formula = "=My Formula"
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP


On Thu, 28 Apr 2011 07:12:33 -0700 (PDT), Pete wrote:

Need to use VBA select all the blank cells, one column to the right of
a column which has a variable range.
example:
Have values in a column, B3:B10. Need to select C3:C10.

The column B range size changes all the time, but always starts in
cell B3, and can range from 50-500 cells.. I'll be entering formulas
in column C, and don't want to enter any formulas where there isn't a
value in column B, so, it necessitates chosing only the blanks that
has a value preceeding it, in column B.

Have located lots of VBA relative navigation codes, but none that will
select this column.
TIA for any ideas.
Pete

Thank you Gord for your reply and answer. 2 things that I might
explore further: . .

The formula I'm entering would be a simple subtraction from the 2
cells to the left.
If I entered in the "=your formula" section the following:
"=B3-A3"
the macro repeats that exact formula all the way down the cells that
have been filled in. . .they all refer to B3-A3.
How would I adjust this to apply to the row that follow and now has a
formula in it? (B4-A4, B5-A5, etc.)

The second thing is assuming new column references. . .
Blank could be N3, and populated columns would be L and M. N is where
to place the formula.
I'm not sure which of the numbers in your code example would refer to
column 14 (the N column)

Many thanks.
Pete
On Apr 28, 10:12*am, Gord Dibben wrote:
No selection necessary, just fill in the blanks.

Adjust "My Formula" to suit.

Sub Insert_Formula()
* * Dim LastRow As Long
* * Dim X As Long
* * LastRow = Cells(Rows.Count, 2).End(xlUp).Row
* * Application.ScreenUpdating = False
* * For X = LastRow To 3 Step -1
* * * * If Cells(X, 2).Value < "" Then
* * * * * * Cells(X, 2).Offset(0, 1).Formula = "=My Formula"
* * * * End If
* * Next X
* * Application.ScreenUpdating = True
End Sub

Gord Dibben * * MS Excel MVP



On Thu, 28 Apr 2011 07:12:33 -0700 (PDT), Pete wrote:
Need to use VBA select all the blank cells, one column to the right of
a column which has a variable range.
example:
Have values in a column, B3:B10. *Need to select C3:C10.

The column B range size changes all the time, but always starts in
cell B3, and can range from 50-500 cells.. *I'll be entering formulas
in column C, and don't want to enter any formulas where there isn't a
value in column B, so, it necessitates chosing only the blanks that
has a value preceeding it, in column B.

Have located lots of VBA relative navigation codes, but none that will
select this column.
TIA for any ideas.
Pete- Hide quoted text -

- Show quoted text -

Revised to account for both your questions.

13 is column M which contains data and blanks. We want to use column M as base
for LastRow

Sub Insert_Formula()
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 13).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 3 Step -1
If Cells(X, 13).Value < "" Then
Cells(X, 13).Offset(0, 1).Formula = "=M" & X & "-L" & X
End If
Next X
Application.ScreenUpdating = True
End Sub

If you want a relative rather than hard-coded macro try this
one..............just remember that it all starts with you selecting a cell in
whichever column you want to be LastRow column............B or M or whatever.

Note also the addition of the GetColLet UDF

Sub Insert_Formula()
Dim LastRow As Long
Dim X As Long
Dim Y As String
Dim Z As String
Y = GetColLet(ActiveCell.Column)
Z = GetColLet(ActiveCell.Column - 1)
LastRow = Cells(Rows.Count, Y).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 3 Step -1
If Cells(X, Y).Value < "" Then
Cells(X, Y).Offset(0, 1).Formula = _
"=" & Y & X & "-" & Z & X
End If
Next X
Application.ScreenUpdating = True
End Sub

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber 26))
End Function


Gord

On Thu, 28 Apr 2011 09:08:00 -0700 (PDT), Pete wrote:

Thank you Gord for your reply and answer. 2 things that I might
explore further: . .

The formula I'm entering would be a simple subtraction from the 2
cells to the left.
If I entered in the "=your formula" section the following:
"=B3-A3"
the macro repeats that exact formula all the way down the cells that
have been filled in. . .they all refer to B3-A3.
How would I adjust this to apply to the row that follow and now has a
formula in it? (B4-A4, B5-A5, etc.)

The second thing is assuming new column references. . .
Blank could be N3, and populated columns would be L and M. N is where
to place the formula.
I'm not sure which of the numbers in your code example would refer to
column 14 (the N column)

Many thanks.
Pete
On Apr 28, 10:12*am, Gord Dibben wrote:
No selection necessary, just fill in the blanks.

Adjust "My Formula" to suit.

Sub Insert_Formula()
* * Dim LastRow As Long
* * Dim X As Long
* * LastRow = Cells(Rows.Count, 2).End(xlUp).Row
* * Application.ScreenUpdating = False
* * For X = LastRow To 3 Step -1
* * * * If Cells(X, 2).Value < "" Then
* * * * * * Cells(X, 2).Offset(0, 1).Formula = "=My Formula"
* * * * End If
* * Next X
* * Application.ScreenUpdating = True
End Sub

Gord Dibben * * MS Excel MVP



On Thu, 28 Apr 2011 07:12:33 -0700 (PDT), Pete wrote:
Need to use VBA select all the blank cells, one column to the right of
a column which has a variable range.
example:
Have values in a column, B3:B10. *Need to select C3:C10.

The column B range size changes all the time, but always starts in
cell B3, and can range from 50-500 cells.. *I'll be entering formulas
in column C, and don't want to enter any formulas where there isn't a
value in column B, so, it necessitates chosing only the blanks that
has a value preceeding it, in column B.

Have located lots of VBA relative navigation codes, but none that will
select this column.
TIA for any ideas.
Pete- Hide quoted text -

- Show quoted text -

You, Sir; are BRILLIANT! Need I say more?

Thank you so much.
Pete

On Apr 28, 12:37*pm, Gord Dibben wrote:
Revised to account for both your questions.

13 is column M which contains data and blanks. *We want to use column M as base
for LastRow

Sub Insert_Formula()
* * Dim LastRow As Long
* * Dim X As Long
* * LastRow = Cells(Rows.Count, 13).End(xlUp).Row
* * Application.ScreenUpdating = False
* * For X = LastRow To 3 Step -1
* * * * If Cells(X, 13).Value < "" Then
* * * * * * Cells(X, 13).Offset(0, 1).Formula = "=M" & X & "-L" & X
* * * * End If
* * Next X
* * Application.ScreenUpdating = True
End Sub

If you want a relative rather than hard-coded macro try this
one..............just remember that it all starts with you selecting a cell in
whichever column you want to be LastRow column............B or M or whatever.

Note also the addition of the GetColLet UDF

Sub Insert_Formula()
* * Dim LastRow As Long
* * Dim X As Long
* * Dim Y As String
* * Dim Z As String
* * Y = GetColLet(ActiveCell.Column)
* * Z = GetColLet(ActiveCell.Column - 1)
* * LastRow = Cells(Rows.Count, Y).End(xlUp).Row
* * Application.ScreenUpdating = False
* * For X = LastRow To 3 Step -1
* * * * If Cells(X, Y).Value < "" Then
* * * * * * Cells(X, Y).Offset(0, 1).Formula = _
* * * * * * "=" & Y & X & "-" & Z & X
* * * * End If
* * Next X
* * Application.ScreenUpdating = True
End Sub

Function GetColLet(ColNumber As Integer) As String
* * GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
* * * * 1 - (ColNumber 26))
End Function

Gord



On Thu, 28 Apr 2011 09:08:00 -0700 (PDT), Pete wrote:
Thank you Gord for your reply and answer. 2 things that I might
explore further: . *.

The formula I'm entering would be a simple subtraction from the 2
cells to the left.
If I entered in the "=your formula" section the following:
"=B3-A3"
the macro repeats that exact formula all the way down the cells that
have been filled in. . .they all refer to B3-A3.
How would I adjust this to apply to the row that follow and now has a
formula in it? (B4-A4, B5-A5, etc.)

The second thing is assuming new column references. . .
Blank could be N3, and populated columns would be L and M. *N is where
to place the formula.
I'm not sure which of the numbers in your code example would refer to
column 14 (the N column)

Many thanks.
Pete
On Apr 28, 10:12 am, Gord Dibben wrote:
No selection necessary, just fill in the blanks.

Adjust "My Formula" to suit.

Sub Insert_Formula()
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 3 Step -1
If Cells(X, 2).Value < "" Then
Cells(X, 2).Offset(0, 1).Formula = "=My Formula"
End If
Next X
Application.ScreenUpdating = True
End Sub

Gord Dibben MS Excel MVP

On Thu, 28 Apr 2011 07:12:33 -0700 (PDT), Pete wrote:
Need to use VBA select all the blank cells, one column to the right of
a column which has a variable range.
example:
Have values in a column, B3:B10. Need to select C3:C10.

The column B range size changes all the time, but always starts in
cell B3, and can range from 50-500 cells.. I'll be entering formulas
in column C, and don't want to enter any formulas where there isn't a
value in column B, so, it necessitates chosing only the blanks that
has a value preceeding it, in column B.

Have located lots of VBA relative navigation codes, but none that will
select this column.
TIA for any ideas.
Pete- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Thanks is great..............the brilliant part might be over the top<g

Glad to help.


Gord

On Thu, 28 Apr 2011 10:52:31 -0700 (PDT), Pete wrote:

You, Sir; are BRILLIANT! Need I say more?

Thank you so much.
Pete

On Apr 28, 12:37*pm, Gord Dibben wrote:
Revised to account for both your questions.

13 is column M which contains data and blanks. *We want to use column M as base
for LastRow

Sub Insert_Formula()
* * Dim LastRow As Long
* * Dim X As Long
* * LastRow = Cells(Rows.Count, 13).End(xlUp).Row
* * Application.ScreenUpdating = False
* * For X = LastRow To 3 Step -1
* * * * If Cells(X, 13).Value < "" Then
* * * * * * Cells(X, 13).Offset(0, 1).Formula = "=M" & X & "-L" & X
* * * * End If
* * Next X
* * Application.ScreenUpdating = True
End Sub

If you want a relative rather than hard-coded macro try this
one..............just remember that it all starts with you selecting a cell in
whichever column you want to be LastRow column............B or M or whatever.

Note also the addition of the GetColLet UDF

Sub Insert_Formula()
* * Dim LastRow As Long
* * Dim X As Long
* * Dim Y As String
* * Dim Z As String
* * Y = GetColLet(ActiveCell.Column)
* * Z = GetColLet(ActiveCell.Column - 1)
* * LastRow = Cells(Rows.Count, Y).End(xlUp).Row
* * Application.ScreenUpdating = False
* * For X = LastRow To 3 Step -1
* * * * If Cells(X, Y).Value < "" Then
* * * * * * Cells(X, Y).Offset(0, 1).Formula = _
* * * * * * "=" & Y & X & "-" & Z & X
* * * * End If
* * Next X
* * Application.ScreenUpdating = True
End Sub

Function GetColLet(ColNumber As Integer) As String
* * GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
* * * * 1 - (ColNumber 26))
End Function

Gord



On Thu, 28 Apr 2011 09:08:00 -0700 (PDT), Pete wrote:
Thank you Gord for your reply and answer. 2 things that I might
explore further: . *.

The formula I'm entering would be a simple subtraction from the 2
cells to the left.
If I entered in the "=your formula" section the following:
"=B3-A3"
the macro repeats that exact formula all the way down the cells that
have been filled in. . .they all refer to B3-A3.
How would I adjust this to apply to the row that follow and now has a
formula in it? (B4-A4, B5-A5, etc.)

The second thing is assuming new column references. . .
Blank could be N3, and populated columns would be L and M. *N is where
to place the formula.
I'm not sure which of the numbers in your code example would refer to
column 14 (the N column)

Many thanks.
Pete
On Apr 28, 10:12 am, Gord Dibben wrote:
No selection necessary, just fill in the blanks.

Adjust "My Formula" to suit.

Sub Insert_Formula()
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 3 Step -1
If Cells(X, 2).Value < "" Then
Cells(X, 2).Offset(0, 1).Formula = "=My Formula"
End If
Next X
Application.ScreenUpdating = True
End Sub

Gord Dibben MS Excel MVP

On Thu, 28 Apr 2011 07:12:33 -0700 (PDT), Pete wrote:
Need to use VBA select all the blank cells, one column to the right of
a column which has a variable range.
example:
Have values in a column, B3:B10. Need to select C3:C10.

The column B range size changes all the time, but always starts in
cell B3, and can range from 50-500 cells.. I'll be entering formulas
in column C, and don't want to enter any formulas where there isn't a
value in column B, so, it necessitates chosing only the blanks that
has a value preceeding it, in column B.

Have located lots of VBA relative navigation codes, but none that will
select this column.
TIA for any ideas.
Pete- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Note also the addition of the GetColLet UDF

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber 26))
End Function

Here is a more concise version of your GetColLet function which the OP might
want to consider using...

Function GetColLet(ColNumber As Long) As String
GetColLet = Split(Cells(1, ColNumber).Address, "$")(1)
End Function

Rick Rothstein (MVP - Excel)

Thanks Rick

Can you "more concise" this one?

Function GetColNum(myColumn As String) As Long
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function


Gord


On Sun, 8 May 2011 11:34:42 -0400, "Rick Rothstein"
wrote:

Note also the addition of the GetColLet UDF

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber 26))
End Function

Here is a more concise version of your GetColLet function which the OP might
want to consider using...

Function GetColLet(ColNumber As Long) As String
GetColLet = Split(Cells(1, ColNumber).Address, "$")(1)
End Function

Rick Rothstein (MVP - Excel)

On May 8, 10:27*am, Gord Dibben wrote:
Can you "more concise" this one?

Function GetColNum(myColumn As String) As Long
* * GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

One way:

GetColNum = Range(myColumn & 1).Column

On May 8, 11:27*am, joeu2004 wrote:
One way:
GetColNum = Range(myColumn & 1).Column

Another way:

GetColNum = Cells(1, myColumn).Column

This is one letter shorter than your first suggestion.

Thanks


Gord

On Sun, 8 May 2011 11:30:29 -0700 (PDT), joeu2004 wrote:

On May 8, 11:27*am, joeu2004 wrote:
One way:
GetColNum = Range(myColumn & 1).Column

Another way:

GetColNum = Cells(1, myColumn).Column

This is one letter shorter than your first suggestion.

Thanks


Gord

On Sun, 8 May 2011 11:30:29 -0700 (PDT), joeu2004 wrote:

On May 8, 11:27*am, joeu2004 wrote:
One way:
GetColNum = Range(myColumn & 1).Column

Another way:

GetColNum = Cells(1, myColumn).Column

Can you "more concise" this one?

Function GetColNum(myColumn As String) As Long
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

The Columns property does not require the repeated, colon delimited string
like the Range does...

Function GetColNum(myColumn As String) As Long
GetColNum = Columns(myColumn).Column
End Function

Rick Rothstein (MVP - Excel)

Another way:

GetColNum = Cells(1, myColumn).Column

The row number is optional, so you can save a character...

GetColNum = Cells(, myColumn).Column

This makes the statement the same length as the one I just posted a few
minutes ago, namely...

GetColNum = Columns(myColumn).Column

Rick Rothstein (MVP - Excel)

On May 8, 12:40 pm, Gord Dibben wrote:
This is one letter shorter than your first suggestion.

I didn't think of it that way. I don't really count characters,
although I know you were interested in being "more concise".

I tend to measure "conciseness" in terms of number and complexity of
apparent operations, not number of characters.

I thought it would be more significant that Cells(1,mycol) does not
require the concatenation that Range(mycol & 1) does.

But I am surprised to learn that both perform about the same.

Actually, the Range implementation is about 5% faster on my
computer(!). But we're only talking about 0.3 microsec on my computer
(YMMV). And I haven't determined if that is statistically
significant.

Not surprisingly, the implementation below is about 55% faster than
the Cells and Range implementations. But it does not qualify as "more
concise", of course.

I'm surprised that it is "only" 54-66% faster, since it does not
require the interprocess communication that I presume the Cells and
Range implementations do.

I somewhat confirmed that they do by substituting Range("a1").Value,
which presumably must communicate with the Excel process. (Of course,
it does not perform the same functionality. That was not its
purpose.)

Note: Initially, everything appeared to perform about the same. Then
I remembered that VBA adds overhead the first time each code path is
executed for __each__ call(!). So for the final comparison, I looped
20 times within the call and averaged the time ignoring the first
iteration. (I did not display the first-iteration time to confirm
that it is 2-5 times longer than the average of the other
iterations.) Also, I sync'd with the process clock and booted the
system in safe mode without networking in order to minimize extraneous
system activity.

-----

A non-concise, but faster implementation....

Function getcolnum(mycolumn As String) As Long
Const lcA As Long = 97 'Asc("a")
If Len(mycolumn) = 1 Then
getcolnum = Asc(LCase(mycolumn)) - lcA + 1
Else
getcolnum = _
(Asc(LCase(Left(mycolumn, 1))) - lcA + 1) * 26 _
+ Asc(LCase(Right(mycolumn, 1))) - lcA + 1
End If
End Function

The second getcolnum expression can be simplified by rearranging
terms, to wit:

Const lcA27 As Long = 27 * lcA
[....]
getcolnum = _
26 * Asc(LCase(Left(mycolumn, 1))) _
+ Asc(LCase(Right(mycolumn, 1))) - lcA27 + 27

Thanks Rick


Gord

On Sun, 8 May 2011 16:28:37 -0400, "Rick Rothstein"
wrote:

Can you "more concise" this one?

Function GetColNum(myColumn As String) As Long
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

The Columns property does not require the repeated, colon delimited string
like the Range does...

Function GetColNum(myColumn As String) As Long
GetColNum = Columns(myColumn).Column
End Function

Rick Rothstein (MVP - Excel)

All I can say is Wow!

Thanks for researching this subject so thoroughly.


Gord

On Sun, 8 May 2011 15:31:27 -0700 (PDT), joeu2004 wrote:

On May 8, 12:40 pm, Gord Dibben wrote:
This is one letter shorter than your first suggestion.

I didn't think of it that way. I don't really count characters,
although I know you were interested in being "more concise".

I tend to measure "conciseness" in terms of number and complexity of
apparent operations, not number of characters.

I thought it would be more significant that Cells(1,mycol) does not
require the concatenation that Range(mycol & 1) does.

But I am surprised to learn that both perform about the same.

Actually, the Range implementation is about 5% faster on my
computer(!). But we're only talking about 0.3 microsec on my computer
(YMMV). And I haven't determined if that is statistically
significant.

Not surprisingly, the implementation below is about 55% faster than
the Cells and Range implementations. But it does not qualify as "more
concise", of course.

I'm surprised that it is "only" 54-66% faster, since it does not
require the interprocess communication that I presume the Cells and
Range implementations do.

I somewhat confirmed that they do by substituting Range("a1").Value,
which presumably must communicate with the Excel process. (Of course,
it does not perform the same functionality. That was not its
purpose.)

Note: Initially, everything appeared to perform about the same. Then
I remembered that VBA adds overhead the first time each code path is
executed for __each__ call(!). So for the final comparison, I looped
20 times within the call and averaged the time ignoring the first
iteration. (I did not display the first-iteration time to confirm
that it is 2-5 times longer than the average of the other
iterations.) Also, I sync'd with the process clock and booted the
system in safe mode without networking in order to minimize extraneous
system activity.

-----

A non-concise, but faster implementation....

Function getcolnum(mycolumn As String) As Long
Const lcA As Long = 97 'Asc("a")
If Len(mycolumn) = 1 Then
getcolnum = Asc(LCase(mycolumn)) - lcA + 1
Else
getcolnum = _
(Asc(LCase(Left(mycolumn, 1))) - lcA + 1) * 26 _
+ Asc(LCase(Right(mycolumn, 1))) - lcA + 1
End If
End Function

The second getcolnum expression can be simplified by rearranging
terms, to wit:

Const lcA27 As Long = 27 * lcA
[....]
getcolnum = _
26 * Asc(LCase(Left(mycolumn, 1))) _
+ Asc(LCase(Right(mycolumn, 1))) - lcA27 + 27

Errata....

On May 8, 3:31*pm, joeu2004 wrote:
So for the final comparison, I looped 20 times within the
call and averaged the time ignoring the first iteration.
*(I did not display the first-iteration time to confirm
that it is 2-5 times longer than the average of the other
iterations.)

Arrgghh! I meant: I __did__ display the first-iteration time.

Usually, I err by omitting "not" where it is important. (Doh!)
Adding "not" needlessly is a new wrinkle.

BTW, I wrote "55% faster" in one place and "54-66% faster" in
another. Although it's not important, the former is correct. I had
changed the implementation, which made the execution time consistent.
But when I edited the posting-in-progress, I fixed one comment, but
not the other.

'Nuf said. The horse is dead.

Just out of curiosity, how does this function compare speed-wise to the
function you posted?

Function GetColNum(myColumn As String) As String
Dim X As Long, B() As Byte
B = UCase(myColumn)
GetColNum = B(UBound(B) - 1) - 64
If UBound(B) 1 Then GetColNum = GetColNum + 26 * (B(0) - 64)
End Function

Rick Rothstein (MVP - Excel)

Dim X As Long, B() As Byte

Uh, you can omit the declaration for the X variable (it was left over from a
different attempt).

Rick Rothstein (MVP - Excel)

If anyone is still reading this thread, here is the generalization of the
function I asked you (Joe) to time test; it will work with XL2007 and XL2010
(and beyond if Microsoft decides to add more columns in the future) as well
as with prior versions...

Function GetColNum(myColumn As String) As Long
Dim X As Long, Multiplier As Long, B() As Byte
B = UCase(myColumn)
Multiplier = 1
For X = UBound(B) - 1 To LBound(B) Step -2
GetColNum = GetColNum + Multiplier * (B(X) - 64)
Multiplier = 26 * Multiplier
Next
End Function

I believe this function (as well as the one I posted earlier for XL2003 and
before) will execute about as fast as is possible due to the use of Byte
arrays and, as such, I expect it to execute (noticeably?) faster than the
String-based version that you (Joe) posted.

Rick Rothstein (MVP - Excel)

If anyone is still reading this thread, here is the generalization of the
function I asked you (Joe) to time test; it will work with XL2007 and XL2010
(and beyond if Microsoft decides to add more columns in the future) as well
as with prior versions...

Function GetColNum(myColumn As String) As Long
Dim X As Long, Multiplier As Long, B() As Byte
B = UCase(myColumn)
Multiplier = 1
For X = UBound(B) - 1 To LBound(B) Step -2
GetColNum = GetColNum + Multiplier * (B(X) - 64)
Multiplier = 26 * Multiplier
Next
End Function

I believe this function (as well as the one I posted earlier for XL2003 and
before) will execute about as fast as is possible due to the use of Byte
arrays and, as such, I expect it to execute (noticeably?) faster than the
String-based version that you (Joe) posted.

Rick Rothstein (MVP - Excel)

On May 8, 5:13 pm, "Rick Rothstein"
wrote:
Just out of curiosity, how does this function
compare speed-wise to the function you posted?

It depends on whether the column name is 1 or 2 letters. To a lesser
degree, it depends on whether you are asking about the code fragment
or the function call.

Also, both of our implementations are not as optimal as they could
be. So it depends on whether you want to consider improved
implementations or the original ones.

Note that both the String and the Byte implementations allow for only
up to 676 columns.


Results....

I had expected the Byte implementation to perform much better than
String implementation. I am surprised that is not the case, with one
exception.

I suspect the explanation is: the same engineers that implemented the
Answers Forum also implementated VBA Byte operations. We all know how
incompetent they are ;-). Seriously, I suspect that MS spent some
time optimizing VBA String operations, but not Byte operations.

Note that I am using VBA 6.5.1024 with XL2003 SP3. Perhaps VBA with
XL2007 and later performs differently. However, my understanding is
that MS has not invested in VBA improvements.

That said, it appears that for 1-letter column names, the String code
fragment is 64-65% faster than the Byte code fragment even with
improvements in the Byte implemenation.

For 2-letter column names, the original String code fragment is 3%
faster than the original Byte code fragement. However, the improved
Byte code fragment is 1.5% faster than the improved String code
fragment.

Those numbers are for the code fragment alone. The performance
figures are significantly different for the function call. Recall
that VBA adds significant overhead the first time a code path is
executed __each__ time a procedure is called.

For 1-letter colulmn names, the String function call is still about
49% faster than the Byte function call for both the original and the
improvement implementations.

However, for 2-letter column names, the original Byte function call is
0.8% faster than the original String function call, and the improved
Byte function is 9% faster than the improved String function call.


Measurement Methodology....

In order to measure such small times, time was measured for 1000
iterations around either the code fragment or the function call.
(Actually, 1001 iterations. I always throw away the first iteration
because of first-time anomalies in VBA.) Thus, the measured time
period is 140-740 times longer than the timer calls around it.

Each 1000-iteration time period was repeated 101 times in order to
develop statistics, notably an average and a 95% confidence interval
(CI).

The 95% CI is remarkably narrow. It is mostly less than +/-0.10% of
average when the system was booted in safe mode without networking.
It is mostly less than +/-0.20% of average when the system was booted
normally. The maximum 95% CI is +/-0.44%.

On my computer (YMMV), code-fragment times ran from 0.293878 to
0.927479 microseconds. Function call times ran from 0.557318 to
1.240892 microseconds. These are divided down from the total 1000-
iteration times.


Improved Implementations....

Note: Limited to and optimized for up to 676 columns as were the
original implementations.

String code fragment:

Const lcA As Long = 96 'Asc("a")-1
Const lcA27 As Long = 27 * lcA
[....]
If Len(mycolumn) = 1 Then
getcolnum = Asc(LCase(mycolumn)) - lcA
Else
getcolnum = _
Asc(LCase(Right(mycolumn, 1))) + _
26 * Asc(LCase(Left(mycolumn, 1))) - lcA27
End If

Byte code fragment:

Const ucA As Long = 64 'Asc("A")-1
Const ucA27 As Long = 27 * ucA
Dim b() As Byte
[....]
b = UCase(mycolumn)
If UBound(b) = 1 Then getcolnum = b(0) - ucA _
Else getcolnum = b(2) + 26 * b(0) - ucA27

Wow, I am shocked. It looks like none of the speed I know Byte arrays to
have in the compiled version of VB translates over to the VBA world at all.
I am going to have to learn not to rely so heavily on my past experience
with the compiled version of VB when trying to develop optimized solutions
for Excel's VBA. Thank you very much for running those time trials... they
were a complete eye-opener for me.

Rick Rothstein (MVP - Excel)




"joeu2004" wrote in message
...

On May 8, 5:13 pm, "Rick Rothstein"
wrote:
Just out of curiosity, how does this function
compare speed-wise to the function you posted?

It depends on whether the column name is 1 or 2 letters. To a lesser
degree, it depends on whether you are asking about the code fragment
or the function call.

Also, both of our implementations are not as optimal as they could
be. So it depends on whether you want to consider improved
implementations or the original ones.

Note that both the String and the Byte implementations allow for only
up to 676 columns.


Results....

I had expected the Byte implementation to perform much better than
String implementation. I am surprised that is not the case, with one
exception.

I suspect the explanation is: the same engineers that implemented the
Answers Forum also implementated VBA Byte operations. We all know how
incompetent they are ;-). Seriously, I suspect that MS spent some
time optimizing VBA String operations, but not Byte operations.

Note that I am using VBA 6.5.1024 with XL2003 SP3. Perhaps VBA with
XL2007 and later performs differently. However, my understanding is
that MS has not invested in VBA improvements.

That said, it appears that for 1-letter column names, the String code
fragment is 64-65% faster than the Byte code fragment even with
improvements in the Byte implemenation.

For 2-letter column names, the original String code fragment is 3%
faster than the original Byte code fragement. However, the improved
Byte code fragment is 1.5% faster than the improved String code
fragment.

Those numbers are for the code fragment alone. The performance
figures are significantly different for the function call. Recall
that VBA adds significant overhead the first time a code path is
executed __each__ time a procedure is called.

For 1-letter colulmn names, the String function call is still about
49% faster than the Byte function call for both the original and the
improvement implementations.

However, for 2-letter column names, the original Byte function call is
0.8% faster than the original String function call, and the improved
Byte function is 9% faster than the improved String function call.


Measurement Methodology....

In order to measure such small times, time was measured for 1000
iterations around either the code fragment or the function call.
(Actually, 1001 iterations. I always throw away the first iteration
because of first-time anomalies in VBA.) Thus, the measured time
period is 140-740 times longer than the timer calls around it.

Each 1000-iteration time period was repeated 101 times in order to
develop statistics, notably an average and a 95% confidence interval
(CI).

The 95% CI is remarkably narrow. It is mostly less than +/-0.10% of
average when the system was booted in safe mode without networking.
It is mostly less than +/-0.20% of average when the system was booted
normally. The maximum 95% CI is +/-0.44%.

On my computer (YMMV), code-fragment times ran from 0.293878 to
0.927479 microseconds. Function call times ran from 0.557318 to
1.240892 microseconds. These are divided down from the total 1000-
iteration times.


Improved Implementations....

Note: Limited to and optimized for up to 676 columns as were the
original implementations.

String code fragment:

Const lcA As Long = 96 'Asc("a")-1
Const lcA27 As Long = 27 * lcA
[....]
If Len(mycolumn) = 1 Then
getcolnum = Asc(LCase(mycolumn)) - lcA
Else
getcolnum = _
Asc(LCase(Right(mycolumn, 1))) + _
26 * Asc(LCase(Left(mycolumn, 1))) - lcA27
End If

Byte code fragment:

Const ucA As Long = 64 'Asc("A")-1
Const ucA27 As Long = 27 * ucA
Dim b() As Byte
[....]
b = UCase(mycolumn)
If UBound(b) = 1 Then getcolnum = b(0) - ucA _
Else getcolnum = b(2) + 26 * b(0) - ucA27

On May 9, 9:01*pm, "Rick Rothstein"
wrote:
here is the generalization of the function I asked you
(Joe) to time test; it will work with XL2007 and XL2010

I would implement the two methods as follows:

Byte method:

Const lcA As Long = 96 'Asc("a")-1
Dim b() As Byte
[....]
b = mycolumn
For k = 0 To UBound(b) Step 2
getcolnum = 26 * getcolnum + (b(k) Or 32) - lcA
Next

String method:

Const lcA As Long = 96 'Asc("a")-1
[....]
For k = 1 To Len(mycolumn)
getcolnum = 26 * getcolnum + _
(Asc(Mid(mycolumn, k, 1)) Or 32) - lcA
Next

Note that I use "Or 32" in place of LCase. I did not measure the
benefit.

I found that the String method significantly outperforms the Byte for
Len(mycolumn) <= 3 (52%, 38% and 3.6% faster). The Byte method
increasingly outperforms the String method for Len(mycolumn) = 4
(21%, 33% etc faster).

Apparently, the assignment byte=string adds significant overhead to
the Byte method. When I replace it with Dim b(0 to 1) and discrete
assignments of constants to b(0) and b(1) for the one-character case,
the Byte method is about 1.2% faster than the String method.