Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 27 of 27

Thread: VBA Error Message Run-Time Error 1004

  1. #21
    New Member
    Join Date
    Feb 2012
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Error Message Run-Time Error 1004

    thank you. i will do that cos i did mean that.

    But still get the run-time error 28 Out of Stack Space on the section

    Private Sub Next_Number_Click()

    Sheet1.Activate

    If Site = "UKCH" Then
    Sheet2.Activate
    ElseIf Site = "NLEI" Then
    Sheet13.Activate
    ElseIf Site = "USHW" Then
    Sheet10.Activate
    ElseIf Site = "SGSG" Then
    Sheet11.Activate
    End If

    Range("B1").End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Offset(0, -1).Select
    Next_Number = ActiveCell

    End Sub

    the userform opens and i can get the first combobox fine i enter a figure in there then i click the Next_Number button and get the run-time 28 error so it must be this section that affects it. when i step it i get the bold section fails

    Ps. thanks guys for the help so far you have been great.

  2. #22
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Error Message Run-Time Error 1004

    I am struggling to see why that would error - does it still error the same if you alter this bit of code:

    Code:
    Range("B1").End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Offset(0, -1).Select
    Next_Number = ActiveCell
    to this:

    Code:
    Next_Number = Activesheet.Range("B1").End(xlDown).Row +1
    Note, I have assumed that you wanted the row of the cell and not its contents again. if I am incorrect here, then you need this line instead:

    Code:
    Next_Number = Activesheet.Range("B1").End(xlDown).Offset(1,-1).Value

  3. #23
    New Member
    Join Date
    Feb 2012
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Error Message Run-Time Error 1004

    ok what i am doing in this section of the code is finding the next empty cell in B and then moving left one to A and reading the number in there and showing it in the text box.

    This finds the last cell in B
    Range("B1").End(xlDown).Select

    Then this would move it down one cell in B onto the empty cell.
    ActiveCell.Offset(1, 0).Select

    Then this would move it to the left one to cell A
    ActiveCell.Offset(0, -1).Select

    This outputs it to textbox Next_Number
    Next_Number = ActiveCell

    so i will try you code

    Next_Number = Activesheet.Range("B1").End(xlDown).Offset(1,-1).Value

    and see if that works cos it looks like it would.

  4. #24
    New Member
    Join Date
    Feb 2012
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Error Message Run-Time Error 1004

    ok the code:

    Next_Number = Activesheet.Range("B1").End(xlDown).Offset(1,-1).Value

    got a new error message:

    Run-time error '-2147417848 (80010108)':

    Method 'Range' of object'_Worksheet' failed

  5. #25
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Error Message Run-Time Error 1004

    Can you replace that code with this:

    Code:
    Next_Number = Activesheet.Range("B1").End(xlDown).Row
    and report back if this errors or what value is returned to Next_Number if not

  6. #26
    New Member
    Join Date
    Feb 2012
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Error Message Run-Time Error 1004

    Sorry Guy i have figured it out it was me again checked out the names and it should have been:


    Range("B1").End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Offset(0, -1).Select
    Numberbox.Value = ActiveCell

    NOT

    Range("B1").End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Offset(0, -1).Select
    Next_Number = ActiveCell

    but i have a part of the code that set the format to (0000) so i get 0004 not 4 and this is not working now the code i have is:

    Private Sub Next_Number_Click()

    Sheet1.Activate

    If Site = "UKCH" Then
    Sheet2.Activate
    ElseIf Site = "NLEI" Then
    Sheet13.Activate
    ElseIf Site = "USHW" Then
    Sheet10.Activate
    ElseIf Site = "SGSG" Then
    Sheet11.Activate
    End If

    Range("B1").End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Offset(0, -1).Select
    Numberbox.Value = ActiveCell

    End Sub

    Private Sub Number_Change()
    Numberbox.Text = Format(Number, "0000")
    End Sub

    I dont know why this would now be affected

  7. #27
    New Member
    Join Date
    Feb 2012
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Error Message Run-Time Error 1004

    Ok i have sorted this thanks to both Firefly2012 and p45cal for all your help.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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