How to search for a value in a range if found run a procedure.Solved/Closed

Question

Hello, 

I am trying to get my spread sheet to run a Macro I have that is working nicely (that copies the row and pastes it to another sheet in the workbook) automatically when i type in Lost in a certain cell. (range L6-L100)

Here is the code i used  

Sub Worksheet_Change()

If Target.Cells = "L6:L100" Then
If Value = "Lost" Then
CopyCells

End If
End If

End Sub

this was placed in sheet2 VBA code as i only wanted it to apply to this sheet2.

Any idea's what i have done wrong it has been a long time since i used VB in Excell in anger.



Configuration: Windows 7 / Chrome 42.0.2311.90

MaxStart · Answer

You need to trigger the macro while the Worksheet_Change event occurs
and what cell will contain the text "Lost", or is it going to be in a range?

MaxStart · Answer

to get to the workshet_change event: hit Alt + F11 to get to the developer window



use this code to check the range L6 to L100 for the word "Lost"
please note that this is case sensitive:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
i = 6
Do Until i = 100
    If Cells(i, 12).Value = "Lost" Then
        CopyCells
    End If
    i = i + 1
Loop
End Sub
 
Je ne parle pas français, mais je l'aime.
je ne pas change.

How to search for a value in a range if found run a procedure.

2 responses

Similar discussions

Subscribe To Our Newsletter!