I'm trying to write a code that makes a graph from different parts of the data I have, the issue is that I need a for loop to go through all my lines of data and when the row changes also the range for the next graph

Basically I need the code to check when i is 1 to graph using the range A2:A12 from a different sheet (The table below exists on Sheet1, while the range is from Sheet2)

The issue I'm facing is that when I try to define Range("Variable 1:Variable 2") the code doesn't seem to work.

I'm sure I'm thinking about this wrong but I haven't found any solutions online.

CodePudding user response：

Never use Range or Cells (as well as Columns, Rows) without specifying in which sheet you expect them to be. Otherwise your code depends on which workbook/worksheet is active the moment it runs and your code is not reliable.

Use something like

ThisWorkbook.Worksheets("Sheet2").Range(…)

or better define a variable so you don't have to repeate it:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet2")

'and then always use
ws.Range(…)

To use variable ranges you either need to concatenate your variables to a valid address you can use:

ws.Range(Variable1 & ":" & Variable2)

or simly use

ws.Range(Variable1, Variable2)

where for example Variable1 = "A2" and Variable2 = "A12"

CodePudding user response：

Reference Ranges Using a List of Cell Addresses

Option Explicit

Sub ReferenceRanges()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet1")
    Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet2")
    
    Dim drg As Range
    Dim r As Long
    
    For r = 2 To sws.Cells(sws.Rows.Count, "B").End(xlUp).Row

        ' Note the importance of using '.Value' (try without it).
        Set drg = dws.Range(sws.Cells(r, "B").Value, sws.Cells(r, "C").Value)
        
        ' Continue using 'drg', e.g.:
        
        Debug.Print drg.Address(0, 0)
    
    Next r
    
End Sub