none
Add variable to Array RRS feed

  • Question

  • Excel 2010, VBA

    Is there an easy way to add my RWx to my Array CalWeek?

    _________________________________________

    'Month = Jan
        Rw1 = 3
        Rw2 = 10
        Rw3 = 17
        Rw4 = 24
        Rw5 = 31
        Rw5 = 38
       
    CalWeek = "B3,D3,F3,H3,J3,L3,N3" & _
             ",B10,D11,F10,H10,J10,L10,N10" & _
             ",B17,D17,F17,H17,J17,L17,N17" & _
             ",B24,D24,F24,H24,J24,L24,N24" & _
             ",B31,D31,F31,H31,J31,L31,N31" & _
             ",B38,D38"

    _________________________________________

    This gets utilized by :

       R = 1
       cellAddressArray = Split(CalWeek, ",")
      
       For i = LBound(cellAddressArray) To UBound(cellAddressArray)
                cellAddress = cellAddressArray(i)
                MsgBox (cellAddress & "/" & R)
                Set currentCell = shtW.Range(cellAddress)
              If currentCell.Value <> " " Then
                currentCell.Formula = Month & R

              End If
              R = R + 1
       Next I

    _________________________________________


    Tuesday, December 29, 2015 5:47 PM

Answers


  •     For C = 1 To 14
        C = C + 1
        testRange = ThisWorkbook.Worksheets("Cal_2016").Cells(R, C).Address(False, False)
        testRange2 = testRange2 & "," & testRange
        Next C

        testRange2 = Right(testRange2, Len(testRange2) - 1) 'removes first comma

    a) Use the STEP command in a FOR loop to adjust the increment, don't modify the loop variable.

    b) Please never build a range address in that way, use the RANGE object directly!

    Execute  the code below in a new file.

    Andreas.

    Sub Test()
      Dim MyCols As Range, MyRows As Range
      Dim Result As Range, This As Range
      Dim i As Integer
      
      'Setup the 1st column
      Set MyCols = Columns("B")
      'Add each second column up to "N"
      For i = Columns("D").Column To Columns("N").Column Step 2
        Set MyCols = Union(MyCols, Columns(i))
      Next
      
      'Setup the 1st row
      Set MyRows = Rows(3)
      'Add each 7th row down
      For i = 10 To 38 Step 7
        Set MyRows = Union(MyRows, Rows(i))
      Next
      
      'Get the intersection
      Set Result = Intersect(MyRows, MyCols)
      MsgBox Result.Address(0, 0)
      
      'In each cell
      i = 0
      For Each This In Result
        'Fill a date
        i = i + 1
        This = DateSerial(Year(Now), Month(Now), i)
      Next
    End Sub
    

    Wednesday, December 30, 2015 7:39 PM

All replies

  • Hi texastwostep,

    As far as I know, we can’t add variable to array, we need to actual value to the array.

    For example:

    Dim arraystr() As String
    Dim i As Long
    For i = 0 To 10
        ReDim Preserve arraystr(i)
        arraystr(i) = I
    Next i
    MsgBox UBound(arraystr)

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by Ciprian Lupu Wednesday, December 30, 2015 8:23 AM
    Wednesday, December 30, 2015 6:05 AM
    Moderator
  • Thank you,

    I probably didn't explain it well as I am still pretty naïve to programming but the following has me on the right track and will add the row counter next.

    Sub RangeTest()
      Dim testRange As String
      Dim testRange2 As String
      Dim targetWorksheet As Worksheet
      Dim R As Integer
      Dim C As Integer
      'Dim MyCell As String
     
       Set targetWorksheet = Worksheets("Cal_2016")
         
       R = 3
       C = 2
        For C = 1 To 14
        C = C + 1
        testRange = ThisWorkbook.Worksheets("Cal_2016").Cells(R, C).Address(False, False)
        testRange2 = testRange2 & "," & testRange
        Next C

        testRange2 = Right(testRange2, Len(testRange2) - 1) 'removes first comma
        MsgBox (testRange2)

    End Sub

    Wednesday, December 30, 2015 3:41 PM
  • No luck on the survey the link took me to a generic search tool. If you're asking, I really don't like the new site. In the past the search tool would render similar questions and eliminate the need for posting questions. Or when you begin to enter a question similar questions would appear. It seems that all of this is gone now and forced to ask a question rather than review similar results.

    I'll make do, but sometimes change is not always helpful.

    Wednesday, December 30, 2015 3:46 PM

  •     For C = 1 To 14
        C = C + 1
        testRange = ThisWorkbook.Worksheets("Cal_2016").Cells(R, C).Address(False, False)
        testRange2 = testRange2 & "," & testRange
        Next C

        testRange2 = Right(testRange2, Len(testRange2) - 1) 'removes first comma

    a) Use the STEP command in a FOR loop to adjust the increment, don't modify the loop variable.

    b) Please never build a range address in that way, use the RANGE object directly!

    Execute  the code below in a new file.

    Andreas.

    Sub Test()
      Dim MyCols As Range, MyRows As Range
      Dim Result As Range, This As Range
      Dim i As Integer
      
      'Setup the 1st column
      Set MyCols = Columns("B")
      'Add each second column up to "N"
      For i = Columns("D").Column To Columns("N").Column Step 2
        Set MyCols = Union(MyCols, Columns(i))
      Next
      
      'Setup the 1st row
      Set MyRows = Rows(3)
      'Add each 7th row down
      For i = 10 To 38 Step 7
        Set MyRows = Union(MyRows, Rows(i))
      Next
      
      'Get the intersection
      Set Result = Intersect(MyRows, MyCols)
      MsgBox Result.Address(0, 0)
      
      'In each cell
      i = 0
      For Each This In Result
        'Fill a date
        i = i + 1
        This = DateSerial(Year(Now), Month(Now), i)
      Next
    End Sub
    

    Wednesday, December 30, 2015 7:39 PM