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

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
 
H

Howard

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
 
R

robzrob

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find the correct sheet then find a value on that sheet 8
Find dupes, list in MsgBox with cell.address convert ws wide code toone sheet 6
Pulling specific Data from one WS to another WS based upon 1 criteria 1
Date showing as numbers in Textbox on Userform 2
Access Control With UserName 2
Runtime Error 1004:Method 'Range' of object'_Worksheet' Failed 4
Change ws designator 11
Copying data from Sheet1 which is on multiple rows for each customerto Sheet2 on single row. 2

Top