CODEWITHSUNDEEP
vbaexcel
Ashok
Ashok

Reputation: 394

How to Populate Range Address which was Stored in Array

i writing code for adding two cells and cell addresses stored in array. the below part of code showing the error - how to retrieve the cell addresses from array and adding those cells - please suggest the correction

Sub test()

    Dim A1add(1 To 100) As String
    lrw = ThisWorkbook.ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row

    A1add(1) = ThisWorkbook.ActiveSheet.Sheets(1).Range("A2")
    A1add(2) = ThisWorkbook.ActiveSheet.Sheets(1).Range("b2")
    Set add1 = ThisWorkbook.ActiveSheet.Sheets(1).Range("C2")


    Select Case 2

    Case 2

        With add1.Resize(lrw)
            .NumberFormat = "0"
            .Formula = "= ( " & A1add(1).Address(False, False) & "+" & A1add(2).Address(False, False) & " )"
        End With

    End Select

End Sub

Upvotes: 0

Views: 64

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

A1add(1) = ThisWorkbook.ActiveSheet.Sheets(1).Range("A2")

Puts the value of the cell in the array not the range or the address of the range. That information is not stored in the array.

And ActiveSheet.Sheets(1). refers to two sheets. remove one.

In this case you would want:

A1add(1) = ThisWorkbook.ActiveSheet.Range("A2").Address(0,0)

Then the formula would use that alone.

"= ( " & A1add(1) & "+"

So:

Sub test()

    Dim A1add(1 To 100) As String
    Dim lrw As Long
    Dim Add1 As Range

    lrw = ThisWorkbook.ActiveSheet.Range("A:A").SpecialCells(xlCellTypeLastCell).Row

    A1add(1) = ThisWorkbook.ActiveSheet.Range("A2").Address(0, 0)
    A1add(2) = ThisWorkbook.ActiveSheet.Range("B2").Address(0, 0)
    Set add1 = ThisWorkbook.ActiveSheet.Range("C2")


    Select Case 2

    Case 2

        With add1.Resize(lrw - 1)
            .NumberFormat = "0"
            .Formula = "= ( " & A1add(1) & "+" & A1add(2) & " )"
        End With

    End Select

End Sub

Upvotes: 1

Related Questions