Loop with dynamic range

  • Thread starter Thread starter mthomas
  • Start date Start date
M

mthomas

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!
 
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
 
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!
 
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
 
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 said:
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
Click to expand...
 
Back
Top