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 -