Loop and Long Formula

[jwburritt]

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!

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]

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]

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]

You can simplify your original formula as follows:
=N1831+(N1831*IF(LEFT(F1831)="c",$N4,$O4))

 

[offthelip]

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]

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!