For each cell in a specific range if blank enter "N/A" - VBA

[KNKN9]

Hi,

I have a code

 For Each cel In rng            If IsEmpty(cel.Value) Or cel.Value = vbNullString Then
                cel.Value = "N/A"
            End If
        Next cel

However, this code does not seem to work when I enter it in a different module. I can trying to amend this code so it is able to work. I need the code to go through each cell in the specific range and if a cell is blank then enter the string "N/A". If you are able to help that'll be much appreciated.

Thanks

 

[My Aswer Is This]

You said:
"in the specific range"

My question:

What "specific range"

 

[My Aswer Is This]

If you want to check the range: "A1:A25"

Try this:

Modify to your needs:

Sub Check_Range()
Dim c As Range
    For Each c In Range("A1:A25")
        If c.Value = vbNullString Then c.Value = "N/A"
    Next
End Sub

 

[Rick Rothstein]

If the range is fixed and never changing (I'll assume A1:A25 for example purposes), then you could so this...

Sub CheckRange()
  [A1:A25] = [If(A1:A25="","N/A",A1:A25)]
End Sub

If the range is not fixed, then you could do this (where you can use whatever method you want to set the address range)...

Sub Check_Range()
  Dim Addr As String
  Addr = "A1:A25"
  Range(Addr) = Evaluate(Replace("If(@="""",""N/A"",@)", "@", Addr))
End Sub

 

[KNKN9]

Hi Rick,

I think the problem I have is that my range is based on selection

  Dim colLast As LongcolLast = Cells(12, Columns.Count).End(xlToLeft).Column
Cells(12, colLast).Select
Range(Selection, Selection.Offset(44, 0)).select
Dim rng As Range
Set rng= Range(Selection, Selection.Offset(44, 0))

My code at the moments finds the last cell in the data set that has data and from this cell to the last cell in the data set with data.

your code below wouldn't work for me

    Dim Addr As String
  Addr = "A1:A25"
  Range(Addr) = Evaluate(Replace("If(@="""",""N/A"",@)", "@", Addr))

 

[Fluff]

You can combine your code & Rick's code

Sub test()

    Dim colLast As Long
    Dim Rng As Range

    colLast = Cells(12, Columns.Count).End(xlToLeft).Column
    Set Rng = Range(Cells(12, colLast), Cells(12, colLast).Offset(44))
    Range(Rng.Address) = Evaluate(Replace("If(@="""",""N/A"",@)", "@", Rng.Address))
    
End Sub

 

[KNKN9]

Thank you all

Managed to get it working !