VBA or macro to find irregular blanks in column and insert value from another sheet

[BeJay]

Hi, I have a column of data including values with intermittent blanks.
I need to refer these blanks to a date field on sheet 1. The number of
populated cells inbetween the blanks is different each time the
spreadsheet is completed. I have tried to do this by recording a macro
using filters, but unsuccessfully. Could do with some VBA pointers as
am useless (but getting better!) Any advice gratefully received! Thanks
in advance Bev J

 

[Norman Jones]

Hi Bev,

Try something like:

'=============>>
Public Sub Tester()
Dim rng As Range
Dim myDate As Date
Const col As String = "A:A" '<<===
CHANGE

With ThisWorkbook
myDate = .Sheets("Sheet1").Range("D1").Value '<<=== CHANGE

On Error Resume Next
Set rng = .Sheets("Sheet2").Columns(col). _
SpecialCells(xlBlanks)
On Error GoTo 0

End With

If Not rng Is Nothing Then
rng.Value = myDate
End If

End Sub
'<<=============

 

[BeJay]

Worked a treat Norman - many thanks

Hi Bev,

Try something like:

'=============>>
Public Sub Tester()
Dim rng As Range
Dim myDate As Date
Const col As String = "A:A" '<<===
CHANGE

With ThisWorkbook
myDate = .Sheets("Sheet1").Range("D1").Value '<<=== CHANGE

On Error Resume Next
Set rng = .Sheets("Sheet2").Columns(col). _
SpecialCells(xlBlanks)
On Error GoTo 0

End With

If Not rng Is Nothing Then
rng.Value = myDate
End If

End Sub
'<<=============