Excel VB pointers needed

[evild]

I have 2 spreadsheets.
Both have the same columns except for an additional 2 columns of data on spreadheet 1.

I want to import the data that is in spreadsheet 2 into spreadsheet 1 and add default values into the two extra colmns.

for example I may have:-

spreadsheet 1:
name address number status letter no.
john yavin 0800 NC 1
james vulcan 0870 NCUR 2

spreadsheet 2:
name address number
sam romulus 0999

I want to have spreadsheet 1 import the data in spreadsheet 2 such that:
spreadsheet 1=
name address number status letter no.
john yavin 0800 NC 1
james vulcan 0870 NCUR 2
sam romulus 0999 NC 1

Note the extra data from the status and letter column. I want default values to be added when the data is inserted.

What is the best wasy to achieve whis using a macro [i.e. vb]? Should I open spreadsheet 2 while in spreadsheet 1 and copy/paste lines etc using code?

I want also to check that an entry does not already exist before inserting. If there is an entry already it will not insert that line data and move to the next.

The reson for this is that a new spreadsheet is created once a month and I want to create a macro that will update a current spreadsheet with the new data.

Some pointers would be great. As well as some vb help

 

[VBAva]

Hello evild

my suggestion would be to record a macro first doing all the actions that you want(the copying and pasting), and then look at the code to see how it is done. from here you will have a good starting point for your program. then add in the procedures to check if the data alredy exists

to check if the data already exists you could get the contents or a cell in sheet two, maybe the name cell, and then search the used range in sheet1 to see if it is already there.
maybe there would be two people with the same name, but their addresses are different, so if you find the name maybe you should check the cell to the right and see if the address is the same.
if they do not exist then copy the data over.

maybe you need to do this for every person?


just off the top of my head in a bit of pseudo code, this should check for one person
(probably a fewmistakes but hopefully will give you an idea)


NameVariable=sheet2.namecell.text
AddressVariable = sheet2.addresscell.text

for each cell in used range(sheet1)
if NameVariable = activecell.text then
if AddressVariable = activecell.offset(0,1).text
end
else
do copy and paste
end if
else
do copy and paste
end if
next

 

[xlbo]

evild - you may get a better response if the thread title is more appropriate to the problem - something like
"Matching data and entering default values if missing"
would be more appropriate for a thread like this

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[evild]

Yes i realised that once I had submitted - but it was too late by then. Apologies.

 

[xlbo]

no probs - just thought I'd mention it for future ref.

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244