Help with macro error 1004 (macro searches for blanks)

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>
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
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)
 
Last edited by a moderator:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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?
 
L

Legacy 96851

Guest
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.
Rich (BB code):
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.:eek:
Rich (BB code):
Resize(N, UBound(b, 2)).Value = b
 
L

Legacy 96851

Guest
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.
 
L

Legacy 96851

Guest
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.
 
L

Legacy 96851

Guest
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top