1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Run time error 1004: Application defined or Object-Defined Error.

Discussion in 'VBA Macros' started by Jagdev Singh, Jan 15, 2015.

  1. Jagdev Singh

    Jagdev Singh Active Member

    Messages:
    601
    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.

    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
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi Jaggi ,

    This is something you can troubleshoot yourself. When the error message is displayed , I assume the Debug button is available ; click on it , and in the Immediate window , type in all of the following , to get as much information as possible :

    ?irow

    ?objSh.Name

    ?objSh.Cells(irow,1).Address

    ?objSh.Cells(irow,1).Value

    If the last line displays something like Error 2042 or any other error value , then that is your problem ; when a cell contains an error value , comparing it with any other value will generate an error ; the only thing to be done is to first check for an error value , using the IsError function.

    Include this statement , just before the Loop statement :

    If IsError(objSh.Cells(irow , 1).Value) Then Exit Do

    Narayan
  3. Jagdev Singh

    Jagdev Singh Active Member

    Messages:
    601
    Hi Narayan

    Thanks for your reply. I am still unable to solve this issue. I am attaching the sample macro and sample data file with the thread. Please let me know what I am missing here

    Regards,
    JD

    Attached Files:

  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi Jaggi ,

    I am sorry but I find it difficult to understand what is going on ; first , I don't know why you have uploaded 2 files , the data in one file and the macro in the other.

    Second , I don't know why you are referring to the Worksheets by number , as in Worksheets(2) , Worksheets(3) ; either you have sheet tabs dedicated to some purpose or you create them ; doing neither , if you just refer to a worksheet by number , it is possible you will generate a runtime error if the specified worksheet does not exist.

    Even otherwise , when you specify a worksheet as Worksheets(1) , the 1 refers to the order of the worksheet tabs ; if a user just drags a worksheet tab into a different order , your code will crash. Never do this ; the least that is expected is to refer to a sheet tab by its name ; even more foolproof is to refer to it by its CodeName.

    My first suggestion therefore is to replace all the numeric references to Worksheets by their tab names. When I do this , I get an error at this line :

    Do While Left(objSh.Cells(irow, 1), 14) <> "Report Printed"

    Since I cannot see any place in the workbook where this is used , it is no wonder , since when the error occurs , irow has a value of 1048577 !

    Always ensure your loops will end , much before such limits are hit.

    Narayan
  5. Jagdev Singh

    Jagdev Singh Active Member

    Messages:
    601
    Hi Narayan

    Thanks for your feedback. I am sorry for the confusion here. To run the macro the following step needs to be done.

    1. We have to open the RAW Data file where we need the "Output -Client" tab.
    2. We have to open the macro and run it.

    In the RAW Data file we have 3 tabs that is what you are refering to. The Do loop is set for individual/respective tab. The
    Do While Left(objSh.Cells(irow, 1), 14) <> "Report Printed"
    is the condition for the 2nd sheet/tab of the RAW data.

    I was facing issue with the first loop - Do While objSh.Cells(irow, 1) <> "Report Total (s) EUR" and hence didn't inform you about the next loop. I appologise for it.

    The "Output -Client" tab/output/result comes in the first tab of the RAW Data sheet and hence the worksheet 4 is used in the code.

    Please let me know if you are still unclear.

    I created one more dummy file with the tabs in it.

    Regards,
    JD

    Attached Files:

  6. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi Jaggi ,

    I am again repeating what I said earlier ; the first line will not give any error.

    The error will come if the first tab ( Worksheets(1) ) is not the proper tab , since without the text on the worksheet , the program execution will keep looping till it comes to row 1048577 , at which point it will abort.

    Replace the reference to Worksheets(1) by Worksheets("TP") , and you will not get an error in the first section.

    Narayan
  7. Jagdev Singh

    Jagdev Singh Active Member

    Messages:
    601
    Hi Narayan

    Thanks a lot for explaining me the concept of the naming the worksheets with their actual name. I tried renaming all the reference sheets/tabs with their respective tab name in RAW data sheet in the above code. The code runs fine on the few instances but again throws an error runtime 9: subscript out of range. Any reason why I am getting this error. I am getting this error here -
    Set objSh = Worksheets("TP")

    EX:
    Set objSh = Worksheets("TP")
    Set objSh = Worksheets("client")
    Set objSh = Worksheets("Ins")

    Regards,
    JD
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi Jaggi ,

    As I have posted earlier , I do not know why you have separated the data from the macro.

    The workbook which has the macro is referred to as ThisWorkbook.

    If you are going to separate the code from the workbook which has the data , then it is better to qualify all worksheet references using the keyword ActiveWorkbook , and before running the macro you need to ensure that the workbook that has the data is the active workbook.

    Narayan
  9. Jagdev Singh

    Jagdev Singh Active Member

    Messages:
    601
    Hi Narayan

    I agree with you point why to add data in a seperate sheet and macro in seperate. My colleagues are working with the same concept from past few months. They find it easy and the issue is it is hard to make them understand the technical concept to use the same macro sheet for the entire operation.

    They find the current concept simple like opening the RAW data first and then open the macro and run it. They are not aware of the backstage function of the macro.

    It will be really hard for me to make them understand the issue we are facing with this macro.

    I tried using the word Activeworkbook infront of the worksheets, but the issue is how will we run the macro every time from the RAW data sheet. Currently I am aware that I need to be on the activesheet and jump to the macro's backend and press F5 the result is in front of me without any error. Can we do something while keeping my colleague concept which they are following from ages. My colleagues are not aware of the VBA option and asking to be on the ActiveSheet and jump to the VBA code and press F5 is definately not gonna work.

    Regards,
    JD
  10. Jagdev Singh

    Jagdev Singh Active Member

    Messages:
    601
    Hi Narayan

    Thanks for all your help and support. I informed my colleague and they are fine with adding RAW data in the Macro sheet. The macro is working fine when we are adding the RAW data in the macro sheet itself.

    I would like to thank you for helping me understanding the worksheet naming issues, which I would have never understood. This is really an eye opener for me and a learning curve. You are doing a wonderful job helping people like us who need such support to learn VBA.

    BTW, congrates for your 10000 post.

    Regards,
    JD

Share This Page