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

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

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
'<<=============


---
Regards,
Norman



"BeJay" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Worked a treat Norman - many thanks

Norman Jones wrote:
> 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
> '<<=============
>
>
> ---
> Regards,
> Norman
>
>
>
> "BeJay" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >