Add values to a two-dimensional table based on conditions [VBA]
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:
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
- Select cell C15
- Go to tab "Data"
- Press with left mouse button on "Data validation" button
- Select "List" in Allow: field
- 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
- Copy VBA code below.
- Press Alt+ F11 to open the VBE (Visual Basic Editor).
- Press with right mouse button on on your workbook in project explorer to open a context menu.
- Press with left mouse button on Insert on the menu.
- Press with left mouse button on Module on the menu.
- Paste VBA code to module window.
- Return to Excel
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 press with left mouse button onable button on worksheet and link it to a macro
- Go to "Developer" tab on the ribbon.
- Press with left mouse button on "Insert" button.
- Press with left mouse button on "Button".
- Press with left mouse button on and drag on worksheet to create a button.
- Press with right mouse button on on macro to display a context menu, press with left mouse button on "Assign macro".
- Select macro "Add Value".
- Press with left mouse button on OK
- Press with mouse on button text to show prompt, then change button text to "Add".
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
5 Responses to “Add values to a two-dimensional table based on conditions [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Smaller macro:
Sub AddValue() Dim c, r As Single If Range("C15") = "" Or Range("C16") = "" Then Exit Sub c = Application.Match(Range("C15"), Range("B1:H1"), 0) r = Application.Match(Range("C16"), Range("A2:A13"), 0) Range("A1").Offset(r, c).Value = Range("C17").Value End With End SubThat is nice! Thanks.
Very useful.
Hi I am trying to do exactly this, but my table is in another worksheet than my values. How do I add that to the code?
Thanks
Rob
Thank you so much!
how would this code look if I'm entering the information from a user form? also these are all called out in a range does this work in a table format, like for example I have PartsTbl1?