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
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