VBA modification help needed, how to get the column reference from Table header, Not named range

I had this question after viewing Simple Macro but is a big challenge. any Expert can solve this please?.


i have this code below helped by Rgonzo1971

on the data sheet i previously had named ranges and the variables were set to these named ranges.

now, i changed the named ranges to Excel tables, how can i reference the column from the table header names.

so my table1 has 4 headers  Year, Month, PRODUCT, Amount

how can i assign intYearCol, intMonthCol,  intProductCol, intAmountCol    to the table instead of named ranges?

  intYearCol = Range("dataYear").Column
    intMonthCol = Range("dataMonth").Column
    intProductCol = Range("dataPRODUCT").Column
    intAmountCol = Range("dataAMOUNT").Column


Sub MacroAll()
Dim Main As Worksheet

    Set Main = Sheets("Main")
    Set ShD = Sheets("NewData")

    intYearCol = Range("dataYear").Column
    intMonthCol = Range("dataMonth").Column
    intProductCol = Range("dataPRODUCT").Column
    intAmountCol = Range("dataAMOUNT").Column

    For Each cl In Range(Main.Range("B3"), Main.Range("F3"))
        mySum = 0
        Application.StatusBar = "Processing month " & Main.Cells(cl.Row, 1).Value & " for year " & Main.Cells(2, cl.Column).Value
        For Each c In Range(ShD.Range("a2"), ShD.Range("a" & Rows.Count).End(xlUp))

            If ShD.Cells(c.Row, intYearCol).Value = (Main.Cells(2, cl.Column).Value2 * 1) And ShD.Cells(c.Row, intMonthCol).Value = (Main.Range("A" & cl.Row).Value2 * 1) _
               And ShD.Cells(c.Row, intMonthCol).Value <> 111 And _
               ShD.Cells(c.Row, intProductCol).Value Like "[5-7]*" Then
                mySum = mySum + ShD.Cells(c.Row, intAmountCol).Value
            End If
        Next
        mySumReported = mySumReported + mySum - cl.Value
        If cl.Value = 0 And mySumReported <> 0 Then
            cl.Value = mySumReported
            mySumReported = 0
        End If
    Next

    Application.StatusBar = False

End Sub

Open in new window

EE.xlsb
LVL 6
FloraAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
You don't say which sheet the table is on or what its name is so I've assume it's on the sheet 'NewData' and is the only table  on that sheet.
intYearCol = Sheets("NewData").ListObjects(1).ListColumns("Year").Range.Column
    intMonthCol = Sheets("NewData").ListObjects(1).ListColumns("Month").Range.Column
    intProductCol = Sheets("NewData").ListObjects(1).ListColumns("PRODUCT").Range.Column
    intAmountCol = Sheets("NewData").ListObjects(1).ListColumns("Amount").Range.Column

Open in new window

0
 
FloraAuthor Commented:
Thank you very much Norie
0
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.

All Courses

From novice to tech pro — start learning today.