Lookup part of a cells contents
 

Hi,

I am trying to pull data from a list based on the results of an input
box.

The example is, I have a list off accounts "Acme1234", "AAA 3456",
"A-1 7890" etc. This list is completely variable based on account
manager.

I want to create an inputbox where I would key in "3456" and I would
get a popup messagebox that has AAA 3456 as well as several pieces of
information in that row on my spreadsheet.

Any help, as always, is appreciated.

Glen

Lookup part of a cells contents
 

Here is a start.

Sub FindSomething()
dim rng as Range
dim res as String
res = InputBox("Enter string to find")
if res < "" then
set rng = cell.Find(What:=res, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
rng.Activate
end if
End if
End Sub


use rng to identify the remainder of the data you want to collect.

--
Regards,
Tom Ogilvy


"gmunro" wrote in message
oups.com...
Hi,

I am trying to pull data from a list based on the results of an input
box.

The example is, I have a list off accounts "Acme1234", "AAA 3456",
"A-1 7890" etc. This list is completely variable based on account
manager.

I want to create an inputbox where I would key in "3456" and I would
get a popup messagebox that has AAA 3456 as well as several pieces of
information in that row on my spreadsheet.

Any help, as always, is appreciated.

Glen

Lookup part of a cells contents
 

Wow, that was fast. Thank you.

I am getting a debug error based on

set rng = cell.Find(What:=res, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
The Cell I am looking for will always be in Column B.
Once I find it, I want to name the active cell, "MyCell" and run from
there.

Any suggestions?

Lookup part of a cells contents
 

You are getting an error because there was a typo - cell should have been
cells

but for column B

set rng = Columns(2).Find(What:=res, _
After:=Range("B65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
rng.Name = "MyCell"
end if

As an example, this ran fine for me:

Sub AA()
Dim rng As Range
res = "ABC"

Set rng = Columns(2).Find(What:=res, _
After:=Range("B65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
rng.Name = "MyCell"
End If

End Sub

--
Regards,
Tom Ogilvy



"gmunro" wrote in message
oups.com...
Wow, that was fast. Thank you.

I am getting a debug error based on

set rng = cell.Find(What:=res, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
The Cell I am looking for will always be in Column B.
Once I find it, I want to name the active cell, "MyCell" and run from
there.

Any suggestions?