Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SELECT CASE FUNCTION vba to find a certain cell name and select data in col

    I have been struggling with this problem for nearly a week.
    I have a VBA code that producess excel formatted output and I have made some graphs through VBA.

    The only problem is I have selected the data by typing in the cell numbers, but in other outputs, the desired graphing data will be in different columns.

    So, I am tryin to find a way to search for a certain name in a cell, such as "Tool LVDT #1" and then auto selecting the data below it.
    I believe this is where the select case comes in, but I am strugglin hard.

    This is apart of the code that needs changing:
    Code:
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Charts.Add
        ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
        ActiveChart.SetSourceData Source:=Sheets("Data").Range( _
            "Data!$B$3:$B$" & LR & ",Data!$AE$3:$AH$" & LR), PlotBy:= _
            xlColumns
    Any help would be awesome!!!!

    THANKS!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,184
    Thanks
    201
    Thanked 781 Times in 715 Posts
    Boarder236,

    Welcome to the lounge as a new poster.

    To find the address of a "Named" cell/range.
    worksheets("SheetNameHere").Range("NameHere").Addr ess

    Hope this helps.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    New Lounger
    Join Date
    Jun 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    THanks for the help.

    But where would I place this code?

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,184
    Thanks
    201
    Thanked 781 Times in 715 Posts
    Boarder236,

    You need to put this code to create the range then use that variable in your chart statement e.g.
    Code:
    Option Explicit
    
    Sub DefineRange()
    
       Dim rngChartData As Range
       
       Set rngChartData = Range(Range("ULC"), Range("ULC").End(xlToRight))
       Set rngChartData = Range(rngChartData, rngChartData.End(xlDown))
       
       Debug.Print rngChartData.Address(, , xlA1)
       
    
    End Sub
    The above assumes that you have named the upper left cell (ULC) of your data range and that the data is contigious both across and down.

    You can then use the statement following debug.print to get the data range address.
    ChartRange.JPG
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Tags for this Thread

Posting Permissions

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