CODEWITHSUNDEEP
excelvba
Siti Sal
Siti Sal

Reputation: 119

Save Cell.Address in array and use it as variable in cell formula

I have 37 columns x 92 rows. Some condition used to separate the data in each column which producing several separate ranges. Number of elements in each ranges need to be count so I use array to save the starting point address and set a range if end point is meet. Then loop inside the range using the CountIf application. " myarray(index) = .Cells(i, ncol).Address" line gives run-time error 13 type mismatch. Which part should I amend?. Thank you.

Option Explicit

Sub makeArray()
Dim ncol As Integer
Dim index As Integer, i As Integer
Dim size As Integer
Dim rng As Range, cell As Range
Dim rng1 As String, rng2 As Integer, rng3 As Integer
Dim myarray() As Integer
ReDim myarray(size)

With ThisWorkbook.Worksheets("sheet2 (2)")
 For ncol = 2 To 37
 size = 1
 index = 0
  For i = 2 To 93
     'assign starting point and save cell.address in array
     If .Cells(i, ncol) >= 12 And .Cells(i - 1, ncol) <= 12 Then
     myarray(index) = .Cells(i, ncol).Address
     size = size + 1
     ReDim Preserve myarray(size)
     index = index + 1
     End If
   Next i

   i = 2
   index = 0
   Do While i <> 94
     'assign end point and set range using array 
     If .Cells(i, ncol) >= 12 And .Cells(i + 1, ncol) <= 12 Then
     rng1 = myarray(index)
     rng2 = .Cells(i, ncol).Address
     Set rng = Range(rng1, rng2)
     rng.Select
       For Each cell In rng.Cells
       rng3 = .Cells(cell.Row, ncol).Address
       .Cells(cell.Row, 74 + ncol).Formula = " =CountIf(" & rng1 & ":" & rng3 & "," & rng3 & ")"
       Next cell
       index = index + 1
     End If
   Loop

 Next ncol
End With

End Sub

Upvotes: 2

Views: 4204

Answers (1)

user6432984
user6432984

Reputation:

You are trying to assign a string value to an array of Integers. Changing Dim myarray() As Integer to Dim myarray() As String will correct the first error.

Dim rng1 As String, rng2 As Integer, rng3 As Integer should be changed to Dim rng1 As String, rng2 As String, rng3 As String

Don't use select unless absolutely necessary.

Set rng = Range(rng1, rng2)
rng.Select
For Each cell In rng.Cells

This accomplishes the same then

For Each cell In Range(rng1, rng2)

Don't use a Loop when iterating over a specific range.

In your Do Loop you never incremented i. This will cause the routine to run indefinitely or until Excel crashes.

This would probably do the same thing that your Loop was intended to do:

For i = 2 to 93

Next

size = size + 1 and index = index + 1 are doing redundant. You resizing your array incorrectly leaving empty values that will cause errors later on:

myarray() demo

If .Cells(i, ncol) >= 12 And .Cells(i - 1, ncol) <= 12 Then
    index = UBound(myarray)
    If myarray(index) <> "" Then
        index = index + 1
        ReDim Preserve myarray(index)
    End If
    myarray(index) = .Cells(i, ncol).Address
End If

Upvotes: 1

Related Questions