Run time error 2147417848 80010108 vba excel

1

18.07.2013, 21:58. Показов 20280. Ответов 102

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

Sub FindDub()
Range("Y:Y").Clear
Application.ScreenUpdating = False
StartCell = ActiveCell.Row
lastcell = Cells(Rows.Count, 3).End(xlUp).Row
Delta = 1
ColDub = 25
For a = 1 To StartCell - 1
  If Cells(a, 3).Value <> "" And Cells(a, ColDub).Value <> 1 Then
    For b = StartCell To lastcell
      If b <> a Then
         If UCase(Cells(a, 3).Value) = UCase(Cells(b, 3).Value) Then
          Range("A" & a & ":" & "Z" & a).Select
          Selection.Cut
          Range("A" & (lastcell + Delta) & ":" & "Z" & (lastcell + Delta)).Select
          ActiveSheet.Paste
          Delta = Delta + 1
          Cells(b, ColDub).Value = 1
         End If
      End If
    Next
  End If
  Next
    LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    For r = LastRow To 1 Step -1
    If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
    Next r
Application.ScreenUpdating = True
End Sub

__________________
Помощь в написании контрольных, курсовых и дипломных работ, диссертаций здесь

0

1. 04-28-2018, 03:20 AM

   
   

   #1

   Run time error -2147417848 (80010108) Method «_range» of object «_worksheet» failed.
   

   Hi All,

   New to this forum and VBA. I am currently making a calculator and it was working fine for sometime but now I am receiving an error «Runtime error -2147417848 (80010108) method _range of object _worksheet failed». Where as the only command that is running is to display a number from a cell into the textbox.
   Major portion of my work is completed and this thing is now causing a heck of a problem for me and I am really sick and tired of dealing with this. Any type of help will be appreciated.

   Regards,
   Aoxolom

   Following is code attached:

   Private Sub TextBox8_Change()
   
   IF KeyAscii > Asc("9") Or KeyAscii < Asc("0") Then
             If KeyAscii = Asc("-") Then
                   If InStr(1, Me.TextBox8.Text, "-") > 0 Or _
                    Me.TextBox8.SelStart > 0 Then KeyAscii = 0
               ElseIf KeyAscii = Asc(".") Then
                    If InStr(1, Me.TextBox8.Text, ".") > 0 Then KeyAscii = 0
               Else
                   KeyAscii = 0
             End If
   End If
       
    If IsNumeric(Application.Sheets("Main Calculator").Range("D19").Value) = False Then '(cause of error)
           TextBox8.Text = "INVALID"
   
   Else
            TextBox8.Value = Application.Sheets("Main Calculator").Range("D19").Value
            TextBox8.Value = Round(TextBox8.Text, 3)
   End If
   
   End Sub

   Last edited by SamT; 04-28-2018 at 06:08 AM.

   ---

