Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using activeworkbook.worksheets
Sub marine()
For Each s In Sheets(Array("Sheet1", "Sheet2")) s.Activate Next End Sub -- Gary''s Student - gsnu2007k |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i assign the ActiveWorkbook.Worksheets to a worksheets object? | Excel Worksheet Functions | |||
How do i assign the ActiveWorkbook.Worksheets to a worksheets object? | Excel Programming | |||
Adding a sheets name to the worksheets that are copied over from an addin file to the activeworkbook | Excel Programming | |||
For Each wks In ActiveWorkbook.Worksheets bar a specific one? | Excel Discussion (Misc queries) | |||
Populating a listbox will a certain cell on multiple worksheets in the activeworkbook | Excel Programming |