Macro for vlookup dynamic range with two worksheets

[Ravi Sutrave]

Hi

Am trying to implement the excel vlookup function into my vba command. In file i have multiple tabs and tabs are named as calendar months. every month i will create a new tab and compare the data with last month tab.

The data i compare is from current month tab cell D11 with previous month tab from range D11:F18 and 3rd column data is my output in respect G cell.

Need help with this.

i am attaching sample file for reference.

 

[Stevie]

Hiya Ravi!
I had a look at the file you attached. if you want to automatically enter the vlookup as in your file with a macro, then you will need to use something like:

dim lastdate as string
lastdate = activesheet.range("D1").text
activesheet.range("G11").formula = "=vlookup(D11, '" & lastdate & "'!D11:F18,3,0)"
activesheet.range("G11").select
Selection.AutoFill Destination:=Range("G11:G16")

This requires you to put the name of the sheet with last month's data in cell D1. Due to the names of your sheets, in order to put 'Feb 15' into the cell, I typed ="feb 15" to force excel to treat it as text instead of as a date. I chose D1 as you have columns A-C hidden for some reason.

I will point out that your range in your vlookup in your sheet is not locked, so it is changing it when you pull it down, take a look at your own file.
If you don't want this to happen you need to include $s in the code like this:

dim lastdate as string
lastdate = activesheet.range("D1").text
activesheet.range("G11").formula = "=vlookup(D11, '" & lastdate & "'!$D$11:$F$18,3,0)"
activesheet.range("G11").select
Selection.AutoFill Destination:=Range("G11:G16")

You will need to put this snippet inside a sub in your code module. I would try it on it's own first so you know what it is doing. Remember you can go through it step by step with f8.

Hope this helps!

Stevie

 

[Smallman]

Hi

If you set up your spreadsheet so the data is pulling off one cell then you should be able to get the job done the following way.

Sub Redo()
  [G11:G18] = "=vlookup(D11, '" & [G8] & "'!$D$11:$F$18,3,0)"
End Sub

Then you could run it off the change in G8.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, [g8]) Is Nothing Then Redo
End Sub

Slightly different method I guess. File attached to prove workings.

Take care

Smallman

 

[Ravi Sutrave]

Hiya Ravi!
I had a look at the file you attached. if you want to automatically enter the vlookup as in your file with a macro, then you will need to use something like:

dim lastdate as string
lastdate = activesheet.range("D1").text
activesheet.range("G11").formula = "=vlookup(D11, '" & lastdate & "'!D11:F18,3,0)"
activesheet.range("G11").select
Selection.AutoFill Destination:=Range("G11:G16")

This requires you to put the name of the sheet with last month's data in cell D1. Due to the names of your sheets, in order to put 'Feb 15' into the cell, I typed ="feb 15" to force excel to treat it as text instead of as a date. I chose D1 as you have columns A-C hidden for some reason.

I will point out that your range in your vlookup in your sheet is not locked, so it is changing it when you pull it down, take a look at your own file.
If you don't want this to happen you need to include $s in the code like this:

dim lastdate as string
lastdate = activesheet.range("D1").text
activesheet.range("G11").formula = "=vlookup(D11, '" & lastdate & "'!$D$11:$F$18,3,0)"
activesheet.range("G11").select
Selection.AutoFill Destination:=Range("G11:G16")

You will need to put this snippet inside a sub in your code module. I would try it on it's own first so you know what it is doing. Remember you can go through it step by step with f8.

Hope this helps!

Stevie

Hi Stevie,

Thanks for your reply.
I was also looking for code which can compare data in multiple sheets on error.

The data i want compare is from current month tab cell D11 with all month tabs which are present in workbook from range D11:F18 and 3rd column data is my output in respective G cell.

I am look on the same sample file which i have attached previously ..

Need help with this.

 

[Ravi Sutrave]

Hi

If you set up your spreadsheet so the data is pulling off one cell then you should be able to get the job done the following way.

Sub Redo()
  [G11:G18] = "=vlookup(D11, '" & [G8] & "'!$D$11:$F$18,3,0)"
End Sub

Then you could run it off the change in G8.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, [g8]) Is Nothing Then Redo
End Sub

Slightly different method I guess. File attached to prove workings.

Take care

Smallman

Hi Smallman,

Thanks for your reply.
I was also looking for code which can compare data in multiple sheets on error.

The data i want compare is from current month tab cell D11 with all month tabs which are present in workbook from range D11:F18 and 3rd column data is my output in respective G cell.

I am look on the same sample file which i have attached previously ..

Need help with this.