Bit of a VBA problem.

[mazzarin]

Hello all,

I'm trying to write up a macro for excel to streamline some things I
have to do regularly, but this one is giving me a bit of trouble. I
don't think it is too complicated, I could probably do it in C++
easily, but VBA is always iffy with me. Luckily, with some help from
others, I got most of it written but I am having a small problem
still.

First some background:
I have two workbooks, each with 1 sheet in them.

'WorkbookA.xls|Sheet1' is more or less static list but it may have
additional entries from month to month (think of it as a 'flag it'
list). It essentially contains information about things. The names are

all in column A (lets say down to 100 for simplicity's sake). The
locations are in column B.


Code:
--------------------
Example:
1) Column A Column B

2) Person A CHARLOTTE
3) Person B JACKSONVILLE
4) Person C HALIFAX

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


'WorkbookB.xls|Sheet1' has a rather large list of transactions which
get assigned an ID in column A by a macro we already have in place. The

names are in column C. The locations are in column D


Code:
--------------------

Example:
1) A B C D


2) 123 Contact A Person A CHARLOTTE (More info)
3) 123 Contact A Person B JACKSONVILLE (More info)
4) 225 Contact B Person A CHARLOTTE (More info)
5) 225 Contact B Person D LOS ANGELES (More info)
6) 334 Contact C Person C HALIFAX (More info)


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


However, Workbook B does not account for any of the 'flagged' items in

workbook A. In the above Workbook B example, rows 2,3,4, and 6 should
be flagged.

So I need this macro to take the data from column A in the
first workbook, and run it through Workbook B (column C). If it were to

find a match, it should change Workbook B (Column A) to another ID
value (lets say
5000).
For simplicity, only name matching was done at first, here is the code
thus far:

Code:
--------------------
Dim rngA as Range, rngB as Range, rng as Range
Dim cell as Range, sAddr as String
with workbooks("WorkbookA.xls").Worksheets("Sheet1")
set rngA = .range(.Cells(1,1),.Cells(1,1).end(xldown))
End with
With workbooks("WorkbookB.xls").Worksheets("Sheet1")
set rngB = .Range(.Cells(1,3),.Cells(1,3).End(xldown))
End with
for each cell in rngA
set rng = rngB.Find(What:=cell.Value, _
After:=rngB(rngB.count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
sAddr = rng
do
if rng.offset(0,1).Value = _
cell.offset(0,1).Value then
cell.offset(0,-2).Value = 5000
end if
set rng = rngB.FindNext(rng)
loop until rng.Address = sAddr
End if
Next
--------------------


I get errors on the following line:
cell.offset(0,-2).Value = 5000

Any ideas?

Thanks for your help.

 

[Al]

try including a carriage return symbol _ on the previous line.

 

[paul.robinson]

Hi
rngA is column 1 so cell.offset(0,-2) is an error. Do you mean
rng.offset(0,-2), so that the 5000 is in workbookB?
regards
Paul

 

[mazzarin]

I have attempted that, and I just tried it again, and here is the code
just to be sure:

Code:
--------------------

If Not rng Is Nothing Then
sAddr = rng
Do
If rng.Offset(0, 1).Value = _
cell.Offset(0, 1).Value Then _
cell.Offset(0, -2).Value = 5000
End If
Set rng = rngB.FindNext(rng)
Loop Until rng.Address = sAddr
End If

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


which results in the error:
End If without block If

pointing to line:

Code:

 

[mazzarin]

Hi
rngA is column 1 so cell.offset(0,-2) is an error. Do you mean
rng.offset(0,-2), so that the 5000 is in workbookB?
regards
Paul

That must be it, it runs!

But it seemingly continues on to infinity - stuck on Loop Until
rng.Address = sAddr

More debugging needed it seems...

 

[mazzarin]

Okay, after stepping through the code a bit I've established that i
runs all the way down to the bottom. On its way, it sets sAddr a
Customer A.


Code
-------------------

Loop Until rng.Address = sAddr

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


Then, it jumps back to


Code
-------------------

If rng.Offset(0, 1).Value = _
cell.Offset(0, 1).Value Then
rng.Offset(0, -2).Value = 5000
End If
Set rng = rngB.FindNext(rng)
Loop Until rng.Address = sAddr

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


and doesn't change any values on the worksheet, nor does it update it
own variables.

This continues constantly, no matter how many times I step through it
Going to work on it a bit more

 

[paul.robinson]

Hi
Do you want to put the update inside your For loop as you have found
the cell you want (so there is no point finding its address)

e.g.
Dim rngA as Range, rngB as Range, rng as Range
Dim cell as Range
with workbooks("WorkbookA.xls").Worksheets("Sheet1")
set rngA = .range(.Cells(1,1),.Cells(1,1).end(xldown))
End with
With workbooks("WorkbookB.xls").Worksheets("Sheet1")
set rngB = .Range(.Cells(1,3),.Cells(1,3).End(xldown))
End with
for each cell in rngA
set rng = rngB.Find(What:=cell.Value, _
After:=rngB(rngB.count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
if rng.offset(0,1).Value = _
cell.offset(0,1).Value then
cell.offset(0,-2).Value = 5000
end if
End if
Next Cell

regards
Paul

 

[mazzarin]

Aha, found (part of) the problem, the loop was looking in the wrong
location!

rng.Offset(0, 0)
cell.offset(0,0) (instead of 0,1 in both)

make it look in the right location and then update the ID, however it
does not move on to check for person B, it only updates the ID for both
instances of person A (and continues to loop infinitely)

 

[mazzarin]

Paul, your method doesn't seem to put a limit as to how far things need
to go. The method above accounts for the final address if I am not
mistaken. However, I will go down your route as well, and report my
results when I get the chance.

 

[Tom Ogilvy]

Dim rngA as Range, rngB as Range, rng as Range
Dim cell as Range, sAddr as String
with workbooks("WorkbookA.xls").Worksheets("Sheet1")
set rngA = .range(.Cells(1,1),.Cells(1,1).end(xldown))
End with
With workbooks("WorkbookB.xls").Worksheets("Sheet1")
set rngB = .Range(.Cells(1,3),.Cells(1,3).End(xldown))
End with
for each cell in rngA
set rng = rngB.Find(What:=cell.Value, _
After:=rngB(rngB.count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
sAddr = rng.address
do
if rng.offset(0,1).Value = _
cell.offset(0,1).Value then
rng.offset(0,-2).Value = 5000
end if
set rng = rngB.FindNext(rng)
loop until rng.Address = sAddr
End if
Next

 

[Tom Ogilvy]

That isn't correct. It has already made the match on the values in rngA and
rngB. You said you would then like to check that the values to the right
also match. This if did that. If you change it as you indicated, then
there is no need in having the test. I have posted some corrected
(although untested) code. Your problem with saddr should be fixed in that.

 

[mazzarin]

Got it! Thanks.