Results 1 to 11 of 11

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

Threaded View

  1. #1
    Registered User
    Join Date
    01-15-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    25

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

    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
    Last edited by Niedermee; 08-18-2014 at 01:11 PM. Reason: Fixed asignments in "intStartRow" and "ActiveWorkbook.sheets"

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] multiple conditions to count large amount of data by Type, month & activity type
    By norfolk_lass in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-03-2014, 12:54 PM
  2. Replies: 0
    Last Post: 07-06-2013, 12:59 PM
  3. Proper handling of "cancel" for range-type input box response (type 8)
    By MCCCLXXXV in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-28-2011, 11:19 AM
  4. Type mismatch using rnge as Range with Type 8 Input Box
    By STEVE BELL in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-03-2005, 01:10 AM
  5. [SOLVED] Help: Compile error: type mismatch: array or user defined type expected
    By lvcha.gouqizi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1