Looping through Range and using in a formula

[ItalianPlatinum]

I am currently looping through a range and looking to also use that in a formula. Like so:

Dim CompList
Dim FormCompList As Range
'run for each
  j = 0
  Do Until WsFT.Range("CompList").Offset(j, 0) = ""
    CompList = WsFT.Range("CompList").Offset(j, 0)
'OTHER CODE
ElseIf Comp <> "" Then
    .Range("B2:B" & lr3).Formula2 = "=IFERROR(COUNTIFS(ALL!$C$2:$C$" & lr2 & ",FormComplist,ALL!$D$2:$D$" & lr2 & ",A2),0)"
    j = j + 1
Loop

Looking to leverage FormCompList for it

 

[Vogateer]

Have you tried creating this as a function and then using that function with the sheet itself?

If you can show the whole Sub, that would help.

 

[ItalianPlatinum]

My whole sub is very large. So I tried to cut it down to just the important details. the other part of the sub is doing a lot of different items. Eventually I would love to have a VBA solution to calculate those formulas for speed because I lose a lot of performance this way but I was going to take it in strides with this first.

 

[Vogateer]

You could make that section of code a separate function, which might be a good practice anyway, then you could call it within the larger Sub and also call it as a User Defined Function within your workbook. I don't know of any performance problems with using a User Defined Function, but I honestly don't do it that often.

You have variables called Comp, lr2, and lr3 that aren't defined or given values. We would need to see how you're getting those to create a function from this. Or maybe I'm completely misunderstanding what you're wanting to do. I would need a lot more info before I can figure out what you're trying to accomplish. Some examples of the data from the sheet and the outcome you're trying to achieve would be really helpful.

 

[ItalianPlatinum]

Here is some variables that you are looking for. So I guess to accomplish what I want the only way is for a function?

FormComplist is where I'd need the Complist that is being cycled through to be used for the formula in the countifs.

Dim lr As Long, lr1 As Long, lr2 As Long, lr3 As Long, LrS As Long, lastrow As Long

'clear and unprotect, set time for start day, set ranges
With WsExec
    .Unprotect
    .Application.Calculation = xlAutomatic
    .Range("C7:C9").Cells.ClearContents
    .Range("Start") = Time
If Composite <> "" Then
    .Range("C2").Cells.ClearContents
End If
    Com = .Range("Comp")
End With

''''additional code

'this is included in one of my loops
With WsALL
lr2 = .Cells(rows.count, "A").End(xlUp).row 'find the maximum row
    .Range("D:D").NumberFormat = "@"
    .Range("F:F").NumberFormat = "@"
    .Range("1:1").AutoFilter
    .Cells.EntireColumn.AutoFit
    .Activate
End With

''''additional code

'check if nothing to calculate if so set everything to zero
With WsSum
lr3 = .Cells(rows.count, "A").End(xlUp).row
    .Range("B2:B" & lr3).ClearContents
If WsALL.Range("A" & rows.count).End(xlUp).row < 2 Then
    .Range("B2:B" & lr3).Formula = "=0"
    .Range("C2:C" & lr3).Formula = "=0"
ElseIf Comp = "" Then
    .Range("B2:B" & lr3).Formula2 = "=IFERROR(COUNTIF(ALL!$D$2:$D$" & lr2 & ",A2),0)"
    .Range("C2:C" & lr3).Formula = "=if(B2=0,0,COUNTA(UNIQUE(FILTER(ALL!$P$2:$P$" & lr2 & ",ALL!$D$2:$D$" & lr2 & "=A2)))-COUNTIFS(ALL!$D$2:$D$" & lr2 & ",A2))"
ElseIf Comp <> "" Then
    .Range("B2:B" & lr3).Formula2 = "=IFERROR(COUNTIFS(ALL!$C$2:$C$" & lr2 & ",FormComplist,ALL!$D$2:$D$" & lr2 & ",A2),0)"
    .Range("C2:C" & lr3).Formula = "=if(B2=0,0,COUNTA(UNIQUE(FILTER(ALL!$P$2:$P$" & lr2 & ",ALL!$D$2:$D$" & lr2 & "=A2)))-COUNTIFS(ALL!$D$2:$D$" & lr2 & ",A2))"
End If
    .Range("Total1").Formula = "=SUM($C$2:$C$" & lr3 & ")"
TOTAL1 = .Range("Total1")
    .Range("B1:E" & lr3).Value = .Range("B1:E" & lr3).Value
    .Range("A1:C1").AutoFilter
    .Cells.EntireColumn.AutoFit
End With

''''additional code

 

[Vogateer]

A function might be the easiest way to do what you want, but I'm not sure about that because I don't know anything about what you're trying to do. I don't know anything about the structure of your sheets, I also don't know what "Comp" is referring to because it's not defined in any of the code, and I don't know what your end goal is. Maybe this could all be done with a simple formula, but I can't tell you that without a lot more info.

 

[ItalianPlatinum]

I think I mistakenly left out a big sentence in my original post that would help. And I apologize for that. Complist I cycle through using my loop to do various tasks (below range). I want to use them as a criteria for the countifs formula labeled now as FORMCOMPLIST just as a placeholder. So 83 is used, then placed in the countifs as a criteria, then 84 is used, then placed in the countifs as a critieria and so on.

 

[ItalianPlatinum]

I got it! I should have known that too. had to wrap it in " &

.Range("B2:B" & lr3).Formula2 = "=IFERROR(COUNTIFS(ALL!$C$2:$C$" & lr2 & "," & CompList & ",ALL!$D$2:$D$" & lr2 & ",A2),0)"