FillDown in VBA

[ldashev]

Hi,

Can someone please help...I have a macro where an input box prompts a user to input a single date...it's only a single date that needs to be copied down based on the length of an adjacent column:

myResponse = InputBox("Date EMAIL Recieved in MM-DD-YYYY Format")
Range("F2") = myResponse
Range("F2").Select
'Paste down
Dim lastRowFF As Long
lastRow = Range("G" & Rows.Count).End(xlUp).Row
Range("F2").AutoFill Destination:=Range("F2:F" & lastRow)

This doesn't work because the dates are auto filled so you get:

12-31-2016 (Cell F2)

01-01-2017

01-02-2017

01-03-2017

01-04-2017

<tbody>
</tbody>

What I'm looking to do with .filldown is

12-31-2016 (F2)

12-31-2016

12-31-2016

12-31-2016

12-31-2016

<tbody>
</tbody>

I tried applying this, but it didn't work:


myResponse = InputBox("Date EMAIL Recieved in MM-DD-YYYY Format")
Range("F2") = myResponse
Range("F2").Select
'Paste down
Dim lastRowFF As Long
lastRow = Range("G" & Rows.Count).End(xlUp).Row
Range("F2").FillDown Destination:=Range("F2:F" & lastRow)

 

[pgc01]

Hi

If I understand correctly what you want is to copy, not to fill down.

Try:

Sub CopyDown()
Dim myResponse As Variant
Dim lastRow As Long

myResponse = InputBox("Date EMAIL Recieved in MM-DD-YYYY Format")
Range("F2") = myResponse

lastRow = Range("G" & Rows.Count).End(xlUp).Row

'Paste down
Range("F2").Copy Destination:=Range("F2:F" & lastRow)

End Sub

 

[pgc01]

Notice that you could also write the value directly in the range:

Sub CopyDown()
Dim myResponse As Variant
Dim lastRow As Long

myResponse = InputBox("Date EMAIL Recieved in MM-DD-YYYY Format")

lastRow = Range("G" & Rows.Count).End(xlUp).Row

Range("F2:F" & lastRow).Value = myResponse

End Sub

 

[ldashev]

Perfect! That worked! Thanks

Notice that you could also write the value directly in the range:

Sub CopyDown()
Dim myResponse As Variant
Dim lastRow As Long

myResponse = InputBox("Date EMAIL Recieved in MM-DD-YYYY Format")

lastRow = Range("G" & Rows.Count).End(xlUp).Row

Range("F2:F" & lastRow).Value = myResponse

End Sub

 

[pgc01]

You're welcome.