Insert formula In Last Used Column = VBA

[Nanogirl21]

Is there any way to create a <acronym title="visual basic for applications">VBA</acronym> that will insert a VLOOKUP statement in cell #2 of in the last used column used in Sheet 1. I would run the VBA several times a week, but the last column will always be different.

The VLookup that I would like to use is:

=VLOOKUP($H:$H,Sheet2!$D:$O,12,FALSE)

Thank you.

 

[baitmaster]

A function to return the ACTUAL last used column (not the last column of the "used range", which can be completely different) is

Function lastUsedColumn(ws As Worksheet) As Long

On Error Resume Next
    lastUsedColumn = ws.Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
On Error GoTo 0

End Function

with this you could use

Sub writeVL()
Sheet1.Cells(2, lastUsedColumn(Sheet1)).Formula = "=VLOOKUP($H:$H,Sheet2!$D:$O,12,FALSE)"
End Sub

 

[Nanogirl21]

A function to return the ACTUAL last used column (not the last column of the "used range", which can be completely different) is

Function lastUsedColumn(ws As Worksheet) As Long

On Error Resume Next
    lastUsedColumn = ws.Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
On Error GoTo 0

End Function

with this you could use

Sub writeVL()
Sheet1.Cells(2, lastUsedColumn(Sheet1)).Formula = "=VLOOKUP($H:$H,Sheet2!$D:$O,12,FALSE)"
End Sub

Thank you for this. Do I need to put both codes into VBA or only the 2nd one? I am not sure what the first one does.

 

[baitmaster]

Put both. Run the Sub only, the function is called from the sub

 

[Nanogirl21]

Put both. Run the Sub only, the function is called from the sub

I added both of the codes but it does not work. Neither are showing in my Macro list to run after adding the code in VBA.

 

[Rick Rothstein]

I added both of the codes but it does not work. Neither are showing in my Macro list to run after adding the code in VBA.

The function won't show there (it is not a macro), but the Sub writeVL should show there. Where did you install the code at? You should have put it in a General Module (Insert/Module from the VB editor's menu bar).