Help with macro error 1004 (macro searches for blanks)

[Legacy 96851]

I've been working on this spreadsheet all week now, just because it's my current "if you have some free time, do this" project.

I have this block of code
<CODE>
</CODE>

Sub ListBlanks()
Dim a, i As Long, ii As Long, b(), N As Long
With Sheets("ProjectData")
    a = .Range("a1", .Cells.SpecialCells(11)).Value
End With
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 2 To UBound(a, 1)
        If Not IsEmpty(a(i, 1)) Then
            If Not .exists(a(i, 1)) Then
                N = N + 1: b(N, 1) = a(i, 1): .Add a(i, 1), N
            End If
            For ii = 2 To UBound(a, 2)
                If a(i, ii) = "" Then
                    b(.Item(a(i, 1)), 4) = b(.Item(a(i, 1)), 4) & _
                        IIf(b(.Item(a(i, 1)), 4) = "", "", ",") & a(1, ii)
                End If
            Next
        End If
    Next
End With
With Sheets("BlankByRow").Range("a2")
    .CurrentRegion.Clear
    .Resize(N, UBound(b, 2)).Value = b
End With
End Sub

As is, it runs fine. However, I want to start on column B, not column A, so it seems obvious to change the fourth line to

    a = .Range("b1", .Cells.SpecialCells(11)).Value

However, when I do this, the macro works fine for the first 7 rows of the sheet, then at cell (8,D) (which is not blank) gives a runtime error (1004) and I really can't figure out why.

Anyone have any ideas?
(oh yeah, and I know that code's pretty sloppy. But the error seems to have nothing to do with that)

 

[Richard Schollar]

Hi

So which exact line with which exact code (post it all again, and highlight in eg Red the problem line) do you get the error?

Code looks like something Jindon would put together - did he?

 

[Legacy 96851]

It is indeed at least based on a Jindon post. Of course his code worked fine, but it turned out it wasn't quite what I needed, so I had to edit it some.

Sub ListBlanks()
Dim a, i As Long, ii As Long, b(), N As Long
With Sheets("ProjectData")
    a = .Range("b1", .Cells.SpecialCells(11)).Value
End With
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 2 To UBound(a, 1)
        If Not IsEmpty(a(i, 1)) Then
            If Not .exists(a(i, 1)) Then
                N = N + 1: b(N, 1) = a(i, 1): .Add a(i, 1), N
                b(N, 2) = a(i, 2)
                b(N, 3) = a(i, 3)
            End If
            For ii = 4 To UBound(a, 2)
                If a(i, ii) = "" Then
                    b(.Item(a(i, 1)), 4) = b(.Item(a(i, 1)), 4) & _
                        IIf(b(.Item(a(i, 1)), 4) = "", "", ",") & a(1, ii)
                End If
            Next
        End If
    Next
End With
With Sheets("ValMacro").Range("a2")
    .CurrentRegion.Clear
    .Resize(N, UBound(b, 2)).Value = b
End With
End Sub

I just tried it on a different spreadsheet, and it again stopped at a seemingly random cell.

 

[Richard Schollar]

What are the values of N and UBound(b,2) at this point? You can find out by hovering the mouse pointer over the variables in debug mode in the VBE.

 

[Norie]

What is the code actually meant to do?

And why did you say the error ocurred at (8,D) (D8 I assume) then say it occurs on this line.

Resize(N, UBound(b, 2)).Value = b

 

[Legacy 96851]

N = 100, Ubound(b, 2) = 29

D8 is where the worksheet ValMacro ends (i.e. it looks right up til then, then is blank afterwards).

The code goes through the "ProjectData" sheet and finds all the blanks, then fills in ValMacro as follows: columns A-C on ValMacro are the same as columns B-D on ProjectData, and column D on ValMacro lists the names of the columns in which there is a blank in the corresponding row of ProjectData.

I had it working by forumlas, but my boss told me they were going to be adding rows to ProjectData and he'd prefer I use a macro to keep it updated.

 

[Legacy 96851]

Furthermore, checking the variable watch, variant b(every index, 4) has the correct values.

Sorry for making this such a problem (I'm sure it should be simple), this is my first time even seeing Visual Basic, so editing the code at all was the best I could do.

 

[Legacy 96851]

Thanks anyway, I've rewritten the code from null, getting rid of what had changed since Jindon originally supplied it. It now works just fine, most likely N was doing something stupid (and was unnecessary anyway). For anyone who's curious, that's

Sub ValList()
Dim a, i As Long, ii As Long, b(), N As Long
With Sheets("ProjectData")
    a = .Range("b1", .Cells.SpecialCells(11)).Value
End With
ReDim b(1 To UBound(a, 1), 1 To 4)
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 2 To UBound(a, 1)
        If Not IsEmpty(a(i, 1)) Then
            b(i, 1) = a(i, 1)
            b(i, 2) = a(i, 2)
            b(i, 3) = a(i, 3)
            For ii = 4 To UBound(a, 2)
                If a(i, ii) = "" Then
                    b(i, 4) = b(i, 4) & IIf(b(i, 4) = "", "", ", ") & a(1, ii)
                End If
            Next
        End If
    Next
End With
With Sheets("ValList").Range("a1")
    .CurrentRegion.Clear
    .Resize(UBound(a, 1), 4).Value = b
End With
End Sub