PDA

View Full Version : [SOLVED] Compare and insert missing Row Values Problem



steelstorm
05-02-2012, 09:51 AM
I have 2 worksheets in the same workbook (sheet1, sheet2). What I need is help with the code to look at sheet1 and add any values to sheet2 that are not present.

Now the thing that is giving me the issue is I guess I need to concatenate? Or something along those lines. I need the fruit column and the numbers to be looked at together, not individually.

So from my example attached the following values should be placed into sheet2 since they are not present. I am looking for the VBA code to do this btw.

Apple 3
Pear x
Pinapple 1
Kiwi 1
Banana 6
Nectarine 0


Thanks in advance for any help or advice.

Trebor76
05-02-2012, 07:52 PM
Hi steelstorm,

Welcome to VBA Express!!

See how this goes:


Option Explicit
Sub Macro1()

'http://www.vbaexpress.com/forum/showthread.php?t=42039

Dim lngMyRow As Long, _
lngMyCol As Long
Dim lngLookUpCol As Long
Dim strLookUpCol As String, _
strMyFormula As String
Dim wstSource As Worksheet, _
wstCompare As Worksheet
Dim rngCell As Range

Set wstSource = Worksheets("Sheet1")
Set wstCompare = Worksheets("Sheet2")

Application.ScreenUpdating = False

'Create a primary key in the first unused column of the 'wstSource' tab.
With wstSource
lngMyCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
lngMyRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With .Range(.Cells(2, lngMyCol), .Cells(lngMyRow, lngMyCol))
.Formula = "=A2&B2"
.Value = .Value
End With
End With

'Create a primary key in the first unused column of the 'wstCompare' tab.
With wstCompare
lngMyCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
lngMyRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With .Range(.Cells(2, lngMyCol), .Cells(lngMyRow, lngMyCol))
.Formula = "=A2&B2"
.Value = .Value
End With
End With

'Copy any data (Col's A & B) from the 'wstSource' tab to the 'wstCompare' tab where the 'wstSource' primary key does not exist on the 'wstCompare' tab.
With wstSource
lngMyCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
lngMyRow = .Cells(Rows.Count, lngMyCol).End(xlUp).Row
lngLookUpCol = wstCompare.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
strLookUpCol = Left(Cells(1, lngLookUpCol).Address(True, False), Application.WorksheetFunction.Search("$", Cells(1, lngLookUpCol).Address(True, False)) - 1)
For Each rngCell In .Range(.Cells(2, lngMyCol), .Cells(lngMyRow, lngMyCol))
If IsError(Evaluate("VLOOKUP(" & rngCell.Address & "," & CStr(wstCompare.Name) & "!" & strLookUpCol & ":" & strLookUpCol & ",1,FALSE)")) = True Then
.Range("A" & rngCell.Row & ":B" & rngCell.Row).Copy _
Destination:=wstCompare.Cells(Rows.Count, "A").End(xlUp)(2)
End If
Next rngCell
End With

'Delete primary key columns
With wstSource
lngMyCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
.Columns(lngMyCol).Delete
End With

With wstCompare
lngMyCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
.Columns(lngMyCol).Delete
End With

Application.ScreenUpdating = False

MsgBox "Any missing data has been copied."

End Sub

HTH,

Robert

steelstorm
05-03-2012, 06:08 AM
That works perfect thank you for the help. Now it's time to study your code a little and understand this more:)

I originally had this written to just look at data from one column, but for some reason when the second column of data got put into the mix I just could not wrap my head around the concept for some reason.

Thanks again!

Trebor76
05-03-2012, 06:13 AM
Thanks for the feedback. I'm glad we were able to help :)