Cursor Position

Posted by Yuniati on May 07, 2001 5:05 PM

Hi,

I'm in the middle of making a macro for Excel 2000.
I have two questions.

1. I wanted a return value/string of current cursor position.
For instance, my cursor is now at B200 (and always change). How do I write the macro program for this?

2. Related to the first question, I wanted to run a Histogram (from Tools - Data Analysis - Histogram) for B1:B200. Below is my script, it means that the Histogram add-ins analyze data in range B1:B200 and put the result in F1. How do I change the range automatically if for the next data set the range are different?

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$B$1:$B$200"), _
ActiveSheet.Range("$F$1"), , False, False, False, True

Thanks in advance. Yuniati.

Posted by Sean on May 08, 2001 8:09 AM

Hi,

I have pasted below an extract from one of my files for speed, otherwise I will not be able to fully answer your query.

It uses the currently selected cell reference:
curref = Selection.Address
curref now holds the value of the currently selected cell.
This which was part(1) of your question.

In my program I need to find the last used line in a table and add a line below this by copying the formulas in the previous line.

you could equally well adapt it to the range for your Addin function using a different combination of & (concatenate function)

I know I have not answered your question exactly, but short of having your table at least this gives you a start...It shows you how how to manipulate the reference.

If not come back to me directly and I'll see if I can help more.

Sean
s-o-s@lineone.net

========
'Next bit finds the last completed row, Inserts row afterwards
'Copies the last complete 1 over the top.

Sheets("Sales Pipeline").Select
Range("E6").Select
Selection.End(xlDown).Select
curref = Selection.Address
nxtref = "$B$" & (Right(curref, Len(curref) - 3) + 1)
Range(nxtref).Select
Selection.EntireRow.Insert
Lstrow = Right(curref, Len(curref) - 3)
cpyrow = Lstrow & ":" & Lstrow
pstrow = (Lstrow + 1) & ":" & (Lstrow + 1)
Rows(cpyrow).Select
Selection.Copy
Rows(pstrow).Select
ActiveSheet.Paste
Application.CutCopyMode = False

======