Optimizing loop: apply equations to ranges without using loop

coocooworld

New Member
Joined
Aug 19, 2011
Messages
7
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:
Code:
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
What's actually in the cells, and what's the actual equation?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.....& ")"?
 
Upvote 0
With no clear idea of what you're trying to do,

Code:
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
 
Upvote 0
I think you got what i'm trying to get at with this code:
Code:
Sub coocoo()
    With Range("A1:C2")
        .Value = eval(.Cells)
    End With
End Sub

However, I get an error when running that.
 
Upvote 0
Works fine here.

Did you include the eval function I posted?
 
Upvote 0
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":

Code:
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.
 
Last edited:
Upvote 0
If you'd like to share your eval function, I'll try to help. Otherwise, I have no suggestion.
 
Upvote 0
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
Code:
Worksheets(2).Range("A1:C3").Cells.Value

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

The code I have

Code:
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 "

Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top