Help writing my first procedure in Visual Basic

[KidNewbie]

What I need to do is to write a procedure that will utilize an input text box and allow a user to enter a variant into the text box as a search term. And as long as the number of characters in the input box is >=1 character in the text box I need whatever is input in that textbox to reference a single column of data and if an exact match is found, to look at the cell in the same row/adjacent column (to the right) and open the hyperlink in the adjacent column in a web browser. I have looked all around the net but have not found anything remotely similar with EXCEPTION to a statement made in this closed thread on this website (hence why I have signed up here):

http://www.tek-tips.com/viewthread.cfm?qid=1235908

Specifically in the post you mention, "i.e., returning a secondary column's data from the same row as that of a key column's lookup value"

I don't need the exact url returned as a result. But what I do need is if the variant search term that is typed in by the user into the text input box matches ANY cell in any row of column 1, then offset (look at the cell in adjacent column/same row to the right AND follow the hyperlink in that cell.

Here is some FYI on the simplistic structure of my spreadsheet. In my spreadsheet I have only 2 columns. Column 1 consists of cells that have multiple words (up to 32 words). Column 2 consists of all hyperlinks to web pages that correspond to the cell to their left. I'd like the ability if possible when typing in the input box for a vlookup or match function to be occurring simultaneously and if matching to autofill BUT allow user to complete the word they are typing (just in case word they are typing is different). I'd like a button with an assigned macro created to accomplish this. Any help provided would be so greatly appreciated and just fyi I started trying to learn visual basic this afternoon at 1pm and am just now breaking. Here is what I have so far. I just sincerely don't know how to bring all of this together, please don't laugh too hard:


Option Explicit

Sub MyFirstProcedure()

End Sub
Const dataSheetName = "Sheet1" ' name of sheet

Dim theInput As Variant ' from user
Dim theResult As Variant 'from user
Dim Column1 As Variant 'from user
Dim Column2 As Variant 'from user


theInput = InputBox("Lookup sports car", "Type in what type of sports car?")
theResult = vlookup

 

[SkipVought]

hi,

Do you expect an exact match or some kind of fuzzy match?

Please post a relevant example of a lookup value, the data that would be searched and the expected result from that example.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, have you tried the lookups you think might work, directly on the sheet?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kjv1611]

Just thought I'd mention... If you try and debug your code or execute it, you'll just get errors at this point... the "End Sub" should always go after all other code within a procedure (same goes for a function... end function)

So you want it to be like this instead:

Option Explicit

Sub MyFirstProcedure()
    Const dataSheetName = "Sheet1" ' name of sheet
    
    Dim theInput As Variant ' from user
    Dim theResult As Variant 'from user
    Dim Column1 As Variant 'from user
    Dim Column2 As Variant 'from user
    
    
    theInput = InputBox("Lookup sports car", "Type in what type of sports car?")
    theResult = vlookup
[HIGHLIGHT]End Sub[/HIGHLIGHT]

Then if you build out your vlookup piece (loads of code avail as examples for that), you may get there.

And as an alternative to VLookup, you could also look at INDEX and MATCH (it's really nifty, though I do have a hard time following that one, myself).

Also, I'd personally suggest you use this method of working with your worksheet:

Sub MyProcName()
  Dim wb As Workbook
  Dim ws As Worksheet

  Set wb = ActiveWorkbook
  Set ws = wb.ActiveSheet
  .....
End Sub

or

Sub MyProcName()
  Dim wb As Workbook
  Dim ws As Worksheet

  Set wb = Workbooks("C:\MyWorkbook.xls")
  Set ws = wb.Worksheets("MyWorksheetName")
  .....
End Sub

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[KidNewbie]

Thank you SkipVought and kjv1611 I appreciate both of you helping me. I believe I'd want to expect an exact match. Hypothetically if there is not an exact match I'd like to be able to implement an IF statement if not mmatching exactly, then to write the search term to a separate worksheet or workbook depending on what you feel is best. If it is a preference type of decision on whether or not to write to workbook vs worksheet, please write it to the same workbook, but different worksheet. lets call it "Sheet2". I'd like each false or NOT exact lookup logged for future lookups.

The following data is from a spreadsheet I have saved:

COLUMN 1
$Ferrari
$Maserati
$Bugatti

COLUMN 2

http://www.ferrari.com/English/GT_Sport Cars/CurrentRange/FF/Pages/FF.aspx

http://www.maseratigranturismomcstr...tmctr=maserati&__utmv=-&__utmk=60485677#/home

http://www.bugatti.com/en/veyron-16.4.html

Sorry for the miserable layout. Basically the data can be anything in any of the columns. Essentially there are 3 possible search terms (for now). There are 3 hyperlinks that I'd like opened up if their adjacent cell in column 1 is searched and brought back automatically upon pressing the button in the sheet that has the correct VBA procedure.

The spreadsheet that has search terms in column 1 and the corresponding hyperlinks (in column 2) that I want opened/followed in a web browser IF an exact search search term is input by user and matched withing column 1. I have tried a few combinations but still no luck in getting this code to work correctly. Have been doing many searches on Google. No help, thus far. Thanks for the information on the End Sub and also the excellent formatting advice/tips you offered. I basically found a .95 pg .pdf, read it in a day and was trying to implement what I learned right way. The only problem is I think I can get the procedure to the point of lookup but uncertain how to bridge the oepning of an adjacent cell in same row's hyperlink and how to work a else if statement in there. Any help you can provide would be greatly appreciated. I did look up each of vlookup, index and match functions in your site's frequently asked questions as well as my .pdf and since my data that is being searched is going to be in the far left column I assume I will want to utilize Vlookup. For whatever reason I read and it doesn't make sense or sink in until I see a working example of it ...

 

[PHV]

Why not simply use the Find method of the Range object ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Also check out the AutoFilter. Select in the filter in column A. Immediately see result in column B

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[KidNewbie]

Hello guys, I've searched all over this forum for how to initiate an input box upon opening program. I've found that. Now I cannot find for the life of me the correct way search 1 column via range.find and upon locating variant that was searched as exactly typed within the input box, to have the procedure look at the adjacent cell in column 2 and to follow that hyperlink in column 2. The is as close as I have gotten thus far and can't seem to make it do exactly what I described above. The search inbutbox isn't popping up right away and makes me input a Cell Column and number.

Option Explicit

Sub MyFirstProcedure()
Dim wb As Workbook
Dim ws As Worksheet
Dim name As Variant ' input by the user
Dim MyRange As Variant ' range of search by programmer (me)
Dim cellvalue As Variant 'actual cell value

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
Set MyRange = Range("A1:A50") ''// for example

If name = cellvalue Then

ActiveWorkbook.FollowHyperlink "

http://www.google.com"

''// do something with this cell

End If

MsgBox ("Sorry, invalid search term, please try again") ''// invalid search return verbiage

End Sub

 

[kjv1611]

I think I see 2 problems right away:
1. No Inputbox in your code - did you accidentally omit it when posting the question? Or am I just blind this morning?

2. At the end, I think this is what you're after:

If name = cellvalue Then
    ActiveWorkbook.FollowHyperlink "[URL unfurl="true"]http://www.google.com"[/URL] ''// do something with this cell
Else    
    MsgBox ("Sorry, invalid search term, please try again") ''// invalid search return verbiage
End If

End Sub

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57