Loop with dynamic range

Need to do the following:

Example:
Assuming Loop finds The first populated cell at A8
If A8 <> "" Then
variable = B8.value
For each cell below A8 that = ""
Each cell below B8 = variable
Until A <> ""
Continue Loop from NEXT CELL <> ""

My code so far, which works except for iterating down the column.

Dim i2
For Each i2 In Range([profile!C1], [profile!C1].End(xlDown))
If i2.Offset(0, -2) <> "" Then
Dim empID
empID = i2.Offset(0, -1).Value
If i2.Offset(1, -2) = "" Then
i2.Offset(1, -1) = empID
End If
End If
Next

Thanks again everyone!

Just following up on this everyone. See original post. Thanks!

This might work.

Sub FillInData()
Dim rng As Range, rng1 As Range
Dim rng4 As Range, rng2 As Range
With Worksheets("Profile")
Set rng4 = .Cells(1, 3)
If IsEmpty(rng4) Then _
Set rng4 = rng4.End(xlDown)
Set rng = .Range(rng4, _
.Cells(Rows.Count, 3).End(xlUp))
End With
Set rng1 = rng.Offset(0, -2)
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" <[email protected]> wrote in
message news:[email protected]...
>
> Need to do the following:
>
> Example:
> Assuming Loop finds The first populated cell at A8
> If A8 <> "" Then
> variable = B8.value
> For each cell below A8 that = ""
> Each cell below B8 = variable
> Until A <> ""
> Continue Loop from NEXT CELL <> ""
>
> My code so far, which works except for iterating down the column.
>
> Dim i2
> For Each i2 In Range([profile!C1], [profile!C1].End(xlDown))
> If i2.Offset(0, -2) <> "" Then
> Dim empID
> empID = i2.Offset(0, -1).Value
> If i2.Offset(1, -2) = "" Then
> i2.Offset(1, -1) = empID
> End If
> End If
> Next
>
> Thanks again everyone!
>
>
> --
> mthomas
> ------------------------------------------------------------------------
> mthomas's Profile:
http://www.excelforum.com/member.php...o&userid=25649
> View this thread: http://www.excelforum.com/showthread...hreadid=486450
>

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!

What celll is rng2 set to? You'll get an error if rng2 is
referencing a cell in row 1.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


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

Tom and Chip, I used the following code and it works great. I just needed to use "C" for the lastrow and I will use a prompt for the user to enter the row that the code will start in. Thanks for everything!

Sub AddEmployeeNumber()
Dim lastrow As Long, r As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For r = 3 To lastrow
If Cells(r, 1) = "" Then Cells(r, 2) = Cells(r - 1, 2)
Next r
End Sub

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

Please Login or Register  to view this content.

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