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.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.