using activeworkbook.worksheets

[SangelNet]

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]

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

 

[royUK]

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

 

[SangelNet]

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/)

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]

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

 

[SangelNet]

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]

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]

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]

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/)

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]

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