Error 400

W

wisewood

Board Regular
Joined
Nov 7, 2002
Messages
193
Hi all,

I'm working on some VBA to automate the creation of a report.

Everything is working perfectly on my pc (win xp, excel 2003) but on my colleagues pc (same os & excel versions) it falls down.

Everything seems to work up until somewhere this section of the code, where it throws up an error 400.

Column I is supposed to check array to see if the value is less than 0, if it is, it puts a 0, else it puts a 1. It then sorts & inserts a Match function to find the first row containing a zero in column I; and after that it will use that match() result as a basis to select only the appropriate range to put into another array.

Problem seems to be that the code below doesn't provide any 1's on my colleagues computer.

Code: 
                    If strTempAry(n + 1, 21) < 1 Then
                        Worksheets("Details").Range("I" & n + 1).Value = 0
                    Else
                        Worksheets("Details").Range("I" & n + 1).Value = 1
                    End If


This is the code immediately preceeding.

Code: 
Worksheets("Details").Activate
    ' Loop through the array
    n = 0
    While n < UBound(strTempAry)
       
        If Left(strTempAry(n + 1, 1), 1) = "Z" Or Left(strTempAry(n + 1, 7), 3) <> 210 Then
                ' Skip it
                n = n + 1
            Else
                If strTempAry(n + 1, 9) = Null Then
                    ' Skip it
                    n = n + 1
                Else
                    Worksheets("Details").Range("A" & n + 1).Select
                    Worksheets("Details").Range("A" & n + 1).Value = strTempAry(n + 1, 21)
                    Worksheets("Details").Range("B" & n + 1).Value = strTempAry(n + 1, 1)
                    Worksheets("Details").Range("C" & n + 1).Value = strTempAry(n + 1, 2)
                    Worksheets("Details").Range("D" & n + 1).Value = strTempAry(n + 1, 7)
                    Worksheets("Details").Range("E" & n + 1).Value = strTempAry(n + 1, 8)
                    Worksheets("Details").Range("F" & n + 1).Value = strTempAry(n + 1, 9)
                    Worksheets("Details").Range("G" & n + 1).Value = strTempAry(n + 1, 11)
                    Worksheets("Details").Range("H" & n + 1).Value = strTempAry(n + 1, 13)
                    If strTempAry(n + 1, 21) < 1 Then
                        Worksheets("Details").Range("I" & n + 1).Value = 0
                    Else
                        Worksheets("Details").Range("I" & n + 1).Value = 1
                    End If
                    n = n + 1
                End If
        End If
        
    Wend
            
    xVal = Sheets("Details").Cells(Rows.Count, 1).End(xlUp).Row
    
    Worksheets("Details").Range("A1:I" & xVal).Select
    Selection.Sort Key1:=Worksheets("Details").Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        
    Worksheets("Details").Range("K1").Value = "=match(0,I:I,0)-1"


Can anyone see anything obviously wrong here that would cause a problem??
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
I imagine strTempAry is a UDF? Could that be what is causing your code to error? When you get the Error 400, are you given an option to debug and see exactly what line is eroring for you?
 
Upvote 0
Sorry, UDF?

No option to debug is given. Just OK or Help. "Help" is a very frustrating button as, thus far, it doesn't "help" at all.

Full code for the whole thing here;
"Current" worksheet contains A1:T4000+ to which i add an extra calculation in column U in the macro.
"Details" should result (and does, on my pc) with only the required data - for the specific data i am using its 132 rows.

Code: 
Sub AutomateReport()
    ' Declare the variables to be used
    Dim strTempAry As Variant, n As Integer, xVal As Integer, FinalArray As Variant
    
    Worksheets("Current").Activate
    Worksheets("Current").Range("U1").Select
    ' count the rows in the Baan Download data
    xVal = Sheets("Current").Cells(Rows.Count, 1).End(xlUp).Row
    
    Worksheets("Current").Range("U1").Value = "age"
    Worksheets("Current").Range("U2").Value = "=today()-I2"
    Worksheets("Current").Range("U2").Select
    Selection.Copy
    Worksheets("Current").Range("U2:U" & xVal).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Worksheets("Current").Columns("U:U").Select
    Selection.Style = "Comma"
    Worksheets("Current").Range("U1").Select
    
    ' load all the Baan Download data into a 2D array
    strTempAry = Worksheets("Current").Range("A2:U" & xVal)
    
    Worksheets("Details").Activate
    ' Loop through the array
    n = 0
    While n < UBound(strTempAry)
       
        If Left(strTempAry(n + 1, 1), 1) = "Z" Or Left(strTempAry(n + 1, 7), 3) <> 210 Then
                ' Skip it
                n = n + 1
            Else
                If strTempAry(n + 1, 9) = Null Then
                    ' Skip it
                    n = n + 1
                Else
                    Worksheets("Details").Range("A" & n + 1).Select
                    Worksheets("Details").Range("A" & n + 1).Value = strTempAry(n + 1, 21)
                    Worksheets("Details").Range("B" & n + 1).Value = strTempAry(n + 1, 1)
                    Worksheets("Details").Range("C" & n + 1).Value = strTempAry(n + 1, 2)
                    Worksheets("Details").Range("D" & n + 1).Value = strTempAry(n + 1, 7)
                    Worksheets("Details").Range("E" & n + 1).Value = strTempAry(n + 1, 8)
                    Worksheets("Details").Range("F" & n + 1).Value = strTempAry(n + 1, 9)
                    Worksheets("Details").Range("G" & n + 1).Value = strTempAry(n + 1, 11)
                    Worksheets("Details").Range("H" & n + 1).Value = strTempAry(n + 1, 13)
                    If strTempAry(n + 1, 21) < 1 Then
                        Worksheets("Details").Range("I" & n + 1).Value = 0
                    Else
                        Worksheets("Details").Range("I" & n + 1).Value = 1
                    End If
                    n = n + 1
                End If
        End If
        
    Wend
            
    xVal = Sheets("Details").Cells(Rows.Count, 1).End(xlUp).Row
    
    Worksheets("Details").Range("A1:I" & xVal).Select
    Selection.Sort Key1:=Worksheets("Details").Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        
    Worksheets("Details").Range("K1").Value = "=match(0,I:I,0)-1"
 
End

    ' load all the data into a 2D array
    FinalArray = Worksheets("Details").Range("A1:I" & Worksheets("Details").Range("K1").Value)
    
    Worksheets("Details").Cells.Clear
    
    n = 0
    While n < UBound(FinalArray)

            Worksheets("Details").Range("B" & n + 1).Select
            Worksheets("Details").Range("B" & n + 1).Value = FinalArray(n + 1, 1)
            Worksheets("Details").Range("E" & n + 1).Value = FinalArray(n + 1, 2)
            Worksheets("Details").Range("F" & n + 1).Value = FinalArray(n + 1, 3)
            Worksheets("Details").Range("G" & n + 1).Value = FinalArray(n + 1, 4)
            Worksheets("Details").Range("H" & n + 1).Value = FinalArray(n + 1, 5)
            Worksheets("Details").Range("I" & n + 1).Value = FinalArray(n + 1, 6)
            Worksheets("Details").Range("J" & n + 1).Value = FinalArray(n + 1, 7)
            Worksheets("Details").Range("K" & n + 1).Value = FinalArray(n + 1, 8)
            n = n + 1
    Wend
    
    xVal = Sheets("Details").Cells(Rows.Count, 2).End(xlUp).Row
    Worksheets("Details").Range("A1").Select
    MsgBox xVal & " Overdue Invoices"
    
End Sub
 
Upvote 0
There is a lot of unnecessary select statements in there, try out this adjusted code:

Code: 
Sub AutomateReport()
    ' Declare the variables to be used
    Dim strTempAry As Variant, n As Integer, xVal As Integer, FinalArray As Variant
    Application.ScreenUpdating = False
    
    ' count the rows in the Baan Download data
    xVal = Sheets("Current").Cells(rows.Count, 1).End(xlUp).row
    
    Worksheets("Current").Range("U1").Value = "age"
    Worksheets("Current").Range("U2").Formula = "=today()-I2"
    Worksheets("Current").Range("U2").Copy
    With Worksheets("Current").Range("U2:U" & xVal)
        .PasteSpecial Paste:=xlPasteFormulas
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
    Worksheets("Current").Columns("U:U").Style = "Comma"
    
    ' load all the Baan Download data into a 2D array
    strTempAry = Worksheets("Current").Range("A2:U" & xVal)
    
    Worksheets("Details").Activate
    ' Loop through the array
    'n = 0
    For n = 0 To UBound(strTempAry)
        If left$(strTempAry(n + 1, 1), 1) = "Z" Or left$(strTempAry(n + 1, 7), 3) <> "210" Or strTempAry(n + 1, 9) = "" Then
            ' Skip It
        Else
            Worksheets("Details").Range("A" & n + 1).Value = strTempAry(n + 1, 21)
            Worksheets("Details").Range("B" & n + 1).Value = strTempAry(n + 1, 1)
            Worksheets("Details").Range("C" & n + 1).Value = strTempAry(n + 1, 2)
            Worksheets("Details").Range("D" & n + 1).Value = strTempAry(n + 1, 7)
            Worksheets("Details").Range("E" & n + 1).Value = strTempAry(n + 1, 8)
            Worksheets("Details").Range("F" & n + 1).Value = strTempAry(n + 1, 9)
            Worksheets("Details").Range("G" & n + 1).Value = strTempAry(n + 1, 11)
            Worksheets("Details").Range("H" & n + 1).Value = strTempAry(n + 1, 13)
            If strTempAry(n + 1, 21) < 1 Then
                Worksheets("Details").Range("I" & n + 1).Value = 0
            Else
                Worksheets("Details").Range("I" & n + 1).Value = 1
            End If
        End If
    Next n
            
    xVal = Sheets("Details").Cells(rows.Count, 1).End(xlUp).row
    
    Worksheets("Details").Range("A1:I" & xVal).Sort Key1:=Worksheets("Details").Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Worksheets("Details").Range("K1").Formula = "=MATCH(0,I:I,0)-1"
    
    ' load all the data into a 2D array
    FinalArray = Worksheets("Details").Range("A1:I" & Worksheets("Details").Range("K1").Value)
    Worksheets("Details").Cells.ClearContents
    For n = 0 To UBound(FinalArray)
        Worksheets("Details").Range("B" & n + 1).Value = FinalArray(n + 1, 1)
        Worksheets("Details").Range("E" & n + 1).Value = FinalArray(n + 1, 2)
        Worksheets("Details").Range("F" & n + 1).Value = FinalArray(n + 1, 3)
        Worksheets("Details").Range("G" & n + 1).Value = FinalArray(n + 1, 4)
        Worksheets("Details").Range("H" & n + 1).Value = FinalArray(n + 1, 5)
        Worksheets("Details").Range("I" & n + 1).Value = FinalArray(n + 1, 6)
        Worksheets("Details").Range("J" & n + 1).Value = FinalArray(n + 1, 7)
        Worksheets("Details").Range("K" & n + 1).Value = FinalArray(n + 1, 8)
    Next n
    
    xVal = Sheets("Details").Cells(rows.Count, 2).End(xlUp).row
    Application.Goto Worksheets("Details").Range("A1")
    MsgBox xVal & " Overdue Invoices"
    
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
I use the selects & allow screen updating to help me figure out at which point the script is falling over lol.

Subscript Error 9 in the code you gave me there.
 
Upvote 0
Ahh, I think I know what is happening. In the two loops, it is trying to refer to an item in the array that doesn't exist. When n gets to the UBound of the array, the loop is trying to reference to n+1.

Also, you can manually step through the code by going to the VBA editor, choosing your macro, and going to Debug>Step Into (F8) to find out exactly what line is erroring, and under what conditions.

Lets try running the code by having n go from 0 to the UBound of the array - 1:

Code: 
Sub AutomateReport()
    ' Declare the variables to be used
    Dim strTempAry As Variant, n As Integer, xVal As Integer, FinalArray As Variant
    'Application.ScreenUpdating = False
 
    ' count the rows in the Baan Download data
    xVal = Sheets("Current").Cells(rows.Count, 1).End(xlUp).row
 
    Worksheets("Current").Range("U1").Value = "age"
    Worksheets("Current").Range("U2").Formula = "=today()-I2"
    Worksheets("Current").Range("U2").Copy
    With Worksheets("Current").Range("U2:U" & xVal)
        .PasteSpecial Paste:=xlPasteFormulas
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
    Worksheets("Current").Columns("U:U").Style = "Comma"
 
    ' load all the Baan Download data into a 2D array
    strTempAry = Worksheets("Current").Range("A2:U" & xVal)
 
    Worksheets("Details").Activate
    ' Loop through the array
    'n = 0
    For n = 0 To UBound(strTempAry) - 1
        If left$(strTempAry(n + 1, 1), 1) = "Z" Or left$(strTempAry(n + 1, 7), 3) <> "210" Or strTempAry(n + 1, 9) = "" Then
            ' Skip It
        Else
            Worksheets("Details").Range("A" & n + 1).Value = strTempAry(n + 1, 21)
            Worksheets("Details").Range("B" & n + 1).Value = strTempAry(n + 1, 1)
            Worksheets("Details").Range("C" & n + 1).Value = strTempAry(n + 1, 2)
            Worksheets("Details").Range("D" & n + 1).Value = strTempAry(n + 1, 7)
            Worksheets("Details").Range("E" & n + 1).Value = strTempAry(n + 1, 8)
            Worksheets("Details").Range("F" & n + 1).Value = strTempAry(n + 1, 9)
            Worksheets("Details").Range("G" & n + 1).Value = strTempAry(n + 1, 11)
            Worksheets("Details").Range("H" & n + 1).Value = strTempAry(n + 1, 13)
            If strTempAry(n + 1, 21) < 1 Then
                Worksheets("Details").Range("I" & n + 1).Value = 0
            Else
                Worksheets("Details").Range("I" & n + 1).Value = 1
            End If
        End If
    Next n
 
    xVal = Sheets("Details").Cells(rows.Count, 1).End(xlUp).row
 
    Worksheets("Details").Range("A1:I" & xVal).Sort Key1:=Worksheets("Details").Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Worksheets("Details").Range("K1").Formula = "=MATCH(0,I:I,0)-1"
 
    ' load all the data into a 2D array
    FinalArray = Worksheets("Details").Range("A1:I" & Worksheets("Details").Range("K1").Value)
    Worksheets("Details").Cells.ClearContents
    For n = 0 To UBound(FinalArray) - 1
        Worksheets("Details").Range("B" & n + 1).Value = FinalArray(n + 1, 1)
        Worksheets("Details").Range("E" & n + 1).Value = FinalArray(n + 1, 2)
        Worksheets("Details").Range("F" & n + 1).Value = FinalArray(n + 1, 3)
        Worksheets("Details").Range("G" & n + 1).Value = FinalArray(n + 1, 4)
        Worksheets("Details").Range("H" & n + 1).Value = FinalArray(n + 1, 5)
        Worksheets("Details").Range("I" & n + 1).Value = FinalArray(n + 1, 6)
        Worksheets("Details").Range("J" & n + 1).Value = FinalArray(n + 1, 7)
        Worksheets("Details").Range("K" & n + 1).Value = FinalArray(n + 1, 8)
    Next n
 
    xVal = Sheets("Details").Cells(rows.Count, 2).End(xlUp).row
    Application.Goto Worksheets("Details").Range("A1")
    MsgBox xVal & " Overdue Invoices"
 
    'Application.ScreenUpdating = True
End Sub
 
Upvote 0
I was just about to say it was working (on my pc); but then on a second attempt i got an error 400 right at the end, the script had finished, but i got error 400 instead of the MsgBox.
 
Upvote 0
Try stepping through the code manually with F8 and see where it is erroring. If it errors inside of the loops, what is the value of n at the time of the error (and what is the UBound of the array it is using)?
 
Upvote 0
Hi,

I've stripped back the source data so its only running 20 rows of data so I can use F8 to find the problem. It seems that its the part where i calculate today() minus a date in a cell that is causing a problem. This looked like the problem on my colleagues computer also, but until now this has always worked for my pc.

The result of the formula is correct (it ended up in column A) but then when the If statement is checking whether that value is < 1 or not, its saying they're all less than 1 which means column I ends up with 0 in each cell - which destroys the chance of only the relevant data being picked up into the next array.

Argh!!

This is driving me mad.
 
Upvote 0
I'm going back to the drawing board to see if i can come up with a better solution starting from scratch. Thanks for the help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
134
montecarlo2012
montecarlo2012
H
  • Solved
Reduce size of (or speed up) excel spreadsheet
Replies
5
Views
163
helpcharity
H
S
  • Solved
Copy Textbox values from UserForm to the worksheet
Replies
6
Views
307
AlphaFrog
AlphaFrog
G
  • Question Question
Why does #VALUE error in VBA function occur when creating a ReDim Matrix of Stock prices from a Named Range?
Replies
2
Views
96
Peter_SSs
Peter_SSs
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
247
Pingaware
P

Forum statistics

Threads
1,224,567
Messages
6,179,571
Members
452,927
Latest member
whitfieldcraig

Share this page

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
Back
Top