how to pass range in a vba function without errors in it & replace eerror with a string value ir 0 (without for loops)

[Spyros13]

is this possible?

For example I have Function:

My_Func(r1 As Range, DimensionA As Long) As Variant

if

r1

contains any error the whole function will return #value.

or if I have

Function My_Func(r1 As Range, DimensionA As Long) As Variant
On Error Resume Next

before the rest of the code, it returns

1

in the cell.

As long as

r1

dooesnt contain any errors, the UDF works and calculates intended results.

Without employing a subroutine first to replacre the erros in r1, or detecting, removing or replccing errors myself before the udf is used, i there (what is the way) to replace any and all errors in r1 with a 0, string or even "", before passing that new r1 to the rest of the function, - and if possible (though would be ok with that too if it works) without using for loops and/or the use of lbound ubound and without changing the source range in the sheet.

I.e. the range

r1

would be the same, but what is eventually passed to r1 has any errors in/from r1 source, dealt with (replaced with strings or zeros).

Is this possible. I was thinking the range.replace method might help , but im not sure & wouldnt know how to use employ that. (or i might I refer to some old udfs have, which I will look into, but appreciate any help assistance offered)

 

[Spyros13]

& here that is -

Function Convert_Single_Column_to_One_Dimensional_Array39(R2 As Range, DimensionA As Long) As Variant

Dim Myarray As Variant
Dim Myarray2 As Variant

'Dim Cell As Range 'in R1
'Dim MyString As String
'For Each Cell In R1.SpecialCells(xlCellTypeFormulas)
 '   MyString = "X" ' Right(Cell.Formula, Len(Cell.Formula) - 1)
    'MyString = Replace(NewFormula, Delim, MyString)
  '  Cell.Formula = MyString
'Next Cell
Dim R1 As Variant

Set R1 = R2 ' Evaluate("iferror" & "(" & R1 & "," & 1 & ")")    'R2 ' Evaluate("iferror" & "(" & R2 & "," & 1 & ")")


'R1 = R1 ' Evaluate("=iferror" & "(" & R1 & "," & 1 & ")")
    

'R1 = Evaluate("iferror" & "(" & R2 & "," & "1" & ")")

'With appliation.Caller

arr1 = R1 ' Evaluate("=iferror" & "(" & R1 & "," & 1 & ")") '  R1 ' Evaluate("iferror(" & R2 & ",1)") '  'Chr(34) & r & Chr(34) ' Evaluate("iferror" & "(" & R1 & "," & 1 & ")")
'End With



'Dim arr2 As Variant
'arr2 = Evaluate("iferror(" & arr1 & ",1)")
'Debug.Print (arr2)


 ''''Dim x As Variant, temp As String
 'x = R1.Value


'If d = 1 Then

'Dim r2 As String


'r2 = Evaluate(R1.Value)


'Set R1 = R1


If DimensionA = 1 Then

Myarray = Application.Transpose(arr1)

Dim x As Long

ReDim Myarray(R1.Cells.Count)

For Each Cell In R1.Cells
    If IsError(Cell.Value) Then
    Myarray(x) = 0
    Else:
    Myarray(x) = Cell.Value
    End If
    x = x + 1
Next Cell
  



Saa1 = Application.Evaluate("LOOKUP(" & "2,1/(" & R1.Address & "<>" & Chr(34) & Chr(34) & ")," & R1.Address & ")")

Debug.Print (Saa1)


Saa2 = "|" & Saa1 ' & "|"
Debug.Print (Saa2)

On Error Resume Next
If IsError(Join(Myarray, "|")) = True Then
Saa3 = 1 & "|" 'Join(Myarray, "")
Else: Saa3 = Join(Myarray, "|")
End If
On Error GoTo 0

'Saa3 = Join(Myarray, "|")
    
  '  temp = ""
   ' For Each x In RowRange
    '        If IsError(x) = True Then
     '       x = "y"
      '      Else
       '     x = x
        '    End If
            
        'temp = temp & x & "|"
    'Next

    'Saa3 = Left(temp, Len(temp) - Len("|")) 'ConcatenateRow ''https://stackoverflow.com/questions/32054665/how-do-i-concatenate-cell-values-and-text-together-using-excel-vba


'With ActiveSheet.Range(R1)
 '   If Application.IsNA(.Value) Then
  '      .Value = ""
   '''''     ElseIf
     '.Value = 0
      '  .Value = ""
    'End If
'End With

Debug.Print (Saa3)

End If

If DimensionA = 2 Then

'Application.Transpose(R1) = B1


Saa1 = Application.Evaluate("LOOKUP(" & "2,2/(" & R1.Address & "<>" & Chr(34) & Chr(34) & ")," & R1.Address & ")")

Debug.Print (Saa1)

Myarray2 = Application.Transpose(Application.Transpose(arr1))


Saa2 = "|" & Saa1 ' & "|"
Debug.Print (Saa2)

Saa3 = Join(Myarray2, "|")
Debug.Print (Saa3)

End If



Convert_Single_Column_to_One_Dimensional_Array39 = Len(Left(Saa3, InStrRev(Saa3, Saa2))) - Len(Replace(Left(Saa3, InStrRev(Saa3, Saa2)), "|", "")) + 1


End Function

 

[Spyros13]

Nope the way I did it didnt work.

Myarray = Application.Transpose(arr1)

Dim x As Long

ReDim Myarray(R1.Cells.Count)

For Each Cell In R1.Cells
    If IsError(Cell.Value) Then
    Myarray(x) = "x" 'or =0 or =1 or ="1"
    Else:
    Myarray(x) = Cell.Value
    End If
    x = x + 1
