Vba data adding in column

[royalshah4all]

Plz solve my problem.
I knw how to add data in rows like adding data in a1,b1,c1.
But i dont knw how to add data in column like a1,a2,a3.
And i need to add data continuesly in excel like for 1st time it should be add in a1,a2,a3 second time it should be add in b1,b2,b3 like that. Plz guide me. I am new one in vba

[kev_]

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

[royalshah4all]

Respected Sir.
As you said I attached Excel Sheet with VBA. I am dealing with Fragrances. In this Excel there are 3 sheet with different
sheet name.I am purchasing the raw material as per the requirements. Suppose think that I am purchasing Lilly fragrance raw
material for 04-06-2017 date. Then i need to select Lilly Fragrance name in combo box then i need to type said date. after that
I need to type all the new price to said fragrance in said date. I need all the past purchase detail remain in the database.
after that when i need to check price per kg then i need to select fragrance name and date in combo box of userform1.
then it should display price chemical name and per kg price in textbox.
Plz help me. I already created the database and 2 userform.

[kev_]

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

[royalshah4all]

I am extremely sorry. You re right. that should be price. (chemical formula is in kg, that wont be change. only price are changing)

[kev_]

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

[kev_]

No problem - I will post amended workbook tomorrow

[royalshah4all]

Ok. Thanks alot

[kev_]

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

[royalshah4all]

thanks bro... its working fine.

[kev_]

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

[royalshah4all]

bro some problem is there. when i select fragrance name and date i will get the value after that if i select some other fragrance name and different date then its show some error. plz solve that bro .....plzzz

[kev_]

which user form 1 or 2?

[royalshah4all]

userform 1.

[royalshah4all]

sir solve it sir plz.....

[kev_]

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

Private Sub cb_clearValues_Click()
    Unload UserForm1
    UserForm1.Show
End Sub

[royalshah4all]

Sir I cant change the fragrance name. It's showing error. Plz guide me how to change it.

[kev_]

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

[royalshah4all]

reset button working fine now. but how to edit and add new Fragrance name in existing and new sheets. Editing part:- I changed Sheet Name Rose to RoseA. Edited In DataRose to DataRoseA. Edited in "List Sheet" A2 and C1 instead of Rose I edited RoseA. While running its show some error. Plz tell the step to editing and adding new Fragrance NAME

[kev_]

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

[kev_]

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

[kev_]

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

......

[kev_]

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

Sub RestartEvents()
'run this with {CTRL} r
    Application.EnableEvents = True
End Sub

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"

[royalshah4all]

I love u Bro. Thanks alot for everything. You simplified my problem.I am waiting for attachment.

[kev_]

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)

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Cells.Count > 1 Or Target.Column <> 1 Or Target.Row = 1 Then Exit Sub
    
    Dim rng As Range, cel As Range, ref As String, fragrance As String
    Application.EnableEvents = False
  
'if fragrance deleted then delete the cell
    If IsEmpty(Target) Then Target.Delete Shift:=xlUp: GoTo ErrorHandling
    
'remove spaces in fragrance
    Target.Value = Replace(Target.Value, " ", "")
    fragrance = Target.Value
    Set rng = Range("fragrance")
    
'if new fragrance added, then test to see if name already exists
    If ActiveSheet.Application.WorksheetFunction.CountIf(rng, fragrance) > 1 Then Call FragranceExists(Target, fragrance)
        
'now test to see if either or both required new sheets exist
    Call DoSheetsExist(fragrance)
    
'now create 2 new sheets
    Call AddSheets(fragrance)
    
'now format new sheets
    Call FormatNewSheets(fragrance)
    
 'create named range
    Call AddNamedRange(fragrance)
    
'sort fragrance names alphabetically
    Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
    
ErrorHandling:
Err.Clear
Application.EnableEvents = True
End Sub

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

[kev_]

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

[royalshah4all]

Thanks Bro.Its working fine..

[vshah]

I am Vipul and I want customize my invoice with adding customer name, Item Date, Ship Address with automatic invoice. I want to develop this is vba can u pls. help me my email id is v.j.shah@hotmail.com.

Awaiting for your reply.

[FDibbins]

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.