• 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.

macros for vlookup in dynamic range with two worksheets

Dalia

Member
Hi,

am trying to implement the excel vlookup function into my vba command. There are two different excel sheet,book1 and book2. both the excel sheets are dynamic i.e. everyday there will be a row increase in book1 and there may be an increase or decrease in rows in book2The formula will be=VLOOKUP(A2,$A$2:$B$64,2,0) for cell Q2
The output will always be in book2 in column Q starting from Q2
The look_up value will always be in book2 from column A starting from A2
The table_array will be in book1 starting from A2:B2 the row length is dynamic, according to the row length of column A & B
Autofill Column Q with the formulas

How can i solve it
 
How's this? Change the sheet names as appropriate.
Code:
Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet

'What are the names of our worksheets?
Set sourceSheet = Worksheets("Sheet1")
Set outputSheet = Worksheets("Sheet2")

'Determine last row of source
With sourceSheet
    SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet
    'Determine last row in col P
    OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
    'Apply our formula
    .Range("Q2:Q" & OutputLastRow).Formula = _
        "=VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
End With
End Sub
 
Hi Luke,

Sorry, I was wrong. I mean there are two different workbooks book1 and book2.
Book1 will have the lookup value and book2 will have the table array and both are dynamic.

Can you please assist me on that
 
Hi Luke,
after a vlookup, the values which are found need to be pasted from book1 to book2.
is there any way if it can be done via macros. I dont want to do a copy paste manually.
Have attached both the file for your reference
 

Attachments

  • Book2.xlsx
    9.8 KB · Views: 230
  • Book1.xlsx
    10.1 KB · Views: 211
Hi Dalia,

Made a few small changes to the macro. Note that Book2 needs to be saved as xlsm or xlsb format, not xlsx, or the macro won't be saved. Change the folder path as necessary.
Code:
Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceBook As Workbook
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
Application.ScreenUpdating = True

'Where is the source workbook?
Set sourceBook = Workbooks.Open("C:\My Documents\Book1.xlsx")

'what are the names of our worksheets?
Set sourceSheet = sourceBook.Worksheets("Sheet1")
Set outputSheet = ThisWorkbook.Worksheets("Sheet1")

'Determine last row of source
With sourceSheet
    SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet
    'Determine last row in col P
   OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
    'Apply our formula
   .Range("Q2:Q" & OutputLastRow).Formula = _
        "=VLOOKUP(A2,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
End With

'Close the source workbook, don't save any changes
sourceBook.Close False
Application.ScreenUpdating = True
End Sub
 
Hi Luke,

Its done. was trying to solve it for quite few days....You are so helpful... thanks for sharing your knowledge with the world... once again thank you so much...
Regards
Dalia
 
Hi,

I want to write a vba code to subtract different values in two different columns. The range is A1:B4. it will be like A1 - B1,A2-B2. the output value will be in column C i.e. from C1: C4.
And what shall I do in case all the columns are dynamic
Regards
Dalia
 
Hi, Dalia!

For the first part of your question you can slightly adapt the same code provided by Luke M, changing this:
Code:
  .Range("Q2:Q" & OutputLastRow).Formula = _
        "=VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
by this:
Code:
  .Range("C1:C" & SourceLastRow).Formula = "=A1-B1"
and omitting this:
Code:
    'Determine last row in col P
  OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row

For the last part, elaborate and explain how would you use the code with dynamic columns.

Regards!
 
hi,
Thank you
The data is attached below. What if I need to find the subtraction within this range and what if the data goes on increasing each day.
Ihave one more question... it is not related to this but as I am trying to learn VBA I would like to know when shall we use "&" in VBA.

in the vba code I see there is use of apostrophes both single and double. So when shall we use single and double apostrophes?
"=VLOOKUP(A2,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
 

Attachments

  • subtract.xlsx
    8.5 KB · Views: 88
Hi, Dalia!

Give a look at the uploaded file. There're 2 aspects regarding dynamism, one is adding or deleting rows from columns A:C and another is moving columns A, B or C independently to other columns or worksheets. I worked on the 2nd case.

I defined 3 dynamic named ranges:
MinuendList: =DESREF(Sheet1!$A$2;;;CONTARA(Sheet1!$A:$A)-1;1) -----> in english: =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1,1)
SubtractingList: =DESREF(Sheet1!$B$2;;;CONTARA(Sheet1!$B:$B)-1;1) -----> in english: =OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B)-1,1)
ResultList: =DESREF(Sheet1!$C$2;;;FILAS(MinuendList);1) -----> in english: =OFFSET(Sheet1!$C$2,,,ROWS(MinuendList),1)

So you can cut & paste these columns independently or together to any other place in the worksheet/workbook and the definitions will get adjusted properly. And you can add or delete cells as well.

The involved code is this:
Code:
Option Explicit

