Macro for copying cell from column B if column A is not blank

[stevewood1]

Hello,

I wonder if you can help.

I have a worksheet with a list of dates is column A and an amount in Cell B2.

I need a Macro that will copy the value of B2 and paste it into every cell in column B where the corresponding cell in column A is not blank.

I have seen various code for doing similar things but not one for this exact scenario and I haven't managed to alter any of the ones that I have found to do this.

Many thanks,

Steve

 

[trunten]

Sub copyValues()

    Dim s As String, c As Range
    
    s = Range("B2").Value
    For Each c In Range("A1", Cells(Rows.Count, 1).End(xlUp))
        If LenB(c.Value) Then c(1, 2).Value = s
    Next c
End Sub

 

[stevewood1]

That's great. Thanks for your help!

 

[stevewood1]

Thank you for the really quick response.

I have another sheet where I have to do the same thing but the dates are in row E10 and down and the value to copy is F10. I thought I would just be able to duplicate the code and amend the range but when I try the numbers are going in random cells.

Thanks you for all your help.

Steve

 

[Fluff]

Are your dates hard values, or the result of a formula?

 

[trunten]

Ok, I've just changed it a bit so you can adapt it as you see fit.

Sub copyValues()

    Dim c As Range, pasteValue As String, dateCol As String, pastCol As String
    
    dateCol = "A"
    pastecol = "B"
    pasteValue = Range("B2").Value
    
    For Each c In Range(dateCol & "1", Cells(Rows.Count, dateCol).End(xlUp))
        If LenB(c.Value) Then Range(pastecol & c.Row).Value = pasteValue
    Next c
End Sub