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)