Find a variable cell within a worksheet using a macro.


J

J.R.

Good day all,

I have looked through many of the postings on various questions, but because
of limited time I thought it best to post my problem directly. I have a need
to move data from a set location in one workbook, to a variable location
within another workbook called: Order Tracking Log.

My situation: I generate a sequential Master Tracking Number (MAS) for
either sales or service items. I then provide numerous quotes (QT),
acknowledge (ACK) the orders received against those quotes, and then invoice
(INV) once shipped (sometimes multiple invoices against a single order).
Although my QT, ACK, and INV numbers are sequential, and are tied to a MAS #,
they rarely if ever match that MAS number.

My issue: I am currently manually coping data from a range of cells in a
single row within one workbook, then manually moving that data to a range of
cells within my “Order Tracking Log†workbook. I need to automate this
process.

My format: The rows are in descending sequential order by MAS # (Column B):

A B C D I
N O
1 Customer MAS RMA QT ACK PK INV
2 Customer A 003/10 002/10 003/10 002/10 003/10 005/10
3 Customer Z 002/10 001/10 002/10 004/10 001/10 001/10
4 Customer A 001/10 001/10 003/10 002/10 004/10
5 Customer D 156/09 208/09 001/10 004/10 002/10
6 Customer D 156/09 208/09 001/10 005/10 003/10
(PK) = Packing Slip
(RMA) = Return Material Authorization

Following each category (QT, ACK, & INV) is a breakdown of that categories
data:

D E F G H
1 QT Amount Part Cost Labor Shipping
2 003/10 =SUM(F2:H2) $100.00 $100.00 $300.00
3 002/10 =SUM(F3:H3) $150.00 $200.00 $150.00

My requirement: To take the data destined for columns F, G, & H from another
workbook, locate the row identified by a given MAS #, and then paste the data
in columns F, G, & H of the “Order Tracking Logâ€. This process will be
randomly repeated for all three categories; QT, ACK, & INV.

My skill set: I am currently able to callout the “Order Tracking Logâ€
workbook, and then locate the correct worksheet within that workbook, but I
am unable to locate the variable row & column I require to paste the data. I
can move data to a fixed point within a workbook, but my need is to take the
data from a random MAS # and its corresponding QT, ACK or INV, and paste that
data in the correct location.

I am sorry for the lengthy description, but I thought more detail is better
in this situation.

Thank you for your help,

J.R.
 
Ad

Advertisements

P

Patrick Molloy

and you tried the VLOOKUP() function?

J.R. said:
Good day all,

I have looked through many of the postings on various questions, but because
of limited time I thought it best to post my problem directly. I have a need
to move data from a set location in one workbook, to a variable location
within another workbook called: Order Tracking Log.

My situation: I generate a sequential Master Tracking Number (MAS) for
either sales or service items. I then provide numerous quotes (QT),
acknowledge (ACK) the orders received against those quotes, and then invoice
(INV) once shipped (sometimes multiple invoices against a single order).
Although my QT, ACK, and INV numbers are sequential, and are tied to a MAS #,
they rarely if ever match that MAS number.

My issue: I am currently manually coping data from a range of cells in a
single row within one workbook, then manually moving that data to a range of
cells within my “Order Tracking Log†workbook. I need to automate this
process.

My format: The rows are in descending sequential order by MAS # (Column B):

A B C D I
N O
1 Customer MAS RMA QT ACK PK INV
2 Customer A 003/10 002/10 003/10 002/10 003/10 005/10
3 Customer Z 002/10 001/10 002/10 004/10 001/10 001/10
4 Customer A 001/10 001/10 003/10 002/10 004/10
5 Customer D 156/09 208/09 001/10 004/10 002/10
6 Customer D 156/09 208/09 001/10 005/10 003/10
(PK) = Packing Slip
(RMA) = Return Material Authorization

Following each category (QT, ACK, & INV) is a breakdown of that categories
data:

D E F G H
1 QT Amount Part Cost Labor Shipping
2 003/10 =SUM(F2:H2) $100.00 $100.00 $300.00
3 002/10 =SUM(F3:H3) $150.00 $200.00 $150.00

My requirement: To take the data destined for columns F, G, & H from another
workbook, locate the row identified by a given MAS #, and then paste the data
in columns F, G, & H of the “Order Tracking Logâ€. This process will be
randomly repeated for all three categories; QT, ACK, & INV.

My skill set: I am currently able to callout the “Order Tracking Logâ€
workbook, and then locate the correct worksheet within that workbook, but I
am unable to locate the variable row & column I require to paste the data. I
can move data to a fixed point within a workbook, but my need is to take the
data from a random MAS # and its corresponding QT, ACK or INV, and paste that
data in the correct location.

I am sorry for the lengthy description, but I thought more detail is better
in this situation.

Thank you for your help,

J.R.
 
D

Don Guillett

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
 
Ad

Advertisements

J

J.R.

Hi Patrick,

Sorry for the delay in answering.

I have tried the VLOOKUP() function, but I am trying to perform my tack
within a macro, and I was unaware that you could perform the VLOOKUP()
function within a macro.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top