CODEWITHSUNDEEP
excelvba
Satrio Adi
Satrio Adi

Reputation: 1

object variable or with block variable not set on excel macro vba

i try to create button to add data with looping on last row, but i got error with code "object variable or with block variable not set"

here is my code

Sub Add_Click2()

Application.ScreenUpdating = False

Dim emptyRow1 As Long
Dim Celltujuan2 As Range

Set Menu = Sheets("Interface")
Set Db2 = Sheets("Database CMMS")

'DB Power
Db2.Activate
RCNumberCMMS = Menu.Range("D20").Value
Set Celltujuan2 = Db2.Range("A:A").Find(What:=RCNumberCMMS)

If RCNumberCMMS = "" Then
MsgBox ("Silakan isi RC Number terlebih Dahulu!")
Exit Sub
End If


Celltujuan2 = Db2.Range("A99999").End(xlUp).Row + 1

'Info
For i = 25 To 35
Cells(Celltujuan2, Menu.Range("E" & i).Value) = Menu.Range("D" & i).Value
Next i

'DOP & Approval
For i = 25 To 34
Cells(Celltujuan2, Menu.Range("I" & i).Value) = Menu.Range("H" & i).Value
Next i


'Install & Material Eks
For i = 25 To 33
Cells(Celltujuan2, Menu.Range("M" & i).Value) = Menu.Range("L" & i).Value
Next i

'Write Off
For i = 25 To 26
Cells(Celltujuan2, Menu.Range("Q" & i).Value) = Menu.Range("P" & i).Value
Next i

MsgBox ("Data Telah Terupdate")


Menu.Activate
Menu.Range("D20").Select

End Sub

i do the same code for other sheet and running well, i create this code in the same workbook but in diffren module, is that the problem ?

Upvotes: 0

Views: 73

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57693

After using Find you need to check if something was found.

Set Celltujuan2 = Db2.Range("A:A").Find(What:=RCNumberCMMS)
If Celltujuan2 Is Nothing Then
    MsgBox """" & RCNumberCMMS & """ was not found."
    Exit Sub
End If

If you read the documentation of the Range.Find method it says:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

So you need to specify at least those parameters or something random is used (there is no default).

Set Celltujuan2 = Db2.Range("A:A").Find(What:=RCNumberCMMS, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchByte:=False)
If Celltujuan2 Is Nothing Then
    MsgBox """" & RCNumberCMMS & """ was not found."
    Exit Sub
End If

Upvotes: 1

Related Questions