Sheet selection help

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Robert McCurdy]

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

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

 

[Guest]

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

 

[Guest]

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

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

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

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)

 

[Guest]

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

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

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

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)