Excel VBA checking column/row of data

[Jovillanueva]

Hi,

I have a requirments in excel vba to evaluate and check the column data from other workbook. I would like to filter remarks for all "INTRANSIT" from my workingfile then If the data or tracking# is exist from the source file evaluate if transaction remarks is "Done" or if "Not yet" exist. If remarks from SourceFile is "Done" i will put "RECEIVED" or If "Not yet" i will put "INTRANSIT" in my Working file Remarks column. THank you in advance.



Workbook WorkingFile:
Tracking# Qty Model Remarks
---------------------------------------------------
D-8840862402| 20 llll RECEIVED
D-8840862402| 20 kkkk RECEIVED
D-8840862411| 20 mmm INTRANSIT
D-8840865000| 230 mmm INTRANSIT
D-8840865000| 50 bbbb INTRANSIT
D-8840862450| 20 bbbb INTRANSIT
D-8840862450| 120 bbbb INTRANSIT


SouceFile --- to evaluate if Done or Not Yet

Tracking# Qty Model Remarks
----------------------------------------------------------
D-8840862402| 20 llll Done
D-8840862402| 20 kkkk Done
D-8840862411| 20 mmm Not Yet
D-8840865000| 230 mmm Not Yet
D-8840865000| 50 bbbb Not Yet
D-8840862450| 20 bbbb Not Yet
D-8840862450| 120 bbbb Not Yet

[mike7952]

Can you upload a small sample of the exact data structure of both files?

[Jovillanueva]

Thanks Mike for your prompt reply. attaching a sample data structure.

Please Login or Register  to view this content.

[mike7952]

Give this a try. Change the Const in the "" for your needs.

Please Login or Register  to view this content.

[Jovillanueva]

Hi Mike, Thank you very much. I try the codes and i notice that the column E of working file is overwrite even if the remarks is RECEIVED. Its possible that all the remarks with "RECEIVED" will be as ease and the "IN TRANSIT" will be evaluated to Sourcefile if Done or Not Yet. If Done the IN TRANSIT will be change to "RECEIVED" while if No Yet will be IN TRANSIT or as ease? Then in your codes the columns for working file and the columns for sourcefile should be the same structure of data, I'll give you another set of sample data with a little bit changes. This is a bit closer to actual data. Again, thanks for your time and effort.

[mike7952]

Yes I forgot to reupload the code with the Received and In transit comments. I'm at work now for another 7 hours, I'll reupload it when I get to my computer.

[Jovillanueva]

Thanks Mike. actually your codes is a little bit closer to my requirements.
Btw, its possbile not to use all the columns from MyWorking file as reference liikup to Sourcefile. becuase some of the columns are not exist from the other workbook.
Maybe we can use Tracking#, Model, Qty and type as reference.

[Jovillanueva]

Hi Mike, do you have already the code for received and intransit comments? thanks.

[mike7952]

Give this a try

Please Login or Register  to view this content.

[mike7952]

Give this a try

Please Login or Register  to view this content.

[Jovillanueva]

Thank you mike. try this codes later and i'll give you the feddback.

[Jovillanueva]

SOLVED. Thank you very much mike. its working now.