how to use variable in range using vba

Hello I have written code for generating graph using vba. everything working correctly ,but problem is i want to use variable for selecting particular column the code is :

Set x = Range("$CF$2", Range("$CF$2").End(xlDown))
Set y = Range("$CG$2", Range("$CG$2").End(xlDown))
Dim c As Chart
Set c = ActiveWorkbook.Charts.Add
Set c = c.Location(Where:=xlLocationAsObject, Name:=assume)
With c
.ChartType = xlXYScatterLines

' set other chart properties

With .Parent
.Top = Range("cl1").Top
.Left = Range("cl12").Left
.Name = "c"
End With
End With

Dim s As Series
Set s = c.SeriesCollection(1)
With s
.Values = y
 .XValues = x
' set other series properties

End With

I want to use variable COLs in first to line they are

Set x = Range("$CF$2", Range("$CF$2").End(xlDown))
Set y = Range("$CG$2", Range("$CG$2").End(xlDown))

COLs is variable of string

Answers


I'm not sure I understand, but if you want a Range object based on a string, why not try this:

Option Explicit

Sub TestRange()

    '***** Declare variables
    Dim oX As Range
    Dim sCOLs As String

    '***** Select column
    sCOLs = "A"


    '***** Set Range based on column from sCOLs
    Set oX = Range(sCOLs & "2", Range(sCOLs & "2").End(xlDown))

    '***** Do something with oX
    Debug.Print TypeName(oX)

    '***** Clean up
    Set oX = Nothing

End Sub

You could also try and have the whole range as a string, maybe a bit cleaner code?

Dim sRange as String

sRange = "A2"

Set oX = Range(sRange, Range(sRange).End(xlDown))

You could also use Inputbox to have the user click on a certain cell. This then creates a variable "UserRange" which contains the cell reference you can use.

Sub test()

Dim UserRange As Range

Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)
UserRange.Value = "Test"

End Sub

Need Your Help

When to use Socket and when to use XMLSocket in Flex?

java flex actionscript-3 sockets

In what situations should i use Socket class or XMLSocket when trying to connect from Flex to a Java Server that is going to listen to a port?

using minidom to parse xml

python xml minidom

Hi I have trouble understanding the minidom module for Python.