Select range by using variables for row and column

**Chris79** · 11-01-2012, 05:17 AM

Hi,

I'm currently trying to select a Range by using variables for row and column number.
Here's the whole thing I'm trying to do:
1) select a unique ID in Worksheet A and store this in a variable called ISIN
2) jump to Worksheet B and find the row in col A that contains this unique ID (I do this by using Cells.Find...)
3) I store the row number in a variable called REIHE
4) I find and store the column number with the last value (see code below) in a variable called SPALTE

This is where i don't get any further. I'm using the following code to select a range, but it always produces a runtime error. Any Help would be highly appreciated! I'm using/have to use Excel 2003...

Dim ISIN As String
    Dim REIHE, SPALTE As Integer
    Dim BEREICH As Range
    
    'ISIN auslesen
    ISIN = ActiveCell.Value
    'Historische Spreads im HIST Sheet finden
    Sheets("HIST").Select
    Range("A1").Select
    Cells.Find(What:=ISIN, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    
    REIHE = ActiveCell.Row
    With ActiveSheet
    SPALTE = .Cells(REIHE, .Columns.Count).End(xlToLeft).Column
    End With
    
    Range(Cells(REIHE, 4), Cells(REIHE, SPALTE)).Select

EDIT: Just to complete the picture: My ultimate goal is to create a Graph from this and another Range. Idea is to replace the bold code below by the code (or code similar to that) above....

Charts.Add
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.SetSourceData Source:=Sheets("HIST").Range("D549:O549"), PlotBy _
        :=xlRows
    ActiveChart.SeriesCollection(1).XValues = "=HIST!R2C4:R2C15"
    ActiveChart.SeriesCollection(1).Name = "=HIST!R549C1"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="GRAPH"
    With ActiveChart
        .HasTitle = False
        .HasLegend = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With

**patel45** · 11-01-2012, 09:52 AM

try with
SPALTE = .UsedRange.Columns.Count

**Chris79** · 11-01-2012, 10:26 AM

Hi patel45,

Thanks for your reply. I've also been able to make some progress on my own:

    Dim ISIN As String
    Dim REIHE, SPALTE As Integer
    
    'ISIN auslesen
    ISIN = ActiveCell.Value
    'Historische Spreads im HIST Sheet finden
    Sheets("HIST").Select
    Range("A1").Select
    Cells.Find(What:=ISIN, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    
    REIHE = ActiveCell.Row
    With ActiveSheet
    SPALTE = .Cells(REIHE, .Columns.Count).End(xlToLeft).Column
    End With
    
    Range(Cells(REIHE, 4), Cells(REIHE, SPALTE)).Name = "GraphSpreadRange"
    Range(Cells(2, 4), Cells(2, SPALTE)).Name = "GraphTimeRange"

This gives me the correct ranges back. I thought names ranges would make it easier to refer to when it comes to draw the graph i mentioned in the second part of my thread, but so far no luck... I think i will open a new thread regarding my problem with the graph. Topic of this thread doesn't look right any more.