How to select cells between two values in column and draw the chart from selected elements

Question

I have a problem. I need to write the macro which select the values in column E. Values of selected items should be between values in cell T2 and U2. After selection, macro should draw the chart.

I tried 3 ways:

First Approach:

Sub wykres1()  
    Dim rng As Range
    Dim cell As Range 

    Set rng = Range("E1", Range("E65536").End(xlUp))  

    For Each cell In rng  

    If cell.Value > "T2" and cell.value < "U2" Then Cell.Select    
        With Selection    
            ActiveSheet.Shapes.AddChart2    
        End With    
    Next cell    
End Sub

Wykres1 Doesn't work, because the line with if is highlighted on red.

Second Approach:

Sub wykres2()    
    Dim rng As Range    
    Dim cell As Range    

    Set rng = Range("E1", Range("E65536").End(xlUp))    

    For Each cell In rng    
        If cell.Value > ActiveSheet.Cell(2,20).Value and cell.value < ActiveSheet.Cell(2,21).Value Then Cell.Select    
        With Selection    
            ActiveSheet.Shapes.AddChart2    
        End With    
    Next cell
End Sub

Wykres2 Doesn't work, because the line with if is highlighted on red.

Third Approach:

Sub wykres3()     
    Dim rng As Range
    Dim cell As Range

    Set rng = Range("E1", Range("E65536").End(xlUp))

    For Each cell In rng
        If cell.value > -35 And cell.value < -32 Then cell.Select
        With Selection
            ActiveSheet.Shapes.AddChart2    
        End With
    Next cell
End Sub

Wykres3 freeze after run. When I remove the part with draw chart, the macro select one cell not the range with selected values. And here I put the values in macro (-35) (-32) - but I'm interested in possibility to put values from cells (T2) (U2).

---

As I mentioned - I need to create macro which select the cells in column E with values between values in cells T2 and U2. After selection macro must draw the chart.

Thank You for Your help.

Answer 1

Try this (Untested). avoid the use of .Select. Work with objects. You may want to see How to avoid using Select in Excel VBA

Sub wykres1()
    Dim rng As Range, cell As Range
    Dim lRow As Long, i As Long
    Dim ws As Worksheet

    '~~> Change as applicable
    Set ws = Sheet1

    With ws
        '~~> Find last row in Col E
        lRow = .Range("E" & .Rows.Count).End(xlUp).Row

        '~~> Loop though the range
        For i = 1 To lRow
            If .Range("E" & i).Value > .Range("T2").Value And _
               .Range("E" & i).Value < .Range("U2").Value Then
                With .Range("E" & i)
                    '
                    '~~> Do Something
                    '
                End With
            End If
        Next i
    End With
End Sub

As I mentioned - I need to create macro which select the cells in column E with values between values in cells T2 and U2. After selection macro must draw the chart.

You can store each range found above in one range object and then use that. See this example

Sub wykres1()
    Dim rng As Range, cell As Range
    Dim lRow As Long, i As Long
    Dim ws As Worksheet
    Dim Obj As ChartObject

    '~~> Change as applicable
    Set ws = Sheet1

    With ws
        '~~> Find last row
        lRow = .Range("E" & .Rows.Count).End(xlUp).Row

        '~~> Liip though the range
        For i = 1 To lRow
            If .Range("E" & i).Value > .Range("T2").Value And _
               .Range("E" & i).Value < .Range("U2").Value Then
                '~~> Store the cell in a range object
                If rng Is Nothing Then
                    Set rng = .Range("E" & i)
                Else
                    Set rng = Union(rng, .Range("E" & i))
                End If
            End If
        Next i

        '~~> Once you have the range, create a chart and assign range
        If Not rng Is Nothing Then
             With .ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
                .Chart.SetSourceData Source:=rng
                .Chart.ChartType = xlColumnClustered
            End With
        End If
    End With
End Sub