0

I want to find a cell that fit some conditions with a function chkBelow, and then store that cell's address in the array chkBelow which is returned and stored in another array, ch, in the main sub; moveBelow.

Finally I want to store the column- and row-addresses from the array ch, and store them in the variables a and b, but an error occurrs at a = ch(1): Type Mismatch(Error 13)

Sub moveBelow()
    Option Base 1
    Dim ch As Variant
    ch = chkBelow
    a = ch(1)
    b = ch(2)

    Cells(a - 1, b) = Cells(a, b)
    Cells(a, b) = ""

End Sub

Private Function chkBelow() As Variant
    Dim c(1 To 2)
    For k1 = 2 To 5
        For k2 = 3 To 5
            If Cells(k1, k2 + 1).Interior.Pattern = xlNone Then
                c(1) = k1
                c(2) = k2
                chkBelow = c
            End If
        Next k2
    Next k1
End Function
eirikdaude
  • 3,106
  • 6
  • 25
  • 50
Syz
  • 45
  • 1
  • 6

1 Answers1

1

I'm surprised an error doesn't occur earlier, as there are several issues with your code.

First of all, you need to move Option Base 1 to the top of the module, as it is a module-level statement - trying to run your code confirms this, as it aborts before running stating that an invalid statement has been made inside the procedure.

Secondly, and this is not strictly speaking something you have to do, but more good coding practice, your naming of variables makes the code hard to read. Probably not an issue in a small procedure such as this, but giving your variables proper names is a good habit to get into anyway. You should also be more explicit both when defining what cells you are working on (i.e. include at least the worksheet name they are on, and maybe also what workbook), and dimension your variables to a specific type if you can. In addition to all this, you should dimension all variables you use in your code, e.g. a and b in your code above. To make it easier to remember this, I recommend adding Option Explicit at the top of any module you create. This makes it much easier to avoid type mismatch errors, etc.

Assuming it isn't any of the above which has created the errors for you, it may be that either of the elements in your array hasn't been set by the function you're calling, so you should check if they are values can be used when referencing a cell, i.e. if their values will be larger than 0 for all instances where you use them to reference cells.

While rewriting your code I also notice that there is a distinct possibility that you'll want to break out of your for-loops before they finish, if the conditions for creating the array is met - I have not taken this into consideration in this answer, but it is something you should consider. This should be easily achievable by exiting the function once the array-values have been set.

Making some improvements on the code in your question, it could look something like this:

Option Explicit
Option Base 1

Sub moveBelow()
    Dim ch As Variant
    Dim a As Long, b As Long

    ch = chkBelow

    If a > 1 And b > 0 Then
        ThisWorkbook.Worksheets("Sheet1").Cells(a - 1, b) = ThisWorkbook.Worksheets("Sheet1").Cells(a, b)
        ThisWorkbook.Worksheets("Sheet1").Cells(a, b) = ""
    End If

End Sub

Private Function chkBelow() As Variant
    Dim c(1 To 2) As Long
    Dim k1 As Long, k2 As Long

    For k1 = 2 To 5
        For k2 = 3 To 5
            If ThisWorkbook.Worksheets("Sheet1").Cells(k1, k2 + 1).Interior.Pattern = xlNone Then
                c(1) = k1
                c(2) = k2
            End If
        Next k2
    Next k1

    chkBelow = c

End Function

All that said, I think you go to a lot of effort (and obfuscation) to check if all the cells in a range are formatted or not. Personally I'd probably change your function to something like:

Private Function checkBelow() As Range
    Dim rngCurrentCell As Range

    For Each rngCurrentCell In ThisWorkbook.Worksheets("Sheet1").Range("D2:F5")
        If rngCurrentCell.Interior.Pattern = xlNone Then
            Set checkBelow = rngCurrentCell
        End If
    Next rngCurrentCell
End Function

And then work on the current cell and its neighbours using e.g. OFFSET. Please note that if you do this, you need to use to use the set-keyword when working on objects (see for example how I use it in the function above).

Community
  • 1
  • 1
eirikdaude
  • 3,106
  • 6
  • 25
  • 50
  • Wow what a kind! I really appreciate your very detail explanation. But I have another question: I use `a` and `b` to take `ch(1)` and `ch(2)`, but in your code `a` and `b` are just used in `If a>1 And b>0`. How `If a>1 And b>0` can take `ch(1)` and `ch(2)`? – Syz Feb 19 '16 at 06:34