Application.DoubleClick question
Tom, thanks for that. But...
When I run it, I get the message box "Runtime error '424'
In Debug, if I hover over the "set rng" it says "rng=Nothing" If I then
hover over the following [s] it says "GSOPs$A$22" which is the correct
reference for the first in the range.
If I select the definition, it jumps to the rng in the Dim statement.
Any thoughts while I search?
Thanks,
--
Brian McCaffery
"Tom Ogilvy" wrote:
Here is a guess at what you are trying to do:
It assumes the formula in the cells of refrange are like
=Sheet3!B9
or
=Sheet3!$B$9
Private Sub ComboBox1_Change()
Dim i as Long, rng as Range
Dim refrange As Range
Dim c As Range
ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1
Select Case ComboBox1.Value
Case "GSOP_0286"
Set refrange = Sheets("Sheet2").Range("A3:A20")
i = 0
For Each c In refrange
s = Replace(c.formula,"=","")
set rng = [s]
rng.entireRow.copy
Sheets("Report").Range("A2") _
.offset(i,0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
Next c
End Select
End Sub
--
Regards,
Tom Ogilvy
"Brian" wrote:
Joel,
Thanks for the reply. It actually did something.
This is my first attempt at using Excel VBA. Wish I hadn't suggested putting
together a mini form of config management.
My error was not explaining everything. The cross references in each range
could be in any one of four sheets. When I click on the cell, I can see the
sheet and column\row reference in the formula bar. Is there a way to select
the sheet to activate after the Application.Double click. If I manually
double click on the cell it takes me to the correct location.
Thanks again
--
Brian McCaffery
"Joel" wrote:
I made some changes. Changed the functtion from click to change.
Private Sub ComboBox1_Change()
ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1
Dim refrange As Range
Dim c As Range
Select Case ComboBox1.Value
Case "GSOP_0286"
Set refrange = Sheets("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
'With ActiveSheet
Sheets("sheet2").Activate
myRow = ActiveCell.Row
Sheets("Sheet2").Rows(myRow).Select
Selection.Copy
Sheets("Report").Activate
Sheets("Report").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
'End With
Next c
End Select
End Sub
"Brian" wrote:
Hello all,
I have a sheet containing a matrix of Cross references. Using the case
statement, I would like to be able to loop through each cell in the range,
double click to go to the sheet containing the data, select the row, then
paste into a report sheet. Guess what -I can't get it to work. Any
suggestions please.
This is one case statement from the code:
Private Sub ComboBox1_click()
ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1
Dim refrange As Range
Dim c As Range
Select Case ComboBox1.Value
Case "GSOP_0286"
Set refrange = Sheets("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
With ActiveSheet
myRow = ActiveCell.Row
Rows(myRow).Select
Selection.Copy
Sheets("Report").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
End With
Next c
Thanks,
--
Brian McCaffery
|