Move Cursor to Search Result

[mikemmb]

Hi,
Can someone please help me, I am trying to position the cursor in a spreadsheet column based on the data within the column but am struggling with the software to do it.

What I have is:
(a) A column (A:A) containing a mix of up to 500 numbers (1 to 3 digit) and a few text entries (1 to 6 char). These form the basis of a movement log where say 100 different drivers (each with unique identity) are logged in and out, so there will be duplicates.
(b) A number of other columns logging task/times etc but the one I am interested in (B:B), contains a tick (martlet font "a") to denote the driver has departed.

What I want to do when a driver departs, is have a quick way of finding (from the top down) the row containing the drivers number (A:A) where the driver logged in, but has not yet departed (so I can tick him out!).

So from the top: find the first row (in A:A) matching a specific number/string, where B:B is also blank and position the cursor at B"x").

I have imagined a "button" with data entry at the top of the sheet being the easiest physical way of doing it but am open to offers.

Can the Cursor (or cell?) be highlighted as well, because this is not always easy to see in bright light.

Thanks,
Mike

 

[VoG]

Hello and welcome to MrExcel.

Try this: press ALT + F11 to open the Visual Basic Editor, Insert > Module and paste in:

Sub Ffind()
Dim Found As Range, tempcell As Range, Response As Integer, X
X = InputBox("Please enter what you want to find")
Set Found = Columns("A").Find(What:=X)
If Found Is Nothing Then
    MsgBox X & " Not Found"
    Exit Sub
End If
If Found.Offset(, 1) = "" Then
    Application.Goto reference:=Found.Offset(, 1), scroll:=True
Else
    Do
        Set tempcell = Columns("A").FindNext(After:=Found)
        If Found.Row >= tempcell.Row Then
            MsgBox X & " Not Found"
            Exit Do
        End If
        Set Found = tempcell
        If Found.Offset(, 1) = "" Then
            Application.Goto reference:=Found.Offset(, 1), scroll:=True
            Exit Do
        End If
    Loop
End If
End Sub

Press ALT + F11 to return to your sheet, Tools > Macro > Macros, highlight Ffind and click the Run buttom.

You could add a button from the Forms toolbar and assign the macro to it.

 

[mikemmb]

Hi VoG,
Thanks that was a super fast reply and it worked straight out of the box ...... and I didn't even screw up the copy and paste!
Plus it actually goes a step further than I imagined and "Ticks" the Cell, which is great.

Only one minor irritation in that it ends up with column B against the LH margin (ie column A is off screen left), can it be tweaked to bring column A back into view?

Thanks,
Mike

 

[VoG]

Try

Sub Ffind()
Dim Found As Range, tempcell As Range, Response As Integer, X
X = InputBox("Please enter what you want to find")
Set Found = Columns("A").Find(What:=X)
If Found Is Nothing Then
    MsgBox X & " Not Found"
    Exit Sub
End If
If Found.Offset(, 1) = "" Then
    Application.Goto reference:=Found.Offset(, 1), scroll:=True
Else
    Do
        Set tempcell = Columns("A").FindNext(After:=Found)
        If Found.Row >= tempcell.Row Then
            MsgBox X & " Not Found"
            Exit Do
        End If
        Set Found = tempcell
        If Found.Offset(, 1) = "" Then
            Application.Goto reference:=Found, scroll:=True
            ActiveCell.Offset(, 1).Select
            Exit Do
        End If
    Loop
End If
End Sub

 

[mikemmb]

Hi VoG,
Thanks for reply which I tried and thought it worked at first but it has some snags and unfortunately I don't (yet) know enough to figure it out?

(a) If I want to find say "6", it will find any cell with an entry containing a 6 in it (ie 216, 62 etc).

(b) The Re-Aligning of Columns to keep A visible is not consistent, it often does not work and leaves column A off screen left. I have tried to look for a pattern to it (such as varies with number of digits etc) but I cannot find a pattern to it.

Thanks again,
Mike

 

[VoG]

(a) Try

