Vba data adding in column

I knw how to add data in rows like adding data in a1,b1,c1.

Please post your code and I will modify it for you
(select the text and then click on # icon above to include code tags)

How is the data being added?
- user input?
- another worksheet

The respective sheets do not contain a column for latest data in kg
They do contain a column for latest data price
I think column 2 on userform2 should be latest price?

If I am incorrect, please explain where the value in UserForm2 should go

StandardMix.jpg

OneDelivery.jpg

UserForm2.jpg

No problem - I will post amended workbook tomorrow

The layout of your workbook is not good
-the way it is currently laid out will make it very messy to change anything later
- VBA is not like Excel - if you insert a row in Excel , then Excel adjusts all the other cell references - VBA cannot do this
(if we tell VBA to put a formula in G18, and we insert a row above A3, VBA will not know that it now should put the formula in G19 and that it needs to add another row into the sum)
- there are totals at the bottom which drive average calculations at the top, and data is added into rows between the two
- if you add a "new" chemical, then you will need to rewrite some of the VBA
- that is storing up lots of potential problems for the future

I suggest your daily data is put into one sheet and that the information you want to see is generated as you want it using a lookup formula
- it will look exactly the same, but it means your VBA will be much simpler
- it also means you will be able to change things yourself if necessary

I will post my suggested approach tomorrow

Try this:

Download attached file and click on Add Data
- add some data and check the results

Does it do what you want?

Notes
Most userform values are dependant on the fragrance name
- that is key to almost everything

Adding new data
- updates 1 of 3 sheets with names beginning "Data..."
- updates 1 of 3 sheets (Lilly, Lotus, Rose)
- a column is added in each case

If you want to edit names of chemicals in worksheet "Lists" NOT in the other sheets
- column A in all other sheets refer to these lists
- 4 (dynamic) named ranges created Fragrance, Lilly, Lotus, Rose
- these named ranges are used by the VBA
- by linking everything together in this way it means all changes are consistent everywhere

Sheets Lilly, Lotus, Rose are now formula-driven EXCEPT for column B ( = kg )

Private Sub OnlyNumbers included to ensure that user enters numbers in Price boxes

There is also a check to ensure that all price boxes are completed

Dates are not being validated - you need to do that at some point - bad values will cause the match function to fail.

Before you start amending the workbook you need to properly understand the VBA - how it works, which areas of the workbook it uses etc

You are welcome
Please click Thread Tools (top of thread) and mark thread as SOLVED
thanks

which user form 1 or 2?

This should resolve your problem with userform1

- button "New Enquiry" added to userform 1
- click on button to start a new enquiry
- this makes the userform unload and then re-load and clear previous values
- some boxes are disabled to prevent user changing the values without clicking on new button
- the fragrance dropdown now appears automatically

Please Login or Register  to view this content.

Userform1 works correctly every time for me
I have tried it with both Excel2016 and Excel2010
I click on New Enquiry, it clears the form, I select another fragrance, another date
I click on New Enquiry, it clears the form, I select another fragrance, another date
I click on New Enquiry, it clears the form, I select another fragrance, another date
etc
etc
50 times without error

It's showing error

What is the error?
What does the message box say?
On which line does VBA stop?

Which version of Excel are you running?
Are you testing this in my file or have you moved the code to another file?

thanks

In post#5, I wrote:
"Before you start amending the workbook you need to properly understand the VBA - how it works, which areas of the workbook it uses etc "

In the next post, I will attach some notes to help you understand how the code works

How VBA works
The first think you need to understand is that VBA does not behave like Excel.
- VBA does not think - it does exactly what it is told to do.
When you change sheet names in Excel, formulas are updated automatically, VBA does not do that.
When you insert rows and columns in Excel, formulas are updated automatically, VBA does not do that.
When you automate things you must be methodical, otherwise VBA becomes complicated to write.

Your code
VBA was instructed to update specific worksheets based on the "fragrance" selected by the user.
So when user selects "Lotus", worksheets "Lotus" and "DataLotus" are updated
If you change the sheet name, VBA is still looking for the old name and the code fails.
Or if you change the fragrance to "LotuX", VBA will seek to update sheets "LotuX" and "DataLotuX" and the code fails if they do not exist.

How the code currently behaves
- 2 worksheets are updated
the name of first sheet = "fragrance name"
the name of second sheet = concatenation of "Data" + "fragrance name"

Sheet naming
If you use above rule when creating sheet names, the code will always work.
If you don't then then code will fail
If you do not like that rule then change the code.
It is your code

Detailed explanation of the code for you, to help you understand what you need to change

1. The "Fragrance" combo box dropdown values are derived from
RowSource:fragrance

2. fragrance is a Named Range in the workbook

3. named range "fragrance" gets its values from worksheet "List" (column A)
RefersTo:=OFFSET(Lists!$A$1,1,0,COUNTA(Lists!$A:$A)-1,1)

4. the value selected by user in the combo box (rose,lotus,lilly) drives other values in the code

5. "fragrance" used to determine correct worksheet name

6. if you change sheet names then you must alter the code to match


How sheet names are determined in the code:

For this explanation we assume user chooses Lotus in fragrance combo box

At the top of the code, fragrance was declared as a string variable
Dim ..........fragrance As String

Later in the code, fragrance takes its value from what you select the the combo box
fragrance = ComboBox_fragrance
(example: variable fragrance = [COLOR=#008080"]Lotus[/COLOR])

Later in the code, fragrance is used to determine two worksheet names:
Set ws = Sheets(fragrance)
(example: ws = Sheets("Lotus")
and
set ws = Sheets("Data" & fragrance)
(example: ws = Sheets("DataLotus")

Before changing the code
- decide on the "pattern" that you will always use for naming the 2 sheets

If you provide me your new pattern, I am happy to help you amend the code


Row source and named range Fragrance

RowSourceProperty.jpg

NamedRangeFragrance.jpg

Today I will send you updated code that will make it easier to add a new fragrance
I will make sure that it is not difficult for you.

As I update each area of code, I will post it separately and explain what it does
- I am changing a few things in the old workbook so do not try to make it work in the old workbook

......

I apologise if I did not explain things properly with previous version. I hope I did a better job below:
- new file is attached
- see next post for explanation of VBA when new fragrance is added
- If while you are testing the event trigger stops working (you will know this because nothing happens when you try to add a new fragrance), procedure included to enable events again
it in sheet "Lists", sheet module and run with {CTRL} r

Please Login or Register  to view this content.

NOTES

Changes from previous version
- to make future maintenance easier layout of both sheets is now almost identical
- procedure to add a new fragrance is now automated
- fragrance named ranges are no longer in sheet "Lists"

New fragrance "Mint" used as the example to explain everything

Adding a new fragrance
To add new fragrance "Mint"
- click on Add Fragrance
- takes user to sheet "Lists"
- add fragrance "Mint" in next available cell
NOTE - all spaces are automatically removed from fragrance name

This auto-creates
- sheet "Mint"
- sheet "DataMint"
- named range "Mint"
NOTE - a message box appears if either the fragrance,sheet name or range name already exists

In the 2 new sheets
- columns A and B are auto-created

Named Range
- the named range is dynamic
- named range Mint is based on the values in column A in sheet DataMint
(ReferTo =OFFSET(DataMint!$A$2,1,0,COUNTA(DataMint!$A:$A)-1,1)

User can
- change chemical names text in column A in sheet "DataMint"
- insert kg values in column B in sheet "DataMint"
(warning appears on sheet "Mint" if kg is missing)

Beware
- all values in sheet "Mint" are derived either from formula or from VBA
- user should not amend sheet "Mint"

Deleting a fragrance created in error
- delete the named range
- delete the 2 sheets
- delete fragrance name on sheet "Lists"

To add new data
- click on "Add Data" in sheet Forms
(no change - userform2 looks the same as before)

Data input error
- if user makes an error when inputting data, make manual correction on sheet "DataMint"

Explanation of VBA when fragrance is added

Everything is controlled by one procedure, which calls several other procedures:
(All procedures are in sheet "Lists" SHEET module)

Please Login or Register  to view this content.

NOTE
refer to sheet "Lists" sheet module for the individual procedures
I have tried to put in notes where an explanation is required

I may not be on the forum until July after today

File is now attached
Please test everything today and I will try to answer all your questions

Hi Vipul, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.