L
Legacy 96851
Guest
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>
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
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)
I have this block of code
<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
Code:
a = .Range("b1", .Cells.SpecialCells(11)).Value
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)
Last edited by a moderator: