Optimizing loop: apply equations to ranges without using loop

[coocooworld]

Hello Experts,

I'm am trying to optimize my vba codes and am running into a road block; I'm pretty sure there is a solution out there but I haven't been able to find it for days.

I am trying to apply an equation to a ranges of cells without using a loop. I have been successful at using the loop to apply the equation; the problem is, it is a for loop and it is taking a very long time to go through all the cells. I have thousands and thousands of numerical value that I would like to apply an equation to. Here is the code I am trying to optimize:

dim mycell as range
dim lastcol as string
dim lastrow as integer
......
.....
For Each mycell In Workbooks(xWb).Worksheets(2).Range("A1:" & lastcol & lastrow)
      mycell.Value = eval(mycell.Value)
Next

Is there a way to apply "=eval" to every cells without using a for loop?
For example, I have this:

3_______5_______2
4_______1_______6
2_______3_______2


Let's say "eval" will add 1 to it (actual equation is a lot more complicated), so I will get this

4_______6_______3
5_______1_______7
3_______4_______4


It would be easier to apply an equation to all the cells rather then use a for loop. So I want to do this:

=eval(3)_______=eval(5)_______=eval(2)
=eval(4)_______=eval(1)_______=eval(6)
=eval(2)_______=eval(3)_______=eval(2)


Please help. Your opinions are greatly appreciated. thanks

 

[shg]

What's actually in the cells, and what's the actual equation?

 

[coocooworld]

It could be a number or string text, the content of it is what I'm trying to change in general. I'm using number as an example here. "eval" is a private function that will evaluate a solution base on the number.

 

[coocooworld]

Maybe another way of looking at this would be to duplicate the sheet and figure out an algorithm to re-address itself:

Let's say sheet 2 have this starting at cell A1:

3_______5_______2
4_______1_______6
2_______3_______2

On sheet 3, i would have a formula to evaluate using address starting at A1:

=eval(sheet2!A1)______=eval(sheet2!B1)______=eval(sheet2!C1)
=eval(sheet2!A2)______=eval(sheet2!B2)______=eval(sheet2!C2)
=eval(sheet2!A3)______=eval(sheet2!B3)______=eval(sheet2!C3)

How would I code the sheet "A1"...."B1"....."C1" and etc... into a function and apply it to a range?

In the above example, the range would be from A1:C3
Workbooks(xWb).Worksheets(3).Range("A1:C3").formula = "=eval(Sheet2!" &.....same self address.....& ")"?

 

[shg]

With no clear idea of what you're trying to do,

Sub coocoo()
    With Range("A1:C2")
        .Value = eval(.Cells)
    End With
End Sub
 
Function eval(rInp As Range) As Variant
    Dim avOut       As Variant
    Dim i           As Long
    Dim j           As Long
 
    avOut = rInp.Value2
 
    For i = 1 To UBound(avOut, 1)
        For j = 1 To UBound(avOut, 2)
            If VarType(avOut(i, j)) = vbDouble Then avOut(i, j) = avOut(i, j) + 1
        Next j
    Next i
 
    eval = avOut
End Function

 

[coocooworld]

I think you got what i'm trying to get at with this code:

Sub coocoo()
    With Range("A1:C2")
        .Value = eval(.Cells)
    End With
End Sub

However, I get an error when running that.

 

[shg]

Works fine here.

Did you include the eval function I posted?

 

[coocooworld]

My eval functions can't handle a ranges/matrix of item. It can only handle one item at a time. I'm going to have to use the formula method, but am having some trouble with "type mismatch":

With Worksheets(3).Range("A1:C3")
        .Formula = "=eval(" & Chr(34) & Worksheets(2).Range("A1:C3").Cells.Value2 & Chr(34) & ")"
End With

where chr(34) is a double quote.

Yours work fine with just number, but the elements i'm inputing into eval is a variation of number and text.

 

[shg]

If you'd like to share your eval function, I'll try to help. Otherwise, I have no suggestion.

 

[coocooworld]

The eval function I have works with 50 other functions and subfunctions combine to calculate and iterate a very complex mathematical formula; hence kind of hard to send.

I am wondering if I can split this part

Worksheets(2).Range("A1:C3").Cells.Value

into each of its element to be put into the formula.

The code I have

With Worksheets(3).Range("A1:C3")
        .Formula = "=eval(" & Chr(34) & Worksheets(2).Range("A1:C3").Cells.Value & Chr(34) & ")"
End With

work fine if I simplify to this "

With Worksheets(3).Range("A1:C3")
        .Formula = "=eval(" & Chr(34) & "AG523H4523K333" & Chr(34) & ")"
End With

where "AG523H4523K333" is the element from A1 on sheet 2.
but when i put "Worksheets(2).Range("A1:C3").Cells.Value" into that, I'll get a type mismatch because it is an array.

This ".Formula = "=eval(" & Chr(34) & "AG523H4523K333" & Chr(34) & ")" is

"=eval(AG523H4523K333)" in cell format

Thank you very much for your help.