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!
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
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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.