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

Question

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 ?

Celltujuan2 = Db2.Range("A99999").End(xlUp).Row + 1 needs to be set Celltujuan2 = Db2.Range("A99999").End(xlUp).Row + 1 — Christofer Weber
– Christofer Weber, Commented Jun 17, 2021 at 6:12
Also Db2.Range("A99999").End(xlUp).Row + 1 returns a number, which isn't compatible with a range variable. — Christofer Weber
– Christofer Weber, Commented Jun 17, 2021 at 6:22
Hy Christofer, any suggest for row number that isn't compatible with a range variable ? — Satrio Adi
– Satrio Adi, Commented Jun 17, 2021 at 6:27
Your code does not make sense. First you Set Celltujuan2 = Db2.Range("A:A").Find(What:=RCNumberCMMS) Then you try to overwrite the same variable Celltujuan2 = Db2.Range("A99999").End(xlUp).Row + 1 It is very unclear what you are trying to do here. — Pᴇʜ
– Pᴇʜ, Commented Jun 17, 2021 at 6:29
@ChristoferWeber no actually the If RCNumberCMMS = "" Then has nothing to do with If Celltujuan2 Is Nothing Then that doesn't need to be replaced, that is a completly independent test. Just add the test for If Celltujuan2 Is Nothing Then. — Pᴇʜ
– Pᴇʜ, Commented Jun 17, 2021 at 6:57

Pᴇʜ · Accepted Answer · 2021-06-17 06:48:57Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related