ExcelBanter - using activeworkbook.worksheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - using activeworkbook.worksheets (https://www.excelbanter.com/excel-programming/421039-using-activeworkbook-worksheets.html)

SangelNet

using activeworkbook.worksheets

I have been looking for the correct way to use the following

For Each wsheet In ActiveWorkbook.Sheet(Sheet280, Sheet283,
Sheet284, Sheet285, Sheet286, Sheet287, Sheet288)

how can i use activeworkbook.worksheets and specify certain sheets in
the workbook.

the way i am using it is not giving errors but is not returning
anything. here is the full code :

Function vlookallsheets(Look_Value As Variant, Tble_Array As Range, _
Col_num As Integer, Optional Range_look As
Boolean)

'Use VLOOKUP to Look across ALL Worksheets and stops _
at the first match found.
'''''''''''''''''''''''''''''''''''''''''''''''''
Application.ScreenUpdating = False

Dim wSheet As Worksheet
Dim vFound

On Error Resume Next

For Each wSheet In ActiveWorkbook.Worksheet(Sheet280, Sheet283,
Sheet284, Sheet285, Sheet286, Sheet287, Sheet288)
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup _
(Look_Value, Tble_Array, _
Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then Exit For
Next wSheet

Set Tble_Array = Nothing
vlookallsheets = vFound
Application.ScreenUpdating = True

End Function

Thnx

Gary''s Student

using activeworkbook.worksheets

Sub marine()
For Each s In Sheets(Array("Sheet1", "Sheet2"))
s.Activate
Next
End Sub

--
Gary''s Student - gsnu2007k

royUK[_86_]

using activeworkbook.worksheets

I can't see where you give values to Look_Value, ColNum etc

I would use the VBA .Find method. Maybe

Code:
--------------------
Sub LookUpAllSheets()
Application.ScreenUpdating = False

Dim wSheet As Worksheet
Dim tbl As Range
Dim vFound As Range
Dim sFind As String
On Error Resume Next

sFind = InputBox("Enter search string")
If Len(sFind) = 0 Then
MsgBox "No search string entered", vbCritical, "Input required"
Exit Sub
End If
For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set tbl = .UsedRange
Set vFound = tbl.Find(sFind, LookIn:=xlValues)
If Not vFound Is Nothing Then Exit For
End With
Next wSheet
MsgBox vFound.Value
On Error GoTo 0
Set tbl = Nothing
Set wSheet = Nothing
Application.ScreenUpdating = True

End Sub
--------------------

--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38789

SangelNet

using activeworkbook.worksheets

On Dec 9, 1:16*pm, royUK wrote:
I can't see where you give values to Look_Value, ColNum etc

I would use the VBA .Find method. Maybe

Code:
--------------------
* * Sub LookUpAllSheets()
* Application.ScreenUpdating = False

* Dim wSheet As Worksheet
* Dim tbl * *As Range
* Dim vFound As Range
* Dim sFind *As String
* On Error Resume Next

* sFind = InputBox("Enter search string")
* If Len(sFind) = 0 Then
* MsgBox "No search string entered", vbCritical, "Input required"
* Exit Sub
* End If
* For Each wSheet In ActiveWorkbook.Worksheets
* With wSheet
* Set tbl = .UsedRange
* Set vFound = tbl.Find(sFind, LookIn:=xlValues)
* If Not vFound Is Nothing Then Exit For
* End With
* Next wSheet
* MsgBox vFound.Value
* On Error GoTo 0
* Set tbl = Nothing
* Set wSheet = Nothing
* Application.ScreenUpdating = True

* End Sub
--------------------

--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile:http://www.thecodecage.com/forumz/member.php?userid=15
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=38789

what im trying to do is, to use the code for vlookup to look across
all sheets but narrow it down to some specific sheets.

cant seem to do that yet! Any suggestions?

royUK[_87_]

using activeworkbook.worksheets

Something like this

Code:
--------------------

Sub LookUpAllSheets()
Application.ScreenUpdating = False

Dim wSheet As Worksheet
Dim tbl As Range
Dim vFound As Range
Dim sFind As String
On Error Resume Next

sFind = InputBox("Enter search string")
If Len(sFind) = 0 Then
MsgBox "No search string entered", vbCritical, "Input required"
Exit Sub
End If
For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Select Case wSheet.Name
Case "Sheet280", "Sheet283", "Sheet284", "Sheet285", "Sheet286", "Sheet287", "Sheet288"
Set tbl = .UsedRange
Set vFound = tbl.Find(sFind, LookIn:=xlValues)
If Not vFound Is Nothing Then Exit For
Case Else 'do nothing
End Select
End With
Next wSheet
MsgBox vFound.Value
On Error GoTo 0
Set tbl = Nothing
Set wSheet = Nothing
Application.ScreenUpdating = True

End Sub
--------------------

--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38789

SangelNet

using activeworkbook.worksheets

On Dec 9, 2:24*pm, SangelNet wrote:
On Dec 9, 1:16*pm, royUK wrote:

I can't see where you give values to Look_Value, ColNum etc

I would use the VBA .Find method. Maybe

Code:
--------------------
* * Sub LookUpAllSheets()
* Application.ScreenUpdating = False

* Dim wSheet As Worksheet
* Dim tbl * *As Range
* Dim vFound As Range
* Dim sFind *As String
* On Error Resume Next

* sFind = InputBox("Enter search string")
* If Len(sFind) = 0 Then
* MsgBox "No search string entered", vbCritical, "Input required"
* Exit Sub
* End If
* For Each wSheet In ActiveWorkbook.Worksheets
* With wSheet
* Set tbl = .UsedRange
* Set vFound = tbl.Find(sFind, LookIn:=xlValues)
* If Not vFound Is Nothing Then Exit For
* End With
* Next wSheet
* MsgBox vFound.Value
* On Error GoTo 0
* Set tbl = Nothing
* Set wSheet = Nothing
* Application.ScreenUpdating = True

* End Sub
--------------------

--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile:http://www.thecodecage.com/forumz/member.php?userid=15
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=38789

what im trying to do is, to use the code for vlookup to look across
all sheets but narrow it down to some specific sheets.

cant seem to do that yet! Any suggestions?

basically what i would like is , that out of 100 sheets vlookup JUST
the firt 5.

SangelNet

using activeworkbook.worksheets

SangelNet

using activeworkbook.worksheets

SangelNet

using activeworkbook.worksheets

On Dec 9, 2:40*pm, royUK wrote:
Something like this

Code:
--------------------

* Sub LookUpAllSheets()
* Application.ScreenUpdating = False

* Dim wSheet As Worksheet
* Dim tbl * *As Range
* Dim vFound As Range
* Dim sFind *As String
* On Error Resume Next

* sFind = InputBox("Enter search string")
* If Len(sFind) = 0 Then
* MsgBox "No search string entered", vbCritical, "Input required"
* Exit Sub
* End If
* For Each wSheet In ActiveWorkbook.Worksheets
* With wSheet
* Select Case wSheet.Name
* Case "Sheet280", "Sheet283", "Sheet284", "Sheet285", "Sheet286", "Sheet287", "Sheet288"
* Set tbl = .UsedRange
* Set vFound = tbl.Find(sFind, LookIn:=xlValues)
* If Not vFound Is Nothing Then Exit For
* Case Else 'do nothing
* End Select
* End With
* Next wSheet
* MsgBox vFound.Value
* On Error GoTo 0
* Set tbl = Nothing
* Set wSheet = Nothing
* Application.ScreenUpdating = True

* End Sub
--------------------

--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile:http://www.thecodecage.com/forumz/member.php?userid=15
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=38789

Hi guys, thnx for the help. Im just not getting it to work.
(frustrated). the original code i posted works fine. just cant get it
to look into the specific sheets.

thanx again!!

royUK[_90_]

using activeworkbook.worksheets

Try changing the sheet names in the code that I supplied, or do you mean
the first 5 sheets viewed in tabs? This could be dangerous if a user
moved the sheets around

--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38789

All times are GMT +1. The time now is 12:31 PM.