PDA

View Full Version : [SOLVED] paste a value in a cell that meets 2 conditions



katerina75
11-13-2013, 03:30 AM
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
11-14-2013, 07:00 AM
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
11-14-2013, 09:03 AM
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
11-15-2013, 02:35 AM
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.10836

SamT
11-15-2013, 08:43 AM
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
11-18-2013, 02:21 AM
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.10836

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
11-18-2013, 03:10 AM
Hi katerina75
I think I have an idea of what you need, see attached:10840

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
11-18-2013, 03:59 AM
1084210841
Hi katerina75
I think I have an idea of what you need, see attached:10840

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
11-18-2013, 04:27 AM
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
11-18-2013, 06:08 AM
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
11-18-2013, 06:22 AM
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
11-18-2013, 06:44 AM
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
11-18-2013, 07:05 AM
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!