Matching with respect to Column text

[Doug]

I have been using this macro to replace data back into columns 1-7 after I do
an import update from the web. It matches data in columns 1-7 up with respect
to column O between the old and new sheet data. I have made some changes
since I discovered the advantages of Vlookup and am hoping to find out how to
keep data in columns 1-7 with respect to column O. Right now when I do an
import the Vlookup rows shift, but the rows in columns 1-7 remain the same.
How can I make them shift with the other rows? I hope this makes sense.

Dim myCell As Range
Dim myNewRng As Range
Dim NewSheet As Worksheet
Dim OldSheet As Worksheet
Dim res As Variant

Set NewSheet = Workbooks("Doug's Research.xlsm").Worksheets("Screener")
Set OldSheet = Workbooks("Prior Screen.xlsm").Worksheets("Screener")

With NewSheet
Set myNewRng = .Range("O3", .Cells(.Rows.Count, "O").End(xlUp))

For Each myCell In myNewRng.Cells
If Not IsEmpty(myCell.Value) Then
res = Application.Match(myCell.Value, OldSheet.Range("O:O"), 0)
If IsError(res) Then
'not match, what should happen?
Else
..Cells(myCell.Row, "A").Resize(1, 7).Value _
= OldSheet.Cells(res, "A").Resize(1, 7).Value
End If
End If
Next myCell
End With

 

[Dave Peterson]

It doesn't make sense to me.

I don't understand what you mean by =vlookup() causing the rows to shift.

If you said that in order to use =vlookup() the column to match on has to be the
leftmost column of the lookup range, I'd understand.

If that's what you meant, you could move column O to column A and use
=vlookup().

But there isn't anything really sacred about =vlookup().

=vlookup(a2,sheet999!a:e,4,false)
would be equivalent to:
=index(sheet999!d:d,match(a2,sheet999!a:a,0))

In fact, if you have lots of the =vlookup()'s and the table changes often, you
may find that your workbook calculates more quickly.

=vlookup(a2,sheet999!a:e,4,false)
would recalculate if anything in sheet999 a:e changed.

=index(sheet999!d:d,match(a2,sheet999!a:a,0))
would recalculate only if something in A or D of sheet999 changed.

 

[paul.robinson]

Sorry, too vague for me.
"Vlookup rows shift " what does shift mean?
"shift with the other rows..." what does other mean??

Maybe a simple example of data, what you expect to see and what you
get.
regards
Paul

 

[Doug]

I understand. It is kind of confusing. Let me clarify.

My import sheet updates daily. Since the cells in my table (screener sheet)
are linked to the import sheet with vlookups it also changes what is in each
row when updated.
On the screener sheet, In Columns 1-7 I have comments I enter and other data
that must not be separated from data in the other column rows. I have no
vlookups in 1-7. It is what ever I type in. How can I make sure that each row
in columns 1-7 are always linked to a name in Column "O"?
Because when I update the import sheet there are often a different number of
rows and occasionally a name will be dropped. So columns 1-7 need to keep
track of the name in column "O" and move accordingly. If the name drops then
I am wanting the data in 1-7 to drop with that respective row.

I hope this makes sense?

 

[Doug]

My import sheet updates daily. Since the cells in my table (screener sheet)
are linked to the import sheet with vlookups it also changes what is in each
row when updated.
On the screener sheet, In Columns 1-8 I have comments I enter and other data
that must not be separated from data in the other column rows. I have no
vlookups in 1-8. It is what ever I type in. How can I make sure that each row
in columns 1-8 are always linked to a name in Column "R"?
Because when I update the import sheet there are often a different number of
rows and occasionally a name will be dropped.

So columns 1-8 need to keep
track of the name in column "R" that they are associated with and move
accordingly. If the name drops off the table due to a recent import of web
data then
I am wanting the data in 1-8 to drop with that respective row.

I hope this makes sense?

 

[Doug]

My import sheet updates daily. Since the cells in my table (screener sheet)
are linked to the import sheet with vlookups it also changes what is in each
row when updated.
On the screener sheet, In Columns 1-8 I have comments I enter and other data
that must not be separated from data in the other column rows. I have no
vlookups in 1-8. It is what ever I type in. How can I make sure that each row
in columns 1-8 are always linked to a name in Column "R"?
Because when I update the import sheet there are often a different number of
rows and occasionally a name will be dropped.

So columns 1-8 need to keep
track of the name in column "R" that they are associated with and move
accordingly. If the name drops off the table due to a recent import of web
data then
I am wanting the data in 1-8 to drop with that respective row.

I hope this makes sense?