Help Building Array

[jkelly228]

Hello All,

This goes hand in hand with the thread located here, but it is only a small portion of it. Trying to build an array of Sheet 1 Column A & B and Sheet 3 Column A & B, then compare and where they match move the values from Sheet 3 to Sheet 1.

This is the code that I have so far, and I have never used variables before.

Please Login or Register  to view this content.

[jkelly228]

In building my array I have come across a function that allows me to combine 2 arrays to create a string. The problem with the function though is that it creates a seperate sub routine. Not sure how to use a function without it breaking up my sub routine. Below is the code. Any help on this is greatly appreciated as I am quickly running out of time to finish this.

Please Login or Register  to view this content.

[jkelly228]

Hello All,

Still working on building an array, and I will need that pretty quick. However, right now maybe someone can help me with some simple code fix. In the code highlighted in red it keeps giving me an error (code line 8 in case it doesn't highlight red). Does anyone know what I am doing wrong. I appreciate any help. Thanks

Kelly

Please Login or Register  to view this content.

[Brad4444]

Is the extra period supposed to be in line 8? Between Select and PasteSpecial?

EDIT:

I'd do this

Please Login or Register  to view this content.

[jkelly228]

I'm not sure to be honest. If not, how should it look?

[Brad4444]

Read above code.

[jkelly228]

Thank you, Thank you, Thank you. Brad4444.

I really appreciate the help.

So, do you know anything about creating an array? As you can see from my code I have one specific Item number. Now, I need the system to compare hundreds of Items and Order Types from one sheet to a second sheet and where it finds match do the same copy and paste function as you see in the original code.

Thank you for the help. I really appreciate it.

[Brad4444]

This goes hand in hand with the thread located here, but it is only a small portion of it. Trying to build an array of Sheet 1 Column A & B and Sheet 3 Column A & B, then compare and where they match move the values from Sheet 3 to Sheet 1.

Unfortunately, I am still new to VBA and have a thread myself open on this forum asking for help with an array. So my question is do you have to use an array to do this? As in it was part of the assignment?
Also are you looking for exact matches?
I would read the values of sheet 1 into an array. Then read the values of sheet 3 into a different array. Then it should be fairly easy, I think, to compare them. If you find a match go to that specific cell and copy the values to where you need to go.
The code below is some stuff I was working on earlier this week and I think it might give you some ideas possibly.

EDIT: The code below is used to find the last rows of a column and also looping to do stuff on each row possibly. Sorry I couldn't be more help.

Please Login or Register  to view this content.

Ask your teacher for an extension and you might get it.

[Brad4444]

http://www.excelforum.com/excel-prog...correctly.html

My discussion on arrays. I can make the array just not use it correctly for my purpose.

[jkelly228]

Hi Brad,

Nope, not an assignment for School. Something I am working for my job. I have thousands of rows of data with various Devices on 2 different sheets. I need find how much we are shipping of one device and then paste it into the correct cell of another sheet.

The attached spreadhsheet shows a small portion of what I am looking for. The actual spreadsheet has hundreds of items on more than 20,000 rows of data.

Thanks for the help

Kelly

[Brad4444]

If your due date can wait till Monday I can look at it tonight. I need to learn VBA better anyways so it will give me something to do.

[jkelly228]

Hello All,

Still stuck on the Array function. I have the code all down to where if I identify a specific Item and Order Type then it runs perfectly, but what I need is it to do a mass comparison. This is really holding me up, if anyone can help I would appreciate it. Thanks

Kelly

[jkelly228]

Hello All,

The below code still doesn't work completely, but I think I am closer. The error I get now is 'Type Mismatch' at "If MyArr3 = MyArr1 Then". So not sure what the fix is for that. if anyone can help I would appreciate it.

Thanks

Please Login or Register  to view this content.

[watersev]

hi, jkelly228, can you show real data sample file (100-200 rows will be enough) and result you are trying to obtain?

[jkelly228]

Hi Watersev,

Attached is a report with a lot more lines. The overall look and even the names of the Sheets match the ultimate look of the report. I apreciate any help you can provide. Thanks

Kelly

[watersev]

hi, jkelly228, please check attachment, run code "test

[jkelly228]

@Watersev,

Thank you very much. I really appreciate the help. I am working on moving this over to my production file, but it isn't going as smoothly. I can watch on yours as variable "b" is built and filled in. However, when I move to the production file that same variable is never filled in. It just skips right over.

Below is the code that I am using. As far as I can tell the spreadsheets are nearly almost exactly the same, the only difference is that I am using real Item Numbers. Variable "c" seems to be just fine. Not sure why one would work and another wouldn't. Exact same code.

However, when I copy column A from my spreadsheet and put it in yours, I get the exact same problem. So it seems to be something with my data that is causing it to skip over building the "b" variable. Any idea what could cause this?

Within this code, what is the purpose of the "15"

Please Login or Register  to view this content.

I want to say that I understand most of the rest of the code, but obviously I am still missing something. Thanks again.

Please Login or Register  to view this content.

[watersev]

1. 7 - number of the column for April
2. 15 - number of the column for December
3. Post a sample of the data not working with the code, please

[jkelly228]

Hi Watersev,

It actually did work. I had to change the format of the cells from Date to General and it worked perfectly. Thank you so much for all of the help.

Kelly