Sub MakeFormulasLaFerrari()
    ' constants
    Const ksMinWS = "Sheet1"
    Const ksMinRange = "MinuendList"
    Const ksSubWS = "Sheet1"
    Const ksSubRange = "SubtractingList"
    Const ksResWS = "Sheet1"
    Const ksResRange = "ResultList"
    ' declarations
    Dim rngM As Range, rngS As Range, rngR As Range
    ' start
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    Set rngM = Worksheets(ksMinWS).Range(ksMinRange)
    Set rngS = Worksheets(ksSubWS).Range(ksSubRange)
    Set rngR = Worksheets(ksResWS).Range(ksResRange)
    ' process
    With rngR
        .Formula = "=" & rngM.Cells(1, 1).Address(False, False) & _
                    "-" & rngS.Cells(1, 1).Address(False, False)
    End With
    ' end
    Set rngR = Nothing
    Set rngS = Nothing
    Set rngM = Nothing
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    Beep
End Sub

In the constants section you can change the location of worksheets and ranges as required, obviously matching with the actual range definitions. The application section at start/end prevents from other code events being raised and suspends screen update for increasing speed.

Then the only instruction is that of .Formula result range property definition:
- it uses only the 1st cell of minuend and subtracting and it's updated automatically for each row as you can see
- Address cell property has set both row fixing and column fixing parameters to false to avoid the $ sign in the formulas

Just advise if any issue.

Regards!
 

Attachments

  • macros for vlookup in dynamic range with two worksheets - subtract (for Dalia at chandoo.org).xlsm
    17.3 KB · Views: 230
Hi Luke,

The below macro is working and I want to make it a little bit modified. I want to select or filter all the "N/A" after the vlookup. and then I want to copy paste the values from the outputsheet to the sourcesheet. The sourcesheet is dynamic as I told before. I want to copy the data on the nextrow which is just after the lastrow that contains data in the sourcesheet. And the column A and B data in the outputsheet will be pasted on the column A and B of the sourcesheet, the column F in the outputsheet should be pasted on the column C of the sourcesheet, column E,D,C in the outputsheet should be pasted on the column D, E, F of the sourcesheet. Then the formatting of those rows should be done like the previous rows in the sourcesheet. How can I do it with vba code in macros and how can i save the changes in the sourcesheet. Can you please help me on that.





Hi Dalia,

Made a few small changes to the macro. Note that Book2 needs to be saved as xlsm or xlsb format, not xlsx, or the macro won't be saved. Change the folder path as necessary.
Code:
Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceBook As Workbook
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
Application.ScreenUpdating = True

'Where is the source workbook?
Set sourceBook = Workbooks.Open("C:\My Documents\Book1.xlsx")

'what are the names of our worksheets?
Set sourceSheet = sourceBook.Worksheets("Sheet1")
Set outputSheet = ThisWorkbook.Worksheets("Sheet1")

'Determine last row of source
With sourceSheet
    SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet
    'Determine last row in col P
   OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
    'Apply our formula
   .Range("Q2:Q" & OutputLastRow).Formula = _
        "=VLOOKUP(A2,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
End With

'Close the source workbook, don't save any changes
sourceBook.Close False
Application.ScreenUpdating = True
End Sub
 
Hi ,

Since Luke has initially responded to your original problem , I think you should give him some time to respond to your post of yesterday ; it is anyway late for me tonight , and I can respond only tomorrow. In case you do not get any response by tomorrow , I'll help out.

Narayan
 
Hi Luke,

The below macro is working and I want to make it a little bit modified. I want to select or filter all the "N/A" after the vlookup. and then I want to copy paste the values from the outputsheet to the sourcesheet. The sourcesheet is dynamic as I told before. I want to copy the data on the nextrow which is just after the lastrow that contains data in the sourcesheet. And the column A and B data in the outputsheet will be pasted on the column A and B of the sourcesheet, the column F in the outputsheet should be pasted on the column C of the sourcesheet, column E,D,C in the outputsheet should be pasted on the column D, E, F of the sourcesheet. Then the formatting of those rows should be done like the previous rows in the sourcesheet. How can I do it with vba code in macros and how can i save the changes in the sourcesheet. Can you please help me on that.
 
hi,
...
Ihave one more question... it is not related to this but as I am trying to learn VBA I would like to know when shall we use "&" in VBA.

in the vba code I see there is use of apostrophes both single and double. So when shall we use single and double apostrophes?
"=VLOOKUP(A2,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"

The ampersand (&) symbol is used to concatenate items together. In an XL workbook, let's say that A2 has to word "Bob", and B2 has the word "Smith". an XL formula we could write:
=A2&" "&B2
would produce "Bob Smith", as it has concatenated all the parts together.
In the VB code, we often use the ampersand to concatenate variables that we are using with text strings. In the above formula, that sourceBook.Name and sourceSheet.Name are the variables.

