I am using Excel to keep track of monthly inventory and I need to
input stock on daily basis. Column A has a 6 didget stock numbers
approx. 300 items and I have an in & out column for each day of the
month. I need to remain in one column while I find the stock no.
according to the day.

Ex.

#112233 input 2 to day March 7

#112233 being Column A98, March 7 being column Q entered into Q98 in 2
pcs.

Stock no. #2666 A56 entered to Q56. Each time I enter a stock no. the
highlited cell will remain in column Q until request to change day of
month.

Need macro to enter date, another to find stock cell.

Currently using macro below but offset would not be used because
offset continuously changes daily.

Sub Macro1()
Dim ans
Dim cell As Range
ans = InputBox("Enter Stock number")
If ans < False Then
Set cell = Cells.Find(What:=ans, _
After:=Range("A1"), _
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


Thanks,
Rube

Try something like:

If Not cell Is Nothing Then
cell.Offset(0, Day(Date)+1).Select

This will offset 15 columns to the riht (14/03/2006)

HTH
--
AP

"Rubix" a écrit dans le message de
...
I am using Excel to keep track of monthly inventory and I need to
input stock on daily basis. Column A has a 6 didget stock numbers
approx. 300 items and I have an in & out column for each day of the
month. I need to remain in one column while I find the stock no.
according to the day.

Ex.

#112233 input 2 to day March 7

#112233 being Column A98, March 7 being column Q entered into Q98 in 2
pcs.

Stock no. #2666 A56 entered to Q56. Each time I enter a stock no. the
highlited cell will remain in column Q until request to change day of
month.

Need macro to enter date, another to find stock cell.

Currently using macro below but offset would not be used because
offset continuously changes daily.

Sub Macro1()
Dim ans
Dim cell As Range
ans = InputBox("Enter Stock number")
If ans < False Then
Set cell = Cells.Find(What:=ans, _
After:=Range("A1"), _
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


Thanks,
Rube

Thanks for your response Ardus. Sorry I did not mention, inventory is
not entered on a daily basis. I previously used Lotus. Looking to do
the same here if possible. One function selected the day of the month
and remained in the column until change of day. The other function
found the stock item number and inventory was entered that way.

Rube


On Tue, 14 Mar 2006 10:34:02 +0100, "Ardus Petus"
wrote:

Try something like:

If Not cell Is Nothing Then
cell.Offset(0, Day(Date)+1).Select

This will offset 15 columns to the riht (14/03/2006)

HTH