Run time error 1004 - Application defined or object defined error

dss28

Board Regular
Joined
Sep 3, 2020
Messages
165
Office Version
  1. 2007
Platform
  1. Windows
I have a form in which I have 15 rows for data input and each row has a series of boxes and comboboxes.

Each row has a separate command button to save the data to sheet.

The basic code is same except each line command button has reference to the respective textboxes/comboxes in that row.
VBA Code:

I had no issues with the code for almost 5-6 months but now suddenly the code is giving "Run time error 1004 - Application defined or object defined error" and stopping intermittently on any row. sometimes it goes through but then it is stuck on the first line iteself for last couple of days and can not go further.

for internet i tried to gather information about this error and I tried to use :

1. "Thisworkbook.sheets("SheetName").activate code as there are two sheets involved in the code transfer from userform to sheet.
2. .value added in the code eg. "Sheet3.Cells(final, 1) = UserForm38.ComboBox1.Value"

but no success.

please help to resolve the issue.

The code stops at line "Sheet3.Cells(final, 1) = UserForm38.ComboBox1.Value"


VBA Code:
Private Sub cmd39_Click()
Dim i As Integer
Dim final As Double
Dim j As Integer
Dim actual As Double

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False

ThisWorkbook.Sheets("Data").Activate   ‘ sheet3

For i = 1 To 1000
If Sheet3.Cells(i, 1) = "" Then
final = i
Exit For
End If
Next

Application.ScreenUpdating = False

Sheet3.Cells(final, 1) = UserForm38.ComboBox1.Value     
Sheet3.Cells(final, 2) = UserForm38.TextBox1.Value     
Sheet3.Cells(final, 3) = UserForm38.TextBox69.Value     
Sheet3.Cells(final, 4) = Format(UserForm38.TextBox8, "0.00000000")     
Sheet3.Cells(final, 5) = UserForm38.ComboBox16.Value         
Sheet3.Cells(final, 6) = UserForm38.TextBox7.Value     
Sheet3.Cells(final, 7) = Format(UserForm38.TextBox6, "dd/mm/yyyy")     
Sheet3.Cells(final, 8) = UserForm38.Label17     
Sheet3.Cells(final, 9) = UserForm38.TextBox114.Value     
Sheet3.Cells(final, 10) = UserForm38.TextBox14.Value     
Sheet3.Cells(final, 11) = Format(UserForm38.TextBox115, "0.000000")     
Sheet3.Cells(final, 13) = Format(UserForm38.TextBox130, "0.000")     
Sheet3.Cells(final, 15) = Format(UserForm38.TextBox160, "0.000")     
Sheet3.Cells(final, 16) = UserForm38.TextBox177.Value     

Application.ScreenUpdating = False

For j = 1 To 1000
If sheet6.Cells(j, 1) = Sheet3.Cells(final, 1) Then

Application.ScreenUpdating = False

actual = sheet6.Cells(j, 3)
final = actual - UserForm38.TextBox8
sheet6.Cells(j, 3) = final
Exit For
End If
Next

Application.ScreenUpdating = False

MsgBox "Data of 1st row saved"


UserForm39.Hide

Application.ScreenUpdating = False

UserForm38.CommandButton3.Caption = "Data Saved"
UserForm38.CommandButton3.BackColor = &HFF00&
UserForm38.CommandButton3.Enabled = False

Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
 

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True

End Sub
 
You need to do it like
VBA Code:
For j = 1 To Sheet6.Range("A" & Rows.Count).End(xlUp).Row
 
Upvote 0
Solution

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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