• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Drop down selection that causes macro to do different things

bwood7

New Member
Hello everyone,


What I'm trying to achieve is take data from SHEET1 and one column "A15" on the same line as the drop down box "J15" and if the criteria is met (yes/no selection) then the data from "A15" is pasted on another sheet "Impacts" on column "D" but first the macro has to check columns A through G to make sure no data is pasted there already, and that it doesn't duplicate the data that has been previously entered, this is all within the same workbook. I have multiple lines and multiple drop down boxes and can't seem to get the code to go row by row, checking the drop down boxes. Also if they select "No" I'm not quite sure how to make the code skip to the next line without pasting the data from column A.


Here is my current code:

[pre]
Code:
Sub Test()
Dim i As Integer
For i = 15 To Sheets("SHEET1").Range("J" & Rows.Count).End(xlUp).Row
Select Case Sheets("SHEET1").Range("J" & i)
'If Yes is selected in drop down box
Case "Yes"
Dim LastRow As Integer
LastRow = Worksheets("Impacts").Range("A5:G" & Rows.Count).End(xlUp).Row + 1
Dim order As String
order = Sheets("SHEET1").Range("A" & i)
Sheets("Impacts").Range("D" & LastRow) = order
'If No is selected in drop down box
Case "No"
'If nothing is selected in drop down box
Case ""
'Message displayed for empty box
MsgBox ("Not all boxes have been selected, please make sure to select Yes or No.")
Exit For
End Select
Next i

MsgBox ("All Impacts have been transfered")

End Sub
[/pre]

Thanks for your help in advance.
 
To help clarify what I'm trying to accomplish:


1. Column J has drop down boxes from row 15 on, the number of rows change once updated by another macro. This is on SHEET1


2. Column A has data entered into it from row 15 down to whatever the last line might be.


3. The drop down box has two selections (yes or no)


4. If the selection is "yes" I want the macro to read column A for that line and copy and paste in another sheet called "Impacts"


5. Before the macro copies the data in cell "A" from "SHEET1" I want it to check sheet "Impacts" for two things:

a. That the information has not already been recorded in the sheet "Impacts" in column "D"

b. That the data from SHEET1 is copied on the next row of "D" that from column A5 thru G whatever has no information entered into it. Or if it does put it on the next blank row after it.


6. If the selection is "no" I want the macro to skip to the next row and read the drop box for the answer and take no action on "no"


7. If the selection is left blank, I want the macro to display a message stating it needs a answer either yes or no for that row.
 
bwood7 - stuff like this is SOO much easier to look at if you upload a sample workbook and post a link here.
 
Back
Top