Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.DoubleClick question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.DoubleClick question
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.DoubleClick question
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.DoubleClick question
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.DoubleClick question
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.DoubleClick question
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.DoubleClick question
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.DoubleClick question
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.DoubleClick question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ON.DOUBLECLICK function | Excel Discussion (Misc queries) | |||
doubleclick | Excel Worksheet Functions | |||
Disable Doubleclick | Excel Programming | |||
Before DoubleClick | Excel Programming | |||
Application.Run question | Excel Programming |