CODEWITHSUNDEEP
vbaloopsreplace
Ian
Ian

Reputation: 53

Loop through columns and replace missing values

I'm trying to loop through various columns and replace a blank cell with a value but two errors are emerging:

1.) I know the count should = 8 but its returning 9

2.) the last line of code, ".value = ", is returning a application-defined or object defined error

my test data looks like this starting in "D4":

1   1   1       
    2   2       
3       3       
4   4           
5   5   5       
6   6   6       
7   7   7       
8   8   8       
9   9   9       



Sub test()
Dim c As Integer, nextfree As Integer, rangesum As Integer


For c = 4 To 6
  If Range(Cells(4, c), Cells(12, c)).Count = 8 Then

    nextfree = Range(Cells(4, c), Cells(12, c)).Cells.SpecialCells(xlCellTypeBlanks).Row
    rangesum = Excel.WorksheetFunction.Sum(Worksheets("Sheet1").Range(Cells(4, c), Cells(12, c)))
    Worksheets("Sheet1").Range(Cells(nextfree, c)).Value = rangesum * 2

    End If
Next c
End Sub

Upvotes: 0

Views: 79

Answers (1)

Kirszu
Kirszu

Reputation: 364

Try:

Sub test()
Dim c As Integer, nextfree As Integer, rangesum As Integer

For c = 4 To 6
  If WorksheetFunction.CountA(Range(Cells(4, c), Cells(12, c))) = 8 Then
   nextfree = Range(Cells(4, c), Cells(12, c)).Cells.SpecialCells(xlCellTypeBlanks).Row
   rangesum = Excel.WorksheetFunction.Sum(Worksheets("Sheet1").Range(Cells(4, c), Cells(12, c)))
   Worksheets("Sheet1").Cells(nextfree, c).Value = rangesum * 2
End If
Next c
End Sub

1) First of all use WorksheetFunction.CountA(Range(Cells(4, c), Cells(12, c))) to return 8. It is counting only non-empty Cells. Range(Cells(4, c), Cells(12, c)).Count is counting every Cell in the Range - even when it is empty and that's why it is returning 9

2) You don't need to use Range there. Pointing the cell address directly from Cells method will do the thing. Cells(nextfree, c).Value instead of Range(Cells(nextfree, c).Value

Upvotes: 1

Related Questions