This Excel Hack is a continuation of Excel Hack 66. Click here to see the first part of this tutorial.

Right now, I have a table set up with 4 columns – name of celebrity, name of web page, number of likes and number of comments.

I have also created a User Form set up to open web pages (in particular, Instagram profiles of popular personalities), and receive input on number of likes and number of comments on their most recent post.

There are several approaches to writing back a User form entry to an Excel worksheet table. I will use the approach that utilizes the MATCH function, to connect the User form and the Excel table through the name of the webpage field – the only populated field on the Excel table, that the User form and Excel table have in common.

The MATCH function will return the position of the web page name entered in the User form on the list of web page names in the Excel table, and I will use that return – an index number – to identify the table row to write the other User form entries.

With my scraggly illustrations out of the way :-), let’s go on ahead to code:

Navigate to the Visual Basic Editor, and go to the User Form created earlier. Double-click on the Submit command button to code it.

Place the below code between the opening and closing lines (Private Sub…., End Sub):

Dim currRow As Long 

currRow = Application.WorksheetFunction.Match(TextBox1.Text, Range("B:B"), 0) 

Cells(currRow, 3).Value = TextBox2.Value 
Cells(currRow, 4).Value = TextBox3.Value 

Unload Me

This code:

• Declares an integer variable (currRow) for holding the index number the MATCH function will return
• Assigns the results of a MATCH() function (Excel functions are expressed as Application.WorksheetFunction.Match when writing in VBA) to currRow. The MATCH function is searching for the entered web page name (TextBox1.Text) within column B on the Excel table (which is the web page name column)
• Writes the entered number of likes (TextBox2.Text) to the Excel cell on the index row and the 3rd column (which is the number of likes column)
• Writes the entered number of comments (TextBox3.Text) to the Excel cell on the index row and the 4th column (which is the number of comments column)
• Closes the user form, by unloading it