CODEWITHSUNDEEP
vbaexcel
kalenpw
kalenpw

Reputation: 695

Excel VBA check neighboring cell contents

What I'm doing is creating a Minesweeper game in Excel. My issue arises in trying to count the number of mines in a given cell's neighboring cells. I denote a mine in a cell by an "x"

Here is my code:

Sub countMines(ByVal x As Integer, ByVal y As Integer)
    Dim mineCount As Integer

    mineCount = 0    

    If Cells(x, y) = "x" Then
        'Do nothing if cell has mine
        MsgBox "Cell contains x" & x & y
    Else
        If Cells(x -1, y) = "x" Then
            MsgBox "Cell " & x & y & "has an x near it"
        End If


    End If


End Sub

The first message box does work if a cell contains an 'x' it properly alerts me of the cell's position.

However, the second if If Cells(x -1, y) = "x" Then causes a

Run-time error '1004': Application-defined or object-defined error

I can find loads about this error online as it's a very generic message, but none seem to apply to my case.

This is the only time I've used VBA so this is very well probably a very simple mistake and likely a duplicate question, but I couldn't find the answer.

Upvotes: 1

Views: 1320

Answers (3)

Jayant Kumar jain
Jayant Kumar jain

Reputation: 27

'Use following code to count all "x" in all neabouring cells
'except the host cell. But take care that minus operation does not cross excel boundaries
Function countMines(ByVal x As Integer, ByVal y As Integer)
Dim mineCount As Integer

    mineCount = 0
    For Each c In Range(Cells(x - 1, y - 1), Cells(x + 1, y + 1))
    If c.Address <> Cells(x, y).Address Then
    If c = "x" Then
    mineCount = mineCount + 1
    End If
    End If
    Next c

    countMines = mineCount 'return value to calling function
    MsgBox "Total mines in surrounding cells = " & mineCount
End Function

Upvotes: 1

user3598756
user3598756

Reputation: 29421

you've already been given the answer about the error you were struggling with

this answer just to throw in you might consider using WorksheetFunction.CountIf() method to count all "x" occurrences in a range surrounding a given cell, like follows:

mineCount = WorksheetFunction.CountIf(Cells(x, y).Offset(-1, -1).Resize(3, 3), "x")

this would also count the cell itself but if you put it in the If Cells(x, y) = "x" Then False branch then it'd be no harm

of course both offsets and resizing must be tailored not to exceed your field limits. So the above statement could become:

mineCount = WorksheetFunction.CountIf(Cells(x, y).Offset(-xOffset, -yOffset).Resize(xResize, yResize), "x")

where you have to properly adjust xOffset, yOffset, xResize, yResize against actual cell position (x, y) and your field limits (nRows, nColumns) like, for instance

    xOffset = IIf(x = 1, 0, 1)
    yOffset = IIf(y = 1, 0, 1)
    xResize = IIf(x = nRows, 1, 2) + xOffset
    yResize = IIf(y = nColumns, 1, 2) + yOffset

Upvotes: 0

Denny
Denny

Reputation: 744

Just so that this question can be marked as answered, it's as @Comintern said, you have to check to make sure that x and y don't drop to zero (or go beyond Excel's maximum range).

(I upvoted his comment, but since he's not posting it here... ::shrug::)

If you decide to make this a really big game of Minesweeper, check to make sure that you don't go above cell 32,767 either.

As a side note, even though Run-time error '1004' sounds generic, it usually only pops up if you're referring to an object that doesn't exist (or can't be recognized).

If you're "stepping" through the code, and notice that you're getting the 1004 error at the very start of a loop, then there's a 95% that you're starting the loop with a bad index point. You can get the same type of situation with any codeblock, like with the if statement that you have here.

Upvotes: 1

Related Questions