dwarek

Board Regular
Joined
Jul 15, 2016
Messages
77
Hello everyone i have encountered a problem with this run time error 1004 application defined error or object defined error can any one help me out of this. i have highlighted the line where the error occurred with a sad smiley please help me out
Private Sub CommandButton1_Click()


Dim lrow As Long
Dim x As Long
Dim y As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")


lrow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(lrow, 2).Value = TextBox1.Value
.Cells(lrow, 3).Value = TextBox4.Value
.Cells(lrow, 5).Value = TextBox3.Value
.Cells(lrow, 8).Value = TextBox2.Value
.Cells(lrow, 9).Value = TextBox6.Value
.Cells(lrow, 10).Value = TextBox8.Value
.Cells(lrow, 14).Value = TextBox9.Value
.Cells(lrow, 11).Value = TextBox5.Value


If OptionButton1 = True Then
.Cells(lrow, 4).Value = "NEW"
ElseIf OptionButton2 = True Then
.Cells(lrow, 4).Value = "MODIFY"
ElseIf OptionButton3 = True Then
.Cells(lrow, 4).Value = "NAR"
ElseIf OptionButton6 = True Then
.Cells(lrow, 4).Value = "REX"
End If


If OptionButton7 = True Then
.Cells(lrow, 11).Value = "EQ/EQTY/ALL"
ElseIf OptionButton8 = True Then
.Cells(lrow, 11).Value = "FI/ALL/ALL"
ElseIf OptionButton9 = True Then
.Cells(lrow, 11).Value = "FI/CORP/ALL"
ElseIf OptionButton10 = True Then
.Cells(lrow, 11).Value = "FI/GOVT/ALL"
End If


If OptionButton11 = True Then
.Cells(lrow, 7).Value = "3 TO 10"
ElseIf OptionButton12 = True Then
.Cells(lrow, 7).Value = "LESS THAN 10"
ElseIf OptionButton13 = True Then
.Cells(lrow, 7).Value = "MORE THAN 10"
End If


If OptionButton14 = True Then
.Cells(lrow, 12).Value = "ADDING NEW SSI"
ElseIf OptionButton15 = True Then
.Cells(lrow, 12).Value = "DELETED SSI"
ElseIf OptionButton16 = True Then
.Cells(lrow, 12).Value = "SSI ALREADY PRESENT WITH CORRECT DATA AND FORMAT"
ElseIf OptionButton17 = True Then
.Cells(lrow, 12).Value = "UPDATE TO EXISTING SSI"
ElseIf OptionButton18 = True Then
.Cells(lrow, 12).Value = "UPDATETO FORMAT OF EXISTING SSI"
End If


If OptionButton4 = True Then
.Cells(lrow, 6).Value = "YES"
ElseIf OptionButton5 = True Then
.Cells(lrow, 6).Value = "NO"
End If
End With


Static rngUR As Range: Set rngUR = ActiveWorkbook.ActiveSheet.UsedRange
Dim rngBlank As Range: Set rngBlank = rngUR.Find("")

While Not rngBlank Is Nothing
rngBlank.Value = rngBlank.Offset(-1, 0).Value :(
Set rngBlank = rngUR.Find("", rngBlank)
Wend

Dim ctrl
For Each ctrl In Me.Controls
If TypeOf ctrl Is msforms.TextBox Then
ctrl.Text = ""
End If
If TypeOf ctrl Is msforms.OptionButton Then
ctrl.Value = False
End If
Next ctrl



End Sub
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Have checked the address of rngBlank when you get the error?

If it refers to any cell in row 1 you'll get the error you describe.

What are you trying to do here anyway?
Code:
While Not rngBlank Is Nothing
rngBlank.Value = rngBlank.Offset(-1, 0).Value 
Set rngBlank = rngUR.Find("", rngBlank)
Wend
 

dwarek

Board Regular
Joined
Jul 15, 2016
Messages
77
I am trying to copy the previous cell value if current cell value is empty and no I haven't checked the address of the rngblank and how to solve this problem?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,846
Messages
5,598,423
Members
414,237
Latest member
tereres

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