value input on 1 ws, find on another, put date in

  • Thread starter Thread starter robzrob
  • Start date Start date
R

robzrob

Hello

Setting up a wkbk for other users. The user will type in a value on 1 ws. I want the macro to find this value in another ws, then, in that ws, select this cell: col A, row wherever-the-value-was-found.

Thanks
 
Hello



Setting up a wkbk for other users. The user will type in a value on 1 ws. I want the macro to find this value in another ws, then, in that ws, select this cell: col A, row wherever-the-value-was-found.



Thanks
Click to expand...

Hi Rob,
Give this a try, it may get you started.

Where:
Data is a named range in sheet 2.
Range("D1") is on sheet 1 (can be a drop down list)

Sub DateInCell()
Dim Data As Range
Dim i As Integer
i = Sheets("Sheet1").Range("D1").Value

Sheets("Sheet2").Select
Range("Data").Select
Cells.Find(What:=i, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1) = Date

Range("A1").Select
End Sub

I'm pretty sure you can also use a Selection Change event macro so that all you have to do is enter the number in a designated cell on sheet 1.

Hope this help.
Regards,
Howard
 
Hi Rob,

Give this a try, it may get you started.



Where:

Data is a named range in sheet 2.

Range("D1") is on sheet 1 (can be a drop down list)



Sub DateInCell()

Dim Data As Range

Dim i As Integer

i = Sheets("Sheet1").Range("D1").Value



Sheets("Sheet2").Select

Range("Data").Select

Cells.Find(What:=i, After:=ActiveCell, _

LookIn:=xlFormulas, LookAt:= _

xlWhole, SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False _

, SearchFormat:=False).Activate

ActiveCell.Offset(0, 1) = Date



Range("A1").Select

End Sub



I'm pretty sure you can also use a Selection Change event macro so that all you have to do is enter the number in a designated cell on sheet 1.



Hope this help.

Regards,

Howard
Click to expand...



Thanks, will give it a go. That Selection Change sounds useful too.
 
Back
Top