PDA

View Full Version : [SOLVED] VBA - Run time error 1004: Application defined or Object-Defined Error



Jagdev
01-14-2015, 10:54 PM
Hi Experts,
I am getting 1004 error in the following code: in the Do Statement. Could you please lemme know what am I missing here. The concept here is the the macro should check "Report Total (s) EUR" in the Row 1 and if found terminate the loop. Is it possible if there is a space in the cell, might this error occur?


Do While objSh.Cells(irow, 1) <> "Report Total (s) EUR"
If (objSh.Cells(irow, 4) = "Complete" Or objSh.Cells(irow, 4) = "UnPaid" Or objSh.Cells(irow, 4) = "Part Paid" Or objSh.Cells(irow, 4) = "Paid") Then
objOut.Cells(iTgtRow, 1) = objSh.Cells(irow, 1)
' objOut.Cells(iTgtRow, 2) = strCCode
'objOut.Cells(iTgtRow, 3) = strCName
'objOut.Cells(iTgtRow, 4) = strCurrancy
'If (objSh.Cells(irow, 5)) = "C00002240" Then
' MsgBox "I am here"
'End If
objOut.Cells(iTgtRow, 5) = objSh.Cells(irow, 5)
objOut.Cells(iTgtRow, 6) = objSh.Cells(irow, 6)
Strdate = objSh.Cells(irow, 7)
objOut.Cells(iTgtRow, 7) = GetDateValue(Strdate)
strAmt = objSh.Cells(irow, 8)
' If InStr(strAmt, "(") > 0 Then
' isNegative = True
' End If
strAmt = Replace(strAmt, ".", "")
strAmt = Replace(strAmt, "(", "")
strAmt = Replace(strAmt, ")", "")
strAmt = Replace(strAmt, ",", ".")
objOut.Cells(iTgtRow, 8) = Val(strAmt)
strAmt = objSh.Cells(irow, 9)
strAmt = Replace(strAmt, ".", "")
strAmt = Replace(strAmt, "(", "")
strAmt = Replace(strAmt, ")", "")
strAmt = Replace(strAmt, ",", ".")
objOut.Cells(iTgtRow, 9) = Val(strAmt) * -1
objOut.Cells(iTgtRow, 10) = "Third Party"
iTgtRow = iTgtRow + 1
irow = irow + 1
Else
irow = irow + 1
End If
Loop
Regards,
JD

Jagdev
01-14-2015, 11:15 PM
Hi Experts,

I got the error and the code is working now.

Thanks
JD

Aussiebear
01-15-2015, 03:02 AM
What was the error and how did you overcome it?

Jagdev
01-15-2015, 03:47 AM
Hi Aussiebear

The macro which I was using works as per the below procedure.

1. We have to open the RAW_Data sheet first which consists of 3 tabs of data init.
2. Then you have to open the macro sheet and run the macro.

What macro was doing is merging the specific data from RAW Data sheet(3 tabs) in one more tab in the RAW Data sheet and name this tab.

This was working fine, but on few occation it throws the above error msg(refer my first post). I believe it is because the data is in seperate sheet and macro in seperate.

What I did I consider the Macro sheet as a common sheet and dump the RAW data into it and run it. The macro doesn't have to run and check any sheet open or not. It instead have the data init and I get the expected result.

Is it possible to follow the old process which I explain you in the above explanation.

Regards,
JD