CODEWITHSUNDEEP
excelvba
jaysee
jaysee

Reputation: 3

Inserting a formula all the way to the last row in the last empty column

I am fairly new to writing macros in VBA, but I am doing my best to learn as much as I can as quickly as possible. Anyway, the task I am trying to perform is pretty simple, but I'm having trouble coming up with a way to do it.

What I want to do is paste a formula into all of the rows in the last empty column of a spreadsheet.

So in this case, into the highlighted cells shown in the screenshot: Example:

enter image description here

However, I don't want to rely on typing ("K1:K" & lastrow), what I want to do is create reference to the last column and the last row.

So far I've only been able to paste the formula into the entire column by using this: 

lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1).Column
fvlookup = "=vlookup(@1,@2,@3,False)"
fvlookup = Replace (fvlookup, "@1", rng.Address)
fvlookup = Replace (fvlookup, "@2", [LookupFile.csv]LookupFile!$B:$1")
fvlookup = Replace (fvlookup, "@3", "5")
.Columns(lastcol).Formula = fvlookup

But later on in the process I'm working on, I want to remove all of "#N/A" and place them into a different tab named "JEs" because some of the items in the table actually don't have a value in the table I'm looking up to, and need JEs created for them. So, I would have a lot of unnecessary rows in the new tab if I went down this route.

Anyway, I've tried this:

    lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0,1).Column
    fvlookup = "=VLOOKUP(@1,@2,@3,False)"
    fvlookup = Replace(fvlookup, "@1", rng.Address)
    fvlookup = Replace(fvlookup, "@2", "[LookupFile.csv]LookupFile!$B:$I")
    fvlookup = Replace(fvlookup, "@3", "5")
    With .Columns(lastcol)
    lrow = .range("A" & .Rows.Count).End(xlUp).Row
    .Rows(lrow).Formula = fvlookup
    End With

But it only places the formula into "K1" (referencing the attached image)

I've also tried selecting this value after the formula is pasted and auto filling (I know it is advised to avoid selecting ranges with vba, but I still wanted to try as a last resort), but it says it isn't allowed.

I've also tried using: 

.range(lastcol & .range("A" & .Rows.Count).End(xlUp).Rows).Formula = fvlookup

But that gives me run-time error '1004': Application-defined or object-defined error. I tried creating a variable named 'lrange' and setting it similar to how I set lastcol, but no luck (it returns the same error message as above). I've also tried editing lastcol to lastcol.Column or .Columns(lastcol) in the above string, but still nothing.

I've tried researching similar questions and all of the recommendations advise defining two variables (a lastrow one and a lastcolumn one) and inserting the formula into that range, but that doesn't seem to work with what I'm using.

I feel like the process VBA is trying to execute is restricted to only being able to insert data into the entire column, or part of it, with the way I set the macro up, like it's not able to find the last column and insert all the way to the last row with the way I set it up. So I apologize if this is the case, or if I should have written it differently.

Any advise or direction anyone can provide on this topic would be much appreciated.

Thank you for your time!

Upvotes: 0

Views: 915

Answers (3)

Andy G
Andy G

Reputation: 19367

As long as the table doesn't have an entirely blank row or column (as it shouldn't) Range("A1").CurrentRegion identifies the extent of the table. Then .Rows.Count and .Columns.Count gives you the information you need to be able to populate the adjacent column with a formula.

You can also fill the column's cells with formulas in one go, using FormulaR1C1 - provided you are careful with cell referencing. Here is an example:

Dim tableRange As Range
Dim x As Integer, y As Integer

Set tableRange = Range("A1").CurrentRegion

x = tableRange.Rows.Count
y = tableRange.Columns.Count

'fill the adjacent column with a SUM function summing across each row
Range(Cells(2, y + 1), Cells(x, y + 1)).FormulaR1C1 = "=SUM(RC[-" & y & "]:RC[-1])"

(You can also use y and 1 to give this new column a heading.)

If you need to replicate VLOOKUP with a specific (absolute) address such as $B:$I then I would first name that range and insert the name in the formula.

An example of such is:

.FormulaR1C1 = "=VLOOKUP(RC[-1],mylookup,2,FALSE)"

where mylookup is the range name and RC[-1] is referencing the cell immediately to the left of each formula.

Upvotes: 0

Marcucciboy2
Marcucciboy2

Reputation: 3263

Instead of looping at the end I would just use .FillDown

Cells(2, lastcol).FormulaR1C1 = fvlookup
Range(Cells(2, lastcol), Cells(lrow, lastcol)).FillDown

Upvotes: 1

Xabier
Xabier

Reputation: 7735

How about replacing your code with something like this:

Sub foo()
    lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1).Column
    fvlookup = "=VLOOKUP(@1,@2,@3,False)"
    fvlookup = Replace(fvlookup, "@1", Rng.Address)
    fvlookup = Replace(fvlookup, "@2", "[LookupFile.csv]LookupFile!$B:$I")
    fvlookup = Replace(fvlookup, "@3", "5")

    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 2 To lrow
        .Cells(i, lastcol).Formula = fvlookup
    Next i

End Sub

This will loop from row 2 to lastrow and add the formula to lastcol.

Upvotes: 0

Related Questions