View Full Version : Copying a section if item from a drop down list is selected
stagiaireSTG
10-03-2017, 01:20 PM
Hi, I know it's very basic but I've been stuck on this for about an hour and can't figure out why it's not working.
I want the code to copy a section to another section (AI2 to AI10) if a certain item from the drop down list (positionned at B3) is selected.
Here's my code:
Sub Choix()
If Range("B3") = "Eau" Then
Range("AK2:AK3").Select
Selection.Copy
Range("AI2:AI10").Select
ActiveSheet.Paste
ElseIf Range("B3") = "Ciment" Then
Range("AL2:AL4").Select
Selection.Copy
Range("AI2:AI10").Select
ActiveSheet.Paste
ElseIf Range("B3") = "Agrégats" Then
Range("AM2:AM3").Select
Selection.Copy
Range("AI2:AI10").Select
ActiveSheet.Paste
ElseIf Range("B3") = "Sable" Then
Range("AN2:AN3").Select
Selection.Copy
Range("AI2:AI10").Select
ActiveSheet.Paste
ElseIf Range("B3") = "Béton" Then
Range("AO2:AO3").Select
Selection.Copy
Range("AI2:AI10").Select
ActiveSheet.Paste
End If
End Sub
Thanks
offthelip
10-03-2017, 03:16 PM
put this in the worksheet change event for the sheet, this will call your subroutine when the worksheet is changed
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("B3:B3")) Is Nothing) Then
Call choix
End If
End Sub
Bob Phillips
10-04-2017, 01:35 AM
You can also simplify choix
Sub choix()
Select Case Range("B3").Value
Case "Eau": Range("AK2:AK3").Copy Range("AI2:AI10")
Case "Ciment": Range("AL2:AL4").Copy Range("AI2:AI10")
Case "Agrégats": Range("AM2:AM3").Copy Range("AI2:AI10")
Case "Sable": Range("AN2:AN3").Copy Range("AI2:AI10")
Case "Béton": Range("AO2:AO3").Copy Range("AI2:AI10")
End Select
End Sub
mdmackillop
10-04-2017, 04:05 AM
You are copying 2 cells into a 9 cell range. I'd amend XLD's code as follows
Case "Eau": Range("AK2:AK3").Copy Range("AI2") 'etc
Bob Phillips
10-04-2017, 04:21 AM
I did think of that Malcolm, but it works anyway, two cells goes into two, so I left it alone :).
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then Range("AJ2:AJ3").Offset(, Application.Match(Target, Array("Eau", "Ciment", "Agrégats", "Sable", "Béton"), 0)).Copy Range("AI2")
End Sub
stagiaireSTG
10-04-2017, 05:17 AM
Thank you very much, that's what i did and it works, only problem that I have is that I have to go in the VBA editor and click run every time for the code to to it, I'd like it to do it automatically.
My problem is that since it's from a drop down list, it's just as if Excel sees nothing in the B3 case
stagiaireSTG
10-04-2017, 08:32 AM
I'm trying even the most simple code :
Sub test()
If Cells(1, "AI") = "Eau" Then
Range("AK2:AK3").Copy Range("I5:I6")
End If
End Sub
This doesn't work.
Here's my first attemp
Sub Choix1_Click()
Select Case Range("AI1").Value
Case "Eau": Range("AK2:AK3").Copy Range("AI5:AI6")
Case "Ciment": Range("AL2:AL3").Copy Range("AI5:AI6")
Case "Agrégats": Range("AM2:AM3").Copy Range("AI5:AI6")
Case "Sable": Range("AN2:AN3").Copy Range("AI5:AI6")
Case "Béton": Range("AO2:AO3").Copy Range("AI5:AI6")
End Select
End Sub
Here's my second
Sub test()
If Cells(1, "AI") = "Eau" Then
Range("AK2:AK3").Copy Range("AI5:AI6")
End If
End Sub
Basically, I have a certain number of drop down list depending on one another, this is the code for the first, I only post this one, since they'll all be the same with minor changes.
I have done a data validation for the next drop down list, which refer to AI5:AI6, that's why i want to send it there, but it just doesn't work, if i select item number 1, the corresponding following section wont copy automatically in AI5:AI6
mdmackillop
10-04-2017, 08:37 AM
Did you try the code in post #6?
Also have a look at this. (http://www.contextures.com/xlDataVal13.html)
stagiaireSTG
10-04-2017, 09:08 AM
I've checked what you sent me, but since I have over a thousand combination, it would be freaking long to write them all... And post #6 doesn't work
offthelip
10-04-2017, 09:17 AM
Where did you put the code in post #6? In order to get it to work the code must be placed in the worksheet where you have the pulldown list.
(Not in any of the modules). Under the VBAproject, right click the sheet and select view code, copy the code into the window that opens up.
stagiaireSTG
10-04-2017, 10:01 AM
It says there is a Run-time error '13': Type Mismatch, could you email me so i could send you the whole document? stagiaire at bmhsystems.com
offthelip
10-04-2017, 11:19 AM
Try the code in post #2 with your original sub choix and see if it does anything.
You can post your workbook to this site. Click "Go Advanced" and then manage attachments
stagiaireSTG
10-04-2017, 11:35 AM
No it doesn't... I really have no idea what's not working but it's pretty annoying...
offthelip
10-04-2017, 12:11 PM
it is a simple problem your list of materiaux in AG has a space in front of each of the strings , while the matching strings you are looking for don't
"Ciment" is not equal to " Ciment"
mdmackillop
10-04-2017, 12:49 PM
Something like this (error noted above corrected)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then Range("AJ2:AJ3").Offset(, Application.Match(trim(Target), Array("Eau", "Ciment", "Agrégats", "Sable", "Béton"), 0)).Copy Range("AI2")
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.