3

Why is it when I try:

MsgBox (Cells(1, 1).Value)

The cases are as follows:

  1. When I write #123 in cell A1, I get #123 from MsgBox.
  2. When I write #N/A in cell A1, I get error type mismatch?

I know #N/A is the default error cell value in Excel, but I have manually input this value, does VBA not take my manual input of #N/A as a string?

L42
  • 19,427
  • 11
  • 44
  • 68
Ross
  • 31
  • 3
  • 2
    You can try replacing `.Value` with `.Text`. The value of the cell is an error, but if you are wanting to see the textual representation of the cell, you would use `.Text` instead. – K.Dᴀᴠɪs Jan 09 '18 at 05:05

1 Answers1

1

Charles Williams' post provided a good explanation of the differences between the usage of .Value and .Text.

In response to the question in your comment, there are many different ways to replace cells. One method would be to use an If...Then statement, such as:

If Cell().Text = "#N/A" Then
    ' Your code
End If

You can apply the above for use in a For Each...Next statement: 

Dim Cel as Range
For Each Cel In Worksheets(1).Cells
    If Cel.Text = "#N/A" Then
        Cel.Value = 0
    End If
Next Cel

Also, if you were wanting to look for all errors (such as more than just the #N/A), you could use IsError():

Dim Cel As Range
For Each Cel In Worksheets(1).Cells
    If IsError(Cel) Then
        Cel.Value = 0
    End If
Next Cel

I might add that checking the entire worksheet is time-consuming. It would be wise to add some method of exiting your For statement once you have checked the cells you are needing to, such as utilizing UsedRange for example.

K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43
  • Thanks for that (really great answer!), without that I believe it just crashes my worksheet! I am just thinking, why is it when I use: `kbd>Dim i as Integer` `For i = 0 To 25` `If Cells(1 + i, 1).Text Like "#N/A" Then` `Cells(1 + i, 1).Value = 1` `End If` `Next i` It doesn't work for me? – Ross Jan 09 '18 at 05:36
  • 1
    @Ross Replace `Like` with `=`. The `#` in your `#N/A` is [reserved](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/like-operator) for a wildcard of a number between `0 - 9` when using the `Like Operator`. – K.Dᴀᴠɪs Jan 09 '18 at 05:40
  • Thank you so much! I have been so confused for the past two hours... but this works! One more Question Why would in this case `Like` not work? - Is it because `Like` only looks for strings and in this case, the cell value of "#N/A" is not considered a string? (Nevermind that makes so much sense now....)! – Ross Jan 09 '18 at 05:45