- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to get or return column header based on specific row value in Excel?
This article contains an example to demonstrate the process of returning a column header from a specific row in excel. The provided is based on VBA code. The mentioned VBA code can successfully perform the required operation. To execute the code, firstly open the developer section, and then execute the code editor to write the required VBA code. Article contains stepwise explanations to justify the requirement of all the steps precisely and accurately. Column header is a simple term used to represent the main heading provided for any particular column.
Example 1: To get or return the column header based on some specific row value in excel by using the VBA code
In this example user will learn the process to write VBA code to obtain the column based on some specific row data value in excel. In the example will consider three row and three column based table. Total number of data values are 9, with 3 row headers, and 3 column header.
To execute the VBA code, user need to open the “Developer” tab, and then under the code section, select the option for “Visual Basic”. Consider below snapshot for reference:
The above step will open a “Microsoft Visual Basic for Applications” dialog box. This dialog box open as a blank. Go open the code editing window. Go to the “Insert” option, and then choose “Module” option. Consider be
In the opened code area, type below given code:
' header for function Sub GetColumnHeaderForRowValue() ' declare required variables Dim rowNum As Variant Dim rowValue As Variant ' Prompt the user to enter the row number and value rowNum = InputBox("Enter the row number:") rowValue = InputBox("Enter the row value:") ' Check if the user entered valid inputs If Not IsNumeric(rowNum) Or Not IsNumeric(rowValue) Then ' display message boc data MsgBox "Invalid inputs!" ' exit method Exit Sub ' end of if expression End If ' Get the column header for the specified row value Dim headerRange As Range Dim columnHeader As String ' setting values Set headerRange = ThisWorkbook.Worksheets("Sheet1").Rows(rowNum).Find(What:=rowValue, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) ' if block If headerRange Is Nothing Then ' message box, to display data MsgBox "The row value was not found in the specified row." ' exit expression Exit Sub ' else block Else ' column header columnHeader = headerRange.EntireColumn.Cells(1, 1).Value ' end of if statement End If ' Display the column header in a message box MsgBox "The column header for the row value " & rowValue & " in row " & rowNum & " is: " & columnHeader ' end of sub method End Sub
Please use proper code indentation, otherwise code will definitely display error. Consider code snapshot provided below:
After that go to the above menu bar of dialog box, and then click on “Run” option.
The above step will open the below given code window. Here, user need to input the row number where data needs to be searched. In this example, will enter 2, to generate the required output results. Finally, click on “OK” button.
The above step will open the below-given code window. Here, the user needs to input the data value that needs to be searched inside the above-provided row number. In this example, will enter 5, to generate the required output results. Finally, click on the “OK” button. here, the user is searching number 5 in the 2nd row, if obtained column header will be displayed else an error message will be displayed.
Since 5 is available in the 2nd row, therefore, column header “rahul” is displayed in the dialog box. Consider the below-given snapshot for proper reference:
The available article contains a single example to demonstrate the use of VBA code to execute the results. This example is based on the use of the VBA code. VBA code is an important practice and is used precisely once the user will be able to execute the code without any error. This article contains a stepwise explanation for all the steps. All the provided snapshots are clear and precise. Comments are well described within the code, to ensure that the user will be able to understand the code processing for all steps.
Kickstart Your Career
Get certified by completing the courseGet Started