Excel Help - Match column in one worksheet to all worksheets in different workbook

T

tom_dang

Hello everyone!

I have been viewing the Google Excel groups and found lots of help.
Unfortunately, I am not very skilled with Excel VBA and need some more
specific help. Currently, I have a macro on a sheet that will compare
column D in the current sheet with column B in a different worksheet
(all in the same workbook). Instead, I would like the macro to compare
column B in the current sheet to column D in all of the sheets in a
seperate workbook. The other workbook will be in the same directory as
the current workbook. Any help would be much appreciated!

Thank you!

Below is what I have so far:

----------------------------------------------------------------------------------------------------------
Private Sub Validate_Click()

Dim lastrow As Long, j As Integer

Application.Calculation = xlCalculationManual '<=== stop other formulas
from calculating

With Decline

lastrow = Decline.Range("d6").End(xlDown).Row '<=== get last row in col
D (Decline)

For j = 6 To lastrow
res = Application.Match(.Cells(j, 4), Upload.Range("B:B"), 0)
'<===match column D ('Decline') with column B ('Upload')
If IsError(res) Then
.Cells(j, 5).Value = "no"
Else
.Cells(j, 5).Value = "yes"
End If
Next j
End With

MsgBox "Recalculating Analysis Worksheet"

Application.Calculation = xlCalculationAutomatic '<=== resume
autocalculating formulas

MsgBox "Validation Completed!"
End Sub

-------------------------------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top