Find specific cell value and goto cell

[Lex Luthor]

I need a vba code to find and goto cells with value 1 in it. The range is y17 to y4000 and there can be several cells with the value 1.
I am scrolling down now and my RSI is playing up
Can it be done with one button and goto the first cell with value 1 and then the next when pressed again and so on? or any suggestions?
Thanks in advance

 

[alansidman]

Why not use the built in functionality of Excel. Use the Find Icon or Control +F to achieve the same result

 

[Lex Luthor]

Hi,
Thank you for your reply..
I didnt know about it, tried it works fine indeed however I do use it quite often so it would be easier to place a button on column Y.

 

[alansidman]

Here is an example of something similar

https://msdn.microsoft.com/en-us/library/office/ff839746.aspx

 

[Lex Luthor]

Thank you for the tip!!
I have been working on it and so far the code below works but I cant seem get it to stop at the end of the last value (1) found in the column. Also it doesnt work if the active cell is not in column E.
I am new to VBA so any help would be appreciated.

Sub FindValueColE()


On Error Resume Next
With Sheet1 'With Sheet1.range("e15" & lastrow)...this wont work?

Set rFindnext = .Columns(5).find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

If Not rFindnext Is Nothing Then Application.Goto rFindnext, True

End With

End Sub

 

[Caleeco]

This code will find the next cell containing "1" below the selected cell.

Sub FindNext()


Dim rData As Range
Dim rCell As Range
Dim ThisRow As Long


ThisRow = Selection.Row + 1


Set rData = ActiveSheet.Range("Y" & ThisRow, "Y4000")


For Each rCell In rData
    If rCell.Value = 1 Then
        rCell.Select
        Exit Sub
    End If
Next


End Sub

Hopefully that may be of some use

 

[Peter_SSs]

Does this do what you want?

Sub FindValueColE()
  Dim rFindnext As Range

  With Sheet1.Columns(5)
    On Error Resume Next
    Set rFindnext = .Find(What:="1", After:=.Cells(ActiveCell.Row), LookIn:=xlFormulas, LookAt:=xlPart, _
                          SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    On Error GoTo 0
    If Not rFindnext Is Nothing Then
      If rFindnext.Row > ActiveCell.Row Then Application.Goto rFindnext, True
    End If
  End With
End Sub

 

[Lex Luthor]

Both codes work, thank you so much for that.
It would make things easier if the findnext started at row 20 (same column) but not return to it everytime Findnext is activated.
Also I am going to need some more help to incoperate an application.run, dont know how to handle it, I am new to VBA, hope you dont mind.

It needs to run application.run "copyops" everytime it finds a value 1 in column Y then at the end return to Y20, so it needs to loop I guess?