PDA

View Full Version : Solved: VBA Code Help Requset



dickf
11-23-2008, 01:05 AM
I am trying to update one Excel spreadsheet from another. For a variety of reasons, I do not think linking will work. I need help writing VBA code to execute the project.

Two files are involved, but they have been combined into the single file that I can upload.

Ref A contains a Macro6 that functions the way I want it to, except that I cannot write the following code:

1. I need to start at cell A2 in Ref B and search Column A in Ref A for a like number.
2. Once the like number is found, copy columns B, C, and D from Ref B and paste them as a value into like columns in Ref A on the row with the found (identical) number.
3. If no matching number is found, create a new row in Ref A and paste columns A, B, C, and D from Ref B into A, highlighting those pasted cells with a yellow background or red font, etc.
4. Finally, starting at cell A7 in Ref A, search Column A in reference B for a like number - only numbers, not text. If no like number is found, delete the row in question in Ref A.

Finally, it appears the the spreadsheet is in the form of a GIF file. If so, you cannot see the Macro6 code. In any case, the Macro6 code includes eliminating a number of columns from the original Ref B. I have manually done this for the uploaded file so as to keep under the max file size limit.

I wish I could write this stuff. Ten years ago I probably could have.

Thank you in advance for any help you can give me.

Dick Frederick

10863

rbrhodes
11-23-2008, 02:59 AM
Hi Dick,

Perhaps upload the originals to a free file server?

dickf
11-23-2008, 02:32 PM
Hi Mr. Rhodes (I presume),

Thank you for your interest. Can you suggest any particular web site? I have done this with images but never with an executable.

Thanks again,

Dick Frederick

rbrhodes
11-23-2008, 04:21 PM
Hi,

I've been using http://www.4shared.com/ if I'm not storing them on my own space.

Or you could just email them to me and I could post them.

dickf
11-23-2008, 06:04 PM
Hello, dr,

Unable to post a link to the files because I don't have enough posts yet. So, I am emailing them.

Thanks,

Dick Frederick

rbrhodes
11-23-2008, 07:04 PM
Here's the links for anyone else wants to look at these:

http://www.4shared.com/file/72865102/77a116a1/Ref_A.html
http://www.4shared.com/file/72865209/e2357170/Ref_B.html

rbrhodes
11-23-2008, 11:52 PM
I am trying to update one Excel spreadsheet from another.

1. I need to start at cell A2 in Ref B and search Column A in Ref A for a like number.


So far so good



2. Once the like number is found, copy columns B, C, and D from Ref B and paste them as a value into like columns in Ref A on the row with the found (identical) number.


I think you mean copy:

Ref B Column G to Ref A Column B
Ref B Column K to Ref A Column C
Ref B Column Q to Ref A Column D

As those are the same headings in both files



3. If no matching number is found, create a new row in Ref A and paste columns A, B, C, and D from Ref B into A, highlighting those pasted cells with a yellow background or red font, etc.


Columns as above plus Column A, placed where on the sheet? I put them at the end of the file...



4. Finally, starting at cell A7 in Ref A, search Column A in reference B for a like number - only numbers, not text. If no like number is found, delete the row in question in Ref A.


I did is as it is written.



In any case, the Macro6 code includes eliminating a number of columns from the original Ref B.

I looked at a rewrite of the Macro6 code to eliminate overhead. It's a little sketchy tho. It appears to be turning Ref B into Ref A?

dickf
11-24-2008, 09:46 AM
Thanks dr,

The first part of Macro6 elimiates columns in ref B such that the remaining columns match ref A. Yes, I am trying to make ref A identical to ref B. Why? Ref B is organized and formatted in an unsuitable way and the only way I can get a handle on it is to insure ref A is updated with ref B data.

When adding rows (if ref B contains a line missing from ref A), going to the bottom of the sheet is fine.

Thanks again,

Dick Frederick

rbrhodes
11-24-2008, 02:18 PM
Hi Dick,

Here's the downlaod links BTW

http://www.4shared.com/file/72865102/77a116a1/Ref_A.html

http://www.4shared.com/file/72865209/e2357170/Ref_B.html