Identify column with specific text; use column reference in rest of code

[zookeepertx]

Hello all!

This has been driving me nuts for 3 days now, so I'm coming here for help!
Let me just say that, in words (below) this looks just stupid complicated, but I hope the attached images will make it clearer! I'm at work and am not allowed to download XL2BB to attach a workbook, so I'll have to just upload a couple of images and hope someone will take pity on me and look at this anyway.
At the heart of the matter, I need to figure out how to identify the column letters/number where 13 different instances of 13 different words are located and refer to those letters/numbers within the rest of the code.

I have 2 workbooks and I need to transfer the data from 1 to the other, however, they aren't formatted the same. The first 1 has a tab I'll call "Invoice"; the 2nd is a template and has 2 tabs I'll call "AB" and "CD".
The Invoice sheet will be open and the person needs to run the macro, have it follow the path to the template and let the user choose which of the 2 tabs in which to put the data.
Here's where it gets messy: The person that originally enters the data into the Invoice may (or may not) insert columns - possible even delete a column - as they're working. So, I can't refer specifically to hard and fast columns; I need to find the header text and use that to tell the macro which columns contain the information to send to the template! (I know, it's crazy; I don't know why they can't leave it alone, either)
Also, any data that needs to be transferred, I've colored the text red, to (hopefully) clarify things. I'll just use the tabs Invoice and AB for the example.

So, here's my initial idea; feel free to go a whole other direction if you think it'd be better:

• On Invoice, in row 4, I need to find the word "CONSIGNEE:" and refer to that column to get the 6 rows of data directly under that to put on AB under Vendor Name (B10-B15)
• On Invoice, in row 12, find "DEPT" and "PO" and refer to those columns to get the data directly under them to put the data on AB beside Dept: and PO#. (Data into C16 and E16). *** NOTE: If there's more than 1 PO# under PO on Invoice, those would preferably go on AB, E16, concatenated with ", " between them.***
• On Invoice, in row 7, find "RTV#" and refer to that column to get the data from the next cell to the right and put that on AB directly under RTV# (B19)
• On Invoice, in row 8, find "RA#" and refer to that column to get the data from the next cell to the right and put that on AB in the next cell to RA#. (Data into G16)
• On Invoice, in row 16, find "VENDOR" and refer to that column to get the data from 2 cells below and put that on AB in C19. (I used row 16 because there's a second instance of "STYLE" in row 17
• On Invoice, in row 17, find "STYLE or PIM", "DESCRIPTION", "COLOR", "SIZE", "Qty" and "Total Price". Refer to those columns to get the data to go on AB into D19-I19. Then continue down the Invoice data, pasting the subsequent data into the template.
• On Invoice, in row 16, find "LESS DISCOUNT" and refer to that column to get the data from row 17and put it in AB in column G beside the word DISCOUNT in F

If there aren't enough rows in AB to accept the number of rows from Invoice, I need to insert enough rows to handle them. (The end of the range on AB is, of course, the row with "DISCOUNT" in column F, as shown in the example).

If you've read this far, I bow to your patience. I'll be forever grateful to anyone that can offer a suggestion!

Jenny

INVOICE

sheet AB

 

[steve the fish]

Heres a method to do one. Maybe come back if you get stuck once you have tried to do the rest?

With Sheets("Invoice")
    CONSIGNEE = Application.Match("CONSIGNEE:", .Rows(4), 0)
    If Not IsError(CONSIGNEE) Then
        Sheets("AB").Range("B10:B15").Value = .Range(.Cells(5, CONSIGNEE), .Cells(10, CONSIGNEE)).Value
    End If
End With

If you are positive then it will never error, ie will always find the search term, then it could be shortened.

 

[zookeepertx]

Heres a method to do one. Maybe come back if you get stuck once you have tried to do the rest?

With Sheets("Invoice")
    CONSIGNEE = Application.Match("CONSIGNEE:", .Rows(4), 0)
    If Not IsError(CONSIGNEE) Then
        Sheets("AB").Range("B10:B15").Value = .Range(.Cells(5, CONSIGNEE), .Cells(10, CONSIGNEE)).Value
    End If
End With

If you are positive then it will never error, ie will always find the search term, then it could be shortened.

Thanks for the reply!

I tried it several times just now and nothing happens when I run the code. If I step through it, when it passes the line CONSIGNEE = Application.Match("CONSIGNEE:", .Rows(4),0) and the cursor/yellow highlight is on the next line, if I hover over the word CONSIGNEE in the previous line (that I quoted above), it says error 2042. Naturally, I googled that error and it apparently means that the code didn't find that word, even though the word is right there, staring me in the face, LOL! It is bolded and italicized on the actual sheet, so I removed those 2 formats but it didn't help.

Also, I AM positive that some of the search terms should always be found, but some of them - not sure which ones - might not, so its probably better just to assume that none of them are guaranteed.

Thanks
Jenny

 

[steve the fish]

You may think its there staring you in the face but ill guarantee it isnt

Taking your example invoice sheet. What does this produce:

=LEN(B4)

 

[zookeepertx]

You may think its there staring you in the face but ill guarantee it isnt

I have absolutely no doubt about that. Right in plain sight is the best place to hide from me, LOL!

That LEN gave me a result of 11. Turns out there was a space after CONSIGNEE:
I really should have caught that; removing the space made it work. I also tried changing it to "*CONSIGNEE:*", since I can't control whether someone else might add spaces later and that worked when I tested it.

Thank you! Also, I appreciate you not just writing out the entire code; I REALLY like to get a bit of help and then see if I can get the rest of any project finished by myself. I feel like I at least accomplished SOMETHING that way!

So, just a quick question: do I need to create a With...End With for each of the search terms? Or is it possible to encompass them all within 1 With...End With. I'm pretty sure I have to do a separate one for each, but just thought I'd check. Hoping to shorten the code.

Thanks!
Jenny

 

[steve the fish]

They can stay within the same With. The With is just there to stop you having to keep typing Sheets("Invoice") all the time you can just use a dot/ period.

 

[zookeepertx]

Perfect! Thank you so much!

I'll come back if/when I get stuck again, but hopefully I'll be able to piece the rest of this together.

Have a great day!
Jenny