How do I use VB to create a formula?

[slam]

I have a worksheet that needs some formulas applied each time upon worksheet activate because I have some other stuff going on that wipes them out.

In A2 through A36, I need this formula:

=IF(B2="","",INDEX(Team,MATCH(B2,Driver,0)))

And in V2 through V36, I need this array formula:

{=IF(COUNTBLANK($C2:$U2)=COLUMNS($C2:$U2),"",SUM(IF(A$2:A$36=A2,C$2:U$36)))}

Could anyone help with this?

Thanks

 

[Rick Rothstein]

This sheet activate procedure should do what you want...

Private Sub Worksheet_Activate()
  Range("A2:A36").Formula = "=IF(B2="""","""",INDEX(Team,MATCH(B2,Driver,0)))"
  Range("V2:V36").Formula = "=IF(COUNTBLANK(C2:U2)=19,"""",SUMIF(A$2:A$36,A2,C$2:U$36))"
End Sub

Note that I changed your second formula a little bit... I replace the COLUMNS function call with the number 19 which it will always be equal to (saves Excel the trouble of calculating it in every formula) and I changed your SUM function to a SUMIF function (doing that means the formula can be a normal-entered one).

 

[slam]

That works great, but for some reason I'm getting a different (inaccurate) result in column V now.

 

[slam]

I tried this with the original formula, but it doesn't like it....

Range("V2:V36").FormulaArray = "=IF(COUNTBLANK($C2:$U2)=COLUMNS($C2:$U2),"",SUM(IF(A$2:A$36=A2,C$2:U$36)))"

 

[Rick Rothstein]

I tried this with the original formula, but it doesn't like it....

Range("V2:V36").FormulaArray = "=IF(COUNTBLANK($C2:$U2)=COLUMNS($C2:$U2),"",SUM(IF(A$2:A$36=A2,C$2:U$36)))"

You have to double up the internal quote marks or else VB tries to use them to end and start new text string. Try it this way...

Range("V2:V36").FormulaArray = "=IF(COUNTBLANK($C2:$U2)=COLUMNS($C2:$U2),"""",SUM(IF(A$2:A$36=A2,C$2:U$36)))"

 

[slam]

That is now putting the same formula in every cell without incrementing the row number?

Here is my entire code if that may help:

Private Sub Worksheet_Activate()
Dim headerRow, lastRow, c

'unmerge
For Each c In Array(1, 22)
With Intersect(Columns(c), ActiveSheet.UsedRange)
.UnMerge
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
On Error GoTo 0
.Value = .Value
End With
Next

'apply formulas
Range("A2:A36").Formula = "=IF(B2="""","""",INDEX(Team,MATCH(B2,Driver,0)))"
Range("V2:V36").FormulaArray = "=IF(COUNTBLANK($C2:$U2)=COLUMNS($C2:$U2),"""",SUM(IF(A$2:A$36=A2,C$2:U$36)))"

'sort
Range("A2:X36").Sort Key1:=Range("W2"), Order1:=xlAscending, _
Key2:=Range("X2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

're-merge
On Error GoTo safeExit
headerRow = 1
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
f = headerRow + 1
Application.DisplayAlerts = False
Do Until f > lastRow
l = f + 1
Do Until (Cells(l, 1) <> Cells(l - 1, 1)) Or (Cells(l, 22) <> Cells(l - 1, 22))
l = l + 1
Loop
If f <> l Then
For c = 1 To 22 Step 21
With Range(Cells(f, c), Cells(l - 1, c))
.merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Next c
End If
f = l
Loop
safeExit:
Application.DisplayAlerts = True

End Sub

 

[Rick Rothstein]

That is now putting the same formula in every cell without incrementing the row number?

Sorry, I posted without testing. Replace the code I gave you with this and see if it works for you...

Range("V2").FormulaArray = "=IF(COUNTBLANK($C2:$U2)=COLUMNS($C2:$U2),"""",SUM(IF(A$2:A$36=A2,C$2:U$36)))"
Range("V2:V36").FillDown
Range("A2").Formula = "=IF(B2="""","""",INDEX(Team,MATCH(B2,Driver,0)))"
Range("A2:A36").FillDown

 

[slam]

That works, thank you!