Create macro

[Rubix]

I would like to input my stock according to item number.

A4 to A1000 are my stock items.

Cell D is my balance.

I would like to create a macro where I can Ctrl + F to find the stock
number but apon pressing Enter such as Stock item #42 which would
appear at say A149 to go directly to D149.

Thanks,
Rube

[Bob Phillips]

Sub Macro1()
Dim ans
Dim cell As Range
ans = InputBox("Supply search string")
If ans <> False Then
Set cell = Cells.Find(What:=ans, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not cell Is Nothing Then
cell.Offset(0, 4).Select
End If
End If
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rubix" <whims123@hotmail.com> wrote in message
news:gr6t02d7vqm41mr98pu7p810anjtns147t@4ax.com...
> I would like to input my stock according to item number.
>
> A4 to A1000 are my stock items.
>
> Cell D is my balance.
>
> I would like to create a macro where I can Ctrl + F to find the stock
> number but apon pressing Enter such as Stock item #42 which would
> appear at say A149 to go directly to D149.
>
> Thanks,
> Rube
>
>

[Rubix]

Thanks Bob this is what I'm looking for. However my stock numbers
range from 2 to 6 didgets. How can I insert code to go to Enter Entire
Cell Content. Right now if I go to 8888 for example then to 42 I get
the next item with a 42 in it such as 554260 or the price in column B.

On Wed, 8 Mar 2006 09:23:46 -0000, "Bob Phillips"
<bob.phillips@notheretiscali.co.uk> wrote:

>Sub Macro1()
>Dim ans
>Dim cell As Range
> ans = InputBox("Supply search string")
> If ans <> False Then
> Set cell = Cells.Find(What:=ans, _
> After:=ActiveCell, _
> LookIn:=xlFormulas, _
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False, _
> SearchFormat:=False)
> If Not cell Is Nothing Then
> cell.Offset(0, 4).Select
> End If
> End If
>End Sub

[Bob Phillips]

Sub Macro1()
Dim ans
Dim cell As Range
ans = InputBox("Supply search string")
If ans <> False Then
Set cell = Cells.Find(What:=ans, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not cell Is Nothing Then
cell.Offset(0, 4).Select
End If
End If
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rubix" <whims123@hotmail.com> wrote in message
news:hhvu025pkqfvfelmnk2oa3mfu9h8r2atfc@4ax.com...
> Thanks Bob this is what I'm looking for. However my stock numbers
> range from 2 to 6 didgets. How can I insert code to go to Enter Entire
> Cell Content. Right now if I go to 8888 for example then to 42 I get
> the next item with a 42 in it such as 554260 or the price in column B.
>
> On Wed, 8 Mar 2006 09:23:46 -0000, "Bob Phillips"
> <bob.phillips@notheretiscali.co.uk> wrote:
>
> >Sub Macro1()
> >Dim ans
> >Dim cell As Range
> > ans = InputBox("Supply search string")
> > If ans <> False Then
> > Set cell = Cells.Find(What:=ans, _
> > After:=ActiveCell, _
> > LookIn:=xlFormulas, _
> > LookAt:=xlPart, _
> > SearchOrder:=xlByRows, _
> > SearchDirection:=xlNext, _
> > MatchCase:=False, _
> > SearchFormat:=False)
> > If Not cell Is Nothing Then
> > cell.Offset(0, 4).Select
> > End If
> > End If
> >End Sub
>

[Rubix]

Thanks again Bob, I realized I had to make change to the After string
to Range A1. Works like a charm. I may ask to help make my monthly
database a lot more simplified than the method I am using now.

Currently I have a worksheet made for each day of the month. What I'd
really like to do is have all the days on one worksheet.

Day 1 would be on cell.Offset(0, 4).Select - Day 2 would be on 0, 6
(2 cells per day). Rather than editing macro below to change
cell.Offset I would like to create another macro function to go to a
certain day say Day 2 which is Column F,G then remain in cell F until
day is changed.

Hope this makes sense. If not I can live with what I have until I
decide to learn more about macros.

Rube

On Thu, 9 Mar 2006 10:46:26 -0000, "Bob Phillips"
<bob.phillips@notheretiscali.co.uk> wrote:

>Sub Macro1()
>Dim ans
>Dim cell As Range
> ans = InputBox("Supply search string")
> If ans <> False Then
> Set cell = Cells.Find(What:=ans, _
> After:=ActiveCell, _
> LookIn:=xlFormulas, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False, _
> SearchFormat:=False)
> If Not cell Is Nothing Then
> cell.Offset(0, 4).Select
> End If
> End If
>End Sub