[Solved] macro don't work when assigned to button

[bigme]

dear friends,
i have a macro and it's work fine if i run it from the VBA windows, but when i assign to a button, nothing happen, can someone help me with this issue?
for other macro work fine with the button.

here is my macro :
Sub doubelKoreksiFaktur()

Dim lastRow As Long
Dim x As Long
Dim i As Long

lastRow = Worksheets("Monitoring Faktur").Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lastRow
If Cells(i, 1) <> "" Then
x = WorksheetFunction.Match(Cells(i, 1), Range("A1:A" & lastRow), 0)
If i <> x Then
Cells(i, 9) = "Duplicate"
End If
End If
Next
End Sub

regards,
bigMe

 

[Adnan_1612008]

dear friends,
i have a macro and it's work fine if i run it from the VBA windows, but when i assign to a button, nothing happen, can someone help me with this issue?
for other macro work fine with the button.

here is my macro :
Sub doubelKoreksiFaktur()

Dim lastRow As Long
Dim x As Long
Dim i As Long

lastRow = Worksheets("Monitoring Faktur").Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lastRow
If Cells(i, 1) <> "" Then
x = WorksheetFunction.Match(Cells(i, 1), Range("A1:A" & lastRow), 0)
If i <> x Then
Cells(i, 9) = "Duplicate"
End If
End If
Next
End Sub

regards,
bigMe

Dear bigme,
First of all, thanks for reaching out to the Exceldemy forum.
As you mentioned, your VBA code is doing nothing when you are running it from a button.
As seen from your VBA code, this code looks for duplicates in a column and then writes “Duplicate” if it finds one. I replicated the same code with a slight change in column arrangement ( I used column B instead of column A) in the following Excel file.

I added two buttons in the file,
1. An ActiveX Controls Command Button
In order to add an ActiveX Controls Button follow the steps below-
a. Go to Developer>>Insert>>ActiveX Controls>>Command Button

b. Right on the command button and click on the View Code option.

c. Assign the following macro to the VBA module
Private Sub CommandButton1_Click()
Dim lastRow As Long
Dim x As Long
Dim i As Long
lastRow = Worksheets("Test").Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To lastRow
If Cells(i, 2) <> "" Then
x = WorksheetFunction.Match(Cells(i, 2), Range("B1:B" & lastRow), 0)
If i <> x Then
Cells(i, 6) = "Duplicate"
End If
End If
Next
End Sub
2. A rectangle shape with rounded corners.
Follow the steps below to add a shape button-
a. Go to Insert>>Shapes>>Rectangles.

b. Right click on the shape and select Assign Macro option to assign the macro to this shape.

I am currently using Office 365. Run these to see if these works.

*However, if these do not work

• Check your Excel version.
• Restart your Excel file.
• Add a MsgBox after “x = WorksheetFunction.Match(Cells(i, 1), Range("A1:A" & lastRow), 0)” line to see if this is returning any blank value.

Finally, feel free to reach us again, if all these do not work.

Best Regards,
Adnan
Exceldemy Team