0

I am trying to add the value of a cell in an array, but i am getting a type mismatch error in my code. Why is this happening?

Dim rng As Range
Dim cell As Range
Dim arr As Variant

Set rng = Range("panel_is_on")

For Each cell In rng
    If cell.Value2 = "On" Then
        If cell.Offset(0, -1).Value2 = "ISJ" Or cell.Offset(0, -1).Value2 = "BSJ" Then
            arr(i) = cell.Offset(0, -3).Value2
            i = i + 1
        End If
    End If
Next cell
Improve this question
4
  • 1
    Quick test for you: Debug.Print IsArray(arr) Commented Jun 4, 2019 at 10:31
  • 2
    arr is not an array - Dim arr() As Variant then redim preserve in the loop to extend it as needed. Commented Jun 4, 2019 at 10:31
  • ok, this works, but all elements in the array are empty apart from the last one Commented Jun 4, 2019 at 10:58
  • Can you post your revised code? Commented Jun 4, 2019 at 11:15

1 Answer 1

Reset to default
1

This works for me:

For Each cell In rng

If cell.Value2 = "On" Then
    If cell.Offset(0, -1).Value2 = "ISJ" Or cell.Offset(0, -1).Value2 = "BSJ" Then
    i = i + 1
    ReDim Preserve arr(1 To i)
    arr(i) = cell.Offset(0, -3).Value2
 End if 
Next cell
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.