In VB, Double-quote are are used to indicate a text string. Single quotes are used to precede comments, telling the code to ignore the following. However, in the formula you posted, the single-quotes are contained within the double-quotes. These single-quotes will be put into the formula in the worksheet. Within an XL worksheet, single quotes are used to encapsulate an XL sheet/book name, so that XL knows that there may be spaces in the name, but to take look at everything.
 
Hi Luke,

The below macro is working and I want to make it a little bit modified. I want to select or filter all the "N/A" after the vlookup. and then I want to copy paste the values from the outputsheet to the sourcesheet. The sourcesheet is dynamic as I told before. I want to copy the data on the nextrow which is just after the lastrow that contains data in the sourcesheet. And the column A and B data in the outputsheet will be pasted on the column A and B of the sourcesheet, the column F in the outputsheet should be pasted on the column C of the sourcesheet, column E,D,C in the outputsheet should be pasted on the column D, E, F of the sourcesheet. Then the formatting of those rows should be done like the previous rows in the sourcesheet. How can I do it with vba code in macros and how can i save the changes in the sourcesheet. Can you please help me on that.

Here is a the edited macro, with extra bit to filter/copy the data.

Code:
Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceBook As Workbook
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
Dim pasteRange As Range
Dim saveSource As Boolean

Application.ScreenUpdating = False

'Where is the source workbook?
Set sourceBook = Workbooks.Open("C:\My Documents\Book1.xlsx")
saveSource = False

'what are the names of our worksheets?
Set sourceSheet = sourceBook.Worksheets("Sheet1")
Set outputSheet = ThisWorkbook.Worksheets("Sheet1")

'Determine last row of source
With sourceSheet
    SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet
    'Determine last row in col P
    OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
    'Apply our formula
    .Range("Q2:Q" & OutputLastRow).Formula = _
    "=VLOOKUP(A2,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
   
    'XXXXXXXXx New Code XXXXXXXX
    'Filter the data
    .Range("$A$1:$Q$" & OutputLastRow).AutoFilter Field:=17, Criteria1:="#N/A"
   
    'In case there are not errors, this would normally cause an error, so we
    'will force the code to continue
    On Error Resume Next
    Set pasteRange = .Range("A2:Q" & OutputLastRow).SpecialCells(xlCellTypeVisible)
    'Turn error notifcation back on
    On Error GoTo 0
   
    If Not pasteRange Is Nothing Then
        'Copy our data
        Intersect(.Range("A:B"), pasteRange).Copy
        sourceSheet.Cells(SourceLastRow + 1, "A").PasteSpecial (xlPasteValues)
        Intersect(.Range("F:F"), pasteRange).Copy
        sourceSheet.Cells(SourceLastRow + 1, "C").PasteSpecial (xlPasteValues)
        Intersect(.Range("E:E"), pasteRange).Copy
        sourceSheet.Cells(SourceLastRow + 1, "D").PasteSpecial (xlPasteValues)
        Intersect(.Range("D:D"), pasteRange).Copy
        sourceSheet.Cells(SourceLastRow + 1, "E").PasteSpecial (xlPasteValues)
        Intersect(.Range("C:C"), pasteRange).Copy
        sourceSheet.Cells(SourceLastRow + 1, "F").PasteSpecial (xlPasteValues)
       
        'Copy formatting
        sourceSheet.Rows(SourceLastRow).EntireRow.Copy
        sourceSheet.Cells(SourceLastRow, "A").Resize(pasteRange.Rows.Count, 6).PasteSpecial (xlPasteFormats)
       
        Application.CutCopyMode = False
       
        saveSource = True
    End If
   
End With

'Close the source workbook
sourceBook.Close saveSource
Application.ScreenUpdating = True
End Sub
 
Hi @Luke M

Need your help. Based on above book2 by Dalia. Can we delete unwanted data that based on column O (product) before vlookup? Ex: i want to remain data that product is start with aplhabet f until p only. Only after that will do vlookup.

Appreciate you help.

Thank you:)
 
Hi everybody, i just want to ask what we have to shift colu. I mean we took from 3th column from source, but what if i need to take 4th column's data and put to next column in output sheet.

Code:
 With outputSheet
    'Determine last row in col P
   OutputLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    'Apply our formula
   .Range("H2:Q" & OutputLastRow).Formula = _
        "=VLOOKUP($A2,'" & sourceSheet.Name & "'!$A$2:$AE$" & SourceLastRow & ",X,0)"

I need to increase that X until to columns come to blank.

regards
 
hi,
I want to run same macro for all the worksheets in the workbook. Source file be the same and the output sheet covers all the worksheets in workbook. Format of every worksheet is same.
outputSheet = ThisWorkbook.Worksheets("Sheet1")

also how can i lock cells after the value has been entered via vlookup
 
Last edited:
Back
Top