MrExcel.com - Your One Stop for Excel Tips & Solutions

Resubmitted my cell evaluation question from earlier, forgot the macro or formula

Posted by Jay on February 08, 2002 1:50 PM

Hi
I am trying to look at B2 and if any information is there or number or data then move over to the right one cell and up one cell and copy active cell and move back down one cell and paste move over one cell and up and copy and down and paste. When I copy and paste I want the A2 in formula to change to A3 or A4 or A5 etc. as I continue to copy and move down in my worksheet. If I copy by hand with the mouse and paste down the A2 will change to A3 and so forth. What can I do to make it change. Hope you understand all if this. Thanks Jay Private
This is formula I am doing. The A2 in front of [Book21.xls] is what I am talking about

Sub CommandButton1_Click()
Worksheets("Sheet2").Activate
ActiveCell = UserForm1.ComboBox1.Value
If ("B2") = "=INDEX([Book21.xls]Sheet3!$V$2:$X$4,MATCH(A2,[Book21.xls]Sheet3!$V$2:$V$4,FALSE),2)" Then
ActiveCell.Offset(-1, 1).Activate
Selection.Copy
ActiveCell.Offset(1, 0).Activate
ActiveSheet.Paste
ActiveCell.Offset(-1, 1).Activate
Selection.Copy
ActiveCell.Offset(1, 0).Activate
ActiveSheet.Paste
ActiveCell.Offset(1, -2).Activate
Else
ActiveCell.Offset(0, 1).Activate
ActiveCell = "=INDEX([Book21.xls]Sheet3!$V$2:$X$4,MATCH(A2,[Book21.xls]Sheet3!$V$2:$V$4,FALSE),2)"
ActiveCell.Offset(0, 1).Activate
ActiveCell = "=INDEX([Book21.xls]Sheet3!$V$2:$X$4,MATCH(A2,[Book21.xls]Sheet3!$V$2:$V$4,FALSE),3)"
ActiveCell.Offset(1, -2).Activate
UserForm1.Hide
End If
End Sub

Posted by Goneril on February 08, 2002 2:57 PM

At the start of your macro, where is the active cell? Is it always the next available cell in a paticular column?

Posted by Jay on February 08, 2002 7:42 PM

Posted by Goneril on February 08, 2002 8:09 PM

What column?

Posted by Goneril on February 08, 2002 10:09 PM

Re: What column?


Check whether this does it :-

Sub CommandButton1_Click()
Worksheets("Sheet2").Activate
With ActiveCell
.Value = UserForm1.ComboBox1.Value
If .Offset(0, 1) = "=INDEX([Book21.xls]Sheet3!$V$2:$X$4,MATCH(A" & .Row & ",[Book21.xls]Sheet3!$V$2:$V$4,FALSE),2)" Then
.Offset(-1, 1).Resize(, 2).Copy .Offset(0, 1)
Else
.Offset(0, 1) = "=INDEX([Book21.xls]Sheet3!$V$2:$X$4,MATCH(A" & .Row & ",[Book21.xls]Sheet3!$V$2:$V$4,FALSE),2)"
.Offset(0, 2) = "=INDEX([Book21.xls]Sheet3!$V$2:$X$4,MATCH(A" & .Row & ",[Book21.xls]Sheet3!$V$2:$V$4,FALSE),3)"
.Offset(1, 0).Select
UserForm1.Hide
End If
End With
End Sub