VLOOKUP to Determine Different Options

[freybe06]

Hello Everyone,

I have attached a sample file of the data that I am working with.

Here is what I'm looking to do:

In column B of the Message tab there is a 9 digit code (JohnJohn1, BillBill1, etc.). I need the macro to look at this code and do a vlookup to Sheet1 to see if it is a "yes" or "no" code. If it is a "yes", then I'll need the macro to then go back to the Message tab and change the dates in column H and I using the "DateChanges" tab.

For example:

In cell B1 of the Message tab you will see "DATID(1,JohnJohn1). I need a vlookup to go and find "JohnJohn1" in Sheet1 and if the value in column D (in this case, since JohnJohn1 is in A1, I'll need it look at D1) is a "yes" then the dates on the Message tab in cells H1 and I1 will need to change. To find out what they need to change to, the macro will need to find those dates in column A of the "DateChanges" tab and then change them to the corresponding date in column B. So for this example, the date that is in H1 and I1 of the Message tab is the same as the date in A1 of the DateChanges tab. Therefore, since JohnJohn1 is a "yes" on Sheet1, both H1 and I1 will need to be updated to the date that is in B1 of the DateChanges tab.

On the other side, if JohnJohn1 was a "no", I would only need the date in I1 to change. H1 could stay the same.


I know it sounds a little crazy but I think the solution is a lot easier than it sounds. I'm just kind of lost on how to go about this. If anyone could help, I'd greatly appreciate it!

Thanks!


TestBook1.xlsx

[dilipandey]

HI Freybe,

You desired results are in column K & L in below file:-
TestBook1.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[freybe06]

Hi Dilipandy,

Thanks for the help. However, I'm looking to do this as part of a larger macro so I need to do it in VBA behind the scenes. Also, for the lines where the vlookup returns a "no", I will still need to change the dates in column H. And lastly, the new information needs to overwrite the old information in columns H and I.

I'd like to be able to do something along the lines of making the code loop through each line of data and if the initial vlookup provides a "yes" then it updates both dates, if the vlookup returns a "no" then it only changes the date in column I.

Let me know if you have any questions or need some more clarifications.

Thanks

[ducky_yeng]

Hi freybe06,

(Diilipandy, hope you don't mind I jump in.)
I answered a similar looking question this morning using VLOOKUP and have modified my code to fulfill your spec.
I have tested it and seems to be working ok with the sample data provided.
I haven't added much error checking just in case values are missing or return "", and have hard-coded some reference data (e.g. columns and ranges to look up), but will leave you to make those updates.
Hope the code works for you.

Please Login or Register  to view this content.

[freybe06]

Hi Ducky,

I pasted this code into the middle of my macro and it went through without any errors, however, it doesn't look like any of the dates changed. Does it matter that I copied from "Dim wkSheet1 As Worksheet" to "Wend"
and pasted it into the middle. Then everything below that, I pasted after my macro ended... So I kind of split your code in half. Would that cause a problem?

Also, I'm not sure if its that or maybe the vlookup. The "JohnJohn1" information that I provided was actually just filler data. The actual data is a random 9 digit series of numbers and letters.

Thanks again for your help! I think we're really close and I appreciate you taking the time!

[ducky_yeng]

Hi frebey06,

The code (e.g. DATID(1,JohnJohn1) extracts JohnJohn1) should be ok as it extracts the 9 characters after the comma ",". If the data format for this code is different then it potentially might have an impact. Assuming that the data setup (i.e. columns referenced and data format are the same) then it should work.

You can paste the two methods SubstituteTextInCell and vlookupVBA anywhere in the module.
Copying and pasting from "Dim wkSheet1 As Worksheet" to "Wend" to the middle of your macro should not be a problem. It only depends on whether the code before and after has any impact on the data. Have you debugged and seen it execute code within the While block and have you looked at the what happens to the Message sheet after it has left the While block?
In addition, instead of copying into the middle of your macro, you could call it using, "Call UpdateData()". It will make it cleaner and easier to debug.

Attached is my version of the excel spreadsheet (v0.1) if that helps. By running the UpdateData macro you can see the columns change appropriately.

Alternatively, attach your spreadsheet and I'll have a look.

Hope that helps.

[dilipandey]

Hi ducky, not a problem at all as we all are here to learn and share



Regards,
DILIPandey
<click on below * if this helps>

[freybe06]

Ducky -

I figured out the problem. My dates were in text format! Once I fixed that, everything works! Thank you, and thank you DILIPandey for all your help! It is very much appreciated.

[dilipandey]

You are welcome freybe06


cheers


Regards,
DILIPandey
<click on below * if this helps>