Loop and Long Formula

jwburritt

New Member
Joined
May 22, 2019
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Upvote 0
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&")))"
 
Upvote 0
You can simplify your original formula as follows:
=N1831+(N1831*IF(LEFT(F1831)="c",$N4,$O4))
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
Solution

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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