Hi....

I altered this formula until i almost have what i need except for one
thing...

Instead of the offset transferring to the 'first available' (0,1) cell
five rows over, i need it to copy and transfer to the same row 5 rows
over.



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


Thanx

Not quite sure what you want but this should copy the current cell in sheet
1 if it is the same as I1 in the active sheet, 5 COLUMNS over. Also, do you
really want to take the time to check 65536 rows?

with worksheets("sheet1")
for each c in .Range("A1:A" & .cells(rows.count,"a").end(xlup).row)
If c = Range("I1") Then c.offset(,5)=c
Next c
end with

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi....
>
> I altered this formula until i almost have what i need except for one
> thing...
>
> Instead of the offset transferring to the 'first available' (0,1) cell
> five rows over, i need it to copy and transfer to the same row 5 rows
> over.
>
>
>
> 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
>
>
> Thanx
>

I'm not clear on what you are asking, but let me give it a shot.

c.Offset(0, 1) refers to the cell that is 0 rows down and one column to the
right of the cell in c.

Try adding this to your code within the For/Next Loop.

Debug.print c.address, c.offset(0,1).address

If that's not the problem, let me know.

HTH,
Barb Reinhardt



"J.W. Aldridge" wrote:

> Hi....
>
> I altered this formula until i almost have what i need except for one
> thing...
>
> Instead of the offset transferring to the 'first available' (0,1) cell
> five rows over, i need it to copy and transfer to the same row 5 rows
> over.
>
>
>
> 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
>
>
> Thanx
>
>

As for the range, yes... This is a log sheet that I am going to be
using for a while so it is going to get pretty long.

Thanx! Word purrrrrrr-fectly!

Still better NOT to use the whole column. Try my idea.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> As for the range, yes... This is a log sheet that I am going to be
> using for a while so it is going to get pretty long.
>
> Thanx! Word purrrrrrr-fectly!
>
>

THanx...

Found one issue.

I think it has to do with the portion of the code that states" iA = iA
+ 1".
Once the match is found, i am copying the cell one row over.

The code no longer does that, it just returns the same value of the
referenced cell.

A bit more explanation along with sample data

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> THanx...
>
> Found one issue.
>
> I think it has to do with the portion of the code that states" iA = iA
> + 1".
> Once the match is found, i am copying the cell one row over.
>
> The code no longer does that, it just returns the same value of the
> referenced cell.
>
>

This is the example....

I need to copy the data to the right of the match.


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

Thanx

sub Macro3()
with Range("E1")
.Formula = "=Index(B:B,match(I1,A:A,0))"
.Formula = .Value
end with
end if

or

Sub macro3()
Dim ws As Worksheet
Dim iA As long
Dim iB As long
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
ws.Cells(1, 5) = c.Offset(0, 1)
exit for
End If
Next c
End Sub

But what would you want if I1 contained 9/3?

--
Regards,
Tom Ogilvy






"J.W. Aldridge" wrote:

>
> This is the example....
>
> I need to copy the data to the right of the match.
>
>
> A B C D E F G H I
> 9/3 ABC JKL 9/4
> 9/3 DEF
> 9/3 GHI
> 9/4 JKL
>
> Thanx
>
>

My mistake...

Lateral moves based on meeting the criteria in I1.
(like below).

I only need certain dates to update at a time, so this would be
perfect.


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


Thanx