CODEWITHSUNDEEP
excelvba
jsgunner
jsgunner

Reputation: 41

Looping IF statement in VBA

I'm trying to create an IF statement that displays the value '#N/A' in any cell that is blank within a specific range.

I need the range to be from cell A2 to the last row with data in column E. My code is as follows:

Dim desiredRange as Range
Set my_sheet = ThisWorkbook.Worksheets("Sheet1")
last_row = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count,1).End(xlUp).Row
Set desiredRange = my_sheet.Range("A2:E" & last_row)

On Error Resume Next
For each cell In Range desiredRange.cells
    If cell.Value = "" Then
      cell.Value = "#N/A"
    End If

Next cell

I keep getting the Run-time error '1004: Method 'Range' of object' _Global' failed.

Are there any alternative ways of creating an IF statement to make blank cells = #N/A within a dynamic range?

Upvotes: 0

Views: 75

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Here's another method using the Range.Replace method (no looping; no if)

Note that I check only column E for last row, as you write in your text, and not the A:E range, as you have in your code

Option Explicit
Sub text()
    Dim desiredRange As Range, last_row As Long, my_sheet As Worksheet
    Set my_sheet = ThisWorkbook.Worksheets("Sheet2")
    
    With my_sheet
        last_row = .Cells(.Rows.Count, 5).End(xlUp).Row
        Set desiredRange = Range(.Cells(1, 1), .Cells(last_row, 1))
        desiredRange.Replace "", "#N/A"
    End With
End Sub

Note: If you prefer to have the actual error in the cell, rather than the string representation, change the replacement argument to "=NA()"

Upvotes: 1

Related Questions