How to accept input and then populate/concatenate this with other
 

I need assistance with integrating the following within a macro I have.

Ask the user to enter a 4 digit YEAR.

Cell AM1 should be populated with the word Start.
Cell AN1 should be populated with the word End.
Cell AM2 should be populated with 01/01and the 4 digit year entered above.
Cell AN2 should be populated with 01/03 and the 4 digit year entered above.
Cell AM3 should be populated with 01/04and the 4 digit year entered above.
Cell AN3 should be populated with 31/08 and the 4 digit year entered above.
Cell AM4 should be populated with 01/09and the 4 digit year entered above.
Cell AN4 should be populated with 31/12 and the 4 digit year entered above.

All assistance offered gratefully received.

How to accept input and then populate/concatenate this with other
 

A bit messy but this will do it

Sub fillcells()

Msg = "Enter a year in the format YYYY"
response = InputBox(Msg)
If response = "" Then End
Worksheets("Sheet1").Cells(1, 39).Value = "Start"
Worksheets("Sheet1").Cells(1, 40).Value = "End"
Worksheets("Sheet1").Cells(2, 39).Value = "01/01/" & response
Worksheets("Sheet1").Cells(2, 40).Value = "01/03/" & response
Worksheets("Sheet1").Cells(3, 39).Value = "01/04/" & response
Worksheets("Sheet1").Cells(3, 40).Value = "31/08/" & response
Worksheets("Sheet1").Cells(4, 39).Value = "01/09/" & response
Worksheets("Sheet1").Cells(4, 40).Value = "31/12/" & response
End Sub

Mike

"Pank" wrote:

I need assistance with integrating the following within a macro I have.

Ask the user to enter a 4 digit YEAR.

Cell AM1 should be populated with the word Start.
Cell AN1 should be populated with the word End.
Cell AM2 should be populated with 01/01and the 4 digit year entered above.
Cell AN2 should be populated with 01/03 and the 4 digit year entered above.
Cell AM3 should be populated with 01/04and the 4 digit year entered above.
Cell AN3 should be populated with 31/08 and the 4 digit year entered above.
Cell AM4 should be populated with 01/09and the 4 digit year entered above.
Cell AN4 should be populated with 31/12 and the 4 digit year entered above.

All assistance offered gratefully received.

How to accept input and then populate/concatenate this with ot
 

Mike,

Firstly, thanks for you valuable input.

