Change offset to same row...

J

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
 
D

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
 
B

Barb Reinhardt

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

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!
 
J

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.
 
J

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
 
T

Tom Ogilvy

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top