• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA for Vlookup with dynamic woksheets

S

Soph'

New Member
Hello everyone,
I hope someone will be able to help. I'm new to macros and I have trouble writing a VBA Vlookup across 2 sheets in the same workbook:

1. Sheet "ACT HOURS VS BUDGET" is where the data will be imported
2. sheet 1 is where the data I need is located.
3. in both sheets the common data is situated in Column A
4. the data in sheet 1 has 4 columns: I've managed to do a VBA to name the Range as DATA. I want to get the data from column 4 from DATA
when I do it without VBA in sheet "ACT HOURS VS BUDGET"--> Vlookup=(a2,data,4,0).
5.Sheet ("ACT HOURS VS BUDGET") is dynamic: I add columns everytime I work on it. The formula is inserted in the 1st unused cell of row 2. I've managed to do a VBA to find and name the 1st unused column "NEW DATE" but don't know how to reference row 2 to insert the VLookup.

This is what I have so far.
Sub VLookup()
Dim LastRow As Long
Dim LastCol As Long
Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("ACT HOURS VS BUDGET")
Set ws2 = Worksheets("sheet1")

'Finds first blank column
NewCol = ws1.Cells(1, Columns.Count).End(xlToLeft).Column + 1
' Puts your header at the top of the first blank column
Cells(1, NewCol).Value = "New Date"
' Works out the first blank row under the new header
LastRow = ws1.Cells(Rows.Count, NewCol).End(xlUp).Row + 1
' Updates the first blank row of the first blank column with your desired formula
Cells(LastRow, NewCol).Formula = "=vlookup=('ACT HOURS VS BUDGET'!A2,DDATA,4,FALSE)"

I really appreciate the help you can give me. I've uploaded the file as it may help you?
 

Attachments

  • Copy of ACT HRS VS BUDGET.xlsx
    74 KB · Views: 10
Hi:

Use the below code
Code: 
Sub VLookup1()
Application.ScreenUpdating = False

Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("ACT HOURS VS BUDGET")
Set ws2 = Worksheets("sheet1")

'Finds first blank column
NewCol& = ws1.Cells(1, Columns.Count).End(xlToLeft).Column + 1
' Puts your header at the top of the first blank column
Cells(1, NewCol).Value = "New Date"
' Works out the first blank row under the new header
LastRow& = ws1.Cells(Rows.Count, NewCol).End(xlUp).Row + 1
' Updates the first blank row of the first blank column with your desired formula

Cells(LastRow, NewCol).Formula = "=vlookup('ACT HOURS VS BUDGET'!A2,Sheet1!" & Range("DATA").Address & ",4,FALSE)"

With Sheet1
.Range(.Cells(LastRow, NewCol), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, NewCol)).FillDown
.Range(.Cells(LastRow, NewCol), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, NewCol)).Copy
.Cells(LastRow, NewCol).PasteSpecial xlValues
Application.CutCopyMode = False
End With

Application.ScreenUpdating = True
End Sub

Thanks
 
Nebu said:
Hi:

Use the below code
Code: 
Sub VLookup1()
Application.ScreenUpdating = False

Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("ACT HOURS VS BUDGET")
Set ws2 = Worksheets("sheet1")

'Finds first blank column
NewCol& = ws1.Cells(1, Columns.Count).End(xlToLeft).Column + 1
' Puts your header at the top of the first blank column
Cells(1, NewCol).Value = "New Date"
' Works out the first blank row under the new header
LastRow& = ws1.Cells(Rows.Count, NewCol).End(xlUp).Row + 1
' Updates the first blank row of the first blank column with your desired formula

Cells(LastRow, NewCol).Formula = "=vlookup('ACT HOURS VS BUDGET'!A2,Sheet1!" & Range("DATA").Address & ",4,FALSE)"

With Sheet1
.Range(.Cells(LastRow, NewCol), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, NewCol)).FillDown
.Range(.Cells(LastRow, NewCol), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, NewCol)).Copy
.Cells(LastRow, NewCol).PasteSpecial xlValues
Application.CutCopyMode = False
End With

Application.ScreenUpdating = True
End Sub

Thanks
Click to expand...
Morning, Thanks ever so much. This is great!!
 
Hello Nebu
could I trouble you some more? I've kept on working on this but have issues summing up the new created column (Another one by the one you helped me for).
The code I wrote doesn't do anything: doesn't sum up nor gives me a "debug" window... I've made it bold in the VBA below.

Sub differences()
Dim ws1 As Worksheet
Set ws1 = Worksheets("ACT HOURS VS BUDGET")

'Finds first blank column
NewCol& = ws1.Cells(1, Columns.Count).End(xlToLeft).Column + 1
' Puts your header at the top of the first blank column
Cells(1, NewCol).Value = "Net Change"
' Works out the first blank row under the new header
LastRow& = ws1.Cells(Rows.Count, NewCol).End(xlUp).Row + 1
'Find lastColumn with Data, in row# 2
lastColumn = ws1.Cells(2, Columns.Count).End(xlToLeft).Column
' Updates the first blank row of the first blank column with formula
Cells(LastRow, NewCol).Formula = "=sum(r2c11-rc[-1])"
'Copy and paste special the formula
With Sheet1
.Range(.Cells(LastRow, NewCol), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, NewCol)).FillDown
.Range(.Cells(LastRow, NewCol), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, NewCol)).Copy
.Cells(LastRow, NewCol).PasteSpecial xlValues
Application.CutCopyMode = False
'adds autosum in new column "Net Change"
With Sheet1
Cells(LastRow, NewCol).Application.WorksheetFunction.Sum (NewCol&)
End With
Application.ScreenUpdating = True
End With
End Sub
 
You must log in or register to reply here.
Back
Top