Author: Oscar Cronquist Article last updated on August 14, 2019

This article demonstrates how to place values automatically to a table based on two conditions using a short macro.

Cell C15 and C16 contain the conditions, they both allow you to select a value using drop down lists. Cell C17 contains the value you want to place, type this value and then press Enter.

The example shown above is based on the question below:

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.
Hence:
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.

The animated image above demonstrates how it works, simply pick values in the drop down lists, enter a value in C17 and then press on the button containing text value "Add".

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

How to add macro to your workbook

  1. Copy VBA code below.
  2. Press Alt+ F11 to open the VBE (Visual Basic Editor).
  3. Right-click on your workbook in project explorer to open a context menu.
  4. Click Insert on the menu.
  5. Click Module on the menu.
  6. Paste VBA code to module window.
  7. Return to Excel
Note, save your workbook with file extension *.xlsm macro-enabled workbook in order to keep the code.

VBA macro

'Name macro
Sub AddValue()

'Dimension variables and declare data types 
Dim column as String, row As String
Dim c As Single, r As Single

'The With - End With allows you write shorter lines of code by referring to an object only once instead of using it with each property.
With Worksheets("Sheet1")

'Stop macro if cell C15 or C16 is empty
If .Range("C15").Value = "" Or .Range("C16").Value = "" Then Exit Sub

'Save value in cell C15 to variable column
column = .Range("C15").Value

Save value in cell C16 to variable row
row = .Range("C16").Value

'Find relative position of value in cell C15 in cell range B1:H1 and save to variable c
c = Application.Match(column, .Range("B1:H1"), 0)

'Find relative position of value in cell C16 in cell range A2:A13 and save to variable r
r = Application.Match(row, .Range("A2:A13"), 0)

'Save value to table using coordinates
.Range("A1").Offset(r, c).Value = .Range("C17").Value

End With

'Stop macro
End Sub

How to create a clickable button on worksheet and link it to a macro

  1. Go to "Developer" tab on the ribbon.
  2. Click "Insert" button.
  3. Click "Button".
  4. Click and drag on worksheet to create a button.
  5. Right click on macro to display a context menu, click on "Assign macro".
  6. Select macro "Add Value".
  7. Click OK
  8. Click on button text to show prompt, then change button text to "Add".

Download Excel file


Add-values-to-a-table.xlsm

Related articles