Trying to use VBA

[BB]

Hello,
I'm attempting to use VBA, and am wanting to create a purchase order.
I have a sheet which is a stocklist, containing a 'code' (numbers and
letters) column, description column, cost price, sell price and quantity in
stock. I also have a supplier sheet, which has a 4 letter supplier code,
supplier name, address, phone and email, all in seperate columns. On my
purchase order, i want to create a button (have succeeded there...) which
when pressed changes the purchase order number, consecutively, starting from
say 10001 (have failed so far...). Also, want to enter the company code, and
have their name and phone number appear. Then, I have a boxed area, and
under the "code" heading, i will enter the appropriate code, and i want the
description and cost price to appear, under their appropriate column
headings, this may be several pages long, and i want to have it total at the
end. In most cases i will then print this page/pages out and fax to the
relelvant supplier.

Have set myself a big task, i fear, however, does anyone have any hints on
where to start??? Any help would be greatly appreciated.

Merry Christmas.

 

[carlo]

Hey BB (Funny, that's what I usually call my Big Brother )

You can add following code to your Button:

'-----------------------------------------------
dim MyRange as range
dim Value_ as Double

Set MyRange = worksheets("Sheet1").range("A1")
'this has to be changed to your specific sheet and range

Value_ = MyRange.value + 1

MyRange.value = Value_
'-----------------------------------------------

For the other Problem, I would make an unprotected cell where you can
enter your "code" and in the other cells you can enter a vlookup
formula.
Of course you could also make a userform, but I think that would go
too far.

If you have a question to the above mentioned or to something else,
just ask...i will keep an eye on this thread.

Carlo

PS: When posting, try to include your Excel version and code if you
already have, that way we can easier see where you need help.

 

[BB]

Hello Carlo,

Thank you! That code worked perfectly for the button, and i'm working on
the vlookup formula.

Thanks again, I appreciate you help Carlo

 

[carlo]

You're very Welcome, thanks for the feedback.

If you have any questions, just post them

Carlo

 

[BB]

Hey again Carlo,
I've entered this formula on my Purchase Order sheet:
=IF($C$11=Suppliers!A2,Suppliers!B2), and i'm wondering how i amend it for
when i change the supplier code in the Purchase Order cell C11, so that the
correct corresponding info in the supplier sheet, cells A5 and B5 etc, is
displayed?

Am only new at this, and am having a complete mental blank (not that there
was much there before....)

Thank you once again.

 

[carlo]

Hey BB

thats what the vlookup formula is for, try to look it up in the help,
as it has a better example then i could produce right now.

basically you give the vlookup formula a value, and a area where it
should look for it.
let's say the value is "ABC123" and the area is "Suppliers!A2:C100"
(Note, Vlookup will only look for the value in the first column of the
area, the other columns are used for the returnvalue)
Then you tell Vlookup which column you want to return, where column 1
is the column you were looking in
In the end you tell Vlookup if it should look for the exact value or
something similar, True = similar / False = exact

so your formula should look like:
=vlookup(C11,Suppliers!A2:C100,2,False)
and
=vlookup(C11,Suppliers!A2:C100,3,False)

hope that is more or less clear

Carlo

 

[BB]

Thanks Carlo, will follow your instruction.

Have a Merry Christmas, and thanks once again for all of your help!

 

[carlo]

Thanks Carlo, will follow your instruction.

Have a Merry Christmas, and thanks once again for all of your help!









- Show quoted text -

You're very welcome....merry christmas to you too.

Thanks for the feedback

Carlo