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]

OK, one more and I'll leave you to it.

Put numbers in A1:B2 and run this:

Sub coocoo()
    With Range("A1:B2")
        .Formula = eval(.Value2)
    End With
End Sub
 
Function eval(avInp As Variant) As Variant
    Dim i           As Long
    Dim j           As Long
 
    For i = 1 To UBound(avInp, 1)
        For j = 1 To UBound(avInp, 2)
            avInp(i, j) = "=sin(" & avInp(i, j) & ") + exp(" & avInp(i, j) & ") + 1"
        Next j
    Next i
    eval = avInp
End Function

 

[shg]

Or maybe this:

Sub coocoo()
    With Range("A1:B2")
        .Formula = PutEval(.Value2)
    End With
End Sub
 
Function PutEval(avInp As Variant) As Variant
    Dim i           As Long
    Dim j           As Long
 
    For i = 1 To UBound(avInp, 1)
        For j = 1 To UBound(avInp, 2)
            avInp(i, j) = "=eval(" & avInp(i, j) & ")"
        Next j
    Next i
    PutEval = avInp
End Function
 
Function eval(d As Double) As Variant
    eval = Sin(d) + Cos(d)
End Function

 

[coocooworld]

SOLVED!!!!Yayyyy.

Thank you very much for your help. The two functions above didn't work for me, still getting errors. I came up with a different solution below.

The solution was to use autofill.
My values are on sheet2 and the eval function is on sheet3.

'put initial function in cell A1 of sheet 3
Workbooks(xWb).Worksheets(3).Range("A1").Formula = "=eval(Sheet1!A1)" 

'autofill across the first row through to the last column
Workbooks(xWb).Worksheets(3).Range("A1").AutoFill _
 Destination:=Workbooks(xWb).Worksheets(3).Range("A1:" & lastcol & 1)

'autofill down to the last row using the first row
Workbooks(xWb).Worksheets(2).Range("A1:" & lastcol & 1).AutoFill _
Destination:=Workbooks(xWb).Worksheets(2).Range("A1:" & lastcol & lastrow)

I hope this will help somebody looking for the same solution. Thank you SHG for your help.

Compares to the for loop, this method saved me several minutes off my calculation.

 

[shg]

I thought the objective was to do it in situ.

All's well that ends well -- glad you got it sorted.