Intermittent Error on VBA Code

TimmiT

Board Regular
Joined
Dec 15, 2006
Messages
77
Well, I have a userform for data entry. There are three areas, with various amounts of data entry points : Log, Layer, and Sample. When I go to change the sample or layer number, the respective information is saved to a worksheet, and then the new information is pulled from the worksheet for the new sample or layer that is selected. When I change the log number, it saves and reloads all three groups of data. This allows me to go back and make changes as needed.

I developed this at home, using Excel 2000, and am trying to use it on 2003 at work. It appeared to run correctly under 2000. Sometimes I get the following error when changing the Log number: Runtime error ‘-2147417848(80010108)’: Method ‘Pastespecial’ of object ‘Range’ Failed

I can click the button a few times, and it works, then the error pops up When I debug, the “Application.Run ("lyrinfo")” line is highlighted in the following code, if it doesnt crash Excel:
Code:
 Private Sub CommandButton101_Click()
'Next LOG - Saves existing sample data then adds one to the sample number list.

Application.Run ("snsave")
Application.Run ("lyrsave")
Application.Run ("logsave")

lastlog = Sheets("List").Range("h51").Value + 1 'row number
numlists = 10 * (lastlog - 1) + 10
numlistss = numlists + 8
Sheets("List").Range("I3:J11").Copy
Sheets("List").Range(Cells(numlists, "i"), Cells(numlistss, "j")).PasteSpecial xlPasteValues

nums = 10 * (lastlog + 1) + 10
numss = nums + 8
Sheets("List").Range(Cells(nums, "i"), Cells(numss, "j")).Copy
Sheets("List").Range("I3:J11").PasteSpecial xlPasteValues

If lastlog = 50 Then
MsgBox "There are to many logs for this file.  Please start another file."
Else
Sheets("List").Cells(lastlog + 1, "h") = Sheets("List").Cells(lastlog, "h") + 1
ComboBox109.Value = Sheets("List").Cells(lastlog, "h") + 1
End If
Application.Run ("loginfo")
Application.Run ("lyrinfo")
Application.Run ("sampleinfo")
End Sub

The lyrinfo code pulls the information from the worksheet and places it in the layer section of the userform:
Code:
 Sub lyrinfo()
'LAYER NUMBER - RETRIEVES INFORMATION FROM LOG SHEET
r = UserForm1.ComboBox108.Value + 10 + (UserForm1.ComboBox109.Value - 1) * 37
If UserForm1.ComboBox108.Value = 1 Then
UserForm1.From.Value = Sheets("WORKSHEET").Cells(r, 2)
Else
UserForm1.From.Value = Sheets("WORKSHEET").Cells(r - 1, 3)
End If
UserForm1.TextBox200.Value = Sheets("WORKSHEET").Cells(r, 3)
UserForm1.ComboBox100.Value = Sheets("WORKSHEET").Cells(r, 4)
UserForm1.TextBox300.Value = Sheets("WORKSHEET").Cells(r, 6)
End Sub

ComboBox108 is the layer number. The first row is on row 11.
ComboBox109 is the log number. Each log sheet is 37 rows long.

Any insight on my issues? Your help would be greatly appreciated as I just found out the boss wants to implement this in a day or so. (No real testing to see if my humble programming skills did the job – but go figure)
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

TimmiT

Board Regular
Joined
Dec 15, 2006
Messages
77
A couple of times now, after hitting the debug button, it then gives me the same error numbers, but says " The object invoked has disconected from its clients". (this is an error that occurs when debugging the one above.
 

TimmiT

Board Regular
Joined
Dec 15, 2006
Messages
77
If the code always appears to work the first time that it is run, and if the errors or the unexpected behaviors occur only during subsequent calls to the same code, an unqualified method call is the cause.
This problem occurs because the code creates a new instance of the Office application each time that it runs, but the unqualified code caused VB to set a hidden reference to the old instance. Therefore, subsequent calls on the hidden variable (for an unqualified method) are made to the wrong server.

OK. This appears to be right on. Thank you.

However, I am not sure how I would need to qualify what I am running. I assume I would need to qualify the macros that I am calling. These are all within a module of the Excel workbook I am using. Could you provide some insight on how I would do this? Can this be done in a single place, as I have several macros that run similarly to this?

Thank you.
 

TimmiT

Board Regular
Joined
Dec 15, 2006
Messages
77
Any help on this? I have looked over the linked information and also the VBA help file, but these seem to lean to outside applications and not macros within the application running. How would I qualify these?
 

Quadlet

New Member
Joined
Jul 10, 2007
Messages
10
You'll have to debug this the hard way... comment out code until you find the culprit. I recently battled this error for 2 days, just to find the most unsuspected line causing the error. Eliminate all references to objects, and use variables to hold references to all the objects you use. Then, make sure to set the references to Nothing before ending the sub.
 

TimmiT

Board Regular
Joined
Dec 15, 2006
Messages
77
I can appreciate that, and that is what I have started to do.

I am pretty inexperienced at VB programing though, and I dont understand hoe to Dim the macros properly.

Do I set it up as:

Dim Thismacro As Excel.Application
Thismacto.run
thismacro=nothing

???

Edit: I am inexperienced, not experienced :)
 

