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

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

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

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