Select a cell in the middle of a macro execution
Dave,
I'm curious as to how you are selecting values using MsgBox!
Here's a way using Application.InputBox
Sub test()
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("Select your Target", Type:=8)
If Err.Number Then Err.Clear
On Error GoTo 0
If Not rng Is Nothing Then ActiveCell.Value = rng.Cells(1).Value
End Sub
Rob
"Dave Bash" wrote in message
om...
I am using Excel 2k, writing code that activates 3 files, taking data
from the first, activating a second workbook, pasting via variant
array to a designated input range, assigning a designated output range
to a variant array. The next step is to return the results to the
original workbook, which is activated and similarly, data is brought
into a range, the sheet is calculated, and the output is put into a
variant for further processing.
My issue is this - on the 3rd and final workbook, I want to paste the
contents of the variant into a matrix of results ( ie, each time I run
the code, the target will change). I wanted to know if there is a way
- other than a msgbox where I designate the address - to click on a
cell and have the values be pasted into the active cell. As I type
this, I think that using a Preserve in a Dim statement may allow me to
actually let the code end and then run a new macro, off a double click
event perhaps. But I am looking for something more efficient.
Thanks for the assistance.
|