paste a value in a cell that meets 2 conditions

[katerina75]

Hello everybody,
I use the Excel 7 and I want to copy and paste a value in the cell that meets 2 conditions.
I have 2 worksheets, the first is used for the input

	Fruits	Number
Input	apricots	3

the second is used for keeping the record

	1	2	3	4	5
apples
oranges
bananas
apricots			3
pears

Can you please help me to write a VBA that pastes the value "3" in the correct cell of the 2nd worksheet?
Thank you in advance!

[SamT]

Do you want the "Number" from the input to appear in the same numbered column in the Record Keeping table?

	1	2	3	4
Apple	1
Peaches			3
Cherry			3
Pie		2

[katerina75]

Hello SamT,

thank you for your interest!
No, I don't want the "Number" from the input to appear in the same numbered column in the Record Keeping table. I'm sorry; I probably didn't explain what I want...!
I want to input 3 values in a worksheet, i.e "MrX" wants 2 tickets for the "Concert Z". When the input of the last value (which is the number of tickets) is done, I want this value (in this case the number 2) to appear in a separate worksheet. In this separate worksheet I have a table where I keep in a column all the events and in a row all the customer names.
Is it possible to write a macro that will combine the event and the customer's name and will populate the relevant cell with the number of tickets?
I hope I was as clear as possible...!

Do you want the "Number" from the input to appear in the same numbered column in the Record Keeping table?

	1	2	3	4
Apple	1
Peaches			3
Cherry			3
Pie		2

[Ringhal]

Assuming I understand what you are asking, the attached workbook will be your answer. In the table where it says "input", type in a "fruit", then a number, and then click the button.katerina75.xlsm

[SamT]

If Ringhal's solution doesn't work for you, then I think you should use a VBA UserForm with three inputs, A Customer_Name TextBox; A Venue Listbox; And a Number_Of_Tickets_Sold TextBox.

A little VBA Code in the UserForm will place all those input values in the correct location on the Events/Customers table Worksheet.

A little bit more code can put a TextBox showing the remaining tickets for the selected Venue on the Form

[katerina75]

Assuming I understand what you are asking, the attached workbook will be your answer. In the table where it says "input", type in a "fruit", then a number, and then click the button.katerina75.xlsm

Hello dear Ringhal,
thank you for your answer. Unfortunately this macro doesn't work exactly as I want... I think that the table I've attached, was a misleading one.
I want to input 3 values in a worksheet, i.e "MrX" wants 2 tickets for the "Concert Z". When the input of the last value (which is the number of tickets) is done, I want this value (in this case the number 2) to appear in a separate worksheet. In this separate worksheet I already have a table where I keep in a column all the events and in a row all the customer names.

Any ideas?

[Ringhal]

Hi katerina75
I think I have an idea of what you need, see attached:katerina75 (2).xlsm

The code has no error trapping and probably isn't very efficient. See if that is exactly what you want, and we can make changes/updates if there is anything incorrect.

[katerina75]

PLANNING14.xlsAttachment 10841

Hi katerina75
I think I have an idea of what you need, see attached:katerina75 (2).xlsm

The code has no error trapping and probably isn't very efficient. See if that is exactly what you want, and we can make changes/updates if there is anything incorrect.

Yes!
I believe is exactly what i need but i got stuck! We don't have the same organisation. I will attach my xls file

[katerina75]

If Ringhal's solution doesn't work for you, then I think you should use a VBA UserForm with three inputs, A Customer_Name TextBox; A Venue Listbox; And a Number_Of_Tickets_Sold TextBox.

A little VBA Code in the UserForm will place all those input values in the correct location on the Events/Customers table Worksheet.

A little bit more code can put a TextBox showing the remaining tickets for the selected Venue on the Form

Thank you SamT,
I've tried but I got lost...! I created the UserForm but I don't know which VBA Code in the UserForm will place (via a button I suppose) all those input values in the correct location on the Events/Customers table Worksheet? Maybe this is too complicated for me... I will try Ringhal's solution to see if it works.
Thanks a lot!

[Ringhal]

Hi katerina75

I had some trouble understanding your workbook because not in English.

I think, below, is the correct code for you.

   Sub Button5_Click()
    
    Dim Action As String
    Dim Customer As String
    Dim CustomerCol As Long
    Dim Áñéèìü_èÝóåùí As Long
    
    With Sheets("Sheet1")
        Action = .Range("E4") 'column
        Customer = .Range("N4") 'row
        Áñéèìü_èÝóåùí = .Range("K4") 'number
    End With
    
    With Sheets("planning")
        CustomerCol = .Range("14:14").Find(Customer).Column - 1
        .Range("A:A").Find(Action).Offset(0, CustomerCol) = Áñéèìü_èÝóåùí
    End With
    
    
   End Sub

[katerina75]

Hi katerina75

I had some trouble understanding your workbook because not in English.

I think, below, is the correct code for you.

   Sub Button5_Click()
    
    Dim Action As String
    Dim Customer As String
    Dim CustomerCol As Long
    Dim Áñéèìü_èÝóåùí As Long
    
    With Sheets("Sheet1")
        Action = .Range("E4") 'column
        Customer = .Range("N4") 'row
        Áñéèìü_èÝóåùí = .Range("K4") 'number
    End With
    
    With Sheets("planning")
        CustomerCol = .Range("14:14").Find(Customer).Column - 1
        .Range("A:A").Find(Action).Offset(0, CustomerCol) = Áñéèìü_èÝóåùí
    End With
    
    
   End Sub

Hello again Ringhal,

It works! thank you very much and sorry about the language...!
Do you think I am ready to use it? or should I wait to add some error trapping as you said?
I also wanted to save the document everytime I click on the Button...is it possible?
Thank you very much for being so nice and efficient!

[Ringhal]

Hi katerina75

You are welcome and I am glad to help

I think the only error you might get is if any of the text is not found, as we're using the Find function, but I see you are using Data Validation.

To save, add this to the end of the code:

'Save the workbook
    Application.EnableEvents = False      
ThisWorkbook.Save      
Application.EnableEvents = True

[katerina75]

Hi katerina75

You are welcome and I am glad to help

I think the only error you might get is if any of the text is not found, as we're using the Find function, but I see you are using Data Validation.

To save, add this to the end of the code:

'Save the workbook
    Application.EnableEvents = False      
ThisWorkbook.Save      
Application.EnableEvents = True

Perfect!
Have a nice afternoon!