Help with selecting dynamic last cell in column and apply formula to rows below it

Copper Contributor

Hi.  VBA and Macro noob here. Need help, please. I have a workbook that pulls order details from multiple worksheets into columns A to T, where column A is the order number and column K and Q are always blank.

 

I want to find the last cell in column B, then apply the formula from that cell down column B to the Order number in column A. However because order numbers are cumulative each day, the last cell in column B is never the same each (ie. "B5:B" is not suitable). Failed code is below.

 

I've also tried finding the last cell in column B, then selecting the whole row to the right, and tried to autofill. That doesn't work either. Can someone help, please? 

 

Windows("consolidate.xlsx").Activate
Cells(Rows.Count, 2).End(xlUp).Offset(0, 0).Select

'Range(Selection, Selection.End(xlToRight)).Select

'Range(Selection, Selection.End(xlToRight)).Select

'Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFill Destination:=Range("B5:B" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

2 Replies

@dufusgoofus 

 

not sure I,ve entirely understood your question; however assuming the following columns are what yor describing, the VBA below will fill cells "b3:b9" with your formula

 

Orders (Col A) 

Formulas

(Col B)

1notEmpty
2notEmpty
3 
4 
5 
6 
7 
8 
9 

 

 

 

Sub getLastRows()


Dim ws As Worksheet
Set ws = Sheets("mySheet")

Dim aLastRow As Long
aLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

Dim bLastRow As Long
bLastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row

Dim myFillRange As Range

Set myFillRange = ws.Range("B" & bLastRow + 1 & ":B" & aLastRow)

myFillRange.FormulaR1C1 = _
"=1+1"


End Sub

 

 

 

hope it helps or gives you a lead

@dufusgoofus 

"...column K and Q are always blank."
I first thought that information was not relevant, but now believe they are the reason for your attempted multiple xlToRight moves.  There are better alternatives.

 

"I want to find the last [populated] cell in column B, then apply the formula from that cell down column B to the [last] Order number in column A."
Are my edits above the correct interpretation of what you want?

 

"However because order numbers are cumulative each day, the last cell in column B is never the same each..."
Not sure what you mean about "cumulative" order numbers. Is your point just that the number of pre-existing and new orders (and therefore the number of new order numbers) can vary unpredictably? I will assume so.

 

If you are trying to populate the empty cells at the bottom of column B with a formula, why are you using .Autofill? Why are .Copy and .Paste not the methods you would use? Oh well, at least you can specify xlFillCopy to make the action clearer.

 

Recommendations:
· Do not perform multiple actions (such as changing Selection and using the new Selection to gather info and using that info to change the scope of work...) within a single statement, at least not when you are getting started writing some code. Use multiple statements (which can allow you to set breakpoints at a more granular level), and capture values into variables (which you then can examine during execution breaks or via watches, etc.).
· When writing code -- especially code that you expect others to read -- include meaningful comments. So I think your first .Select should be preceded by:

' Select the bottom (last) populated cell in column B:

· Immediately after you find that cell (after your first .Select), capture its row number into a variable*, for later use:

Dim intRowWithFormula As Long
intRowWithFormula = Selection.Row

· And do very much the same to identify the row with the last order (your description suggests this is identified by the bottom row in column A, but your code appears to identify the bottom populated row using column E; I'll use column A).

' Select the bottom populated cell in column A:
Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Select
Dim intRowWithLastOrder As Long
intRowWithLastOrder = Selection.Row


· Then:

' Build strings that identify the ranges involved in the next step:
Dim strSourceRange      As String
Dim strDestinationRange As String
strSourceRange = "B" & CStr(intRowWithFormula)
strDestinationRange = "B" & CStr(intRowWithFormula) _
    & ":B" & CStr(intRowWithLastOrder)

**

(The overlap of the two ranges seems odd, but that's the way the AutoFill syntax works.)
· Then do your AutoFill using those range identifiers:

' Copy the formula for [whatever] to the new orders:
Range(strSourceRange).AutoFill _
    Destination:=Range(strDestinationRange) _
    , Type:=xlFillCopy

I think that performs the desired work for column B (only).


* It's common practice to define all variables near the top of a procedure; I won't do that here, but feel free to move the Dim statements up. The "int" and "str" prefixes to the variable names are a good practice to help the person reading the code to distinguish between, say, the string identifier for a range (strRange) and the object that is the range (objRange, not used in this sample code).
** The last statement here is the replacement for your literal "B5:B" etc.

 

"then selecting the whole row to the right, and tried to autofill."

The normal use of AutoFill is for Excel to (detect and) use a pattern from the source range (you relied on Selection as the source range and tried to use it to derive the destination range; seems unreliable at best). If the contents of columns C through T are dissimilar, that's a problem. Again, it seems Copy and Paste would be desirable. But we can't get a feel, as you provided no sample data.

 

I hope that gets you headed in the right direction.