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!


--
mthomas
------------------------------------------------------------------------
mthomas's Profile: http://www.excelforum.com/member.php...o&userid=25649
View this thread: http://www.excelforum.com/showthread...hreadid=486450

Loop with dynamic range
 

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


--
mthomas
------------------------------------------------------------------------
mthomas's Profile: http://www.excelforum.com/member.php...o&userid=25649
View this thread: http://www.excelforum.com/showthread...hreadid=486450

Loop with dynamic range
 

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" wrote in
message ...

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

Loop with dynamic range
 

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

Loop with dynamic range
 

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"
wrote in message
...

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

Loop with dynamic range
 

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


--
mthomas
------------------------------------------------------------------------
mthomas's Profile: http://www.excelforum.com/member.php...o&userid=25649
View this thread: http://www.excelforum.com/showthread...hreadid=486450

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" wrote in
message ...

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

Loop with dynamic range
 

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" wrote in message
...
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" wrote in
message ...

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