Input box to needs different type of variable type (RC vs. A1?)

[Niedermee]

I'm trying to use an input box to prompt the user to select the row containing the data they want plotted. I am having a problem with the way VBA likes to have its rows and column references formatted (as a letter?) vs. what is returned from selecting an active cell (as a number?).

Here's the example data that applies:

Book1.xlsx

The portion of the code I'm having trouble with comes just after the first If statement.

Sub MakeFigures()

    Dim intFirstRow As Integer      ' First row of the current chart series
    Dim intLastRow As Integer       ' Last row of the current chart series
    Dim intStartRow As Integer      ' First row of data in the spreadsheet
    Dim intRow As Integer           ' Current row being processed
    Dim strBH As String             ' Current monitoring well
    Dim strDataColumn As Integer
    Dim xlChart As Chart
    Dim xlsheet As Worksheet
    Dim Parameter1 As Range         ' Column containing 1st parameter chosen by user to plot

    ' First select a chart
    If ActiveChart Is Nothing Then
        MsgBox "Please select the empty chart tab before running the macro.", , "No Chart Selected"
        Exit Sub
    End If
    
    '   Hard-coded chart, sheet, row and column selections here could be replaced by prompts to the user
    Set xlsheet = ActiveWorkbook.Sheets("Data")
    Set xlChart = ActiveChart
    intStartRow = 3    ' First row with data in it
    Parameter1 = Application.InputBox(Prompt:="Select the cell containing the name of the parameter you want to plot.", Default:="Navigate to your data and select the header cell for the data of interest.", Type:=8)
    strDataColumn = Parameter1      ' User's first parameter selection to be plotted
    
    '   Start in the first data row
    intRow = intStartRow
    strBH = xlsheet.Cells(intRow, 1).Value
    intFirstRow = intRow
    
    Do
        '   Step down the rows
        '   Is this a new borehole number? If so, create a chart series for the previous rows.
        If xlsheet.Cells(intRow, 1).Value <> strBH Then
            intLastRow = intRow - 1             ' The series ends at the row we just passed
            With xlChart
                Dim s As Series
                .SeriesCollection.NewSeries
                Dim intSeries As Integer
                intSeries = .SeriesCollection.Count
                Set s = .SeriesCollection(intSeries)
                s.Name = RangeAddress(xlsheet, "A", intFirstRow)        ' Set the series name to the monitoring well number
                s.XValues = RangeAddress(xlsheet, "B", intFirstRow, intLastRow) ' Set x values to the date column
                s.Values = RangeAddress(xlsheet, strDataColumn, intFirstRow, intLastRow)  ' Set y values to the data column
            End With
            
            '   Reset to start a new series for the next hole
            intFirstRow = intRow
            strBH = xlsheet.Cells(intRow, 1).Value
        End If
        
        '   Now move down one row
        intRow = intRow + 1

        ' Stop at the first row with no date
    Loop Until strBH = ""
    
    MsgBox "Chart completed!", , "Done"

End Sub

Private Function RangeAddress(xlsheet As Worksheet, strColumn As String, intFirstRow As Integer, Optional intLastRow As Integer) As String
    '   Returns a cell address or range address suitable for input to the x or y values
    '   property of a chart series.
    
    RangeAddress = "=" & xlsheet.Name & "!$" & strColumn & "$" & intFirstRow
    If intLastRow > 0 Then RangeAddress = RangeAddress & ":$" & strColumn & "$" & intLastRow

End Function

[Niedermee]

To make this simpler, how do I change the format of the row/column variables in VBA?

The input box returns a selected cell format as Row 6 when I selected row "H". This is cell is used later in the code, but needs is to be formatted as a letter and not a number so it crashes.

[Norie]

Have you looked at using Cells?

[Niedermee]

I don't understand "using Cells".

I'm a novice at VBA and am trying to change code that originally hard coded the row assignment the same as it currently does for the 1st row.

[Norie]

If you want the user to select a cell/range use this.

 Set Parameter1 = Application.InputBox(Prompt:="Select the cell containing the name of the parameter you want to plot.", Default:="Navigate to your data and select the header cell for the data of interest.", Type:=8)

[Niedermee]

Now it gives me a Compile error: Object Required and highlights Parameter1 before the "="

[Norie]

Strange, that's the error I get if I don't use Set.

Did you change anything else?

When you got the error had you selected a range?

By the way, the reason you should use Set is because Application.InputBox with the Type set to 8 returns a Range object.

[Niedermee]

In addition to adding "Set" to the Parameter1 assignment, I needed to change the value to an address (Parameter1.address) and then used the mid function to select just the row letter (this assumes I never have any more than Z rows), but it works. See attached.

[Niedermee]

How do I mark post as Solved?

[Norie]

Glad it's solved.

Mind you there's no reason to use Address/Mid to get the row/column number.

In fact if you do that you may run into problems if the column goes past Z.

Instead of using Address/Mid you can use the Row and Column properties of Parameter1.

[Niedermee]

That was what my initial and ultimate goal, but couldn't figure it out. Forgive me if the answer is embedded in someone's reply, but I couldn't get it to work for me.

How do I "use the Row and Column properties of Parameter1"?