Search code?

I was wondering how to create a search function that would search column A for whatever was put in E1. I am completely new to this sort of thing. Sorry

Record a macro that does a find for say 10.
This is under Edit, Find.
Look at the produced code.
Replace this bit:
What:="10"
with this:
What:=Cells(5)

RBS

"Durzan" <Durzan.2cgx0o_1155454805.4375@excelforum-nospam.com> wrote in
message news:Durzan.2cgx0o_1155454805.4375@excelforum-nospam.com...
>
> I was wondering how to create a search function that would search column
> A for whatever was put in E1. I am completely new to this sort of thing.
> Sorry
>
>
> --
> Durzan
> ------------------------------------------------------------------------
> Durzan's Profile:
> http://www.excelforum.com/member.php...o&userid=37447
> View this thread: http://www.excelforum.com/showthread...hreadid=571126
>

How do I make the formula search the data I put in E1 and limit it's search to column A?

Is it possible to make a search fomula that searches column a for the text entered in E1 and displays the data listed in adjacent comumns.
eg. I look up cars and it looks for cars in column A when it finds the result say A12 it displays the data in B12,C12,D12,E12 as well.
so A12 displays in F1, C12 in G1 and so on

Replace the bit in your recorded macro:
Cells
with:
Columns(1)

RBS

"Durzan" <Durzan.2cgyn0_1155456904.1644@excelforum-nospam.com> wrote in
message news:Durzan.2cgyn0_1155456904.1644@excelforum-nospam.com...
>
> How do I make the formula search the data I put in E1 and limit it's
> search to column A?
>
>
> --
> Durzan
> ------------------------------------------------------------------------
> Durzan's Profile:
> http://www.excelforum.com/member.php...o&userid=37447
> View this thread: http://www.excelforum.com/showthread...hreadid=571126
>

HI Durzan

Hopefully enough simple code below to get you going

Sub Finder ()

dim what
what = Range("e1").Value
If what = "" Then MsgBox (" NO VALUE IN SOURCE CELL")
If what = "" Then End
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
On Error GoTo 1000
Selection.Find(what:=what, After:=ActiveCell, LookIn:=xlFormulas, LookAt
_
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Value = what

MsgBox (" THE SEARCH ITEM ") & what & " WAS FOUND AT " &
ActiveCell.Address
End

1000:
MsgBox ("THE ITEM WAS NOT FOUND")

End sub

Best N10



"Durzan" <Durzan.2cgz3p_1155457507.4467@excelforum-nospam.com> wrote in
message news:Durzan.2cgz3p_1155457507.4467@excelforum-nospam.com...
>
> Is it possible to make a search fomula that searches column a for the
> text entered in E1 and displays the data listed in adjacent comumns.
> eg. I look up cars and it looks for cars in column A when it finds the
> result say A12 it displays the data in B12,C12,D12,E12 as well.
> so A12 displays in F1, C12 in G1 and so on
>
>
> --
> Durzan
> ------------------------------------------------------------------------
> Durzan's Profile:
> http://www.excelforum.com/member.php...o&userid=37447
> View this thread: http://www.excelforum.com/showthread...hreadid=571126
>

Firstly place a button on the spreadsheet from the control toolbox toolbar.

Then double click the button to go into the VBE and enter the following code
in between Sub commandbutton1 () and End Sub

Dim search, count, val1, val2

val1=Worksheets("Sheet1").Range("E1").Value
count=0

While Search="ON"
val1=Worksheets("Sheet1").Range("A1").Offset(count,0).Value
If val1=val2 Then
'Put what you want to do here once it has found the value in the column
MsgBox "Found value in A:" and count
Search="OFF"
Else
count=count+1
If count=1000 then
MsgBox "Search did not find value in cells A1 to A1000"
Exit Sub
End If
End If

That should work nicely to find the value, but it would help if I knew what
you wanted to do once its found the value.

"Durzan" wrote:

>
> I was wondering how to create a search function that would search column
> A for whatever was put in E1. I am completely new to this sort of thing.
> Sorry
>
>
> --
> Durzan
> ------------------------------------------------------------------------
> Durzan's Profile: http://www.excelforum.com/member.php...o&userid=37447
> View this thread: http://www.excelforum.com/showthread...hreadid=571126
>
>