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:


[TABLE="width: 500"]
<tbody>[TR]
[TD]12-31-2016 (Cell F2)[/TD]
[/TR]
[TR]
[TD]01-01-2017[/TD]
[/TR]
[TR]
[TD]01-02-2017[/TD]
[/TR]
[TR]
[TD]01-03-2017[/TD]
[/TR]
[TR]
[TD]01-04-2017[/TD]
[/TR]
</tbody>[/TABLE]

What I'm looking to do with .filldown is

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]12-31-2016 (F2)[/TD]
[/TR]
[TR]
[TD]12-31-2016[/TD]
[/TR]
[TR]
[TD]12-31-2016[/TD]
[/TR]
[TR]
[TD]12-31-2016[/TD]
[/TR]
[TR]
[TD]12-31-2016[/TD]
[/TR]
</tbody>[/TABLE]

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.