Hi again, thanks for looking. In the following code snippet, I am looking
through a sheet for matching entries. If no match is found, I want to delete
some output that's already been placed and jump ahead a few entries in the
for each loop. I tried to do this by just redefining player, the variable
in the for each loop, as a cell a few cells below where it used to be, but I
get an object type mismatch error. How can I accomplish the goal of just
skipping a few values of player here?

For Each player In ws1.Range("B2:B" & lr1)

Set c = ws2.Range("A2:A" & lr2).Find(What:=player, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)

If c Is Nothing Then
ws3.Cells(x, lc3 + 4).EntireRow.Delete
ws1.Cells(x, 26).Value = "DATA CORRUPTED"
'LINE OF INTEREST IS BELOW, GETTING OBJECT TYPE MISMATCH HERE
player = ws1.Cells(player - y + 7, 2)

y = 1
Set c = ws2.Range("A2:A" & lr2).Find(What:=player, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
End If
code continues on for the case where there is a match

Player is an object in the line "For Each player In ws1.Range("B2:B" & lr1)"

Without testing I'm not sure if you can do what it appears you want to do
but if you can then I think it should have Set as the leading part of the
syntax:
Set player = ws1.Cells(player - y + 7, 2)

--
Regards,

OssieMac


"badmrfrosty8" wrote:

Hi again, thanks for looking. In the following code snippet, I am looking
through a sheet for matching entries. If no match is found, I want to delete
some output that's already been placed and jump ahead a few entries in the
for each loop. I tried to do this by just redefining player, the variable
in the for each loop, as a cell a few cells below where it used to be, but I
get an object type mismatch error. How can I accomplish the goal of just
skipping a few values of player here?

For Each player In ws1.Range("B2:B" & lr1)

Set c = ws2.Range("A2:A" & lr2).Find(What:=player, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)

If c Is Nothing Then
ws3.Cells(x, lc3 + 4).EntireRow.Delete
ws1.Cells(x, 26).Value = "DATA CORRUPTED"
'LINE OF INTEREST IS BELOW, GETTING OBJECT TYPE MISMATCH HERE
player = ws1.Cells(player - y + 7, 2)

y = 1
Set c = ws2.Range("A2:A" & lr2).Find(What:=player, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
End If
code continues on for the case where there is a match

Someone told me that for each statements dont guarantee an order, so i've
switched it to a wend where i mess with the counter.... testing now, ill
fill you in maybe an hour.

"OssieMac" wrote:

Player is an object in the line "For Each player In ws1.Range("B2:B" & lr1)"

Without testing I'm not sure if you can do what it appears you want to do
but if you can then I think it should have Set as the leading part of the
syntax:
Set player = ws1.Cells(player - y + 7, 2)

--
Regards,

OssieMac


"badmrfrosty8" wrote:

Hi again, thanks for looking. In the following code snippet, I am looking
through a sheet for matching entries. If no match is found, I want to delete
some output that's already been placed and jump ahead a few entries in the
for each loop. I tried to do this by just redefining player, the variable
in the for each loop, as a cell a few cells below where it used to be, but I
get an object type mismatch error. How can I accomplish the goal of just
skipping a few values of player here?

For Each player In ws1.Range("B2:B" & lr1)

Set c = ws2.Range("A2:A" & lr2).Find(What:=player, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)

If c Is Nothing Then
ws3.Cells(x, lc3 + 4).EntireRow.Delete
ws1.Cells(x, 26).Value = "DATA CORRUPTED"
'LINE OF INTEREST IS BELOW, GETTING OBJECT TYPE MISMATCH HERE
player = ws1.Cells(player - y + 7, 2)

y = 1
Set c = ws2.Range("A2:A" & lr2).Find(What:=player, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
End If
code continues on for the case where there is a match

Well, the while loop worked, but now I'm having problems with floating point
screwing up ID's, resulting in loss of lots of data. any suggestions? I
thought about prepending a single letter to every cell to insure that the
whole contents get displayed as is.

"badmrfrosty8" wrote:

Someone told me that for each statements dont guarantee an order, so i've
switched it to a wend where i mess with the counter.... testing now, ill
fill you in maybe an hour.

"OssieMac" wrote:

Player is an object in the line "For Each player In ws1.Range("B2:B" & lr1)"

Without testing I'm not sure if you can do what it appears you want to do
but if you can then I think it should have Set as the leading part of the
syntax:
Set player = ws1.Cells(player - y + 7, 2)

--
Regards,

OssieMac


"badmrfrosty8" wrote:

Hi again, thanks for looking. In the following code snippet, I am looking
through a sheet for matching entries. If no match is found, I want to delete
some output that's already been placed and jump ahead a few entries in the
for each loop. I tried to do this by just redefining player, the variable
in the for each loop, as a cell a few cells below where it used to be, but I
get an object type mismatch error. How can I accomplish the goal of just
skipping a few values of player here?

For Each player In ws1.Range("B2:B" & lr1)

Set c = ws2.Range("A2:A" & lr2).Find(What:=player, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)

If c Is Nothing Then
ws3.Cells(x, lc3 + 4).EntireRow.Delete
ws1.Cells(x, 26).Value = "DATA CORRUPTED"
'LINE OF INTEREST IS BELOW, GETTING OBJECT TYPE MISMATCH HERE
player = ws1.Cells(player - y + 7, 2)

y = 1
Set c = ws2.Range("A2:A" & lr2).Find(What:=player, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
End If
code continues on for the case where there is a match

"I'm having problems with floating point screwing up ID's, resulting in loss
of lots of data. any suggestions.

Unfortunately I don't really understand the question. Perhaps a little more
information.

--
Regards,

OssieMac


"badmrfrosty8" wrote:

Well, the while loop worked, but now I'm having problems with floating point
screwing up ID's, resulting in loss of lots of data. any suggestions? I
thought about prepending a single letter to every cell to insure that the
whole contents get displayed as is.

"badmrfrosty8" wrote:

Someone told me that for each statements dont guarantee an order, so i've
switched it to a wend where i mess with the counter.... testing now, ill
fill you in maybe an hour.

"OssieMac" wrote:

Player is an object in the line "For Each player In ws1.Range("B2:B" & lr1)"

Without testing I'm not sure if you can do what it appears you want to do
but if you can then I think it should have Set as the leading part of the
syntax:
Set player = ws1.Cells(player - y + 7, 2)

--
Regards,

OssieMac


"badmrfrosty8" wrote:

Hi again, thanks for looking. In the following code snippet, I am looking
through a sheet for matching entries. If no match is found, I want to delete
some output that's already been placed and jump ahead a few entries in the
for each loop. I tried to do this by just redefining player, the variable
in the for each loop, as a cell a few cells below where it used to be, but I
get an object type mismatch error. How can I accomplish the goal of just
skipping a few values of player here?

For Each player In ws1.Range("B2:B" & lr1)

Set c = ws2.Range("A2:A" & lr2).Find(What:=player, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)

If c Is Nothing Then
ws3.Cells(x, lc3 + 4).EntireRow.Delete
ws1.Cells(x, 26).Value = "DATA CORRUPTED"
'LINE OF INTEREST IS BELOW, GETTING OBJECT TYPE MISMATCH HERE
player = ws1.Cells(player - y + 7, 2)

y = 1
Set c = ws2.Range("A2:A" & lr2).Find(What:=player, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
End If
code continues on for the case where there is a match

Don't worry about it, I figured out a solution. Such a sweet catharsis to
finally get this monster of a macro finished. I've gone from knowing no vba
to understanding lookup/copy/paste routines, cleaner routines, and mass
application of formulas. I learned a lot over the past few days and everyone
on this forum has just been an amazing resource. Thank you all once again,
this ones from the heart.

"OssieMac" wrote:

"I'm having problems with floating point screwing up ID's, resulting in loss
of lots of data. any suggestions.

Unfortunately I don't really understand the question. Perhaps a little more
information.

--
Regards,

OssieMac


"badmrfrosty8" wrote:

Well, the while loop worked, but now I'm having problems with floating point
screwing up ID's, resulting in loss of lots of data. any suggestions? I
thought about prepending a single letter to every cell to insure that the
whole contents get displayed as is.

"badmrfrosty8" wrote:

Someone told me that for each statements dont guarantee an order, so i've
switched it to a wend where i mess with the counter.... testing now, ill
fill you in maybe an hour.

"OssieMac" wrote:

Player is an object in the line "For Each player In ws1.Range("B2:B" & lr1)"

Without testing I'm not sure if you can do what it appears you want to do
but if you can then I think it should have Set as the leading part of the
syntax:
Set player = ws1.Cells(player - y + 7, 2)

--
Regards,

OssieMac


"badmrfrosty8" wrote:

Hi again, thanks for looking. In the following code snippet, I am looking
through a sheet for matching entries. If no match is found, I want to delete
some output that's already been placed and jump ahead a few entries in the
for each loop. I tried to do this by just redefining player, the variable
in the for each loop, as a cell a few cells below where it used to be, but I
get an object type mismatch error. How can I accomplish the goal of just
skipping a few values of player here?

For Each player In ws1.Range("B2:B" & lr1)

Set c = ws2.Range("A2:A" & lr2).Find(What:=player, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)

If c Is Nothing Then
ws3.Cells(x, lc3 + 4).EntireRow.Delete
ws1.Cells(x, 26).Value = "DATA CORRUPTED"
'LINE OF INTEREST IS BELOW, GETTING OBJECT TYPE MISMATCH HERE
player = ws1.Cells(player - y + 7, 2)

y = 1
Set c = ws2.Range("A2:A" & lr2).Find(What:=player, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
End If
code continues on for the case where there is a match

Someone told me that for each statements dont guarantee an order,
Then I think that someone was mistaken. For Each always returns in the
order relevant to the collection though which the iteration loops.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 18 Dec 2008 22:21:01 -0800, badmrfrosty8
wrote:

Someone told me that for each statements dont guarantee an order, so i've
switched it to a wend where i mess with the counter.... testing now, ill
fill you in maybe an hour.

"OssieMac" wrote:

Player is an object in the line "For Each player In ws1.Range("B2:B" & lr1)"

Without testing I'm not sure if you can do what it appears you want to do
but if you can then I think it should have Set as the leading part of the
syntax:
Set player = ws1.Cells(player - y + 7, 2)

--
Regards,

OssieMac


"badmrfrosty8" wrote:

Hi again, thanks for looking. In the following code snippet, I am looking
through a sheet for matching entries. If no match is found, I want to delete
some output that's already been placed and jump ahead a few entries in the
for each loop. I tried to do this by just redefining player, the variable
in the for each loop, as a cell a few cells below where it used to be, but I
get an object type mismatch error. How can I accomplish the goal of just
skipping a few values of player here?

For Each player In ws1.Range("B2:B" & lr1)

Set c = ws2.Range("A2:A" & lr2).Find(What:=player, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)

If c Is Nothing Then
ws3.Cells(x, lc3 + 4).EntireRow.Delete
ws1.Cells(x, 26).Value = "DATA CORRUPTED"
'LINE OF INTEREST IS BELOW, GETTING OBJECT TYPE MISMATCH HERE
player = ws1.Cells(player - y + 7, 2)

y = 1
Set c = ws2.Range("A2:A" & lr2).Find(What:=player, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
End If
code continues on for the case where there is a match