Macro help to populate table from separate table (database)

ebraatz

Board Regular
Joined
Feb 25, 2017
Messages
76
Hi. I'm brand new to VBA and need some help.

I am creating an Excel Program for an auction. I currently have a table: "Solicitations", which lists all the companies solicited from and whether and what they donated and if that item is for the silent or live auction.
On a separate sheet I have a table: "Item_List", which I want to populate and expand as necessary with a macro attached to a button on the page.
The result I want is all Silent Auction items first, then all live auction items populating the fields: Description, Donor, and Value. I would also like the Item# to populate starting with "101" for the first Silent Auction Item and "301" for the first Live Auction Item. I've created some code for the first half (silent auction) and when I tested it I would get a 'ding' but no message. Can you help me figure out what's wrong? Am I even on the right track?

Code:
Sub Populate_Item_List(ByVal target As Range)


    Dim Solicitations As Range
    Dim Item_List As Range
    Dim Item1 As Range
    Dim Solicit1 As Range
    Dim IDAuction As Range
    Dim Donate As Range
    Dim rw As Range
    Dim target As Range
    Dim ItemTotal As Range


    Set Solicitations = Worksheets("Sheet1").Range("solicitaions")     'Solicitations Table
    Set Item_List = Worksheets("sheet5").Range("Item_List")            'Item_List Table
    Set Item1 = Worksheets("sheet5").Range("Item1")                    'location of first item number
    Set Solicit1 = Worksheets("sheet1").Range("Solicit1")              'first cell in solicitations range
    Set IDAuction = Worksheets("sheet1").Range("IDAuction")            'Silent/Live selection column
    Set Donate = Worksheets("sheet1").Range("Donate?")                 'Yes/no column
    Set numRows = Worksheets("sheet5").Range("Item_List").Rows.Count   'Number of rows in Item_List Table
    Set ItemTotal = Worksheets("sheet5").Range("ItemTotal")            'Totals Row in Item_List Table
    Set numList = Worksheets("sheet5").Range("numlist")
    Set wsfIndex = WorksheetFunction.Index
    Set wsfMatch = WorksheetFunction.Match
    Set wsfIsText = WorksheetFunction.IsText
    
    For Each rw In Solicitations
        If IDAuction = "silent" Then
            If Donate = "yes" Then
            
            Set target.Row = Item1.Row                'Add row to Item_List table if there is only one row left.
                If target.Row = ItemTotal.Row - 1 Then
                Application.EnableEvents = False
                Item Totals.EntireRow.Insert
                Application.EnableEvents = True
                Else: End If                                'Delete extra rows in Item_List Table
                If target.Row = ItemTotal.Row - 2 Then
                Application.EnableEvents = False
                Item Totals.EntireRow.Delete
                Application.EnableEvents = True
                Else: End If
                    
                    'code to use index function to fill Description(Col 6), Donor(Col 2), and Value(Col 7) _
                    into Item_List Description(Col 2), Donor(Col 3), and Value(Col 4).
                                                      
                                                      'Select Cell for Desription, then Index Value
            Set target.Address(False, True) = Item1.Offset(0, 1)
            Set target.Value = wsfIndex(Solicitations, wsfMatch(rw, Solicitations, 0), 6)
                                                      'Select Cell for Donor, then Index Value
            Set target.Address(False, True) = Item1.Offset(0, 2)
            Set target.Value = wsfIndex(Solicitations, wsfMatch(rw, Solicitations, 0), 2)
                                                  'Select Cell for Item Value, then Index Value
            Set target.Address(False, True) = Item1.Offset(0, 3)
            Set target.Value = wsfIndex(Solicitations, wsfMatch(rw, Solicitations, 0), 7)
            
                    'code to assign Item #'s
            
            Set Item1.Value = "101"
                If wsfIsText(numList.Offset(0, 1)) = True Then
                numList.Value = Range(numList).AutoFill(numList, xlFillSeries)
                Else: numList.Value = " "
                End If
                
            Else: End If
        Else: End If
        
        Next rw
        
        Exit For
        
        If IDAuction = "live" Then
            If Donate = "yes" Then
            Item_List.Resize Rowsize:=numRows + 1
            'code to insert index function? - Values for Description(Col 6), Donor(Col 2), and Value(Col 7) _
            into Item_List Description(Col 2), Donor(Col 3), and Value(Col 7).
            
            'code to assign Item #'s
            Else: End If
        Else: End If
        
        Next rw
    Exit For
    
    


End Sub

Table I want to populate:
Item #DescriptionDonorValue
Total

<tbody>
</tbody>

Sample of source:
CategoryBusiness NameContact Method and DateLive/SilentDonated?ItemValue
DestinationAmbiance Bed & Breakfast
DestinationArch Cape Inn & Retreat

<tbody>
</tbody>
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Threads
1,136,331
Messages
5,675,156
Members
419,552
Latest member
jsanjur

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
Top