Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Application defined or object defined error

Posted on 2012-03-11
9
Medium Priority
?
412 Views
Last Modified: 2012-03-11
Hi,
This works once and then it doesn't work again.
I can't understand the error.
Any help as usual is most appreciated.

I know the code looks a bit clumsy and maybe a loop would work better, but at least it worksonce.

Option Explicit
Public Sub Trimspaces()
On Error GoTo Err_Trimspaces
Dim Aresponse As Integer
Dim WS1 As Worksheet
Dim DSVdate As Range
Dim UpDateRange As Range
Set WS1 = Sheets("NewToyData")
Set DSVdate = WS1.Range("AM1").End(xlDown).Offset(1, 0)
Set UpDateRange = ActiveCell.Resize(1, 37)
Aresponse = MsgBox("Have you copied the DSV figures from the latest downloaded web-shared spreadsheet?" & vbCrLf & _
                   "And have you selected the next empty cell down in column A?", vbCritical + vbYesNo, "CRITICAL!")
If Aresponse = vbYes Then
    ActiveCell.Offset(, 3).Value = ActiveCell.Offset(0, 7).Value
    ActiveCell.Offset(, 7).ClearContents
    ActiveCell.Offset(, 6).Value = ActiveCell.Offset(0, 14).Value
    ActiveCell.Offset(, 14).ClearContents
    ActiveCell.Offset(, 9).Value = ActiveCell.Offset(0, 21).Value
    ActiveCell.Offset(, 21).ClearContents
    ActiveCell.Offset(, 12).Value = ActiveCell.Offset(0, 28).Value
    ActiveCell.Offset(, 28).ClearContents
    ActiveCell.Offset(, 15).Value = ActiveCell.Offset(0, 35).Value
    ActiveCell.Offset(, 35).ClearContents
    ActiveCell.Offset(, 18).Value = ActiveCell.Offset(0, 42).Value
    ActiveCell.Offset(, 42).ClearContents
    ActiveCell.Offset(, 21).Value = ActiveCell.Offset(0, 49).Value
    ActiveCell.Offset(, 49).ClearContents
    ActiveCell.Offset(, 24).Value = ActiveCell.Offset(0, 56).Value
    ActiveCell.Offset(, 56).ClearContents
    ActiveCell.Offset(, 27).Value = ActiveCell.Offset(0, 63).Value
    ActiveCell.Offset(, 63).ClearContents
    ActiveCell.Offset(, 30).Value = ActiveCell.Offset(0, 70).Value
    ActiveCell.Offset(, 70).ClearContents
    ActiveCell.Offset(, 33).Value = ActiveCell.Offset(0, 77).Value
    ActiveCell.Offset(, 77).ClearContents
    ActiveCell.Offset(, 36).Value = ActiveCell.Offset(0, 84).Value
    ActiveCell.Offset(, 84).ClearContents
    DSVdate = Range("AL1")
    UpDateRange.Copy
Dim wbk2 As Workbook
Dim wbk2path As String
'    wbk2path = "H:\All\Shipping Database\Call_Offs.xlsm"
    wbk2path = "C:\Users\StevieB\Desktop\Call_Offs.xlsm"
    Set wbk2 = Workbooks.Open(wbk2path)
    MsgBox "Select the row which corresponds to the" & vbCrLf & _
        "Saturday you are updating the DSV" & vbCrLf & _
        "figures from, and PasteSpecialValues" & vbCrLf & _
        "from the G column"
End If
    If Aresponse = vbNo Then
    Exit Sub
    End If
Exit_Trimspaces:
    Exit Sub
Err_Trimspaces:
    MsgBox Err.Description
    Resume Exit_Trimspaces
End Sub
0
Comment
Question by:Stephen Byrom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 37707755
Which line of code does it error on?
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 37707770
I don't know.
When I click the button that the code is attached to nothing seems to happen at all. Except the error. "application or object defined error"
0
 
LVL 35

Expert Comment

by:Norie
ID: 37707777
Try removing On Error GoTo Err_Trimspaces.

That won't solve the problem but it should give you a better idea where the error is happening.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37707780
I believe its possible for you to get that error on this line:

Set DSVdate = WS1.Range("AM1").End(xlDown).Offset(1, 0)