2. 04-28-2018, 06:13 AM

   
   

   #2

   «Application.Sheets» refers to the Active workbook. Is the correct workbook active? Can you replace «Application.Sheets» with «Workbooks(BookName).Sheets(….»

   I expect the student to do their homework and find all the errrors I leeve in.

   ---

3. 04-28-2018, 12:04 PM

   
   

   #3

   Thank you for the reply SamT.
   I changed the value for it but the error still continues. The thing is that the program runs for the 1st time in some cases but after resetting it the error pops up again.

   ---

4. 04-28-2018, 12:55 PM

   
   

   #4

   Where is KeyAscii coming from? It doesn’t seem to be a VBA key word.

   Also the Change Event occurs aftweer the KeyUp eent, so it’s too late to capture the pressed Key’s value.

   Try something like this

   Private PreviousTextBox8Value As String
   
   Private Sub TextBox8_Change()
   Dim LastChar As String
   
   With Me
     LastChar = Right(.TextBox8, 1)
     If Not IsNumeric(LastChar) Then
             If Asc(LastChar) = Asc("-") Then
                   If InStr(1, .TextBox8.Text, "-") > 0 Or _
                    .TextBox8.SelStart > 0 Then TextBox8 = PreviousTextBox8Value
               ElseIf LastChar = "." Then
                    If InStr(1, .TextBox8.Text, ".") > 0 Then .TextBox8 = PreviousTextBox8Value
               Else
                   .TextBox8 = PreviousTextBox8Value
             End If
     Else: PreviousTextBox8Value = .TextBox8
     End If
   End With
   '
   'More code
   End Sub

   Just be sure you reset PreviousTextBox8Value to «» When done

   I expect the student to do their homework and find all the errrors I leeve in.

   ---

5. 04-28-2018, 11:34 PM

   
   

   #5

   Again thanks for the reply Sam.

   I changed the declaration of textbox8 from «change» to «afterupdate» and the code seems to run flawless now. Thanks for all the help and will bother you again if the issue comes up.

   Regards,
   aoxolom

   ---

6. 06-27-2018, 11:22 PM

   
   

   #6

   Newbie whose also getting the error and not sure why
   

   Hi Guys

   I also need help with a Run-time error ‘-2147352571(80020005)’: Type Mismatch

   I can’t figure out how to fix this issue. My Script is as follows :

   Private Sub CommandButton1_Click()
   
   
   Dim con As Variant
   Dim cmd As Variant
   Dim StoredProc As String
   Dim WSP1 As Worksheet
   Set con = CreateObject("ADODB.Connection")
   Set cmd = CreateObject("ADODB.Command")
   
   
   Set parm = cmd.CreateParameter("(atsymbol)ChosenBDM", adVarChar, , 4, TextBox1.Value())
   Set parm2 = cmd.CreateParameter("(atsymbol)NewBDM", adVarChar, , 4, TextBox2.Value())
   
   
   Application.DisplayStatusBar = True
   Application.StatusBar = "Contacting SQL Server..."
   
   
       ServerName = "----" ' Enter your server name here
       DatabaseName = "----" ' Enter your database name here
       UserID = "--" ' Enter your user ID here
       Password = "----" ' Enter your password here
       StoredProcedure = "[New_BDM_Creation]" ' Enter Stored Procedure here
   
   
   ' Log into our SQL Server, and run the Stored Procedure
   con.Open "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=" & DatabaseName & ";User ID=" & UserID & ";Password=" & Password & ";Trusted_Connection=no"
   cmd.ActiveConnection = con
   
   
   Application.StatusBar = "Running stored procedure..."
   
   
       cmd.CommandTimeout = 900
       Set cmd = cmd.Execute(parm, parm2, StoredProcedure) ------> Error appears here
   
   
   Set cmd = Nothing
   con.Close
   Set con = Nothing
   
   
   Application.StatusBar = "Data successfully updated."
   
   
   End Sub

   ---

7. 06-28-2018, 06:33 AM

   
   

   #7

   It’s more effective to start a new topic instead of just tagging on to a previous one, even if you think that they’re somewhat related

   ———————————————————————————————————————

   Paul

   Remember: Tell us WHAT you want to do, not HOW you think you want to do it

   1. Use [CODE] ….[/CODE ] Tags for readability
   [CODE]PasteYourCodeHere[/CODE ] — (or paste your code, select it, click [#] button)
   2. Upload an example
   Go Advanced / Attachments — Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
   3. Mark the thread as [Solved] when you have an answer
   Thread Tools (on the top right corner, above the first message)
   4. Read the Forum FAQ, especially the part about cross-posting in other forums
   http://www.vbaexpress.com/forum/faq…._new_faq_item3

   ---

1. 07-01-2016, 05:26 AM

   
   

   #1

   

   
   Registered User
   

   

   ---

   RunTime Error 2147417848 -Method insert of object Range Failed
   

   Hi,
   When running below Code in my excel sheet , Error message >> Run time error 214741848 (80010108) >> Method «Insert» of object «Range» failed.
   Please help

   Last edited by JBeaucaire; 07-11-2016 at 04:52 PM.

   Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.

   ---

2. 07-01-2016, 05:59 AM

   
   

   #2

   Re: RunTime Error 2147417848 -Method insert of object Range Failed
   

   Trevor Shuttleworth — Excel Aid

   I dream of a better world where chickens can cross the road without having their motives questioned

   ‘Being unapologetic means never having to say you’re sorry’ John Cooper Clarke

   

   ---

3. 07-01-2016, 06:01 AM

   
   

   #3

   Re: RunTime Error 2147417848 -Method insert of object Range Failed
   

   Is the sheet protected? You’d get an Insert error for that scenario, though not necessarily the one you quote.

   ---

4. 07-01-2016, 07:34 AM

   
   

   #4

   

   
   Registered User
   

   

   ---

   Re: RunTime Error 2147417848 -Method insert of object Range Failed
   

   Hi TMS,
   No i do not have any lock cell, nor sheet protection. I have tried your new code but still same error message :

   Last edited by JBeaucaire; 07-11-2016 at 04:53 PM.

   ---

5. 07-01-2016, 07:45 AM

   
   

   #5

   Re: RunTime Error 2147417848 -Method insert of object Range Failed
   

   Suggest you post a sample of the workbook (that demonstrates the problem), if you can.

   The code works in a newly created simple workbook, so it’s not the code per se.

   ---

6. 07-01-2016, 08:26 AM

   
   

   #6

   

   
   Registered User
   

   

   ---

   Re: RunTime Error 2147417848 -Method insert of object Range Failed
   

   Hi TMS,
   Thanks for your quick response, i have uploaded a sample of the file(I had to cut the file as it is 39 MB.
   Please note that with this file i had one trial it works and fails again at second attempt. Excel file is blocked. Had to close and reopen file , tried again same error message

   ---

7. 07-01-2016, 09:08 AM

   
   

   #7

   Re: RunTime Error 2147417848 -Method insert of object Range Failed
   

   Works for me. Executed code several times. The only issue that I had is that there are links in the file, presumably to the original. So, when I copy the rows, I get a File Open dialogue box … I just press Escape to continue.

   For example: [Supply Chain Version 29 Dec 2015.xlsm]NLM_LT’!$N$114.. Clearly, I don’t have the relevant workbook OR worksheet.

   ---

8. 07-11-2016, 01:16 AM

   
   

   #8

   

   
   Registered User
   

   

   ---

   Re: RunTime Error 2147417848 -Method insert of object Range Failed
   

   Hi TMS,
   Thanks for your prompt reply and sorry to attend your thread only today as I was ill whole last week. Still problem when working on original file. Same error appears
   The workbook i am working with consist of 20 worksheets which are all linked. Can the links between these sheets be a cause, can the size of the sheet (10 KB) be a problem. As there are formulas in the cells to be copied and insert, can the VBA actions be separated into 3 as follows:
   1 >> Copy and insert Column A to S (as all are values)
   2 >> Copy and paste special column T to XX in the inserted column in action 1 in T to XX
   3 >> Execute formulas in inserted cell after action 2
   Again apologies for late reply. Thanks for your help
   Vassen

   ---

9. 07-11-2016, 08:49 AM

   
   

   #9

   

   
   Registered User
   

   

   ---

   Re: RunTime Error 2147417848 -Method insert of object Range Failed
   

   Hi TMS,
   You were right, it works withe the workbook containing only this worksheet, as I test same with the workbook with 20 worksheets linked, it gives ERROR message, How can this problem be overcome. Thanks for your help
   Vassen

   ---

10. 07-11-2016, 04:54 PM

    
    

    #10

    Re: RunTime Error 2147417848 -Method insert of object Range Failed
    

    Maybe add this near the top of the macro:

    Worth a try…

    _________________
    Microsoft MVP 2010 — Excel
    Visit: Jerry Beaucaire’s Excel Files & Macros

    If you’ve been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE]

    your code here [/CODE]

    ?None of us is as good as all of us? — Ray Kroc
    ?Actually, I *am* a rocket scientist.? — JB (little ones count!)

    ---

11. 07-12-2016, 03:48 AM

    
    

    #11

    

    
    Registered User
    

    

    ---

    Re: RunTime Error 2147417848 -Method insert of object Range Failed
    

    Hi JBeaucaire,
    Can you please precise what you mean by «Near the Top». Is it just before «ActiveCell.EntireRow.Copy. Note that it is not only giving the ERROR message but is also blocking the workbook, I have to close, do not save and re open to be able to work again
    Thanks for your help
    Vassen

    ---

12. 07-13-2016, 07:03 AM

    
    

    #12

    

    
    Registered User
    

    

    ---

    Re: RunTime Error 2147417848 -Method insert of object Range Failed
    

    Hi JBeaucaire,TMS
    I have tried but still same error message and it blocks the sheet,
    Please help
    Vassen

    ---

13. 07-13-2016, 07:34 AM

    
    

    #13

    Re: RunTime Error 2147417848 -Method insert of object Range Failed
    

    Don
    Please remember to mark your thread ‘Solved’ when appropriate.

    ---

14. 07-13-2016, 08:05 AM

    
    

    #14

    Re: RunTime Error 2147417848 -Method insert of object Range Failed
    

    Not sure what else to suggest. The code works, at least, it works in a new workbook/worksheet. So, the implication is that it is something to do with the data in the live file rather than a problem with the code.

    As you cannot insert a row, I’m guessing that Excel sees data on the last row of the sheet. Maybe you have inadvertently copied a formula down to the last row. Or you have formatting or Conditional Formatting or Data Validation.

    If you press Ctrl-End, which cell/row/column is selected? If it is row 1048576 that’s probably the problem. In which case, you need to delete all the rows between the end of the real data and the last row. Select all the rows and use the Delete icon on the Ribbon, not the Delete key. Then save the file.

    ---

15. 07-14-2016, 05:26 AM

    
    

    #15

    

    
    Registered User
    

    

    ---

    Re: RunTime Error 2147417848 -Method insert of object Range Failed
    

    Hi TMS,xlnitwit,
    Thanks for your help.
    Vassen

    ---

16. 07-14-2016, 06:58 AM

    
    

    #16

    Re: RunTime Error 2147417848 -Method insert of object Range Failed
    

    You’re welcome.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

    ---