Secondly, I note that the by using the Worksheets("Sheet1€�), reference we
are defaulting it to Sheet1. How can I change this so that it refers to the
current sheet?. The reason for this is that the text and year should be
inserted in all sheets that are processed in a loop.


"Mike" wrote:

A bit messy but this will do it

Sub fillcells()

Msg = "Enter a year in the format YYYY"
response = InputBox(Msg)
If response = "" Then End
Worksheets("Sheet1").Cells(1, 39).Value = "Start"
Worksheets("Sheet1").Cells(1, 40).Value = "End"
Worksheets("Sheet1").Cells(2, 39).Value = "01/01/" & response
Worksheets("Sheet1").Cells(2, 40).Value = "01/03/" & response
Worksheets("Sheet1").Cells(3, 39).Value = "01/04/" & response
Worksheets("Sheet1").Cells(3, 40).Value = "31/08/" & response
Worksheets("Sheet1").Cells(4, 39).Value = "01/09/" & response
Worksheets("Sheet1").Cells(4, 40).Value = "31/12/" & response
End Sub

Mike

"Pank" wrote:

I need assistance with integrating the following within a macro I have.

Ask the user to enter a 4 digit YEAR.

Cell AM1 should be populated with the word Start.
Cell AN1 should be populated with the word End.
Cell AM2 should be populated with 01/01and the 4 digit year entered above.
Cell AN2 should be populated with 01/03 and the 4 digit year entered above.
Cell AM3 should be populated with 01/04and the 4 digit year entered above.
Cell AN3 should be populated with 31/08 and the 4 digit year entered above.
Cell AM4 should be populated with 01/09and the 4 digit year entered above.
Cell AN4 should be populated with 31/12 and the 4 digit year entered above.

All assistance offered gratefully received.

How to accept input and then populate/concatenate this with ot
 

Changing the lines to

ActiveSheet.Cells(1, 39).Value = "Start"

will do that.

Mike

"Pank" wrote:

Mike,

Firstly, thanks for you valuable input.

Secondly, I note that the by using the Worksheets("Sheet1€�), reference we
are defaulting it to Sheet1. How can I change this so that it refers to the
current sheet?. The reason for this is that the text and year should be
inserted in all sheets that are processed in a loop.


"Mike" wrote:

A bit messy but this will do it

Sub fillcells()

Msg = "Enter a year in the format YYYY"
response = InputBox(Msg)
If response = "" Then End
Worksheets("Sheet1").Cells(1, 39).Value = "Start"
Worksheets("Sheet1").Cells(1, 40).Value = "End"
Worksheets("Sheet1").Cells(2, 39).Value = "01/01/" & response
Worksheets("Sheet1").Cells(2, 40).Value = "01/03/" & response
Worksheets("Sheet1").Cells(3, 39).Value = "01/04/" & response
Worksheets("Sheet1").Cells(3, 40).Value = "31/08/" & response
Worksheets("Sheet1").Cells(4, 39).Value = "01/09/" & response
Worksheets("Sheet1").Cells(4, 40).Value = "31/12/" & response
End Sub

Mike

"Pank" wrote:

I need assistance with integrating the following within a macro I have.

Ask the user to enter a 4 digit YEAR.

Cell AM1 should be populated with the word Start.
Cell AN1 should be populated with the word End.
Cell AM2 should be populated with 01/01and the 4 digit year entered above.
Cell AN2 should be populated with 01/03 and the 4 digit year entered above.
Cell AM3 should be populated with 01/04and the 4 digit year entered above.
Cell AN3 should be populated with 31/08 and the 4 digit year entered above.
Cell AM4 should be populated with 01/09and the 4 digit year entered above.
Cell AN4 should be populated with 31/12 and the 4 digit year entered above.

All assistance offered gratefully received.

How to accept input and then populate/concatenate this with ot
 

Mike,

Thanks a lot, worked a treat.

"Mike" wrote:

Changing the lines to

ActiveSheet.Cells(1, 39).Value = "Start"

will do that.

Mike

"Pank" wrote:

Mike,

Firstly, thanks for you valuable input.

Secondly, I note that the by using the Worksheets("Sheet1€�), reference we
are defaulting it to Sheet1. How can I change this so that it refers to the
current sheet?. The reason for this is that the text and year should be
inserted in all sheets that are processed in a loop.


"Mike" wrote:

A bit messy but this will do it

Sub fillcells()

Msg = "Enter a year in the format YYYY"
response = InputBox(Msg)
If response = "" Then End
Worksheets("Sheet1").Cells(1, 39).Value = "Start"
Worksheets("Sheet1").Cells(1, 40).Value = "End"
Worksheets("Sheet1").Cells(2, 39).Value = "01/01/" & response
Worksheets("Sheet1").Cells(2, 40).Value = "01/03/" & response
Worksheets("Sheet1").Cells(3, 39).Value = "01/04/" & response
Worksheets("Sheet1").Cells(3, 40).Value = "31/08/" & response
Worksheets("Sheet1").Cells(4, 39).Value = "01/09/" & response
Worksheets("Sheet1").Cells(4, 40).Value = "31/12/" & response
End Sub

Mike

"Pank" wrote:

I need assistance with integrating the following within a macro I have.

Ask the user to enter a 4 digit YEAR.

Cell AM1 should be populated with the word Start.
Cell AN1 should be populated with the word End.
Cell AM2 should be populated with 01/01and the 4 digit year entered above.
Cell AN2 should be populated with 01/03 and the 4 digit year entered above.
Cell AM3 should be populated with 01/04and the 4 digit year entered above.
Cell AN3 should be populated with 31/08 and the 4 digit year entered above.
Cell AM4 should be populated with 01/09and the 4 digit year entered above.
Cell AN4 should be populated with 31/12 and the 4 digit year entered above.

All assistance offered gratefully received.