Author: Oscar Cronquist Article last updated on January 09, 2018

Cyril asks:

I do remember seeing one nice way of populating a table with the use of vba such as :

Sub EnterName()

Dim col As Single, Lrow As Single
Dim tmp As String

col = Application.WorksheetFunction.Match(Range("C2"), Range("B4:H4"), 0) - 1
tmp = Range("B" & Rows.Count).Offset(0, col).Address
Lrow = Range(tmp).End(xlUp).Row

Range("B1").Offset(Lrow, col).Value = Range("E2").Value
Range("B1").Offset(Lrow, col + 1).Value = Range("F2").Value
Range("E2").Value = ""
Range("F2").Value = ""

End Sub

How would it be possible to modify the code to populate a table such as: the first column header could be chosen from the drop-down list as well as the first row header. In other word the location of the data to be entered could be determined by the row AND the column.

C2 should be a data validation (list).
B4:H4 (here only 7 columns) would be the headers to match the value in C2.
A second data validation should make reference to Column A.
1st data validation correlated to Column's Headers (B to ect)
2nd data validation correlated to values in Column A ("Row Header")

As for the kind of values, being headers they would most likely be (but not limited to) text strings.


Create drop down lists

  1. Select cell C15
  2. Go to tab "Data"
  3. Click "Data validation" button
  4. Select "List" in Allow: field
  5. Select cell range B1:H1 in Source: field

Repeat above steps with cell C16 and cell range A2:A13
Insert macro

  1. Press Alt+ F11
  2. Right click on your workbook in project explorer
  3. Click Insert
  4. Click Module
  5. Paste vba code to module window
Sub AddValue()
Dim column, row As String
Dim c, r As Single

With Worksheets("Sheet1")
If .Range("C15").Value = "" Or .Range("C16").Value = "" Then Exit Sub
column = .Range("C15").Value
row = .Range("C16").Value
c = Application.Match(column, .Range("B1:H1"), 0)
r = Application.Match(row, .Range("A2:A13"), 0)
.Range("A1").Offset(r, c).Value = .Range("C17").Value
End With

End Sub

Create button

  1. Go to "Developer" tab
  2. Click "Insert" button
  3. Click "Button"
  4. Create a button
  5. Assign macro "AddValues"
  6. Click OK
  7. Change button text

Download excel *.xlsm file
Add values to a table.xlsm