Change offset to same row...

[J.W. Aldridge]

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

 

[Don Guillett]

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

 

[Guest]

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]

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!

 

[Don Guillett]

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

 

[J.W. Aldridge]

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.

 

[Don Guillett]

A bit more explanation along with sample data

 

[J.W. Aldridge]

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

 

[Guest]

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?

 

[J.W. Aldridge]

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