PDA

View Full Version : Application-defined or Object-defined error



LaBamba
09-18-2014, 09:34 AM
Hey guys;

Can you please take a look at the below code...i am really puzzled as to why i get this error on the Set rPS and Set rGS but the other ranges work just fine.
Its exactly the same code except its finding something else.

Let me know your thoughts.


Sub testcalc()
Dim wbCustomerFile As Workbook
Dim wbMaster As Workbook
Dim shCommands As Worksheet


Dim lRankCol As Long
Dim lNRQtyCol As Long
Dim lRebateQtyCol As Long
Dim lPricingCol As Long
Dim i As Long
Dim lRow As Long

Dim rPS As Range
Dim rGS As Range
Dim rNrTotal As Range
Dim rCombined As Range
Dim rRbtTotal As Range

Dim dNonTotal As Double
Dim dRbtTotal As Double
Dim dCombinedTotal As Double

Application.ScreenUpdating = False

On Error GoTo errorHandler

Set wbMaster = ThisWorkbook
Set shCommands = wbMaster.Sheets(g_strCOMMAND_SHEET_NAME)

Set rPS = wbMaster.Sheets(1).Rows(1).Cells.Find(What:="PS", lookat:=xlWhole, Searchorder:=xlByColumns, MatchCase:=True).End(xlUp).End(xlDown).Offset(1, 0)
Set rGS = wbMaster.Sheets(1).Rows(1).Cells.Find(What:="GS", lookat:=xlWhole, Searchorder:=xlByColumns, MatchCase:=True).End(xlUp).End(xlDown).Offset(1, 0)
Set rNrTotal = wbMaster.Sheets(1).Rows(1).Cells.Find(What:="Rank 1", lookat:=xlWhole, Searchorder:=xlByColumns, MatchCase:=True).End(xlUp).End(xlDown).Offset(1, 0)
Set rCombined = wbMaster.Sheets(1).Rows(1).Cells.Find(What:="Rank 1/1R", lookat:=xlWhole, Searchorder:=xlByColumns, MatchCase:=True).End(xlUp).End(xlDown).Offset(1, 0)
Set rRbtTotal = wbMaster.Sheets(1).Rows(1).Cells.Find(What:="Rank 1R", lookat:=xlWhole, Searchorder:=xlByColumns, MatchCase:=True).End(xlUp).End(xlDown).Offset(1, 0)





Thanks,

Kenneth Hobs
09-18-2014, 10:50 AM
Not sure why you did both end down and end up. In any case, your problem is because nothing was found. Trap that sort of error with an On Error routine.
e.g.

Sub ken()
Dim wbMaster As Workbook, rPS As Range
On Error Resume Next
Set wbMaster = ThisWorkbook
Set rPS = wbMaster.Worksheets(1).Rows(1).Cells.Find(What:="PS", lookat:=xlWhole, Searchorder:=xlByColumns, MatchCase:=True).End(xlUp)
MsgBox (rPS Is Nothing)
End Sub

LaBamba
09-18-2014, 10:58 AM
But the "PS" is right next to the other values; i dont know why it doesnt find it; hah.

Ill try and copy paste it into another module...perhaps its bugged.

Kenneth Hobs
09-18-2014, 11:12 AM
Finds can be tricky. I would recommend selecting your range manually to see what your range is or use:

Debug.print yourrangeobect.address

e.g.
left click row 1, press end key and then down, then end key and then up key. Now A1 is selected.

Using offset can be lethal sometimes too. Imagine what would happen if you used:

MsgBox Range("A1048576").Offset(1,0).Address
Right, run-time error.

snb
09-18-2014, 12:03 PM
try


x2=thisworkbook.Sheets(1).Cells.Find("PS",,,1).address