If range AM1 has no data, then you're going to the bottom of the worksheet, THEN trying to get one more row lower.

If this is the case, then I suggest rewriting as follows (as it appears you're just trying to get the last row then go to the blank row below that:

set DVSdate = WS1.Range("AM" & ws1.Rows.Count).End(xlUp).Offset(1,0)

Here's your modified code:
Option Explicit


Public Sub Trimspaces()
On Error GoTo Err_Trimspaces
Dim Aresponse As Integer
Dim WS1 As Worksheet
Dim DSVdate As Range
Dim UpDateRange As Range
Set WS1 = Sheets("NewToyData")
Set DSVdate = WS1.Range("AM1").End(xlDown).Offset(1, 0)
Set DVSdate = WS1.Range("AM" & WS1.Rows.Count).End(xlUp).Offset(1, 0)
Set UpDateRange = ActiveCell.Resize(1, 37)
Aresponse = MsgBox("Have you copied the DSV figures from the latest downloaded web-shared spreadsheet?" & vbCrLf & _
                   "And have you selected the next empty cell down in column A?", vbCritical + vbYesNo, "CRITICAL!")
If Aresponse = vbYes Then
    ActiveCell.Offset(, 3).Value = ActiveCell.Offset(0, 7).Value
    ActiveCell.Offset(, 7).ClearContents
    ActiveCell.Offset(, 6).Value = ActiveCell.Offset(0, 14).Value
    ActiveCell.Offset(, 14).ClearContents
    ActiveCell.Offset(, 9).Value = ActiveCell.Offset(0, 21).Value
    ActiveCell.Offset(, 21).ClearContents
    ActiveCell.Offset(, 12).Value = ActiveCell.Offset(0, 28).Value
    ActiveCell.Offset(, 28).ClearContents
    ActiveCell.Offset(, 15).Value = ActiveCell.Offset(0, 35).Value
    ActiveCell.Offset(, 35).ClearContents
    ActiveCell.Offset(, 18).Value = ActiveCell.Offset(0, 42).Value
    ActiveCell.Offset(, 42).ClearContents
    ActiveCell.Offset(, 21).Value = ActiveCell.Offset(0, 49).Value
    ActiveCell.Offset(, 49).ClearContents
    ActiveCell.Offset(, 24).Value = ActiveCell.Offset(0, 56).Value
    ActiveCell.Offset(, 56).ClearContents
    ActiveCell.Offset(, 27).Value = ActiveCell.Offset(0, 63).Value
    ActiveCell.Offset(, 63).ClearContents
    ActiveCell.Offset(, 30).Value = ActiveCell.Offset(0, 70).Value
    ActiveCell.Offset(, 70).ClearContents
    ActiveCell.Offset(, 33).Value = ActiveCell.Offset(0, 77).Value
    ActiveCell.Offset(, 77).ClearContents
    ActiveCell.Offset(, 36).Value = ActiveCell.Offset(0, 84).Value
    ActiveCell.Offset(, 84).ClearContents
    DSVdate = Range("AL1")
    UpDateRange.Copy
Dim wbk2 As Workbook
Dim wbk2path As String
'    wbk2path = "H:\All\Shipping Database\Call_Offs.xlsm"
    wbk2path = "C:\Users\StevieB\Desktop\Call_Offs.xlsm"
    Set wbk2 = Workbooks.Open(wbk2path)
    MsgBox "Select the row which corresponds to the" & vbCrLf & _
        "Saturday you are updating the DSV" & vbCrLf & _
        "figures from, and PasteSpecialValues" & vbCrLf & _
        "from the G column"
End If
    If Aresponse = vbNo Then
    Exit Sub
    End If
Exit_Trimspaces:
    Exit Sub
Err_Trimspaces:
    MsgBox Err.Description
    Resume Exit_Trimspaces
End Sub

Open in new window


Cheers,

Dave
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 37707786
this is the line where it breaks
Set DSVdate = WS1.Range("AM1").End(xlDown).Offset(1, 0)
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37707796
Sorry - I had a typeo in my code:

Here it is, correcting that line, as I suggested:

set DVSdate = WS1.Range("AM" & ws1.Rows.Count).End(xlUp).Offset(1,0)

Option Explicit


Public Sub Trimspaces()
'On Error GoTo Err_Trimspaces
Dim Aresponse As Integer
Dim WS1 As Worksheet
Dim DSVdate As Range
Dim UpDateRange As Range
Dim wbk2 As Workbook
Dim wbk2path As String
        
    Set WS1 = Sheets("NewToyData")
    'Set DSVdate = WS1.Range("AM1").End(xlDown).Offset(1, 0)
    Set DSVdate = WS1.Range("AM" & WS1.Rows.Count).End(xlUp).Offset(1, 0)
    Set UpDateRange = ActiveCell.Resize(1, 37)
    
    Aresponse = MsgBox("Have you copied the DSV figures from the latest downloaded web-shared spreadsheet?" & vbCrLf & _
                       "And have you selected the next empty cell down in column A?", vbCritical + vbYesNo, "CRITICAL!")
                       
    If Aresponse = vbYes Then
        ActiveCell.Offset(, 3).Value = ActiveCell.Offset(0, 7).Value
        ActiveCell.Offset(, 7).ClearContents
        ActiveCell.Offset(, 6).Value = ActiveCell.Offset(0, 14).Value
        ActiveCell.Offset(, 14).ClearContents
        ActiveCell.Offset(, 9).Value = ActiveCell.Offset(0, 21).Value
        ActiveCell.Offset(, 21).ClearContents
        ActiveCell.Offset(, 12).Value = ActiveCell.Offset(0, 28).Value
        ActiveCell.Offset(, 28).ClearContents
        ActiveCell.Offset(, 15).Value = ActiveCell.Offset(0, 35).Value
        ActiveCell.Offset(, 35).ClearContents
        ActiveCell.Offset(, 18).Value = ActiveCell.Offset(0, 42).Value
        ActiveCell.Offset(, 42).ClearContents
        ActiveCell.Offset(, 21).Value = ActiveCell.Offset(0, 49).Value
        ActiveCell.Offset(, 49).ClearContents
        ActiveCell.Offset(, 24).Value = ActiveCell.Offset(0, 56).Value
        ActiveCell.Offset(, 56).ClearContents
        ActiveCell.Offset(, 27).Value = ActiveCell.Offset(0, 63).Value
        ActiveCell.Offset(, 63).ClearContents
        ActiveCell.Offset(, 30).Value = ActiveCell.Offset(0, 70).Value
        ActiveCell.Offset(, 70).ClearContents
        ActiveCell.Offset(, 33).Value = ActiveCell.Offset(0, 77).Value
        ActiveCell.Offset(, 77).ClearContents
        ActiveCell.Offset(, 36).Value = ActiveCell.Offset(0, 84).Value
        ActiveCell.Offset(, 84).ClearContents
        
        DSVdate = Range("AL1")
        UpDateRange.Copy
        
        '    wbk2path = "H:\All\Shipping Database\Call_Offs.xlsm"
        wbk2path = "C:\Users\StevieB\Desktop\Call_Offs.xlsm"
        Set wbk2 = Workbooks.Open(wbk2path)
        MsgBox "Select the row which corresponds to the" & vbCrLf & _
               "Saturday you are updating the DSV" & vbCrLf & _
               "figures from, and PasteSpecialValues" & vbCrLf & _
               "from the G column"
    End If
    If Aresponse = vbNo Then
        Exit Sub
    End If
Exit_Trimspaces:
    Exit Sub
Err_Trimspaces:
    MsgBox Err.Description
    Resume Exit_Trimspaces
End Sub

Open in new window


Dave
0
 
LVL 1

Author Closing Comment

by:Stephen Byrom
ID: 37707797
Thanks for that.
I now understand a little more about vba.
Thanks again
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37707802
Unless you want otherwise, its always better to find the last row or last column, starting from the bottom (or right) and heading toward your data.

Wks.Range("A" & Wks.Rows.Count).End(XlUp) 'gets the last row with data in column A

Wks.Cells(1,Wks.Columns.Count).End(XlToLeft) 'gets the last column with data on row 1

Cheers,

Dave
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 37707806
Thanks,
I've changed my code to reflect that EndUP advice and will use it from now on.
Thanks again for your help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question