Populating variant formulas in VBA based on column header value

Reedus34

New Member
Joined
Jun 7, 2016
Messages
2
Good afternoon,

I am very new to VBA, so thanks in advance for any assistance provided. I worked through a process of creating a pivot table with appropriate column headers based on a SQL query linked to the excel workbook. This is to populate data in a linear fashion instead of vertical as there can be multiple occurrences for each unique number and I will need each piece. Is there a way to create the proper formulas based on the value in the header? Below is what I have so far, and the commented section was created through record macro function:
Code:
Range("E1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Count"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-4],[@[accnt_num]])"
    Sheets.Add.Name = "Pivot"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "PropertyJurisdictions!R1C1:R1048576C5", Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="Pivot!R3C1", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("accnt_num")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("jurisdictionname")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "=MAX(Table_Query_from_Reporting9[Count])"
    For K = 4 To 13
        If Cells(3, K - 1).Value > 2 Then
        Cells(3, K).FormulaR1C1 = "=RC[-1]-1"
        End If
    Next K
    'Range("D4").Select
    'ActiveCell.FormulaR1C1 = _
    '    "=IF(AND(R[1]C[-3]="""",R[2]C[-3]=""""),R[2]C[-2],"""")"
    'Range("C4").Select
    'ActiveCell.FormulaR1C1 = "=IF(R[1]C[-2]="""",R[1]C[-1],"""")"
    'Range("C4:D4").Select
    'Selection.Copy
    'Range("C4:D49").Select
    'ActiveSheet.Paste
    'Application.CutCopyMode = False
    'Selection.Copy
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    '    :=False, Transpose:=False
End Sub
 
Last edited by a moderator:

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.
I just worked it out by hardcoding the formulas based on the possible number of occurrences available. Thanks for taking a look again.
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,708
Members
453,748
Latest member
akhtarf3

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