Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA IF Statement, VLOOKUP and LOOP?

Status
Not open for further replies.

Joycelet

IS-IT--Management
Mar 26, 2003
79
GB
Hi All

I'm looking for some help with a couple of spreadsheets I have - I know what I want to do, just don't know how to do it!

I have a spreadsheet which is a download from a mainframe this is linked to another spreadsheet using vlookups to pull through the information based on a unique ID - works fine.

What I now need is to copy the values from the middle spreadsheet, which is in one workbook to a new worksheet in another workbook based on a unique identifity. I guess i need something like this but in code, they want to be able to run it from a button on the middle sheet?

VLOOKUP/MATCH Unique ID - if match then

If cell <> then copy cell from middle file, then mark cell A1 * (denotes change) then more to next cell in record then move to the next record all by the touch of a button"

So I thinking something along the lines of a vlookup code then an IF statement looping to next

Any pointers would be great as I'm completely lost on where to start.

Thanks in advance

NJ
 
Hi
I think I've got a little confused in your description of what you want but it may help to look at the possibility of filtering your data on the ID you require then copying that data out.

I got a little confused by this line "the middle spreadsheet, which is in one workbook" as to where your data currently is. Is it in the book pulling in the data from the mainfram or is it in the book containing the source for your VLOOKUPs?

Also, is the destination workbook a new book or an existing book?

Finally, what happens to the data once it's been copied to its new 'home'?

Your post gives me the feeling your no novice so if my assumptions on your requirement are correct see what you can come up with and post back. BTW I've no idea what the code for filtering is - I record it EVERY time!!

Happy Friday!

;-)
If a man says something and there are no women there to hear him, is he still wrong? [ponder]
How do I get the best answers?
 
Hi

I don't think I explained myself very well - it's that Friday feeling!

Book One - Mainframe Data, raw format

Book Two - VLookups data from Mainframe into correct formats etc.

Book Three - Static data that needs to be updated from Book Two in the correct format. This book then gets emailed out to another client.

What I'm looking for is the data from Book Two to be copied across to book three if it is different - matching against a unique code.

I guess the code should be something along these lines but it "proper VBA"

Dim X as UniqueID

UniqueID Match UniqueID in Book 3

If Yes then check to see if each cell in the record is newer or updated

If Yes then mark first cell in record as * and copy data

Move to next cell in record

When complete move to next record

If No then do nothing and move to next record

Does this make it any clearer?

I could email sample data if that would help?
 
>>"Does this make it any clearer?"
Yes, but I still have questions!

>>"I could email sample data if that would help?"
It would help but I don't have time to look at it properly (I did this morning when I was scraping around for something even vaguely interesting to do but things have changed!!) And apart from that it's not really in keeping with the site policy. Others may be able to give input if we deal with the whole thing online.

Anyway, more questions - well one really

Can I just confirm my understanding - are you just looking up a series of records in book3 to see if they have been updated in book2 and if so copy the updated record over to book3?

Also, how is your VBA? If I were to outline an idea for you could you begin coding yourself? It's just that I'm not in a position to code something from scratch and test it etc - as I said before I could've this morning but....

Happy Friday

;-)
If a man says something and there are no women there to hear him, is he still wrong? [ponder]
How do I get the best answers?
 
Hi

Thanks for your response - keeping it online is great and I don't want to get into trouble.

Basically Yes - That's what I'm wanting to do with the odd check along the way.

VBA patchy but I have basic grasp so anything you could throw my way would be very very much appreciated.

 
OK I will look at this but it will be tomorrow (that's right, I have no life!)

I just need to know if the record set to check is a fixed length (rows and columns in book3) and if the length of each record in book2 is the same (no of columns) as in book3?

Happy Friday

;-)
If a man says something and there are no women there to hear him, is he still wrong? [ponder]
How do I get the best answers?
 
Hi

Thank you - I will be online tomorrow.

They could in theory be a different number of records in each - but the "master" so to speak is Book3

HTH

NJ
 
Hi
I've had some problems with this in that everything I do is going t1ts up! Really fundamental stuff like being able to access a workbook without it being activated is giving me range and application errors, which simply isn't right.

Unless it was a new feature introduced with xl2003!

I'll try to get another look at this tomorrow (Monday) night but I can't promise at this stage.

;-)
If a man says something and there are no women there to hear him, is he still wrong? [ponder]
How do I get the best answers?
 
Hi

thanks for having a look - I'm playing around with it this end as well, having problems with looping from cell to cell and then record to record... but I always have problems with looping!

Again Thank You and if I get anything working in a fashion I will post it here
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top