Function routine causes Excel to crash

[Paul Wakefield]

I have a fairly large model in which, on a particular sheet, I am trying to echo the contents from another sheet according to the row in which the active cell is situated. i.e. if the active cell is in Row 15 on sheet 1, I want to show what is in row 15 on sheet 2.

I need to do this because Sheet 1 is used for data input and the results of the input need to be seen immediately at the bottom of the input screen. Flicking from one sheet to another or using separate windows are not viable options.

I have written a small function to achieve this. This will work initially but then causes Excel to crash. Any guidance or an alternative solution would be welcome. I am using Excel 97 SR2.

To recreate the issue, create a 2 sheet file. On Sheet 1, in Cells A1 to A5, enter the numbers 1 to 5.

Create the following function:

Function rowcheck()
Application.Volatile
rowcheck = ActiveCell.row
End Function

On sheet2, in cell A1, enter =Rowcheck()-1
In cell a2, enter =offset(‘Sheet 1’!$a$1,$a$1,0)

Moving around rows 1 to 5 on sheet 2 will reflect the correct results (press F9 each time; in the real model, this is not an issue).

Now return to sheet 1 and start to enter 2 to 6 in cells b1 to b5. the sheets will group, 2 cursors will appear on the same sheet and Excel will fall over. Note: The problem occurs in my “real” model without the switch to any other sheet.


Paul

 

[Andrew Poulsom]

I also get strange happenings in Excel 2000, although it does not crash.

Can I suggest another way?

Put this in the code module for Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A" Then Exit Sub
    Range("A1").Value = Worksheets("Sheet2").Range(Target.Address).Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then
        Set Target = Target.Cells(1, 1)
    End If
    If Target.Address = "$A" Then Exit Sub
    Range("A1").Value = Worksheets("Sheet2").Range(Target.Address).Value
End Sub

This assumes that you want to use cell A1 on Sheet1 to show what's in the relative active cell on Sheet2. Change to suit.

Oh and remove your function nd references to it!

 

[Paul Wakefield]

Andrew,

Thanks for your reply. I'm afraid my VB knowledge is very very limited but I don't think your suggested routine achieves quite what I want and I'm not sure how to tweak it.

What I am trying to do is:

On Sheet 1 I have 500 rows for a user to input various data, each row specifies a particular situation and the input is over about 26 columns.

The consequences of that input are calculated (and displayed) on sheet 2. This sheet is hidden for a variety of reasons. There is however a perfect row match. i.e. input on row 351 on sheet 1 will generate output in row 351 on sheet 2.

When I am entering data on sheet 1 in row 351, I want to show at the bottom of sheet 1 (Row 510) what is being generated in row 351 on sheet 2. Similarly, when I entering input in row 352, row 510 will echo sheet 2 row 352. i.e. I will have 1 row on sheet 1 set aside that knows which is the active row on sheet 1 and echoes certain columns from the equivalent row on sheet2.

What I was originally trying to do was to use my function to identify the row number of the active cell on sheet 1 and then use that to generate an offset from A1 on sheet 2.

I can't get your function to achieve that but that is probably because I don't understand it!

I hope this hasn't confused the matter even further!

Paul

 

[Andrew Poulsom]

First I see that may code says ' "$A" Then Exit Sub' instead of ' "$A$1" Then Exit Sub'. This is a problem with posting to the Board.

The code must be placed in the code module for Sheet1 of your workbook (not in a general module).

This is what it does (for me anyway) if Sheet1 is the active sheet:

1. If I change a cell (other than A1) it puts into cell A1 what is in the same cell on Sheet2.
2. If I select a cell (other than A1) it puts into cell A1 what is in the same cell on Sheet2.
3. If I select a range it puts into cell A1 what is in the first cell in the same range on Sheet2 (unless it is A1).

So if you changed all the reference to A1 (including $A$1) to A510 (or $A$510) you would get what you want.

That's assuming that the user can see cell A510 on the screen.

 

[Paul Wakefield]

Andrew,

Many thanks. I have now got it working just fine. To refine it for my needs, I substituted Row for Value and this allowed me to generate the offset information I needed (so that I could echo the whole row (well most of it!) rather than a single cell at a time).

Paul