vba 如何使用vba在范围内使用变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12350590/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to use variable in range using vba
提问by M_S_SAJJAN
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 :
您好，我已经编写了使用 vba 生成图形的代码。一切正常，但问题是我想使用变量来选择特定的列，代码是：
Set x = Range("$CF", Range("$CF").End(xlDown))
Set y = Range("$CG", Range("$CG").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 COLsin first to line they are
我想首先使用变量COLs来排列它们
Set x = Range("$CF", Range("$CF").End(xlDown))
Set y = Range("$CG", Range("$CG").End(xlDown))
COLsis variable of string
COLs是字符串的变量
采纳答案by Olle Sj?gren
I'm not sure I understand, but if you want a Rangeobject based on a string, why not try this:
我不确定我是否理解，但如果你想要一个Range基于字符串的对象，为什么不试试这个：
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))
回答by Diederik
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.
您还可以使用 Inputbox 让用户单击某个单元格。然后创建一个变量“UserRange”，其中包含您可以使用的单元格引用。
Sub test()
Dim UserRange As Range
Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)
UserRange.Value = "Test"
End Sub
