Loop and Long Formula

jwburritt

New Member
Joined
May 22, 2019
Messages
48
Hello: I have a loop that is filling in formulas based on a cell value. The formula for "A" is fairly simple and is included in the code as a formula. But the one for "AB" has a lot of nested ifs and it's so long, I couldn't get it to work -- I kept getting syntax errors. So, I decided to simply copy the "AB" formula from a cell on the sheet and then loop through copying the formula. It works, but it flashes a ton (even with screen updating off) and seems to be working really hard at it. Anyway, is there a more efficient way to deal with a long nested formula in the code itself? Or am I left with 1. my current copying loop or 2. back to trouble shooting the syntax error. Any help would be appreciated. Thanks!

Code:
Dim VCell As Range
Set VCell = VSh.Cells


Dim DataSh As Worksheet
Set DataSh = UWkbk.Worksheets("Data")
        
For i = 1 To 300
        
        With VCell(i, 15)
            If VCell(i, 16).Value = "A" Then
                .Formula = "=" & VCell(i, 14).Address(0, 0)
            Else
                If VCell(i, 16).Value = "AB" Then
                    DataSh.Range("P4").Copy
                    .PasteSpecial Paste:=xlPasteFormulas
                Else
                End If
            End If
        End With
        
        
Next i
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,328
Nested formulas using IFs, once they get too long, can often be replaced with a short formula and a lookup table.
Post the formula, it's possible it can be shortened just by itself.
 

jwburritt

New Member
Joined
May 22, 2019
Messages
48
Thanks. Below is the original formula along with the vba version that I'm trying to loop with the a variable. I can't get the looping version to work.

Dim VSh As Worksheet
Set VSh = VWkbk.Worksheets("V")
Dim VCell As Range
Set VCell = VSh.Cells


Original
=IF(LEFT(F1831,1)="c",(N1831+(N1831*$N$4)),(N1831+(N1831*$O$4)))

VBA
.Formula="=IF(LEFT("&VCell(i, 6).Address(0, 0) &",1)=’c’,("&VCell(i, 14).Address(0, 0) &"+("&VCell(i, 14).Address(0, 0) &"*"&VCell(4, 14).Address&")),("&VCell(i, 14).Address(0, 0) &"+("&VCell(i, 14).Address(0, 0) &"*"&VCell(4, 15).Address&")))"
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
You can simplify your original formula as follows:
=N1831+(N1831*IF(LEFT(F1831)="c",$N4,$O4))
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,032
Office Version
2010
Platform
Windows
When I am trying to debug writing equations with VBa, I write the equation as text by leaving out the "=" sign, then go to the cell and insert the "=" and excel will very kindly show you where the error is.
 

jwburritt

New Member
Joined
May 22, 2019
Messages
48
Thank you everyone. I used the shortened formula from njimack as a base. That made the debugging much easer. I had also forgotten the "" around ""c"".

This now works:

.Formula = "=" & VCell(i, 14).Address(0, 0) & "+(" & VCell(i, 14).Address(0, 0) & "*IF(LEFT(" & VCell(i, 6).Address(0, 0) & ")=""c"",$N$4,$O$4))"

Thanks again!
 
Last edited:

Forum statistics

Threads
1,085,466
Messages
5,383,858
Members
401,858
Latest member
Nitsalet

Some videos you may like

This Week's Hot Topics

Top