PDA

View Full Version : Comparing spreadsheet - VBA almost working..



cerebrolele
06-09-2012, 08:16 AM
Hi,

I am using a code to compare two spreadsheet.
One of the function is to copy the modified records in a different worksheet, highlighting changes element in red.
The script works, but ONLY when the cell is modified to "blank"(no value), I get coloured in red the upper cell instead of the correct one.

Here the script, any help?


Sub GDV()
Dim WsA As Worksheet, WsB As Worksheet, WsC As Worksheet, WsD As Worksheet, WsE As Worksheet
Dim rFind As Range, c As Range
Dim I As Integer, ColCnt As Integer

Set WsA = Worksheets("OldExport")
Set WsB = Worksheets("NewExport")
Set WsC = Worksheets("Changes")
Set WsD = Worksheets("PosDeleted")
Set WsE = Worksheets("PosAdded")

ColCnt = WsA.Cells(1, Columns.Count).End(xlToLeft).Column

With CreateObject("Scripting.Dictionary")
For Each c In WsA.Range("A2", WsA.Range("A" & Rows.Count).End(xlUp))
If Not .exists(c.Value) Then
.Add c.Value, False
Set rFind = WsB.Columns(1).Find(What:=c.Value, LookIn:=xlValues)
If Not rFind Is Nothing Then
For I = 1 To ColCnt
If Not c.Offset(, I - 1) = WsB.Cells(rFind.Row, I) Then
If .Item(c.Value) = False Then
rFind.Resize(1, ColCnt).Copy WsC.Range("A" & Rows.Count).End(xlUp).Offset(1)
.Item(c.Value) = True
End If
WsC.Cells(Rows.Count, I).End(xlUp).Interior.ColorIndex = 3
End If
Next I
Else
MsgBox c.Value & " PosID has been canceled!"
c.Resize(1, ColCnt).Copy WsD.Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End If
Next c
For Each c In WsB.Range("A2", WsB.Range("A" & Rows.Count).End(xlUp))
If Not .exists(c.Value) Then
MsgBox c.Value & " PosID has been added!"
c.Resize(1, ColCnt).Copy WsE.Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next c
End With
End Sub

dazwm
06-09-2012, 09:31 AM
Please do not multi-post.

http://www.excelforum.com/excel-programming/836871-vba-comparison-spreadsheet.html

http://www.ozgrid.com/forum/showthread.php?t=166275

cerebrolele
06-09-2012, 09:35 AM
I am sorry, I am trying to solve the issue and I am looking for help around.

dazwm
06-09-2012, 09:43 AM
I am sorry, I am trying to solve the issue and I am looking for help around.

The problem with multi posting is you get several people working on giving you the answer on all different websites. Not etiquette!

Cross posting without providing a link to the other thread is a violation of the Forum Rules. We prefer you do not cross post, but when doing so you are obligated to provide the link per your agreement to abide the rules of this forum. (All Excel forums have a similar rule about cross posting.)

cerebrolele
06-10-2012, 12:25 AM
to who may be interested
I found a solution (workaround) here:
h**p://w*w.excelforum.com/excel-programming/836871-vba-comparison-spreadsheet.html?p=2818323&viewfull=1#post2818323

(cannot post links)