Sub Ffind()
Dim Found As Range, tempcell As Range, Response As Integer, X
X = InputBox("Please enter what you want to find")
Set Found = Columns("A").Find(What:=X, lookat:=xlWhole)
If Found Is Nothing Then
    MsgBox X & " Not Found"
    Exit Sub
End If
If Found.Offset(, 1) = "" Then
    Application.Goto reference:=Found.Offset(, 1), scroll:=True
Else
    Do
        Set tempcell = Columns("A").FindNext(After:=Found)
        If Found.Row >= tempcell.Row Then
            MsgBox X & " Not Found"
            Exit Do
        End If
        Set Found = tempcell
        If Found.Offset(, 1) = "" Then
            Application.Goto reference:=Found, scroll:=True
            ActiveCell.Offset(, 1).Select
            Exit Do
        End If
    Loop
End If
End Sub

(b) I'm sorry but I can't reproduce that problem.

 

[mikemmb]

Hi VoG,
Snag (a) fixed, now finds exact match, great thanks.

Still got that weird issue with the disappearing column A. I have tried to figure it out with test cases (entering different digits, using 1, 2 or 3 digit numbers and having different cursor start points) but cannot see a pattern, it just happens a lot?
I thought at first it could be due to not having all columns visible (ie I had active columns off screen right in order to have column A visible .... but this wasn't the problem either!
Can you let me know which bits of your code manipulate cursor position and I can have a play with it.

Bong! ............... I have just realised, it is not important that the cursor remains in the (column B) cell that it has just found (and ticked), it could always move to column A (in the same row of course)?
Perhaps then the row (or cell?) could be highlighted to indicate what was found/changed?
Is this possible?

Thanks,
Mike

 

[VoG]

Try

Sub Ffind()
Dim Found As Range, tempcell As Range, Response As Integer, X
Columns("B").Interior.ColorIndex = xlNone
X = InputBox("Please enter what you want to find")
Set Found = Columns("A").Find(What:=X, lookat:=xlWhole)
If Found Is Nothing Then
    MsgBox X & " Not Found"
    Exit Sub
End If
If Found.Offset(, 1) = "" Then
    Application.Goto reference:=Found.Offset(, 1), scroll:=True
Else
    Do
        Set tempcell = Columns("A").FindNext(After:=Found)
        If Found.Row >= tempcell.Row Then
            MsgBox X & " Not Found"
            Exit Do
        End If
        Set Found = tempcell
        If Found.Offset(, 1) = "" Then
            Application.Goto reference:=Found, scroll:=True
            ActiveCell.Offset(, 1).Interior.ColorIndex = 6
            Exit Do
        End If
    Loop
End If
End Sub

 

[mikemmb]

Hi VoG,
No, sorry ...... still got the problem with the disappearing column A and no colour change.

In fact it seems worse and after the Ffind the cursor remains in column B (with column A off screen left) all the time now with the latest version!

Reverting back a version is better, but still seemingly random?

Thanks for sticking with it.
Regards,
Mike

 

[VoG]

Sorry, I changed one and not the other - try

Sub Ffind()
Dim Found As Range, tempcell As Range, Response As Integer, X
Columns("B").Interior.ColorIndex = xlNone
X = InputBox("Please enter what you want to find")
Set Found = Columns("A").Find(What:=X, lookat:=xlWhole)
If Found Is Nothing Then
    MsgBox X & " Not Found"
    Exit Sub
End If
If Found.Offset(, 1) = "" Then
    Application.Goto reference:=Found, scroll:=True
    ActiveCell.Offset(, 1).Interior.ColorIndex = 6
Else
    Do
        Set tempcell = Columns("A").FindNext(After:=Found)
        If Found.Row >= tempcell.Row Then
            MsgBox X & " Not Found"
            Exit Do
        End If
        Set Found = tempcell
        If Found.Offset(, 1) = "" Then
            Application.Goto reference:=Found, scroll:=True
            ActiveCell.Offset(, 1).Interior.ColorIndex = 6
            Exit Do
        End If
    Loop
End If
End Sub