Help with user prompt

  • Thread starter Thread starter Rob Gould
  • Start date Start date
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
 
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
 
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
(e-mail address removed)
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...
 
A couple of changes. Use this instead TESTED. Input date as 6/1 for current
year or 7/1/2004 for another year. I tested on a sheet named Data from
another sheet in the workbook. Worked fine assuming that there was something
in row 1 after column G.

thisdate = InputBox("Pls enter date mm/dd/yyyy")
With Sheets("Data")
ds = .Columns(5).Find(thisdate).Address
..Range(ds).Offset(, 2) = .Range("g1").End(xlToRight).Value
End With

--
Don Guillett
SalesAid Software
(e-mail address removed)
Rob Gould said:
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
(e-mail address removed)
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...
Click to expand...
 
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...
 
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.
 
Back
Top