Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet selection help
HI
Hope that someone can help. The following looks up a field in workbook 1, then searches workbook 2 for that field. Then returns the price from the adjacent cell back to workbook 1. My problem is that while it does find the correct sheet/cell. When it ruturns that value is uses the correct cell ref, but selects from the sheet that the workbook was last saved from, not the sheet the item was found on. Can anyone advise what I need to do to get it to select from the correct sheet. The plan is that it will finally run through the complete item list in workbook 1, but just now it is set to look at cell A5 only Thanks ------------------------------------------- Sub Findinworkbook() Dim rng As Range, sh As Worksheet Dim vTarget As Variant, fAddr As String, sAddr As String, fDesc As String Dim lookfor As String Dim selectcell As String sAddr = "A5" lookfor = Range(sAddr).Value Workbooks.Open ("workbook 2") vTarget = lookfor For Each sh In ActiveWorkbook.Worksheets With sh.Cells Set rng = .Find(vTarget, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then fAddr = rng.Address Do Range(rng.Address).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate Selection.Copy Workbooks("workbook 1").Activate Range(sAddr).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=2).Activate ActiveSheet.Paste Set rng = .FindNext(rng) Loop While rng.Address < fAddr End If End With Next sh End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet selection help
I don't replicate the problem, but try changing
Range(rng.Address).Offset(rowoffset:=0, columnoffset:=1).Activate to sh.Range(rng.Address).Offset(rowoffset:=0, columnoffset:=1).Activate -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Craig McK" wrote in message ... HI Hope that someone can help. The following looks up a field in workbook 1, then searches workbook 2 for that field. Then returns the price from the adjacent cell back to workbook 1. My problem is that while it does find the correct sheet/cell. When it ruturns that value is uses the correct cell ref, but selects from the sheet that the workbook was last saved from, not the sheet the item was found on. Can anyone advise what I need to do to get it to select from the correct sheet. The plan is that it will finally run through the complete item list in workbook 1, but just now it is set to look at cell A5 only Thanks ------------------------------------------- Sub Findinworkbook() Dim rng As Range, sh As Worksheet Dim vTarget As Variant, fAddr As String, sAddr As String, fDesc As String Dim lookfor As String Dim selectcell As String sAddr = "A5" lookfor = Range(sAddr).Value Workbooks.Open ("workbook 2") vTarget = lookfor For Each sh In ActiveWorkbook.Worksheets With sh.Cells Set rng = .Find(vTarget, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then fAddr = rng.Address Do Range(rng.Address).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate Selection.Copy Workbooks("workbook 1").Activate Range(sAddr).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=2).Activate ActiveSheet.Paste Set rng = .FindNext(rng) Loop While rng.Address < fAddr End If End With Next sh End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet selection help
Hi Bob
Thanks for that, but having a similar problem with this one. As follows:- If the cell is in the worksheet that was saved it works fine If the cell is in another worksheet the macro fails at that line. Just to confirm using Excel 2003 in case the VBA changed. Any thoughts "Bob Phillips" wrote: I don't replicate the problem, but try changing Range(rng.Address).Offset(rowoffset:=0, columnoffset:=1).Activate to sh.Range(rng.Address).Offset(rowoffset:=0, columnoffset:=1).Activate -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Craig McK" wrote in message ... HI Hope that someone can help. The following looks up a field in workbook 1, then searches workbook 2 for that field. Then returns the price from the adjacent cell back to workbook 1. My problem is that while it does find the correct sheet/cell. When it ruturns that value is uses the correct cell ref, but selects from the sheet that the workbook was last saved from, not the sheet the item was found on. Can anyone advise what I need to do to get it to select from the correct sheet. The plan is that it will finally run through the complete item list in workbook 1, but just now it is set to look at cell A5 only Thanks ------------------------------------------- Sub Findinworkbook() Dim rng As Range, sh As Worksheet Dim vTarget As Variant, fAddr As String, sAddr As String, fDesc As String Dim lookfor As String Dim selectcell As String sAddr = "A5" lookfor = Range(sAddr).Value Workbooks.Open ("workbook 2") vTarget = lookfor For Each sh In ActiveWorkbook.Worksheets With sh.Cells Set rng = .Find(vTarget, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then fAddr = rng.Address Do Range(rng.Address).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate Selection.Copy Workbooks("workbook 1").Activate Range(sAddr).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=2).Activate ActiveSheet.Paste Set rng = .FindNext(rng) Loop While rng.Address < fAddr End If End With Next sh End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet selection help
Rewriting your code a bit:
Sub Findinworkbook33() Dim rng As Range, sh As Worksheet, Wks As Worksheet, Wbk As Workbook Dim vTarget As Variant, fAddr As String, sAddr As String, fDesc As String Dim lookfor As String, selectcell As String, i As Long Application.ScreenUpdating = False Set Wks = ActiveSheet sAddr = "A5" Set Wbk = ActiveWorkbook lookfor = Wks.Range(sAddr).Value ' value from [ActiveSheet!A5] Workbooks.Open "workbook 2" ' now the new activeworkbook i = 5 'counter for your paste vTarget = lookfor ' what is *lookfor* for? For Each sh In ActiveWorkbook.Worksheets With sh.Cells Set rng = .Find(vTarget, LookIn:=xlValues, lookat:=xlWhole, _ searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then fAddr = rng.Address Do 'copies from rng's adjacent found cell to [C5] in 1st wbook rng(1, 2).Copy Wks.Cells(i, 3) i = i + 1 ' next found value goes to [C6] Set rng = .FindNext(rng) Loop While rng.Address < fAddr End If End With Next sh 'returning to the original sheet & book Application.Goto Wks.Cells(1, 1), True End Sub Regards Robert McCurdy "Craig McK" wrote in message ... Hi Bob Thanks for that, but having a similar problem with this one. As follows:- If the cell is in the worksheet that was saved it works fine If the cell is in another worksheet the macro fails at that line. Just to confirm using Excel 2003 in case the VBA changed. Any thoughts "Bob Phillips" wrote: I don't replicate the problem, but try changing Range(rng.Address).Offset(rowoffset:=0, columnoffset:=1).Activate to sh.Range(rng.Address).Offset(rowoffset:=0, columnoffset:=1).Activate -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Craig McK" wrote in message ... HI Hope that someone can help. The following looks up a field in workbook 1, then searches workbook 2 for that field. Then returns the price from the adjacent cell back to workbook 1. My problem is that while it does find the correct sheet/cell. When it ruturns that value is uses the correct cell ref, but selects from the sheet that the workbook was last saved from, not the sheet the item was found on. Can anyone advise what I need to do to get it to select from the correct sheet. The plan is that it will finally run through the complete item list in workbook 1, but just now it is set to look at cell A5 only Thanks ------------------------------------------- Sub Findinworkbook() Dim rng As Range, sh As Worksheet Dim vTarget As Variant, fAddr As String, sAddr As String, fDesc As String Dim lookfor As String Dim selectcell As String sAddr = "A5" lookfor = Range(sAddr).Value Workbooks.Open ("workbook 2") vTarget = lookfor For Each sh In ActiveWorkbook.Worksheets With sh.Cells Set rng = .Find(vTarget, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then fAddr = rng.Address Do Range(rng.Address).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate Selection.Copy Workbooks("workbook 1").Activate Range(sAddr).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=2).Activate ActiveSheet.Paste Set rng = .FindNext(rng) Loop While rng.Address < fAddr End If End With Next sh End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet selection help
Okay done some more testing. From my very limited knowledge what seems to be
happening is that the find command does not activate each sheet when it searches through it. Any idea how I activate the correct sheet based on the macro below "Craig McK" wrote: Hi Bob Thanks for that, but having a similar problem with this one. As follows:- If the cell is in the worksheet that was saved it works fine If the cell is in another worksheet the macro fails at that line. Just to confirm using Excel 2003 in case the VBA changed. Any thoughts "Bob Phillips" wrote: I don't replicate the problem, but try changing Range(rng.Address).Offset(rowoffset:=0, columnoffset:=1).Activate to sh.Range(rng.Address).Offset(rowoffset:=0, columnoffset:=1).Activate -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Craig McK" wrote in message ... HI Hope that someone can help. The following looks up a field in workbook 1, then searches workbook 2 for that field. Then returns the price from the adjacent cell back to workbook 1. My problem is that while it does find the correct sheet/cell. When it ruturns that value is uses the correct cell ref, but selects from the sheet that the workbook was last saved from, not the sheet the item was found on. Can anyone advise what I need to do to get it to select from the correct sheet. The plan is that it will finally run through the complete item list in workbook 1, but just now it is set to look at cell A5 only Thanks ------------------------------------------- Sub Findinworkbook() Dim rng As Range, sh As Worksheet Dim vTarget As Variant, fAddr As String, sAddr As String, fDesc As String Dim lookfor As String Dim selectcell As String sAddr = "A5" lookfor = Range(sAddr).Value Workbooks.Open ("workbook 2") vTarget = lookfor For Each sh In ActiveWorkbook.Worksheets With sh.Cells Set rng = .Find(vTarget, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then fAddr = rng.Address Do Range(rng.Address).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate Selection.Copy Workbooks("workbook 1").Activate Range(sAddr).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=2).Activate ActiveSheet.Paste Set rng = .FindNext(rng) Loop While rng.Address < fAddr End If End With Next sh End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet selection help
Hi Robert
Thanks for that, I reposted just before yours came up. But you r solution works just the way I need it Thanks again Craig "Robert McCurdy" wrote: Rewriting your code a bit: Sub Findinworkbook33() Dim rng As Range, sh As Worksheet, Wks As Worksheet, Wbk As Workbook Dim vTarget As Variant, fAddr As String, sAddr As String, fDesc As String Dim lookfor As String, selectcell As String, i As Long Application.ScreenUpdating = False Set Wks = ActiveSheet sAddr = "A5" Set Wbk = ActiveWorkbook lookfor = Wks.Range(sAddr).Value ' value from [ActiveSheet!A5] Workbooks.Open "workbook 2" ' now the new activeworkbook i = 5 'counter for your paste vTarget = lookfor ' what is *lookfor* for? For Each sh In ActiveWorkbook.Worksheets With sh.Cells Set rng = .Find(vTarget, LookIn:=xlValues, lookat:=xlWhole, _ searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then fAddr = rng.Address Do 'copies from rng's adjacent found cell to [C5] in 1st wbook rng(1, 2).Copy Wks.Cells(i, 3) i = i + 1 ' next found value goes to [C6] Set rng = .FindNext(rng) Loop While rng.Address < fAddr End If End With Next sh 'returning to the original sheet & book Application.Goto Wks.Cells(1, 1), True End Sub Regards Robert McCurdy "Craig McK" wrote in message ... Hi Bob Thanks for that, but having a similar problem with this one. As follows:- If the cell is in the worksheet that was saved it works fine If the cell is in another worksheet the macro fails at that line. Just to confirm using Excel 2003 in case the VBA changed. Any thoughts "Bob Phillips" wrote: I don't replicate the problem, but try changing Range(rng.Address).Offset(rowoffset:=0, columnoffset:=1).Activate to sh.Range(rng.Address).Offset(rowoffset:=0, columnoffset:=1).Activate -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Craig McK" wrote in message ... HI Hope that someone can help. The following looks up a field in workbook 1, then searches workbook 2 for that field. Then returns the price from the adjacent cell back to workbook 1. My problem is that while it does find the correct sheet/cell. When it ruturns that value is uses the correct cell ref, but selects from the sheet that the workbook was last saved from, not the sheet the item was found on. Can anyone advise what I need to do to get it to select from the correct sheet. The plan is that it will finally run through the complete item list in workbook 1, but just now it is set to look at cell A5 only Thanks ------------------------------------------- Sub Findinworkbook() Dim rng As Range, sh As Worksheet Dim vTarget As Variant, fAddr As String, sAddr As String, fDesc As String Dim lookfor As String Dim selectcell As String sAddr = "A5" lookfor = Range(sAddr).Value Workbooks.Open ("workbook 2") vTarget = lookfor For Each sh In ActiveWorkbook.Worksheets With sh.Cells Set rng = .Find(vTarget, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then fAddr = rng.Address Do Range(rng.Address).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate Selection.Copy Workbooks("workbook 1").Activate Range(sAddr).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=2).Activate ActiveSheet.Paste Set rng = .FindNext(rng) Loop While rng.Address < fAddr End If End With Next sh End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet selection help Next Part
Robert
Last question for today (Probably!) I also want to copy a description field that resides in column of the selected row. I can't work out how to make the rng(1,2).copy...... an absolute column, can you help Thanks "Robert McCurdy" wrote: Rewriting your code a bit: Sub Findinworkbook33() Dim rng As Range, sh As Worksheet, Wks As Worksheet, Wbk As Workbook Dim vTarget As Variant, fAddr As String, sAddr As String, fDesc As String Dim lookfor As String, selectcell As String, i As Long Application.ScreenUpdating = False Set Wks = ActiveSheet sAddr = "A5" Set Wbk = ActiveWorkbook lookfor = Wks.Range(sAddr).Value ' value from [ActiveSheet!A5] Workbooks.Open "workbook 2" ' now the new activeworkbook i = 5 'counter for your paste vTarget = lookfor ' what is *lookfor* for? For Each sh In ActiveWorkbook.Worksheets With sh.Cells Set rng = .Find(vTarget, LookIn:=xlValues, lookat:=xlWhole, _ searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then fAddr = rng.Address Do 'copies from rng's adjacent found cell to [C5] in 1st wbook rng(1, 2).Copy Wks.Cells(i, 3) i = i + 1 ' next found value goes to [C6] Set rng = .FindNext(rng) Loop While rng.Address < fAddr End If End With Next sh 'returning to the original sheet & book Application.Goto Wks.Cells(1, 1), True End Sub Regards Robert McCurdy "Craig McK" wrote in message ... Hi Bob Thanks for that, but having a similar problem with this one. As follows:- If the cell is in the worksheet that was saved it works fine If the cell is in another worksheet the macro fails at that line. Just to confirm using Excel 2003 in case the VBA changed. Any thoughts "Bob Phillips" wrote: I don't replicate the problem, but try changing Range(rng.Address).Offset(rowoffset:=0, columnoffset:=1).Activate to sh.Range(rng.Address).Offset(rowoffset:=0, columnoffset:=1).Activate -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Craig McK" wrote in message ... HI Hope that someone can help. The following looks up a field in workbook 1, then searches workbook 2 for that field. Then returns the price from the adjacent cell back to workbook 1. My problem is that while it does find the correct sheet/cell. When it ruturns that value is uses the correct cell ref, but selects from the sheet that the workbook was last saved from, not the sheet the item was found on. Can anyone advise what I need to do to get it to select from the correct sheet. The plan is that it will finally run through the complete item list in workbook 1, but just now it is set to look at cell A5 only Thanks ------------------------------------------- Sub Findinworkbook() Dim rng As Range, sh As Worksheet Dim vTarget As Variant, fAddr As String, sAddr As String, fDesc As String Dim lookfor As String Dim selectcell As String sAddr = "A5" lookfor = Range(sAddr).Value Workbooks.Open ("workbook 2") vTarget = lookfor For Each sh In ActiveWorkbook.Worksheets With sh.Cells Set rng = .Find(vTarget, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then fAddr = rng.Address Do Range(rng.Address).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate Selection.Copy Workbooks("workbook 1").Activate Range(sAddr).Select ActiveCell.Offset(rowoffset:=0, columnoffset:=2).Activate ActiveSheet.Paste Set rng = .FindNext(rng) Loop While rng.Address < fAddr End If End With Next sh End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate active sheet on sheet selection | Excel Programming | |||
Selection from list on main sheet from suplemental sheet in same w | New Users to Excel | |||
sheet tabs and selection | Excel Programming | |||
search in a sheet and selection? | Excel Discussion (Misc queries) | |||
Sheet selection | Excel Programming |