0

I have a macro that makes changes in a big list. Currently I show a msgbox() for every change I make, which is rather intruding. Instead i want to write all these changes to a log file.

But I don't want to have to remember on which line I am in the log file if I can avoid it.

So I need a way to write to the CurrentCell in the log file, without activating it, then move that CurrentCell one row down.

Is there an easy way to do this?

thanx, Gijs.

Gijs vK
  • 21
  • 1
  • 2
  • 4
  • Yes, there is a way to do this. Please edit your question to include relevant pieces of your code. Also, [see this](http://stackoverflow.com/help/on-topic) – ARich Feb 06 '14 at 17:39
  • 1
    You might want to take a look at [this](http://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro) as well. – Graham Anderson Feb 06 '14 at 18:22

1 Answers1

-1

To get the current cell in a worksheet, I believe you need to activate it, but you can turn off screen updating so that the user isn't actually aware of the change in focus.

The code below does this, getting the address of the active cell on Sheet 2 of a workbook, outputting this to the debug menu, then moving the cursor on to the next row, before swapping back to the current sheet and turning screens updates:

I hope this helps, Andrew

Sub MoveToNextCell()

    'Turn off screen updates
    Application.ScreenUpdating = False

    'Get the active cell in worksheet 2
    ThisWorkbook.Sheets(2).Activate

    'Execute the movement
    Debug.Print "Pre-movement address: " & Application.ActiveCell.Address
    ThisWorkbook.ActiveSheet.Cells(Application.ActiveCell.Row + 1, Application.ActiveCell.Column).Select
    Debug.Print "Post-movement address: " & Application.ActiveCell.Address

    'revert back to the original worksheet
    ThisWorkbook.Sheets(1).Activate

    'Turn screenupdates back on
    Application.ScreenUpdating = True

End Sub
Andrew - Eversight Ltd
  • 347
  • 1
  • 4
  • The OP specifically asked for a solution that does not require activating the cell. It is almost never a good idea to use the Activate method simply to use the ActiveCell or Selection object. – Rachel Hettinger Feb 06 '14 at 21:26
  • Yeah, but the OP also said "But I don't want to have to remember on which line I am in the log file if I can avoid it." which is the way I would normally do it to avoid using the activate cell method - i.e. just use an integer variable to contain the row count, incrementing after each entry. Not the way I would do it personally, but the closest I could get to meeting the stated requirement. – Andrew - Eversight Ltd Feb 07 '14 at 11:38