Excel/VBA: Work around to insert long formulas with VBA code and maintain relative criteria references

I need to insert the following formula with a VBA code in column K in my sheet: VBA code is running a loop based on values in some other columns so the formula is only inserted in certain rows in column K, and then values only are stored. Row 1 criteria references need to be relative and reflect the actual row the formula is populated into. The problem is the formula is too long to insert via VBA code range.formula method. Depending on the tab name length  VBA code will give an error. Is there a work around where I could store the formula in a hidden sheet, and have the VBA code and my loop just paste formula only from a hidden sheet into column K? How would I adjust row 1 criteria references so that they are relative and when pasted reflect the actual row? I tried to replace row 1 references with =OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-n columns) method to reference the columns the left of column K, but in Excel the formula results in VALUE error. Any other suggestions? (In it's regular form below the formula is tested to work correctly)


=IF(SUMPRODUCT(
(INDIRECT(tab&"!$B$229:$B$388")=E1)*
(INDIRECT(tab&"!$E$4:$AR$4")=F1)*
(INDIRECT(tab&"!$C$229:$C$388")=H1)*
(INDIRECT(tab&"!$E$588:$AR$588")=I1)*
(INDIRECT(tab&"!$D$229:$D$388")=J1))>0,
SUMPRODUCT(
(INDIRECT(tab&"!$B$229:$B$388")=E1)*
(INDIRECT(tab&"!$E$4:$AR$4")=F1)*
(INDIRECT(tab&"!$C$229:$C$388")=H1)*
(INDIRECT(tab&"!$E$588:$AR$588")=I1)*
(INDIRECT(tab&"!$D$229:$D$388")=J1)*
(INDIRECT(tab&"!$E$229:$AR$388"))),
"")
Dmitriy KritskiyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkpieterseCommented:
Odd. I can simply assign the formula like so:

ActiveCell.Formula = "=IF(SUMPRODUCT((INDIRECT(tab&""!$B$229:$B$388"")=E1)*(INDIRECT(tab&""!$E$4:$AR$4"")=F1)*(INDIRECT(tab&""!$C$229:$C$388"")=H1)*(INDIRECT(tab&""!$E$588:$AR$588"")=I1)*(INDIRECT(tab&""!$D$229:$D$388"")=J1))>0,SUMPRODUCT((INDIRECT(tab&""!$B$229:$B$388"")=E1)*(INDIRECT(tab&""!$E$4:$AR$4"")=F1)*(INDIRECT(tab&""!$C$229:$C$388"")=H1)*(INDIRECT(tab&""!$E$588:$AR$588"")=I1)*(INDIRECT(tab&""!$D$229:$D$388"")=J1)*(INDIRECT(tab&""!$E$229:$AR$388""))),"""")"

No errors.
0
Dmitriy KritskiyAuthor Commented:
sure, but if I am inserting the formula via the loop as in the example below, the formula criteria references to row 1 don't change, whereas I need them to change obviously per each row the formula is pasted into. So if the loop pasted the formula to row 20 it would look like:
=IF(SUMPRODUCT((INDIRECT(tab&""!$B$229:$B$388"")=E20)*(INDIRECT(tab&""!$E$4:$AR$4"")=F20)*(INDIRECT(tab&""!$C$229:$C$388"")=H20)*(INDIRECT(tab&""!$E$588:$AR$588"")=I20)*(INDIRECT(tab&""!$D$229:$D$388"")=J20))>0,SUMPRODUCT((INDIRECT(tab&""!$B$229:$B$388"")=E20)*(INDIRECT(tab&""!$E$4:$AR$4"")=F20)*(INDIRECT(tab&""!$C$229:$C$388"")=H20)*(INDIRECT(tab&""!$E$588:$AR$588"")=I20)*(INDIRECT(tab&""!$D$229:$D$388"")=J20)*(INDIRECT(tab&""!$E$229:$AR$388""))),"""")

For r = 2 To lastrow
        
        If ws.Range("D" & r).Value = pg Then
            If ws.Range("E" & r).Value = cg Then

                ws.Range("K" & r).Formula = "=IF(SUMPRODUCT((INDIRECT(tab&""!$B$229:$B$388"")=E1)*(INDIRECT(tab&""!$E$4:$AR$4"")=F1)*(INDIRECT(tab&""!$C$229:$C$388"")=H1)*(INDIRECT(tab&""!$E$588:$AR$588"")=I1)*(INDIRECT(tab&""!$D$229:$D$388"")=J1))>0,SUMPRODUCT((INDIRECT(tab&""!$B$229:$B$388"")=E1)*(INDIRECT(tab&""!$E$4:$AR$4"")=F1)*(INDIRECT(tab&""!$C$229:$C$388"")=H1)*(INDIRECT(tab&""!$E$588:$AR$588"")=I1)*(INDIRECT(tab&""!$D$229:$D$388"")=J1)*(INDIRECT(tab&""!$E$229:$AR$388""))),"""")"

                'ws.Range("M" & r).Value = ws.Range("K" & r).Value
                
               
        
            Else
            End If
       Else
       End If
    Next r

Open in new window

0
Saurabh Singh TeotiaCommented:
You can use this code..This will apply formula on the row which you are working upon...in the loop

For r = 2 To lastrow
        
        If ws.Range("D" & r).Value = pg Then
            If ws.Range("E" & r).Value = cg Then

                ws.Range("K" & r).Formula = "=IF(SUMPRODUCT((INDIRECT(tab&""!$B$229:$B$388"")=E" & r & ")*(INDIRECT(tab&""!$E$4:$AR$4"")=F" & r & ")*(INDIRECT(tab&""!$C$229:$C$388"")=H" & r & ")*(INDIRECT(tab&""!$E$588:$AR$588"")=I" & r & ")*(INDIRECT(tab&""!$D$229:$D$388"")=J" & r & "))>0,SUMPRODUCT((INDIRECT(tab&""!$B$229:$B$388"")=E" & r & ")*(INDIRECT(tab&""!$E$4:$AR$4"")=F" & r & ")*(INDIRECT(tab&""!$C$229:$C$388"")=H" & r & ")*(INDIRECT(tab&""!$E$588:$AR$588"")=I" & r & ")*(INDIRECT(tab&""!$D$229:$D$388"")=J" & r & ")*(INDIRECT(tab&""!$E$229:$AR$388""))),"""")"

                'ws.Range("M" & r).Value = ws.Range("K" & r).Value
                
               
        
            Else
            End If
       Else
       End If
    Next r

Open in new window


Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.