| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Offset on Variable Dinensioneds Object
I have passed a range of data to a variable (PMSMatchRange) that I have
dimensioned as an Object. My first For Next loop below works correctly. However when I get into my second For Next Loop, I cannot seem to use the Offset Method on the PMSMatchRange variable: CorrectCompanyName = PMSMatchRange.Offset(MatchNumber - 1, 1) Is this because it is an object? Effectively I am tyring to do a lookup off 1 column to the right of the range that I passed to this variable. Thanks ImportSheetVbNm.Select ImportSheetVbNm.Range("B2").Select ImportSheetVbNm.Range(Selection, Selection.End(xlDown)).Select NumPMSCodes = Selection.Rows.Count vbPMSCodeList.Select vbPMSCodeList.Range("A2").Select vbPMSCodeList.Range(Selection, Selection.End(xlDown)).Select Set PMSMatchRange = vbPMSCodeList.Range(Selection, Selection.End(xlDown)) For X = 1 To NumPMSCodes ImportSheetVbNm.Select ImportSheetVbNm.Range("B2").Offset(X - 1, 0).Select If IsError(Application.Match(ActiveCell.Value, PMSMatchRange, 0)) Then MsgBox ("You have PMS Codes in your import sheet that do not exist in your PMS Master List.") ActiveCell.EntireRow.Select Exit Sub End If Next For X = 1 To NumPMSCodes ImportSheetVbNm.Select ImportSheetVbNm.Range("B2").Offset(X - 1, 0).Select MatchNumber = Application.WorksheetFunction.Match(ActiveCell.Val ue, PMSMatchRange, 0) CorrectCompanyName = PMSMatchRange.Offset(MatchNumber - 1, 1) ImportSheetVbNm.Range("C2").Offset(X - 1, 0) = CorrectCompanyName Next |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Offset on Variable Dinensioneds Object
CorrectCompanyName = PMSMatchRange.Offset(MatchNumber - 1, 1)
Unless CorrectCompanyName is a variant, your code would probably attempt to assign an array to it and you would get an error. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... I have passed a range of data to a variable (PMSMatchRange) that I have dimensioned as an Object. My first For Next loop below works correctly. However when I get into my second For Next Loop, I cannot seem to use the Offset Method on the PMSMatchRange variable: CorrectCompanyName = PMSMatchRange.Offset(MatchNumber - 1, 1) Is this because it is an object? Effectively I am tyring to do a lookup off 1 column to the right of the range that I passed to this variable. Thanks ImportSheetVbNm.Select ImportSheetVbNm.Range("B2").Select ImportSheetVbNm.Range(Selection, Selection.End(xlDown)).Select NumPMSCodes = Selection.Rows.Count vbPMSCodeList.Select vbPMSCodeList.Range("A2").Select vbPMSCodeList.Range(Selection, Selection.End(xlDown)).Select Set PMSMatchRange = vbPMSCodeList.Range(Selection, Selection.End(xlDown)) For X = 1 To NumPMSCodes ImportSheetVbNm.Select ImportSheetVbNm.Range("B2").Offset(X - 1, 0).Select If IsError(Application.Match(ActiveCell.Value, PMSMatchRange, 0)) Then MsgBox ("You have PMS Codes in your import sheet that do not exist in your PMS Master List.") ActiveCell.EntireRow.Select Exit Sub End If Next For X = 1 To NumPMSCodes ImportSheetVbNm.Select ImportSheetVbNm.Range("B2").Offset(X - 1, 0).Select MatchNumber = Application.WorksheetFunction.Match(ActiveCell.Val ue, PMSMatchRange, 0) CorrectCompanyName = PMSMatchRange.Offset(MatchNumber - 1, 1) ImportSheetVbNm.Range("C2").Offset(X - 1, 0) = CorrectCompanyName Next |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Offset on Variable Dinensioneds Object
I actually had it dimenionsioned as an Object. I then changed it to a Range.
It stopped failing. However, when I hold my cursor over the variable CorrectCompanyName in the statement: CorrectCompanyName = PMSMatchRange.Offset(MatchNumber - 1, 1) It says "Empty". What is even more bizzare is that even though its says "Empty", the next line of code works fine: ImportSheetVbNm.Range("C2").Offset(X - 1, 0) = CorrectCompanyName That is the value in C2 (with offset) takes on the correct value. Odd. EM "Tom Ogilvy" wrote: CorrectCompanyName = PMSMatchRange.Offset(MatchNumber - 1, 1) Unless CorrectCompanyName is a variant, your code would probably attempt to assign an array to it and you would get an error. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... I have passed a range of data to a variable (PMSMatchRange) that I have dimensioned as an Object. My first For Next loop below works correctly. However when I get into my second For Next Loop, I cannot seem to use the Offset Method on the PMSMatchRange variable: CorrectCompanyName = PMSMatchRange.Offset(MatchNumber - 1, 1) Is this because it is an object? Effectively I am tyring to do a lookup off 1 column to the right of the range that I passed to this variable. Thanks ImportSheetVbNm.Select ImportSheetVbNm.Range("B2").Select ImportSheetVbNm.Range(Selection, Selection.End(xlDown)).Select NumPMSCodes = Selection.Rows.Count vbPMSCodeList.Select vbPMSCodeList.Range("A2").Select vbPMSCodeList.Range(Selection, Selection.End(xlDown)).Select Set PMSMatchRange = vbPMSCodeList.Range(Selection, Selection.End(xlDown)) For X = 1 To NumPMSCodes ImportSheetVbNm.Select ImportSheetVbNm.Range("B2").Offset(X - 1, 0).Select If IsError(Application.Match(ActiveCell.Value, PMSMatchRange, 0)) Then MsgBox ("You have PMS Codes in your import sheet that do not exist in your PMS Master List.") ActiveCell.EntireRow.Select Exit Sub End If Next For X = 1 To NumPMSCodes ImportSheetVbNm.Select ImportSheetVbNm.Range("B2").Offset(X - 1, 0).Select MatchNumber = Application.WorksheetFunction.Match(ActiveCell.Val ue, PMSMatchRange, 0) CorrectCompanyName = PMSMatchRange.Offset(MatchNumber - 1, 1) ImportSheetVbNm.Range("C2").Offset(X - 1, 0) = CorrectCompanyName Next |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Offset on Variable Dinensioneds Object
set PMSMatchRange = Range(selection,Selection.end(xldown))
? PMSMatchRange.Address $N$3:$N$122 ? PMSMatchRange.Offset(10,1).Address $O$13:$O$132 v = PMSMatchRange.Offset(10,1) ? typename(v) Variant() -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... I actually had it dimenionsioned as an Object. I then changed it to a Range. It stopped failing. However, when I hold my cursor over the variable CorrectCompanyName in the statement: CorrectCompanyName = PMSMatchRange.Offset(MatchNumber - 1, 1) It says "Empty". What is even more bizzare is that even though its says "Empty", the next line of code works fine: ImportSheetVbNm.Range("C2").Offset(X - 1, 0) = CorrectCompanyName That is the value in C2 (with offset) takes on the correct value. Odd. EM "Tom Ogilvy" wrote: CorrectCompanyName = PMSMatchRange.Offset(MatchNumber - 1, 1) Unless CorrectCompanyName is a variant, your code would probably attempt to assign an array to it and you would get an error. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... I have passed a range of data to a variable (PMSMatchRange) that I have dimensioned as an Object. My first For Next loop below works correctly. However when I get into my second For Next Loop, I cannot seem to use the Offset Method on the PMSMatchRange variable: CorrectCompanyName = PMSMatchRange.Offset(MatchNumber - 1, 1) Is this because it is an object? Effectively I am tyring to do a lookup off 1 column to the right of the range that I passed to this variable. Thanks ImportSheetVbNm.Select ImportSheetVbNm.Range("B2").Select ImportSheetVbNm.Range(Selection, Selection.End(xlDown)).Select NumPMSCodes = Selection.Rows.Count vbPMSCodeList.Select vbPMSCodeList.Range("A2").Select vbPMSCodeList.Range(Selection, Selection.End(xlDown)).Select Set PMSMatchRange = vbPMSCodeList.Range(Selection, Selection.End(xlDown)) For X = 1 To NumPMSCodes ImportSheetVbNm.Select ImportSheetVbNm.Range("B2").Offset(X - 1, 0).Select If IsError(Application.Match(ActiveCell.Value, PMSMatchRange, 0)) Then MsgBox ("You have PMS Codes in your import sheet that do not exist in your PMS Master List.") ActiveCell.EntireRow.Select Exit Sub End If Next For X = 1 To NumPMSCodes ImportSheetVbNm.Select ImportSheetVbNm.Range("B2").Offset(X - 1, 0).Select MatchNumber = Application.WorksheetFunction.Match(ActiveCell.Val ue, PMSMatchRange, 0) CorrectCompanyName = PMSMatchRange.Offset(MatchNumber - 1, 1) ImportSheetVbNm.Range("C2").Offset(X - 1, 0) = CorrectCompanyName Next |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
| Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
| Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
| Cells.Find error Object variable or With block variable not set | Excel Programming | |||
| Pivot Table - Object variable or with block variable not set? | Excel Programming | |||
Linear Mode
