Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    May 2008
    Location
    Jamestown, Worcestershire, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vb in excel - variable subst (excel 2003 11.8206.8202 sp3)

    Hello. Below is piece of a macro, whereby I am trying to pass a cell range by a variable, which itself is represented by a string concatenation. It looks like the problem is that vb doesn't see the actual range, rather, it looks like it sees the literal variable name. xystring3 has a value, and I want that value to be passed in using a character variable built from "xystring" and 3. I can use the variable xystring3 directly, and it works, but if I use another variable to represent the xystring3 variable, it chokes. Any idea how to get past this?


    Dim xyrange1 As String
    Dim xyrange2 As String
    Dim xyrange3 As String
    Dim xyrange4 As String
    Dim xyrange5 As String
    Dim xyrange6 As String
    Dim xyrange7 As String
    Dim xyrange8 As String
    Dim xyrange9 As String

    Dim seriesname As String
    Dim seriesnum As Integer


    xyrange3 = "a1:a30001,d1:d30001"

    For seriesnum = 3 To 9 Step 1

    seriesname = "xyrange" & Format$(seriesnum)
    .SeriesCollection.Add Source:=wsh.Range(seriesname)
    Set ser = .SeriesCollection(.SeriesCollection.Count)
    ser.AxisGroup = xlSecondary

    Next

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vb in excel - variable subst (excel 2003 11.8206.8202 sp3)

    You can't refer to a variable this way. You can use an array instead

    Dim xyRange(1 To 9) As String
    xyRange(1) = ...
    ...
    xyRange(9) = ...

    For seriesNum = 1 To 9
    seriesName = "xyRange" & seriesNum
    ...
    Next seriesNum

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •