+ Reply to Thread
Results 1 to 8 of 8

Error message 400

  1. #1
    Registered User
    Join Date
    07-25-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    5

    Error message 400

    I'm following the instructions to run the macro on this thread. When I do I get an error dialogue box saying 'Microsoft Visual Basic 400'. I'm very much a novice at macros and VBA. I'm not sure how to fix the problem. Any advice would be greatly appreciated. I'm using Excel 2011 14.4.3.

  2. #2
    Registered User
    Join Date
    07-17-2014
    Location
    Edmonton
    MS-Off Ver
    2010
    Posts
    29

    Re: Error message 400

    Press F8 in your code and step through to find where the problem is.

  3. #3
    Registered User
    Join Date
    07-25-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    5

    Re: Error message 400

    I'm now getting RUN TIME ERROR 1004. Not sure why the macro won't work for me.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    18,974

    Re: Error message 400

    There are two different codes in that thread. Which code are you using? Post it here. Also, when you click on debug, which line of code is highlighted? It would also help if you uploaded your spreadsheet with the code.

  5. #5
    Registered User
    Join Date
    07-25-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    5

    Re: Error message 400

    Hi,
    I'm trying to run this code:

    Sub ShiftValuesLeft()
    Dim iRows As Integer, iCols As Integer, x As Integer, y As Integer, icount As Integer
    Dim MyArray(), rTop As Range
    iRows = 50
    iCols = 10
    Set rTop = Range("Top")
    ReDim MyArray(iRows, iCols)


    For x = 1 To iRows
    For y = 1 To iCols
    If rTop.Cells(x, y) <> "" Then
    MyArray(x, icount) = rTop.Cells(x, y)
    icount = icount + 1
    End If

    Next y
    icount = 0

    Next x

    For x = 1 To iRows
    For y = 1 To iCols
    If MyArray(x, icount) <> "" Then
    Range("Output").Cells(x, y) = MyArray(x, icount)
    icount = icount + 1
    End If
    Next y
    icount = 0
    Next x

    End Sub
    When I debug (using F8) the following lines are highlighted:
    Sub ShiftValuesLeft()
    iRows = Range("Top").CurrentRegion.Rows.Count

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Error message 400

    Please Login or Register  to view this content.
    isn't in the code you said you're trying to run.
    Remember what the dormouse said
    Feed your head

  7. #7
    Registered User
    Join Date
    07-25-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    5

    Re: Error message 400

    Oops, I'm sorry. Got a bit confused with my cut and pastes. This is the code in my macro:

    Sub ShiftValuesLeft()
    Dim iRows As Integer, iCols As Integer, x As Integer, y As Integer, icount As Integer
    Dim MyArray(), rTop As Range
    iRows = Range("Top").CurrentRegion.Rows.Count
    iCols = Range("Top").CurrentRegion.Columns.Count - 1
    Set rTop = Range("Top")
    ReDim MyArray(iRows, iCols)


    For x = 1 To iRows
    For y = 1 To iCols
    If rTop.Cells(x, y) <> "" Then
    MyArray(x, icount) = rTop.Cells(x, y)
    icount = icount + 1
    End If

    Next y
    icount = 0

    Next x

    For x = 1 To iRows
    For y = 1 To iCols
    If MyArray(x, icount) <> "" Then
    Range("Output").Cells(x, y) = MyArray(x, icount)
    icount = icount + 1
    End If
    Next y
    icount = 0
    Next x

    End Sub

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Error message 400

    Is there a range named Top on the active sheet? How large is the table - you really should declare iRows as Long rather than Integer.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. An error message on open - a totally blank VBA message box
    By Mr_Tigas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-05-2013, 11:29 AM
  2. Error Message For Function Procedure WITHOUT using a message box
    By bmr8002 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-08-2012, 02:01 PM
  3. Replacing run time error message with a different message
    By penny in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-14-2009, 10:51 AM
  4. replace VBA run-time error message with custom message
    By BEEJAY in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-14-2006, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1