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

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

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

I can't answer the question

"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

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

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

My error,
change that line to

set rng = Evaluate(s)

--
Regards,
Tom Ogilvy


"Brian" wrote:

> 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

Hooray, nearly there. Thank you for your time, only one minor problem. When
it has copied the last of the range successfully, it then throws up the 424
again, however when in debug it says that "cannot jump to 'Evaluate' because
it is hidden"

Do I need to switch it off before it tries to run when the last c has been
processed?

Sorry to be a pain,
--
Brian McCaffery


"Tom Ogilvy" wrote:

> My error,
> change that line to
>
> set rng = Evaluate(s)
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Brian" wrote:
>
> > 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

Not sure about that error message, but perhaps this:

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
Set rng = Nothing
on Error Resume Next
s = Replace(c.formula,"=","")
set rng = Evaluate(s)
On Error goto 0
if not rng is nothing then
rng.entireRow.copy
Sheets("Report").Range("A2") _
.offset(i,0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
end if
Next c
End Select
End Sub

--
Regards,
Tom Ogilvy


"Brian" wrote:

> Hooray, nearly there. Thank you for your time, only one minor problem. When
> it has copied the last of the range successfully, it then throws up the 424
> again, however when in debug it says that "cannot jump to 'Evaluate' because
> it is hidden"
>
> Do I need to switch it off before it tries to run when the last c has been
> processed?
>
> Sorry to be a pain,
> --
> Brian McCaffery
>
>
> "Tom Ogilvy" wrote:
>
> > My error,
> > change that line to
> >
> > set rng = Evaluate(s)
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Brian" wrote:
> >
> > > 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

Tom,

Thanks for all your help. I was just about to post that I had found a
solution when I saw your last post. It all works a treat now. I added an If
c.value ="" then end Else Etc.

Thank you and Joel for taking the time to help. My wife, an excel VBA guru,
has been unavailable to help me this last week and a bit, but I have achieved
quite a lot with the help I have recieved here.

All the best,


--
Brian McCaffery


"Tom Ogilvy" wrote:

> Not sure about that error message, but perhaps this:
>
> 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
> Set rng = Nothing
> on Error Resume Next
> s = Replace(c.formula,"=","")
> set rng = Evaluate(s)
> On Error goto 0
> if not rng is nothing then
> rng.entireRow.copy
> Sheets("Report").Range("A2") _
> .offset(i,0) _
> .PasteSpecial Paste:= _
> xlPasteAll, _
> Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
> i = i + 1
> end if
> Next c
> End Select
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Brian" wrote:
>
> > Hooray, nearly there. Thank you for your time, only one minor problem. When
> > it has copied the last of the range successfully, it then throws up the 424
> > again, however when in debug it says that "cannot jump to 'Evaluate' because
> > it is hidden"
> >
> > Do I need to switch it off before it tries to run when the last c has been
> > processed?
> >
> > Sorry to be a pain,
> > --
> > Brian McCaffery
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > My error,
> > > change that line to
> > >
> > > set rng = Evaluate(s)
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Brian" wrote:
> > >
> > > > 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