VBA Vlook up Fill down Help!!

[MurraySMTH]

Hi, I am trying to get the entire vlookup formula to fill down onto my 3rd sheet. I feel like i am close, please help!!


Sub CopyVLOOKUP()
Dim LastRow As Long
Dim StoreData As Range
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

Set StoreData = Sheets("Manual Hire Upload").Range("A2:A" & LastRow)

With Sheets("Manual Hire Upload")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("A2:A" & LastRow).Formula = "=VLOOKUP('Onbase Report'!F:F,'Hubble Report'!A:B,2,FALSE)"
End With

 

[davesexcel]

''Onbase Report'!F:F,' is the value you are looking for, it most likely needs to be a single cell such as Onbase Report'!F2
As long as the cell is not absolute, they should change accordingly, next cell would show Onbase Report'!F3 and so on.

 

[MurraySMTH]

''Onbase Report'!F:F,' is the value you are looking for, it most likely needs to be a single cell such as Onbase Report'!F2
As long as the cell is not absolute, they should change accordingly, next cell would show Onbase Report'!F3 and so on.

Hi Dave, thank you for answering so quickly. I added the F2 but it is still giving me the same result. It is not filling down the vlook up formula for the entire column like i would hope.

 

[MARK858]

Seems a bit odd to be filling the formula down to the last row of column A in the same sheet that the last row is defined as being in column A.

What is in column A of sheet Manual Hire Upload when the code runs? and what is the last filled cell in that column?

 

[MurraySMTH]

Seems a bit odd to be filling the formula down to the last row of column A in the same sheet that the last row is defined as being in column A.

What is in column A of sheet Manual Hire Upload when the code runs? and what is the last filled cell in that column?

Good point. my vlookup is coming out of sheet 1 and sheet 2, then pasted into sheet 3. I am hoping it will fill down to sheet 3 column A.

 

[MARK858]

I am hoping it will fill down to sheet 3 column A

Down to what cell in sheet 3 column A? What is that cell based on.... exactly? and what column is the formula being placed in sheet 3?

 

[MurraySMTH]

Down to what cell in sheet 3 column A? What is that cell based on.... exactly? and what column is the formula being placed in sheet 3?

Down to the end of the data from sheet 2. So whatever is in column F of sheet 2 should reflect the formula and paste in sheet 3. Column A to fill in sheet 3.

 

[MARK858]

Down to the end of the data from sheet 2. So whatever is in column F of sheet 2 should reflect the formula and paste in sheet 3. Column A to fill in sheet 3.

Then that is what your last row needs to look at (assuming that Manual Hire Upload is sheet 3)

With Sheets("Manual Hire Upload")
LastRow = Sheets("WhatEver the name of sheet 2 is").Range("A" & .Rows.Count).End(xlUp).Row
.Range("A2:A" & LastRow).Formula = "=VLOOKUP('Onbase Report'!F:F,'Hubble Report'!A:B,2,FALSE)"
End With

As long column A in sheet 2 matches the number of rows in column F of sheet 2

 

[MurraySMTH]

Then that is what your last row needs to look at (assuming that Manual Hire Upload is sheet 3)

With Sheets("Manual Hire Upload")
LastRow = Sheets("WhatEver the name of sheet 2 is").Range("A" & .Rows.Count).End(xlUp).Row
.Range("A2:A" & LastRow).Formula = "=VLOOKUP('Onbase Report'!F:F,'Hubble Report'!A:B,2,FALSE)"
End With

As long column A in sheet 2 matches the number of rows in column F of sheet 2

That worked!!! you are right i needed to define the source.

Amazing. thank you so very much.

 

[MARK858]

You're welcome