VBA error

Question

Hi there,

When I created a VBA code as below:

---

Private Sub CommandButton1_Click()

Dim last As Integer

last = Sheet2.Range("B10000").End(xlUp).Row + 1

Sheet2.Cells(last, "B").Value = sheet1.Range("G6").Value

Sheet2.Cells(last, "C").Value = sheet1.Range("G8").Value

Sheet2.Cells(last, "D").Value = sheet1.Range("G10").Value

Sheet2.Cells(last, "E").Value = sheet1.Range("G12").Value

Sheet2.Cells(last, "F").Value = sheet1.Range("G14").Value

Sheet2.Cells(last, "G").Value = sheet1.Range("G16").Value

Sheet2.Cells(last, "H").Value = sheet1.Range("J6").Value

Sheet2.Cells(last, "I").Value = IIf(OptionButton1.Value = True, "ÐßÑ", "ÃäËì")

Sheet2.Cells(last, "J").Value = sheet1.Range("J10").Value

Sheet2.Cells(last, "K").Value = sheet1.Range("J12").Value

Sheet2.Cells(last, "L").Value = sheet1.Range("J14").Value

Sheet2.Cells(last, "M").Value = sheet1.Range("J16").Value

sheet1.Range("G6").Value = ""

sheet1.Range("G8").Value = ""

sheet1.Range("G10").Value = ""

sheet1.Range("G12").Value = ""

sheet1.Range("G14").Value = ""

sheet1.Range("G6").Value = ""

sheet1.Range("J8").Value = ""

sheet1.Range("J10").Value = ""

sheet1.Range("J12").Value = ""

sheet1.Range("J14").Value = ""

sheet1.Range("J16").Value = ""

OptionBotton1.Value = ""

OptionBotton2.Value = ""

MsgBox "Êã ÅÏÎÇá ÇáÈíÇäÇÊ ÈäÌÇÍ", vbInformation, "Done"

End Sub

---

There was an error and showed up as below:

Run-time error “424”

Object required

Can you help me to fix this issue, please?

Many thanks in advanced

Answer 1

https://1drv.ms/u/s!AuwYikQUshB30zp8H-VgcYt9Uk0n

Worked. :-)

Alright, the first step to find out all the errors is to add the line

Option Explicit

at the top of the module, then click Debug \ Compile

The compiler selects Sheet2 is this line

Last = Sheet2.Range("B10000").End(xlUp).Row + 1

Why?

Sheet2 in here is (should be) the code module name to make the line working. But as your code module name  contains Unicode chars I suggest not to use the code module names.

Use the sheet names and access the sheet using the Sheets collection:

Last = Sheets("Sheet2").Range("B10000").End(xlUp).Row + 1

That works, next error is Sheet1 in this line (and of course Sheet2 also)

Sheet2.Celles(Last, "B").Value = Sheet1.Range("G6").Value

So we can change the line to

Sheets("Sheet2").Celles(Last, "B").Value = Sheets("Sheet1").Range("G6").Value

and if we comment out all other lines and run the code we get a RTE 438 on Celles. That's a typo the correct word is Cells

Sheets("Sheet2").Cells(Last, "B").Value = Sheets("Sheet1").Range("G6").Value

Okay, how to optimize the code and make it better readable?

The code is inside the code module of a sheet, that means if we execute e.g. Range("A1")=1 we write a 1 into A1 into this sheet (the sheet that contains the code) regardless which sheet is active! That means this line works the same

Sheets("Sheet2").Cells(Last, "B").Value = Range("G6").Value

Next optimizing step: As we have a lot of Sheet2 to replace it is better to use a WITH statement:

With Sheets("Sheet2")

.Cells(Last, "B").Value = Range("G6").Value
End With

Note the dot in front of Cells!

And we can do a bit more, see code below.

BTW, it is is easier if you do not write a string into column I into Sheet2, it is easier if you simple write True/False from the Optionbutton

.Cells(Last, "I").Value = OptionButton1.Value

this will be easier to reload later (if you intend to).

Take a look into this file also
https://www.dropbox.com/s/50kr8mpmgdk0p25/SimpleFormDatabase.xls?dl=1

Andreas.

Private Sub CommandButton1_Click()
Dim Last As Long
If OptionButton1 = False And OptionButton2 = False Then
MsgBox "You must choose an option first!", vbExclamation
Exit Sub
End If
With Sheets("Sheet2")
Last = .Range("B" & Rows.Count).End(xlUp).Row + 1
.Cells(Last, "B").Value = Range("G6").Value
.Cells(Last, "C").Value = Range("G8").Value
.Cells(Last, "D").Value = Range("G10").Value
.Cells(Last, "E").Value = Range("G12").Value
.Cells(Last, "F").Value = Range("G14").Value
.Cells(Last, "G").Value = Range("G16").Value
.Cells(Last, "H").Value = Range("J6").Value
.Cells(Last, "I").Value = IIf(OptionButton1.Value = True, "ÐßÑ", "ÃäËì")
.Cells(Last, "J").Value = Range("J10").Value
.Cells(Last, "K").Value = Range("J12").Value
.Cells(Last, "L").Value = Range("J14").Value
.Cells(Last, "M").Value = Range("J16").Value
End With
Range("G6,G8,G10,G12,G14,G16,J6,J8,J10,J12,J14,J16").ClearContents
OptionButton1.Value = False
OptionButton2.Value = False
End Sub

Answer 2

The compiler selects the line with the error, the blue line. In there I can read

msgbox("whatever",vbInformation,"Done"

Means this is a different issue.

I see an opening parenthesis, but the closing one is missing, hence the syntax error. Try

msgbox "whatever",vbInformation,"Done"

Please take a look into this tutorial for VBA beginners, IMHO it is helpful for you:

Excel Visual Basic Tutorial - VBA Macros Online Training

If there are more errors in your file it is easier if you share the file, so I can take a look.
Share OneDrive files and folders - Office Support

Andreas.

Answer 3

there is still erorr???

Answer 4

Add the line

Option Explicit

at the top of the module and click Debug \ Compile

IMHO the control is named

OptionButton1

not

OptionBotton1

Andreas.