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.

Basically, 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.

'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

However, Workbook B does not account for any of the 'flagged' items in
workbook A
However, I need this new macro to take the data from column A in the
first workbook, and run it through Workbook B (column B). If it were to
find a match, it should change column A to another ID value (lets say
5000).

For simplicity, I'm just looking for name matching, I'm sure I could
add in location matching once the foundation is in place.

Thank you for any assistance you can provide.

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
if rng.offset(0,1).Value = _
cell.offset(0,1).Value then
cell.offst(0,-2).Value = 5000
end if
set rng = rngB.FindNext(rng)
loop until rng.Address = sAddr
End if
Next




--
Regards,
Tom Ogilvy



"mazzarin" wrote in message
oups.com...
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.

Basically, 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.

'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

However, Workbook B does not account for any of the 'flagged' items in
workbook A
However, I need this new macro to take the data from column A in the
first workbook, and run it through Workbook B (column B). If it were to
find a match, it should change column A to another ID value (lets say
5000).

For simplicity, I'm just looking for name matching, I'm sure I could
add in location matching once the foundation is in place.

Thank you for any assistance you can provide.

Thank you for your assistance. I'm receiving some compile errors (loop
without do) but I'll try manipulating the code.

Unless you have a better idea of course :)

your right, I left out the Do statement:

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.offst(0,-2).Value = 5000
end if
set rng = rngB.FindNext(rng)
loop until rng.Address = sAddr
End if
Next

--
Regards,
Tom Ogilvy


"mazzarin" wrote in message
oups.com...
Thank you for your assistance. I'm receiving some compile errors (loop
without do) but I'll try manipulating the code.

Unless you have a better idea of course :)

Thanks for your help again!!!