I have a formula that looks for matching criteria (in cell I1 vs.
Column A) and copies the values to the right of it and pastes them in
E (starting with E1).

What I need to do is the same except the last part (starting with E1).
I need to copy, but a lateral move (like shown below).

If the matching value is found on row 2, then copy and paste the
target cells value directly over on the same row.


A B C D E F G H I
9/3 ABC 9/4
9/4 DEF DEF
9/3 GHI
9/4 JKL JKL

The formula that I am working with is as follows:

Sub macro3()
Dim ws As Worksheet
Dim iA As Integer
Dim iB As Integer
Dim c As Range
Dim rng As Range


Set ws = Worksheets("Sheet1")
Set rng = ws.Range("A1:A65536")
For Each c In rng
If c = Range("I1") Then
iA = iA + 1
ws.Cells(iA, 5) = c.Offset(0, 1)
End If
Next c
End Sub

Sub macro3()
Dim c As Range
Dim rng As Range
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")
Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
For Each c In rng
If c.Value = Range("I1").Value Then
c.Offset(0, 4).Value = c.Offset(0, 1).Value
End If
Next c
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"J.W. Aldridge" wrote in message
ups.com...

I have a formula that looks for matching criteria (in cell I1 vs.
Column A) and copies the values to the right of it and pastes them in
E (starting with E1).

What I need to do is the same except the last part (starting with E1).
I need to copy, but a lateral move (like shown below).

If the matching value is found on row 2, then copy and paste the
target cells value directly over on the same row.


A B C D E F G H I
9/3 ABC 9/4
9/4 DEF DEF
9/3 GHI
9/4 JKL JKL

The formula that I am working with is as follows:

Sub macro3()
Dim ws As Worksheet
Dim iA As Integer
Dim iB As Integer
Dim c As Range
Dim rng As Range


Set ws = Worksheets("Sheet1")
Set rng = ws.Range("A1:A65536")
For Each c In rng
If c = Range("I1") Then
iA = iA + 1
ws.Cells(iA, 5) = c.Offset(0, 1)
End If
Next c
End Sub

When I get my first million....... I'm buying you a Krystal Burger
(with Cheese!)

THANX!!!!!!!!!

Big spender

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"J.W. Aldridge" wrote in message
ups.com...

When I get my first million....... I'm buying you a Krystal Burger
(with Cheese!)

THANX!!!!!!!!!