Swap Axis of an Excel Chart Without Changing Excel Sheet Data

This post explains how you can swap the axis of an Excel chart without swapping values in the sheet. Assume we have an Excel sheet like this.

If we select the data and insert a “Scatter with smooth lines” chart, then we will get a chart like this.

Or else we can insert a “Line” chart like this.

If you look at these charts, you can see that names are in the x axis. In the line chart names are visible in the x axis. But the Scatter chart shows some numbers instead of the names. Yet if you take the cursor to the Scatter chart it will show you the data points like this.

Therefore it is clear that the Scatter chart also has names in the x axis.

But then these charts don't make much sense according to how the Excel application has chosen the x axis. But it will be more understandable if we can set “Tests” as x axis instead of “Names”. One way to do that is re-arranging the data in the Excel sheet. However there is an easier way to do that without doing any change to the Excel data. We can simply do that by changing the x axis of the chart. Below are the steps to change the x axis.

First click somewhere on the chart. You will see two new tabs appear at the Excel ribbon called “Design” and “Format”.

Go to the design tab and click on the “Switch Row/Column” icon in the “Data” Group.

Scatter chart will change to something like this.

You can do the same for the line chart as well.

How to Add Search Function to a VBA Data Entry Application

In this post I will show you how to add a search function to a data entry application. I have already created the data entry form and sheet to save the data.

This is the form used to input the data.

Entered data will be saved to this worksheet.

Now the next step is to add the search function to this application. There are two ways to do that. We can either add the search function to existing data input form or add the search function to a dedicated form. So in this example I’m going to add the search function to a separate form. After creating the two forms you can add buttons to switch between each form. Here is the search form designed for this application. Labels and textboxes are the same as input form. I have changed the title to “SEARCH ORDER INFORMATION”. Also the “ENTER” button is replaced by the “SEARCH” button. Name of the “SEARCH” button is “cmdSearch''.

In this application, users will be able to search the information using the order number. So the user will enter the order number they want to search and click on the search button. Then the program will check the data sheet and show the relevant information in the form.

Now let’s develop the code for the “SEARCH” button.

First we need to declare a few variables

Dim WS_Data As Worksheet
Dim LastRow As Long
Dim i As Long
Dim SearchedValue As String
Dim DataArr() As Variant

Then assign the worksheet for the WS_Data variable.

Set WS_Data = Worksheets("Data")

Next find the last row of the Data sheet.

LastRow = WS_Data.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

Now we need to find the matching row from the data sheet and show that information in the form. There are various ways to find the matching row from the data sheet. This post explains three different ways to find the matching row.

Get Row Number of a Matching Value

So I’m going to use the method 2 explained in that post. In this method we use an array to hold all the data in the worksheet. Using an array will increase the speed of the searching process if you have a large amount of data.

Add all the data from Data sheet to an array.

DataArr = WS.Range("A1:G" & LastRow)

Name of the “Order Number” textbox is txtOrderNumber. So we can assign the value to the SearchedValue variable from the Order Number textbox like this.

SearchedValue = txtOrderNumber.Value

Before proceeding further we need to check whether the user has entered a value to the Order Information textbox.

If Len(SearchedValue) > 0 Then

End If

Above If statement will be true if there is a value in the textbox.

Then we can use a For Next statement to loop through the data of the array.

If Len(SearchedValue) > 0 Then
     For i = 1 To UBound(DataArr, 1)

     Next i
End If

Check for the matching value using StrComp function

If Len(SearchedValue) > 0 Then
     For i = 1 To UBound(DataArr, 1)
          If StrComp(DataArr(i, 1), SearchedValue, vbTextCompare) = 0 Then

          End If
     Next i
End If

If a match is found then write information from that row to textboxes and exit the for loop.

If Len(SearchedValue) > 0 Then
     For i = 1 To UBound(DataArr, 1)
          If StrComp(DataArr(i, 1), SearchedValue, vbTextCompare) = 0 Then
               txtSize.Value = DataArr(i, 2)
               txtSerialNumber.Value = DataArr(i, 3)
               txtProjectNumber.Value = DataArr(i, 4)
               txtType.Value = DataArr(i, 5)
               txtDate.Value = DataArr(i, 6)
               txtSurveyor.Value = DataArr(i, 7)
               Exit For
          End If
     Next i
End If

So the complete code of the “SEARCH” button should look like this.

Private Sub cmdSearch_Click()

Dim WS_Data As Worksheet
Dim LastRow As Long
Dim i As Long
Dim SearchedValue As String
Dim DataArr() As Variant

Set WS_Data = Worksheets("Data")

LastRow = WS_Data.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
DataArr = WS_Data.Range("A1:G" & LastRow)
SearchedValue = txtOrderNumber.Value

If Len(SearchedValue) > 0 Then
     For i = 1 To UBound(DataArr, 1)
          If StrComp(DataArr(i, 1), SearchedValue, vbTextCompare) = 0 Then
               txtSize.Value = DataArr(i, 2)
               txtSerialNumber.Value = DataArr(i, 3)
               txtProjectNumber.Value = DataArr(i, 4)
               txtType.Value = DataArr(i, 5)
               txtDate.Value = DataArr(i, 6)
               txtSurveyor.Value = DataArr(i, 7)
               Exit For
          End If
     Next i
End If

End Sub

Assume a user enters 8782 and performs a search.

Then the program will show the result in the form like this.