TimmiT

Board Regular
Joined
Dec 15, 2006
Messages
77
OK, I think I have most of this worked out:
Code:
Private Sub CommandButton101_Click()
'Next LOG - Saves existing sample data then adds one to the sample number list.
SNS = "Samples.snsave"
LYS = "Layers.lyrsave"
LGS = "Logs.logsave"

Application.Run SNS
Application.Run LYS
Application.Run LGS

Set SNS = Nothing
Set LYS = Nothing
Set LGS = Nothing

lastlog = Sheets("List").Range("h51").Value + 1 'row number

If lastlog = 50 Then
    MsgBox "There are to many logs for this file.  Please start another file."
Else
    Sheets("List").Cells(lastlog + 1, "h") = Sheets("List").Cells(lastlog, "h") + 1
    ComboBox109.Value = Sheets("List").Cells(lastlog, "h") + 1
End If

End Sub

But, when ComboBox109 changes, it triggers this:
Code:
Sub loginfo()
'LOG NUMBER - RETRIEVES INFORMATION AFTER CHANGING
r = 4 + (UserForm1.ComboBox109.Value - 1) * 37
UserForm1.TextBox1.Value = Sheets("WORKSHEET").Cells(5, 35) 'Proj No.
UserForm1.TextBox310.Value = Sheets("WORKSHEET").Cells(5, 36) 'Phase No.
UserForm1.TextBox2.Value = Sheets("WORKSHEET").Cells(4, 13) 'Proj Name
UserForm1.TextBox3.Value = Sheets("WORKSHEET").Cells(4, 22) 'Date
UserForm1.TextBox4.Value = Sheets("WORKSHEET").Cells(r + 1, 27) 'Hole No.
UserForm1.TextBox5.Value = Sheets("WORKSHEET").Cells(r + 1, 28) 'Block
UserForm1.TextBox6.Value = Sheets("WORKSHEET").Cells(r + 1, 29) 'Filing
UserForm1.TextBox10.Value = Sheets("WORKSHEET").Cells(5, 13) 'Engineer
UserForm1.TextBox9.Value = Sheets("WORKSHEET").Cells(r + 2, 4) 'Elevation
UserForm1.TextBox7.Value = Sheets("WORKSHEET").Cells(r + 2, 11) 'Lat
UserForm1.TextBox8.Value = Sheets("WORKSHEET").Cells(r + 2, 19) 'Lon
UserForm1.OptionButton9.Value = Not IsEmpty(Sheets("WORKSHEET").Cells(5, 23)) 'TH-
UserForm1.OptionButton12.Value = Not IsEmpty(Sheets("WORKSHEET").Cells(5, 24)) 'LOT
UserForm1.OptionButton11.Value = Not IsEmpty(Sheets("WORKSHEET").Cells(5, 25)) 'S-
UserForm1.OptionButton10.Value = Not IsEmpty(Sheets("WORKSHEET").Cells(5, 26)) 'P-

UserForm1.ComboBox2.Value = Sheets("WORKSHEET").Cells(35, 21) 'Drill Co.
UserForm1.ComboBox104.Value = Sheets("WORKSHEET").Cells(36, 21) ' Driller
UserForm1.ComboBox3.Value = Sheets("WORKSHEET").Cells(37, 21) 'Drill Rig

UserForm1.TextBox305.Value = Sheets("WORKSHEET").Cells(r + 31, 6) 'Bedrock
UserForm1.TextBox306.Value = Sheets("WORKSHEET").Cells(r + 32, 6) 'GW
UserForm1.TextBox307.Value = Sheets("WORKSHEET").Cells(r + 31, 15) 'Caving
UserForm1.TextBox309.Value = Sheets("WORKSHEET").Cells(r + 32, 15) 'Refusal

UserForm1.TextBox311.Value = Sheets("PROJECT").Range("a3") 'Client

lastlog = UserForm1.ComboBox109.Value 'Log number

nums = 10 * (lastlog) + 10
numss = nums + 8
Sheets("List").Range(Sheets("List").Cells(nums, "i"), Sheets("List").Cells(numss, "j")).Copy
Sheets("List").Range("I3:J11").PasteSpecial xlPasteValues

lastlayer = Sheets("List").Range("i12").Value + 1
lastsample = Sheets("List").Range("j12").Value + 1
UserForm1.ComboBox106.Value = Sheets("List").Cells(lastsample, "j")
UserForm1.ComboBox108.Value = Sheets("List").Cells(lastlayer, "i")

End Sub

When I include the nums and numss copy and paste lines it tells me there is not enough memory. I have the sheet set up to save 2 lists for combo boxes to a designated place , based on the log number, and this is designed to retrieve it.

Why would there be no memory in this copying and pasting of 16 cells? Thee is no issue in copying and pasting the other direction. What am I missing? Is there something I need to clear?

Edit. The code works with the lines remed , and debugs to the paste line.
 

Forum statistics

Threads
1,181,728
Messages
5,931,696
Members
436,798
Latest member
spprtpplcm

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