VBA 'find / go to' function?

[BLaCKouT]

Hi there,
First time poster, so feel free to throw sticks at me if this isn't clear (or, alternatively, if it's clearly a n00b question ).

I've been playing with VBA in Excel for about 6 weeks now, and so far, I've either been able to find the solution(s) I need on the internet**, or play around with the code enough to make it work.
Back in the late 80's I did a lot of Basic programming on the Amstrad, so the concept and syntax of VBA is fine with me, it's just some of the actual commands that I'm having trouble finding. Anyway...

+ + + + + + + + + +

I've got a worksheet (essentially a database, but the Access version of this is in such a mess, an Excel version is easier for me to work with) which lists various components, one row each. The file's 2mb so it seems silly attaching or linking to it. Screenshot here:

http://www.skittlez.co.uk/stuff/excel_01.jpg

Generally, the sheet is referenced by another spreadsheet, where VLOOKUP searches for the Cast number (Column B*) that a user inputs, and brings back the rest of the details (Columns C-H).

As new components arrive, they're manually entered into the sheet at the bottom. However, my department are given bundles of paper with component certificates to check, and 90% of those will be already in the system.

So, rather than the 'Find' function, I've built in a VLOOKUP section at the top of the page. When a user enters a Cast number into I2, the cert number (ie the value in Column C) is returned in J2. This basically lets you know if it's already in there, and is a lot neater than Excel's Edit/Find funtion, visually speaking.

From time to time, however, it's neccessary to actually go to the row containing the information, to inspect the other properties. So I'd like to assign a Macro to the 'Go' button to do just that.

Once a value is in J2, I'd like the macro to search for that value in Column C, then select that cell, effectively bypassing the Find dialog box.

I've tried playing with Index/Match, but it only seems to be returning information for me, not actually going to a cell. Although I suspect that's me not being familiar enough with Index/Match.

I think that's all the info you'll need, but I may well be wrong. Obviously, let me know if I've been vague about any of it
Many thanks in advance,
BLaCKouT.


*A few further notes on the attached pic:
• The green column (A) is concatenated info from B and F. This is what the external spreadsheet actually searches, as from time to time a user inputs information from the 'Plate' column (F), and I needed a VLookup to effectively search both.
• The blue, 'Go to end' button is a macro to search down column C until it finds the last entered number, then select the next one down. It's a quick way of getting to the 'fresh' part of the sheet.
• The Char.Count column (I) alerts a user if the Column F goes over 235 characters. This is because Excel won't do the VLookup I need if the concatenated information in Column A is over 256 characters.

** Largely down to this forum, thanks very much!

 

[xenou]

Actually, using Find Method is probably what you want - it works quite efficiently in Excel vba. You can "go there" invisibly, if you will, but working in code, or "literally" by selecting the found cell. Excel VBA help is pretty good on this one (there's a number of arguments here that need to be at least considered). Setting a reference to the found cell is generally helpful:

dim r as Range
Set r = worksheet1.Cells.Find(What:="Sought Value")

If not r is nothing then
    r.Select [COLOR="SeaGreen"]'//go to the cell.  Must be on the active sheet[/COLOR]
Else
    msgbox "cell not found"
End if

 

[BLaCKouT]

Hi Alexander,
Firstly, thank you very much for your help

I'm having trouble getting it to search for a variable though.

• Using your code, I changed the worksheet1.Cells.Find part to Sheet1.Cells.Find, to reflect the name of the sheet in my workbook.

• The search as it is looks for the characters in between the quotes, but I need the search content to be the value in cell J2. I've tried changing the line to:

Set r = Sheet1.Cells.Find(What:=J2)

But when I generate a value for cell J2 and run the macro, it selects cell I1 (which is empty). I thought this might be because the actual content of J2 is lookup-code, so I tried typing a value (which I know is elsewhere in the sheet) into J1, and changed the find reference in the code to:

Set r = Sheet1.Cells.Find(What:=J1)

But it still selects the empty cell at I1.

Given that the search itself is working with a static field, I'm putting it down to me not specifying search criteria properly. Obviously I can't tell it to look for "J2", as it just goes to the first cell where J2 occurrs.

So how can I get it to search for a variable (ie user defined) value?
Sorry to be a pain...

 

[xenou]

Hmm, maybe:

...[COLOR="royalblue"](What:=Range("J2").Value)[/COLOR]

Or:

[COLOR="RoyalBlue"]Dim x as variant
x = Range("J2").Value[/COLOR]
...
...[COLOR="RoyalBlue"](What:=x)[/COLOR]

 

[jbeaucaire]

I believe you've made an error with your sheet name. Sheet1 does not equate to a sheet name with a sheet tab name of "Sheet1". That would be:

Set r = Sheets("Sheet1").Cells.Find(What:=J2)

 

[xenou]

One thing you'll find tremendously useful in Excel vba is using reference variables for major objects (workbooks, worksheets, ranges). That way you can handily go back to these objects without too much fuss (for instance, if you were working on multiple sheets or even in multiple workbooks). Always assume you'll need to know what sheet you started on, at minimum.

Here's an example where we set a reference to Sheet1, with the variable sh. If I had two sheets, I might reference the first and second sheets early in the program, so that later I would easily be able to figure out which was which.

I'm guessing your lookup range is a particular set of cells so we can limit the find to those cells (otherwise, of course, we'll "Find" J2!).

Sub Foo()
Dim sh As Worksheet
Dim r As Range
Dim x As Variant

Set sh = Sheet1
x = Range("J2").Value

With sh.Range("A1:A10") [COLOR="SeaGreen"]'//"lookup" range[/COLOR]
    Set r = .Cells.Find(What:=x, After:=Range("A1"), Lookat:=xlWhole, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False)
    If Not r Is Nothing Then
        MsgBox r.Address
        r.Activate '//goto cell
    Else
        MsgBox "value not found: " & x
    End If
End With

End Sub

 

[RobMatthews]

And do you want to hard-code the address of the search cell? That sort of stuff always messes me up when i do it, i find i need to add another column or something...

 

[xenou]

I believe you've made an error with your sheet name. Sheet1 does not equate to a sheet name with a sheet tab name of "Sheet1".

Ah, thanks jbeaucaire. Good point. And if so in my most recent example change:

Set sh = Sheet1

To

Set sh = Worksheets("Sheet1")

 

[xenou]

Nota bene:

Set r = Sheet1.Cells.Find(What:=J1)

Excel has probably invisibly initialized a variable called J1 with an "empty" value. Which is why you basically find an empty cell (the first in search region probably).

 

[RobMatthews]

Nota bene:

Set r = Sheet1.Cells.Find(What:=J1)

Excel has probably invisibly initialized a variable called J1 with an "empty" value. Which is why you basically find an empty cell (the first in search region probably).

... which you can probably avoid by "Option Explicit"?