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
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
What's actually in the cells, and what's the actual equation?
 

coocooworld

New Member
Joined
Aug 19, 2011
Messages
7
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
Joined
Aug 19, 2011
Messages
7
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
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
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
Joined
Aug 19, 2011
Messages
7
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
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
Works fine here.

Did you include the eval function I posted?
 

coocooworld

New Member
Joined
Aug 19, 2011
Messages
7
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
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
If you'd like to share your eval function, I'll try to help. Otherwise, I have no suggestion.
 

coocooworld

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

Forum statistics

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

Some videos you may like

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...
Top