Change offset to same row...
 

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

Change offset to same row...
 

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

Change offset to same row...
 

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

Change offset to same row...
 

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!

Change offset to same row...
 

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!

Change offset to same row...
 

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.

Change offset to same row...
 

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.

Change offset to same row...
 

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

Change offset to same row...
 

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

Change offset to same row...
 

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