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
Bookmarks