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!

 

[BLaCKouT]

Hi everybody,
Wow, thank you SO much for your help

• The reason I reason I renamed the worksheet in the code was that if I ran it with the line:

Set r = worksheet1.Cells.Find(What:=Range("J2").Value)

I get an error box with

Run-time error '424':
Object required

I played with changing the value, and the error stopped. I assure you I wasn't changing it for the sake of it.

• Anywho, after a little tinkering to ranges, and adding on a section to select the relevant details for the row to be viewed, the final code is:

Dim sh As Worksheet
Dim r As Range
Dim x As Variant

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

With sh.Range("C5:C10000")
Set r = .Cells.Find(What:=x, After:=Range("C5"), Lookat:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not r Is Nothing Then
r.Select
With ActiveCell
Range(Cells(.Row, "B"), Cells(.Row, "H")).Select
End With
Else
MsgBox "value not found: " & x
End If
End With

http://www.skittlez.co.uk/stuff/excel_02.jpg
When you type a 'Cast number' into I2, the VLookup generates the relevant Cert number in J2. I like this because it tells you the cert exists without dragging you all over the sheet.
If you do need to see the details of that cert, hit the GO button and you're taken to that line and cells B to H are selected for ease of viewing.
When you're done, hitting the 'Go to End' button clears the value of cell I2 and selects the first blank cell at the bottom of column C, ready for the next user to open the file and start inputting data.

In short, it does exactly what I want it to. I owe it all to you folks, thank you once again

While it's debatable how much advice I can offer at this stage, I'll stick around because I know I will learn here.
Cheers folks,
B.

 

[jbeaucaire]

Glad it worked out.

Forum Tip: do not wrap your code in QUOTE.../QUOTE tags, wrap them in CODE.../CODE tags and they will appear AS CODE. Notice the difference with your posts and everyone else's code?

You can do it manually by just typing in the CODE tags to appear the same way the QUOTE tags appear, or GO ADVANCED and use the # icon.

Welcome on board!

Dim sh As Worksheet
Dim r As Range
Dim x As Variant

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

With sh.Range("C5:C10000")
    Set r = .Cells.Find(What:=x, After:=Range("C5"), Lookat:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False)
        If Not r Is Nothing Then
            r.Select
            With ActiveCell
                Range(Cells(.Row, "B"), Cells(.Row, "H")).Select
            End With
        Else
            MsgBox "value not found: " & x
        End If
End With

 

[xenou]

You're a quick study. Keep excelling!