Next Cell

This just skips over the error , or if it does change it to 1,0 or "x" as I ask, it seems to not recognise it, does domething funny, so, instead of giving me 12 it gives me 11, or when instead of giving me 12, it gives 13

=Convert_Single_Column_tne_Dimensional_Array39(B66:B79,1) = 11 (should be 12)
=Convert_Single_Column_tne_Dimensional_Array39(B66:B80,1) = 13 (should be 12)


11th position row 78 - value1
12th position row 79 - value 2 - contans #N/A
13th positon row 80 - value 3

I might change it to when if error myarray(x) = cell.address - that night work>!?

Atleast now doesnt give me any errors or values seems to work better, but doesnt count properly.

(or is my loop wrong, my vba wrong? or when Ive done it in the code?) Can you help me fix it? Oh im an idiot. I applied the loop to the wrong variable (im still referencing the unchanged r1 values when doing my joins>) gosh need to take some time out & do it properly.


will be back

 

[Spyros13]

no i think it does handle the errors but its the lookup thats doing what it normally does, it ignores last value if the last value is an error and returns the last value before the error. Must make it return the error (or its position).

Need to
want to.

 

[Spyros13]

I could make it to work , with the current loop i put it, if I re-asssign Saa1, or change the way Saa1 is being calculated to take into acccount a last value of error. (thats 1 way).

or improve my loop (move it in the code - make it surround other values). Idk.

But you iteration idea, to iterate over the range cell by cell and check for error and asign new value if error (though there must be other ways to : re-asign ranges to variables, change their values only in the variable, without changing their source seems to be the best one so far, but I cant even get that to work flawlesly.

 

[Spyros13]

This line is giving me an error in VBA (but not in Excel. In excel it works).

Saa1 = Application.Evaluate("LOOKUP(" & "2,1/(" & R1.Address & "<>" & Chr(34) & Chr(34) & ")," & R1.Address & ")")

My vba evaluate's normally work (and this indeed so does this when there is no errror in R1 (which A. I dont mind if there is and B. I want them to be there) It also works in excel when there are errors in R1. So why not here? So what do i need to do to make the VBA evaluate even work with errors in R1 from vba (vba already makes error values 0), so I dont get why this evaluate statement isnt working.

Basiclly I need something which wraps/deals with if R1.Address is error , make either r1.that-particular-value-0-or-none-or-1, or evaluate it as the lookup in excel would (a result regardless if errors in r1 or not)

That is where my code is failing. I don't even need an iteration or loop!

vba changes any error values in r1 to 0 anyway (as my debug console proves), so they arnt the problem.

The problem is my evaluate statement evaluating to error when there are errors in r1 (which doesnt happen in excel for that lookup). but is happening here!! Anyone care to help?

so when im evaluating the substitutions at the end, the funnction cant, because Saa2 is based on Saa1 which has evaluated as an error


Here the full code again (working for any all values except if error in R1, but It should work for that too, jusy as Excel does!):

So question becomes, - How do I fix my evaluate statement to work all the time (and not 90% of time?)?

Function Convert_Single_Column_to_One_Dimensional_Array33(R1 As Range, DimensionA As Long) As Variant

Dim Myarray As Variant
Dim Myarray2 As Variant
arr1 = R1 'Chr(34) & r & Chr(34)


Set R1 = R1

If DimensionA = 1 Then

Myarray = Application.Transpose(arr1)
Saa1 = Application.Evaluate("LOOKUP(" & "2,1/(" & R1.Address & "<>" & Chr(34) & Chr(34) & ")," & R1.Address & ")")

Debug.Print ("Test 1A  = ")
Debug.Print (Saa1)


Saa2 = "|" & Saa1 ' & "|"
Debug.Print ("Test 2A  = ")
Debug.Print (Saa2)


Saa3 = Join(Myarray, "|")
Debug.Print ("Test 3A  = ")
Debug.Print (Saa3)

End If

If DimensionA = 2 Then

'Application.Transpose(R1) = B1


Saa1 = Application.Evaluate("LOOKUP(" & "2,2/(" & R1.Address & "<>" & Chr(34) & Chr(34) & ")," & R1.Address & ")")
Debug.Print ("test 1b - ")
Debug.Print (Saa1)

Myarray2 = Application.Transpose(Application.Transpose(arr1))


Saa2 = "|" & Saa1 '& "|"
Debug.Print ("test 2b - ")
Debug.Print (Saa2)


Saa3 = Join(Myarray2, "|")
Debug.Print ("test 3b - ")
Debug.Print (Saa3)

End If

Convert_Single_Column_to_One_Dimensional_Array33 = Len(Left(Saa3, InStrRev(Saa3, Saa2))) - Len(Replace(Left(Saa3, InStrRev(Saa3, Saa2)), "|", "")) - 1

End Function

I tried

Saa1 = Application.Evaluate("LOOKUP(" & "2,1/(" & "iferror(" & R1.Address & "),0)" & "<>" & Chr(34) & Chr(34) & ")," & "iferror(" & R1.Address & "),0)" & ")")

didnt work. ill try it again though when im calmer.

maybe use a vba one instead?

 

[Spyros13]

Should i hold my horses?????!!!!!!!! ???!!!! I think I did it!!
BUT AS FAR AS MY ORIGINAL QUESTON WENT - ITS SOLVED !!

THANK YOU FOR YOUR PUSSHES.

i can sleep now !!

 

[Joe4]

Sorry, I was out of town yesterday, so did not see any of your replies since my last reply until today.
Glad to see that you got it working the way you need now.