Help with user prompt

R

Rob Gould

Hi all,

I have a macro with the following code:

thisDate = Range("Dates!A4").Value
Sheets("Data").Select
Range("G1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("E6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Find(What:=thisDate, After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("PAC Track").Select
Range("C6").Select
Application.CutCopyMode = False
End Sub

As there is the possibility that a user may forget to change the date
before running this, I wish to replace the first line with code that
will open a prompt asking the user to enter the date for the data
entered. This date will then become "thisDate" and the rest of the
code can run.

Please could someone help with this

Thanks...Rob
 
D

Don Guillett

thisdate = InputBox("Pls enter date mm/dd/yy")

untested but this might work from anywhere in the workbook.
thisdate = InputBox("Pls enter date mm/dd/yy")
with sheets("Data")
ds=.columns(5).find(thisdate).address
..[ds].offset(,2)=.range("g1").end(xltoright).value
end with
 
R

Rob Gould

Thanks Don, but that doesn't seem to set the date entered into the
input box as "thisDate" and the process stops during the search for the
date in column E on Data sheet.

Rob

Don said:
thisdate = InputBox("Pls enter date mm/dd/yy")

untested but this might work from anywhere in the workbook.
thisdate = InputBox("Pls enter date mm/dd/yy")
with sheets("Data")
ds=.columns(5).find(thisdate).address
.[ds].offset(,2)=.range("g1").end(xltoright).value
end with

--
Don Guillett
SalesAid Software
[email protected]
Rob Gould said:
Hi all,

I have a macro with the following code:

thisDate = Range("Dates!A4").Value
Sheets("Data").Select
Range("G1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("E6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Find(What:=thisDate, After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("PAC Track").Select
Range("C6").Select
Application.CutCopyMode = False
End Sub

As there is the possibility that a user may forget to change the date
before running this, I wish to replace the first line with code that
will open a prompt asking the user to enter the date for the data
entered. This date will then become "thisDate" and the rest of the
code can run.

Please could someone help with this

Thanks...Rob
Click to expand...
Click to expand...
 
R

Rob Gould

Don,

Well, I'm not sure what I'm doing wrong then. I used that code and it
stops on this line:

ds = .Columns(5).Find(thisdate).Address

Even tried it in a new workbook with a worksheet called Data and same
thing...
 
D

Don Guillett

As I said, this was tested and worked fine. Either you don't have an
appropriate date in column 5 (a,b,c,d,E) or your dates are not formatted as
dates or you didn't enter the date question as directed.
 
Top