Hoping its an easy question

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

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.

[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
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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

[mazzarin]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[mazzarin]

Thanks for your help again!!!