Loop with dynamic range

Your sample code was working in column C, but your later post and the
original talk about column B. It it is actually column B:

Sub FillInData()
Dim rng As Range, rng1 As Range
Dim rng4 As Range, rng2 As Range
With Worksheets("Profile")
Set rng4 = .Cells(1, 2)
If IsEmpty(rng4) Then _
Set rng4 = rng4.End(xlDown)
Set rng = .Range(rng4, _
.Cells(Rows.Count, 2).End(xlUp))
End With
Set rng1 = rng.Offset(0, -1)
On Error Resume Next
Set rng2 = rng1.SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng2 Is Nothing Then
rng2.Formula = "=" & rng2(1).Offset(-1, 0).Address(0, 0)
rng1.Formula = rng1.Value
End If
End Sub

--
Regards,
Tom Ogilvy


"mthomas" <mthomas.1yv37y_1132601402.5061@excelforum-nospam.com> wrote in
message news:mthomas.1yv37y_1132601402.5061@excelforum-nospam.com...
>
> Thanks Tom so much for your reply. I'm getting the following error
> message:
>
> "Application-defined or Object-defined error"
>
> The line of code is:
>
> rng2.Formula = "=" & rng2(1).Offset(-1, 0).Address(0, 0)
>
>
> Thanks again for everything!
>
>
> --
> mthomas
> ------------------------------------------------------------------------
> mthomas's Profile:
http://www.excelforum.com/member.php...o&userid=25649
> View this thread: http://www.excelforum.com/showthread...hreadid=486450
>

Never mind, I misunderstood your description of what you wanted filled in.

the code could be easily fixed, but your happy with the looping approach, so
no sense in it.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:OXhvGct7FHA.1864@TK2MSFTNGP12.phx.gbl...
> Your sample code was working in column C, but your later post and the
> original talk about column B. It it is actually column B:
>
> Sub FillInData()
> Dim rng As Range, rng1 As Range
> Dim rng4 As Range, rng2 As Range
> With Worksheets("Profile")
> Set rng4 = .Cells(1, 2)
> If IsEmpty(rng4) Then _
> Set rng4 = rng4.End(xlDown)
> Set rng = .Range(rng4, _
> .Cells(Rows.Count, 2).End(xlUp))
> End With
> Set rng1 = rng.Offset(0, -1)
> On Error Resume Next
> Set rng2 = rng1.SpecialCells(xlBlanks)
> On Error GoTo 0
> If Not rng2 Is Nothing Then
> rng2.Formula = "=" & rng2(1).Offset(-1, 0).Address(0, 0)
> rng1.Formula = rng1.Value
> End If
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "mthomas" <mthomas.1yv37y_1132601402.5061@excelforum-nospam.com> wrote in
> message news:mthomas.1yv37y_1132601402.5061@excelforum-nospam.com...
> >
> > Thanks Tom so much for your reply. I'm getting the following error
> > message:
> >
> > "Application-defined or Object-defined error"
> >
> > The line of code is:
> >
> > rng2.Formula = "=" & rng2(1).Offset(-1, 0).Address(0, 0)
> >
> >
> > Thanks again for everything!
> >
> >
> > --
> > mthomas
> > ------------------------------------------------------------------------
> > mthomas's Profile:
> http://www.excelforum.com/member.php...o&userid=25649
> > View this thread:
http://www.excelforum.com/showthread...hreadid=486450
> >
>
>

this is a really good code and have used it as modified below..

but I need to actually loop a formula with formatting .can this be done?

Sub AddEmployeeNumber()
Dim lastrow As Long, r As Integer


lastrow = Cells(Rows.Count, "B").End(xlUp).Row
For r = 5 To lastrow Step 4
If Cells(r, 2) = "" Then Cells(r, 2) = Cells(r - 1, 2)

Next r

End Sub

Originally Posted by Tom Ogilvy

Never mind, I misunderstood your description of what you wanted filled in.

the code could be easily fixed, but your happy with the looping approach, so
no sense in it.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:OXhvGct7FHA.1864@TK2MSFTNGP12.phx.gbl...
> Your sample code was working in column C, but your later post and the
> original talk about column B. It it is actually column B:
>
> Sub FillInData()
> Dim rng As Range, rng1 As Range
> Dim rng4 As Range, rng2 As Range
> With Worksheets("Profile")
> Set rng4 = .Cells(1, 2)
> If IsEmpty(rng4) Then _
> Set rng4 = rng4.End(xlDown)
> Set rng = .Range(rng4, _
> .Cells(Rows.Count, 2).End(xlUp))
> End With
> Set rng1 = rng.Offset(0, -1)
> On Error Resume Next
> Set rng2 = rng1.SpecialCells(xlBlanks)
> On Error GoTo 0
> If Not rng2 Is Nothing Then
> rng2.Formula = "=" & rng2(1).Offset(-1, 0).Address(0, 0)
> rng1.Formula = rng1.Value
> End If
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "mthomas" <mthomas.1yv37y_1132601402.5061@excelforum-nospam.com> wrote in
> message news:mthomas.1yv37y_1132601402.5061@excelforum-nospam.com...
> >
> > Thanks Tom so much for your reply. I'm getting the following error
> > message:
> >
> > "Application-defined or Object-defined error"
> >
> > The line of code is:
> >
> > rng2.Formula = "=" & rng2(1).Offset(-1, 0).Address(0, 0)
> >
> >
> > Thanks again for everything!
> >
> >
> > --
> > mthomas
> > ------------------------------------------------------------------------
> > mthomas's Profile:
> http://www.excelforum.com/member.php...o&userid=25649
> > View this thread:
http://www.excelforum.com/showthread...hreadid=486450
> >
>
>