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.

Please Login or Register  to view this content.

'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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.

"mazzarin" wrote:

>
> 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.
>
>
> --
> mazzarin
> ------------------------------------------------------------------------
> mazzarin's Profile: http://www.excelforum.com/member.php...o&userid=32186
> View this thread: http://www.excelforum.com/showthread...hreadid=519316
>
>

[[email protected]]

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:

Please Login or Register  to view this content.

which results in the error:
End If without block If

pointing to line:

Please Login or Register  to view this content.

[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 it runs all the way down to the bottom. On its way, it sets sAddr as Customer A.

Please Login or Register  to view this content.

Then, it jumps back to

Please Login or Register  to view this content.

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

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

[[email protected]]

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.

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

[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

--
Regards,
Tom Ogilvy


"mazzarin" <[email protected]> wrote in
message news:[email protected]...
>
> Okay, after stepping through the code a bit I've established that it
> runs all the way down to the bottom. On its way, it sets sAddr as
> 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 its
> own variables.
>
> This continues constantly, no matter how many times I step through it.
> Going to work on it a bit more.
>
>
> --
> mazzarin
> ------------------------------------------------------------------------
> mazzarin's Profile:
http://www.excelforum.com/member.php...o&userid=32186
> View this thread: http://www.excelforum.com/showthread...hreadid=519316
>

[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.

--
Regards,
Tom Ogilvy

"mazzarin" <[email protected]> wrote in
message news:[email protected]...
>
> 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
> ------------------------------------------------------------------------
> mazzarin's Profile:
http://www.excelforum.com/member.php...o&userid=32186
> View this thread: http://www.excelforum.com/showthread...hreadid=519316
>

[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

--
Regards,
Tom Ogilvy


"mazzarin" <[email protected]> wrote in
message news:[email protected]...
>
> Okay, after stepping through the code a bit I've established that it
> runs all the way down to the bottom. On its way, it sets sAddr as
> 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 its
> own variables.
>
> This continues constantly, no matter how many times I step through it.
> Going to work on it a bit more.
>
>
> --
> mazzarin
> ------------------------------------------------------------------------
> mazzarin's Profile:
http://www.excelforum.com/member.php...o&userid=32186
> View this thread: http://www.excelforum.com/showthread...hreadid=519316
>

[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.

--
Regards,
Tom Ogilvy

"mazzarin" <[email protected]> wrote in
message news:[email protected]...
>
> 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
> ------------------------------------------------------------------------
> mazzarin's Profile:
http://www.excelforum.com/member.php...o&userid=32186
> View this thread: http://www.excelforum.com/showthread...hreadid=519316
>

[mazzarin]

Got it! Thanks.