Ned help with vba error

[Pasi12]

Hi All,

I have lo vba code that compares 2 sheets cell by cell and outputs the matched results into output sheet but I am getting errors like expected variables, and other errors, type mismatch , cant figure out?? is there a way to shorten this code to better easier code?

Basically what I am looking for to compare 2 sheets every row/cells and out put the matched to next sheet ( no sheet numbers). I don't want to assign sheet numbers if possible?

Thanks!
Pasi.



Sub Macro1()

Dim SourceSheet As Worksheet, _
CompareSheet As Worksheet
outputSheet As Worksheet
Dim rngCell As Range, _
rngSourceRange As Range, _
rngCompareRange As Range
Dim strFormulaString As String
Dim lngPasteRow As Long

Set SourceSheet = ThisWorkbook.ActiveSheet 'Source sheet name.
Set CompareSheet = "Sheet2" 'Compare sheet name.
Set outputSheet = ThisWorkbook.Worksheets.Add 'Output sheet name for matches.

Set rngSourceRange = Sheets(SourceSheet).Range("A3:A" & Sheets(SourceSheet).Range("A" & Rows.Count).End(xlUp).Row)
Set rngCompareRange = Sheets(CompareSheet).Range("E3:E" & Sheets(CompareSheet).Range("E" & Rows.Count).End(xlUp).Row)

Application.ScreenUpdating = False

For Each rngCell In Sheets(strSourceSheet).Range(rngSourceRange.Address)

If InStr(CompareSheet, " ") = 0 Then
strFormulaString = strSourceSheet & "!" & rngCell.Address & "," & CompareSheet & "!" & rngCompareRange.Address & ",1,FALSE"
Else
strFormulaString = "'" & SourceSheet & "'!" & rngCell.Address & ",'" & CompareSheet & "'!" & rngCompareRange.Address & ",1,FALSE"
End If

'If there's no error (i.e. a match) for the current cell value, then...
If IsError(Evaluate("VLOOKUP(" & strFormulaString & ")")) = False Then
'...copy the record to the next avialbale row in Col A of the 'strOutputSheet' tab.
lngPasteRow = Sheets(outputSheet).Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets(SourceSheet).Range("A" & rngCell.Row & ":D" & rngCell.Row).Copy _
Sheets(outputSheet).Range("A" & lngPasteRow)
Application.CutCopyMode = False
End If

Next rngCell

Application.ScreenUpdating = True

'Sheets(3).Select
End Sub

[Trebor76]

Hi Pasi12,

That code looks familiar - did I write it? Could you attach the workbook?

Robert

[Pasi12]

Hi Pasi12,

That code looks familiar - did I write it? Could you attach the workbook?

Robert

HI Trebor,,

I am not sure if this is yours got it from my friend and did some mods to it. attached is the file. basically looking to match both sheets and copy matched it to next sheet. my original data are huge so the code needs to do search to the end of the file and find match between 2 sheets.
Thanks!

[Trebor76]

What are the matching columns between the two tabs?

Could you mock up a tab with you expected results after the macro has run so I can get an idea of how to code a solution because the way the data is from your example I have no idea :confused:

[Pasi12]

HI trebor

The attached is updated with "final sheet". Pls see attached. basically match the 2 wksheet and populate the corresponding columns into new sheet. the "final sheet" is what I am looking for. hope this helps.

[Trebor76]

The way the data is laid out like it is I can't provide any solution I'm afraid :(

There may well be others who can though :)

Good luck with it.

Robert

[Pasi12]

is there a way you can help just matching 2 worksheets only the names and out put them into next sheet with columns header from sheet1? don't worry about the final sheet.
Thanks!

[Aussiebear]

The way the data is laid out like it is I can't provide any solution I'm afraid :(

@pasi12, Robert is quite skilled with vba, and there's a good deal of truth in his advice about the way your data is laid out. Change the layout to help us help you

[Trebor76]

Robert is quite skilled with vba

Thanks Aussiebear - I'm always learning ;)

Pasi12 - the data you present is near impossible to work with i.e. how could someone code some logic to get a patient's name out of column A of Sheet1 when it's mixed in with other data? How could someone then match that to presumably column E of Sheet2 when the names aren't in the same format i.e. NICHOLS in Sheet1 but NICHOLS, in Sheet2 :confused:

[Pasi12]

@pasi12, Robert is quite skilled with vba, and there's a good deal of truth in his advice about the way your data is laid out. Change the layout to help us help you

Thanks Guys I'll find a way to do it.
Pasi.