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
Bob Phillips - 21 Sep 2007 20:04 GMT
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

Signature
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
> 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
[quoted text clipped - 30 lines]
> Next c
> End Sub
J.W. Aldridge - 21 Sep 2007 21:43 GMT
When I get my first million....... I'm buying you a Krystal Burger
(with Cheese!)
THANX!!!!!!!!!
Don Guillett - 21 Sep 2007 22:18 GMT
Big spender

Signature
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
>
> When I get my first million....... I'm buying you a Krystal Burger
> (with Cheese!)
>
> THANX!!!!!!!!!