# Optimizing loop: apply equations to ranges without using loop

#### coocooworld

##### New Member
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

#### shg

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

#### coocooworld

##### New Member
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:

#### coocooworld

##### New Member
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

##### MrExcel MVP
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``````

#### coocooworld

##### New Member
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.

#### shg

##### MrExcel MVP
Works fine here.

Did you include the eval function I posted?

#### coocooworld

##### New Member
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:

#### shg

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

#### coocooworld

##### New Member
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:

Threads
1,081,751
Messages
5,361,082
Members
400,612
Latest member
mr_sound

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
• Listbox Header
Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
• Complex Heat Map using conditional formatting
Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
• Conditional formatting
Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...