Loop and If module for checking and copying cells 1

[BG123]

I am writing my first VBA macro for Excel and struggling a bit at the moment. What I am trying to do is as follows:
I have an old job list (Book1.xls) and a new job list (Book2.xls), I want to update cells in a column on the old spread sheet using the new one. What I have tried to do is write a macro that checks the job numbers on the old job list with those on the new one and when it finds a match pastes a cell from the relevant row on the new sheet to the old one. However if it doesn't find a match for a particular job number on the old job sheet it pastes the new job number onto sheet2 in Book1.xls. I hope that makes sense! This is the macro that I have writen:

Option Explicit
Dim Wbk1 As Object
Dim Wbk2 As Object
Dim i As Integer
Dim j As Integer
Dim k As Integer

Sub Macro1()
'

i = 1
j = 1
k = 1

Set Wbk1 = Workbooks("book1.xls&quot.Worksheets("Sheet1&quot
Set Wbk2 = Workbooks("book2.xls&quot.Worksheets("Sheet1&quot

Do
If Wbk2.Cells(i, 2) = Cells(j, 2) Then
Wbk2.Cells(i, 3).Copy Destination:=Cells(j, 3)
i = i + 1
ElseIf Wbk2.Cells(i, 2) = "" Then Exit Do
i = 1
j = 1
k = 1
ElseIf Wbk1.Cells(j, 2) = "" Then
Wbk2.Cells(i, 3).Copy Destination:=Worksheets("Sheet2&quot.Cells(k, 3)
k = k + 1
i = i + 1
Else
j = j + 1
End If
Loop
End Sub

What is happening is that it seems to keep looping and doesn't stop. If anyone could give me some pointers at to where I am going wrong that would be great

Thanks in advance

 

[xlbo]

I'd go about this slightly differently - I don't use Do Loops very often:

nRow=1
Set Wbk1 = Workbooks("book1.xls&quot
Set Wbk2 = Workbooks("book2.xls&quot
for each c in wbk2.sheets("Sheet1&quot.range("A1:A" & wbk2.sheets("Sheet1&quot.range("A65536&quot.end(xlup).row)
with wbk1.sheets("Sheet1&quot
set fCell = .columns("A&quot.find(c.text,lookin:=xlvalues, lookat:=xlwhole)
If not fCell is nothing then
'match found
c.offset(0,3).copy destination:= fCell.offset(0,3)
else
c.offset(0,3).copy destination:= wbk1.sheets("Sheet2&quot.cells(nRow,3)
wbk1.sheets("Sheet2&quot.cells(nRow,1).value = c.value
end if
end with
next Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[BG123]

Geez that was a quick response,thanks for that Geoff. I've just ried to run it and had a compile error come up pointing out that the variable c and nRow were not defined, so I defined nRow as an Integer but didn't know what to do about c. I noticed that c is used quite a bit in your code, what should I declare this variable as.

Thanks
Brent

 

[xlbo]

Ooops - forgot about the option explicit
I would define nRow as long
because I don't know how many records you have and integers top out at apprx 32500 (ish)

c should be defined as RANGE

HTH Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[BG123]

Geoff
It works, that is excellent, thanks so much!! Gotta sit down now and run through you code to try and understand it!!
thanks again
Brent

 

[xlbo]

Just noticed an error:

c.offset(0,3).copy destination:= wbk1.sheets("Sheet2&quot.cells(nRow,3)
wbk1.sheets("Sheet2&quot.cells(nRow,1).value = c.value
nmRow = nRow + 1

Basically, it doesn't rely on anything being in order
Just loops thru your 2nd workbook and uses the FIND method to determine whether the job exists in the 1st then either adds a cell in sheet1 or sheet2, depending on whether it was found or not Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[BG123]

Got it, just changed nRow to nRow + 1 and it works perfectly. And thanks for the explanation of how the code works
Cheers
Brent