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

"J.W. Aldridge" wrote in message
oups.com...
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

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

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

"J.W. Aldridge" wrote in message
oups.com...
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.

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