copy and paste to new row

[rayoconnell]

hey all,

i am trying to create a form to auto complete a spreadsheet.
the problem i am having is this.
if "Y"appears in column "H" then apply "vessel" to column "C"
if "Y"appears in column "I" then apply "FERM" to column "C"
if "Y"appears in column "J" then apply "WASTE_SYS" to column "C"
i have this part done, however the problem i have is "Y" sometimes appears in column "H" and "I" and/or "J" or a mixture of these.
in this situation i want to copy and paste that line to bottom of the spreadsheet. if it happens to occur in all 3 columns then i need to copy and paste it twice.

im not even sure this is possable, im new to this vba.
i have been trying to do it in excel but not having any luck,
any help would be greatly appreciated!!!:dunno


Cross-posted:
http://mrexcel.com/board2/viewtopic.php?t=272472 (http://mrexcel.com/board2/viewtopic.php?t=272472)
http://mrexcel.com/board2/viewtopic.php?t=272226 (http://mrexcel.com/board2/viewtopic.php?t=272226)

Edited 16-May-07 by geekgirlau. Reason: insert links to cross-post(s)

[vonpookie]

There's probably a better way to do this, but this seems to be working for me:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, x As Integer, Lrow As Long

If Target.Count > 1 Or Intersect(Target, Range("H:J")) Is Nothing Then Exit Sub


Set Rng = Range(Cells(Target.Row, "H"), Cells(Target.Row, "J"))

Application.EnableEvents = False

x = WorksheetFunction.CountIf(Rng, "Y")
Select Case x
Case 1
Select Case WorksheetFunction.Match("Y", Rng, 0)
Case 1: Cells(Target.Row, "C") = "vessel"
Case 2: Cells(Target.Row, "C") = "FERM"
Case 3: Cells(Target.Row, "C") = "WASTE_SYS"
End Select
Case 2
Lrow = Cells.Find("*", searchdirection:=xlPrevious).Row + 1
Target.EntireRow.Copy Range("A" & Lrow)
Case 3
Lrow = Cells.Find("*", searchdirection:=xlPrevious).Row + 2
Target.EntireRow.Copy Range("A" & Lrow - 1 & ":A" & Lrow)
End Select

Application.EnableEvents = True

End Sub

Right-click the tab of the sheet you want to use it on and select 'View Code.' Paste the code into the window that opens up. Then it should automatically run whenever you make a change on the sheet--but it will also check to only run the code if the change was made in columns H:J.

[Aussiebear]

Set Rng = Range(Cells(Target.Row, "H"), Cells(Target.Row, "J"))



Just a quick question. Does this line also include Cells(Target.Row, "I"), because of its structure?

[lucas]

Ted,
Yes, try this:
paste this in the code for a sheet then go to the sheet and type something in a cell and hit enter:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, x As Integer, Lrow As Long
Set Rng = Range(Cells(Target.Row, "H"), Cells(Target.Row, "J"))
Rng.Select
End Sub

[rayoconnell]

that dosnt seem to work,
i need it to be a click event on a form first of all,
when i try this i get a object variable or with block variable not set.
the If Target.Count > 1 Or Intersect(Target, Range("H:J")) Is Nothing Then Exit Sub was highlighted

Also i tryed it anyway the way you said and it did not make any changes when i tryed it.
HELP:doh:

[mdmackillop]

i need it to be a click event on a form first of all,

What do you mean by this?

[vonpookie]

that dosnt seem to work,
i need it to be a click event on a form first of all,
when i try this i get a object variable or with block variable not set.
the If Target.Count > 1 Or Intersect(Target, Range("H:J")) Is Nothing Then Exit Sub was highlighted
Because a generic button click event doesn't use the Target variable like the Change sheet event I posted.


Also i tryed it anyway the way you said and it did not make any changes when i tryed it.
Did you follow the directions? You have to put that code in the correct module or it will not work. And then it was written to run whenever you made a change in columns H:J of the sheet.

If you want this to be a generic macro to run when you click a button, it needs some changes.