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 SubHow 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"
- Click "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).
- Right-click on your workbook in project explorer to open a context menu.
- Click Insert on the menu.
- Click 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 clickable button on worksheet and link it to a macro
- Go to "Developer" tab on the ribbon.
- Click "Insert" button.
- Click "Button".
- Click and drag on worksheet to create a button.
- Right click on macro to display a context menu, click on "Assign macro".
- Select macro "Add Value".
- Click OK
- Click on button text to show prompt, then change button text to "Add".
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 [โฆ]
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 [โฆ]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The [โฆ]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to execute a VBA macro using a Drop Down list. The Drop Down list contains two [โฆ]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and [โฆ]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder [โฆ]
4 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.
English
Spanish
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!