Change code to look in another column

[Valemaar]

Hey guys

I have "stolen" this peace of code from an old thread in here. I cant figure out how to change the code to go look for a specific word i column "I" insted of column "B".

Can anyone helt me?

Manually enter searches on sheet (1).xls

Please Login or Register  to view this content.

[ragulduy]

Please Login or Register  to view this content.

the "2"'s are what are defining your column number. Note, your code is a little inefficient, I would suggest rewriting as follows:

Please Login or Register  to view this content.

[Valemaar]

Thanks a lot .

Your code works . But I'm sorry that I have not made it totally clear what my goal is. Sorry.

Every day I will get new data into "input". If the new data is labeled "Appels" that entire row has to go to the "Apple" sheet under the already filled rows and be deleted from the "input" sheet. If the new data has no label (blank field), the row should just stay in input sheet.

Sorry again for the newbie expressions.

[ragulduy]

Please Login or Register  to view this content.

?

[Valemaar]

I cant figure out where to place your answer in continuation of previous thread.

Private Sub CommandButton1_Click()
Dim search_col, search_term(3), i, l_row
search_term(1) = "peaches"
search_term(2) = "pens"
search_term(3) = "apples"
search_col = 9
For i = 1 To Cells(Rows.Count, search_col).End(xlUp).Row
If Cells(i, search_col) = search_term(1) Then
With Worksheets("Sheet1")
l_row = .Cells(Rows.Count, search_col).End(xlUp).Row + 1
.Range("A" & l_row & ":E" & l_row).Value = Range("A" & i & ":E" & i).Value
End With
ElseIf Cells(i, search_col) = search_term(2) Then
With Worksheets("Sheet2")
l_row = .Cells(Rows.Count, search_col).End(xlUp).Row + 1
.Range("A" & l_row & ":E" & l_row).Value = Range("A" & i & ":E" & i).Value
End With
ElseIf Cells(i, search_col) = search_term(3) Then
With Worksheets("Sheet3")
l_row = .Cells(Rows.Count, search_col).End(xlUp).Row + 1
.Range("A" & l_row & ":E" & l_row).Value = Range("A" & i & ":E" & i).Value
End With
End If

Dim search_col, i, l_row
search_col = 9
For i = Cells(Rows.Count, search_col).End(xlUp).Row To 1 Step -1
If Not Cells(i, search_col) = "" Then
With Worksheets(Cells(i, search_col))
l_row = .Cells(Rows.Count, search_col).End(xlUp).Row + 1
.Range("A" & l_row & ":E" & l_row).Value = Range("A" & i & ":E" & i).Value
End With
Rows(i).Delete
End If
Next
End Sub

[ragulduy]

Ignore the code in post #2, the post in #4 is a separate macro to do what you wanted.

[Valemaar]

I have tried, but I can't get it to work ..

When there is entered "peaches" in column B in "input", the entire row has to be cut out and inserted into the next empty line in "Sheet1".
When there is entered "pens" in column B in "input", the entire row has to be cut out and inserted into the next empty line in "Sheet2".
When there is entered "apples" in column B in "input", the entire row has to be cut out and inserted into the next empty line in "Sheet3".
When there is nothing entered into column B, the row has to stay in "Input".

What do I do?

[ragulduy]

You said when it was apples it had to go to the "Apples" sheet!

Please Login or Register  to view this content.