VBA - value conversion #REF! exception

Question

It would be a problem that the designated area should be converted to value except where #REF! is error.

Sub keplet_helyett_ertek()
   Range("C3:J65").Select

   For Each akt_range In Selection.Areas
     If akt_range.Value <> CVErr(xlErrRef) Then
       akt_range.Formula = akt_range.Value
     End If
   Next
End Sub

Then, Run-time '13' error

`Selection.Areas` enumerates areas. `Value` of an Area is a 2D array. Apparently you wanted `Selection.Cells`. Then again, [you don't need `Selection`](https://stackoverflow.com/q/10714251/11683). — GSerg, Sep 14 '17 at 07:03

score 2 · Answer 1 · answered Sep 14 '17 at 07:08

Another problem is that if a cell dos NOT contain an error value you get the type mismatch too. The test to take is:

  For Each akt_range In Range("C3:J65")
     If Not IsError(akt_Range.Value) Then
       akt_range.Formula = akt_range.Value
     End If
   Next

score 0 · Answer 2 · answered Sep 14 '17 at 07:04

0

For Each akt_range In Selection.Areas

should be

For Each akt_range In Selection

To be more efficient replace

Range("C3:J65").Select
For Each akt_range In Selection.Areas

with

Dim akt_range As Range
For Each akt_range In Range("C3:J65")

answered Sep 14 '17 at 07:04

Mrig

11,612
2
13
27

score 0 · Answer 3 · answered Sep 14 '17 at 07:04

0

Sub keplet_helyett_ertek()
Dim akt_range As Range, ok As Boolean
  Range("C3:J65").Select
  For Each akt_range In Selection
    ok = Not IsError(akt_range.Value)
    If Not ok Then ok = (akt_range.Value <> CVErr(xlErrRef))
    If ok Then akt_range.Formula = akt_range.Value
Next
End Sub

answered Sep 14 '17 at 07:04

Richárd Nemes

1

1

I would argue that `ok` does not add to the readability or functionality of the code and should be removed. `IsError` is clearly a boolean function and and the only other reason to set a flag is if the flag condition is needed outside of the original evaluation. I recommend comparing @jkpieterse's answer side by side with yours and see which one is more readably. – Sep 14 '17 at 07:55

score 0 · Answer 4 · answered Sep 14 '17 at 14:36

If, like the question states, you are only interested in excluding #REF! errors, this is the correct check:

  For Each akt_range In Range("C3:J65")
    If CStr(akt_range.Value2) <> CStr(CVErr(xlErrRef)) Then
      akt_range.Value = akt_range.Value2
    End If
  Next

Technically, the above will fail in the very special case of a formula returning the error code string for #REF! exactly, e.g., ="Error 2023". The absolutely bulletproof check is this:

  For Each akt_range In Range("C3:J65")
    If Not IsError(akt_range.Value2) Or CStr(akt_range.Value2) <> CStr(CVErr(xlErrRef)) Then
      akt_range.Value = akt_range.Value2
    End If
  Next

If you wish to exclude all errors, a better solution would be to use .SpecialCells() to eliminate the errors from the range before looping:

  For Each akt_range In Range("C3:J65").SpecialCells(xlCellTypeFormulas, xlLogical + xlNumbers + xlTextValues)
    akt_range.Value = akt_range.Value2
  Next

VBA - value conversion #REF! exception

4 Answers4