Using Arrays to look up Information.

[Chris101]

I am new to writing VBA., please bear with me.

I have two worksheets I am using. Sheet1 is my source data and sheet2 is my output. What I am trying to do is look up the "calling Feature Description" from column "D" on sheet 2 and match it to the "Calling Feature Description" in column "A" on sheet1. When the calling feature description is found then pull the "Feature Code" associated with the "Calling Feature Description" from Column "B". I want to take the "Feature Code" and place it in Column "E" next to the appropriate "Calling feature description.

For example, on Sheet2 column "D2" the first "calling feature description" is "messaging Unlimited". I want to find "Messaging Unlimited" in "Calling Feature Description" in column "A24". The associated "Feature Code" in "A24" is found in "B24" "Messaging". I want to take "messaging from sheet1 and move it to sheet2 under "Feature Code" column "E2" next to its "calling feature description." Then move down to the next cell in D2 and find "AT&T World connect" and so on until all of the Calling feature Descriptions have a "feature code associated with it." My calling feature descriptions will have around 800 records and will vary monthly. I tried to wright the arrays dynamically.

What happens when I run my code:
It will fill the output arrays, I think correctly, but it will take my heading "Feature code" and the first "feature code" "Messaging" and paste only those two into cell "E25 and E26". On sheet2 below is what happens when I run the code. Not quite what I was hoping for.

I would greatly appreciate any help in how to fix my code to run properly.

I cannot seem to download the app for attaching my spreadsheet. If the workbook is needed, please let me know and I will continue to try and make that work.

Again, Thank you for your time and assistance in this issue.

Best Regards,
Chris

 

[Alex Blakenburg]

On your very last line before the End Sub, change the letter i to the row no 1.

 

[Chris101]

On your very last line before the End Sub, change the letter i to the row no 1.

Alex- Thank you so much for your suggestion. It worked great! I really do appreciate your time and effort in making this code work. Thank You!

Best Regards,
Chris

 

[Alex Blakenburg]

Glad I could help and Welcome to the Forum.
Your code was well written and very easy to follow and the information you provided was really helpful.

Generally you will find it easier if you use the VBA code button in the toolbar rather than having to copy paste multiple pictures fit in all the code. If you want to add formatting use the RICH button.
It also helps us since we can drop the code into a test workbook so we can try to reproduce the issue.

Have a great day.

 

[Chris101]

Thank you.

I am going to look into the VBA code button in the toolbar along with the RICH button as I have a lot of code that I will need to figure out in regard to managing my clients IT invoices. I want to make sure that I make things as easy as possible for anyone that wants to help me.

I do have a question if you have the time? I manage billing for companies. I was thinking about taking an invoice and converting it from a PDF to excel and run code to pull items from the excel to load into my access database in order to validate billing and to keep historical information for management. The problem i see with this is the invoice format changes drastically which I knew it would. Looking at the crazy format it would be very difficult for me to find and pull information from excel using VBA. Instead of converting the invoice into excel can I use VBA to scan the PDF for strings and numbers and then pull that information to place in a workbook. Once in the workbook then I can load into my database.

Thank you for your time and assistance!

 

[Alex Blakenburg]

It would be better if you opened a new thread for trying to read a PDF using VBA, I have not done it before and that way you open it up to get the right people to respond. My go to for reading PDF files is Power Query but then I am generally only interested in extracting particular tables from the PDF.
The only other way I have seen it done in the Forum is to use VBA to pull it in via MS Word before moving the required data into Excel.

In my previous organisation we used commercial software to do this and it required "training" the software to read the format from the various suppliers.

 

[Chris101]

Thank you so much for your time and asisstance. When l get a little closer to starting the project I will open it up to the forum.

You have been a big help.

Have a great rest of the week.