CODEWITHSUNDEEP
vbaexcel
cookiemonster
cookiemonster

Reputation: 368

VBA For each cell in

I have a sheet with data in columns A to H. Some cells in column A and B have no values or are empty, but there are values in that rows other columns that is C to H. Now I have this code to do this. loop through each empty cell in B and put in the values. that is my code will fill down till the last non empty cell in B. but I get an error. 

Sub filldownemptyAB()     
 Dim cel As Range, rng As Long
rng = Cells(Rows.Count, 1).End(xlUp).row

For Each cel In rng.Cells
 With ActiveCell
 .Offset(, 0).Formula = "=year(today())"
.Offset(, -1).Value = "Actual"
End With
Next cel
  End Sub

the error is at this line

 For each cel in rng

Upvotes: 0

Views: 9077

Answers (2)

user1274820
user1274820

Reputation: 8144

I believe you're looking for this:

(I edited a few times to simplify things a bit and to scan column B instead of column A)

Sub FillDownEmptyAB()
Dim c, lr
lr = ActiveSheet.UsedRange.Rows.CountLarge
For Each c In Range("B1:B" & lr)
    If c.Value = "" Then
        c.Offset(,-1).Value = "Actual"
        c.Formula = "=YEAR(TODAY())"
    End If
Next c
End Sub

Input:

Input

Output:

Output

Upvotes: 5

braX
braX

Reputation: 11755

A Long is not a collection of Ranges.

I think you are trying to do something like this:

Sub filldownemptyAB()     
  Dim cel As Range
  Dim lastRow As Long
  lastRow = Cells(Rows.Count, 1).End(xlUp).row

  Dim rng as Range
  Set rng = Range("B" & lastRow)

  For Each cel In rng
    With cel
      .Offset(, 0).Formula = "=year(today())"
      .Offset(, -1).Value = "Actual"
    End With
  Next cel
End Sub

But it's hard to tell.

Upvotes: 2

Related Questions