Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass worksheet object name to a class module
Hello
I am hoping to get some insight or help into a problem I have using class modules. At the moment i am passing some data to the class via a loop which skips through all the data in my list and performs an action on it. The problem i have is that my list is set out on different sheet tabs in Excel, so that I can run through the first tab but I then need to switch to the next tab so that my results from the class module can then be referenced to this tab. My loop is here........ For Each Worksheet In Worksheets If Worksheet.Name Like "Outstanding*" Then sName = Worksheet.CodeName Set sName = Worksheet Set mycell = sName.Range("A4") j = mycell.CurrentRegion.Rows.Count + 2 aRng = sName.Cells.Range("A4:A" & j) For Each mycell In aRng oEvent.rngnum = i oEvent.name = mycell oEvent.New_Event i = i + 1 Next mycell End If Next Worksheet Feeding ino the class module here............ Public Sub New_Event() Dim sURL As String Dim i As Integer Dim mycell As Excel.Range Dim j As Integer, iOffset As Integer With Sheet3 Sheet3.Cells.Range("A3:A250").ClearContents sURL = "http://brain.fserver.federal.com" With .QueryTables.Add(Connection:= _ "URL;" & sURL, Destination:=Range("DA3")) .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebFormatting = xlWebFormattingNone .WebSelectionType = xlAllTables .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End With Set mycell = .Range("DB6") iOffset = -1 If mycell.Value < "Event" Then Set mycell = .Range("DC6") iOffset = -2 If mycell.Value < "Event" Then Set mycell = .Range("DD6") iOffset = -3 End If End If j = mycell.CurrentRegion.Rows.Count For i = 0 To j If mycell.Offset(i, 0).Value = "" Then GoTo datanotavail If InStr(mycell.Offset(i, 0), SrchText) < 0 Then .Cells.Range("R4").Offset(iRow, 0).Value = mycell.Offset(i, 0) .Cells.Range("Q4").Offset(iRow, 0).Value = mycell.Offset(i, iOffset) GoTo datanotavail End If Next i End With datanotavail: End Sub In a nutshell, what I want to do is change the Sheet3 to any sheet tab depending on where the list of data is held. Does anyone have any ideas on how to pass the sheet tab name as a variable, I have tried many ways but always erroring with missing object variable etc. Thanks very much |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass worksheet object name to a class module
Hi
At the top of the code module containing the loop put: Public wST as Worksheet This can now be seen by the class module. Rewrite your loop as For Each Worksheet In Worksheets If Worksheet.Name Like "Outstanding*" Then sName = Worksheet.CodeName Set wSH = Worksheets(sName) Set mycell = sName.Range("A4") j = mycell.CurrentRegion.Rows.Count + 2 aRng = sName.Cells.Range("A4:A" & j) For Each mycell In aRng oEvent.rngnum = i oEvent.name = mycell oEvent.New_Event i = i + 1 Next mycell End If Next Worksheet In your class module do: With wSH .Cells.Range("A3:A250").ClearContents etc regards Paul On May 9, 12:34 pm, wrote: Hello I am hoping to get some insight or help into a problem I have using class modules. At the moment i am passing some data to the class via a loop which skips through all the data in my list and performs an action on it. The problem i have is that my list is set out on different sheet tabs in Excel, so that I can run through the first tab but I then need to switch to the next tab so that my results from the class module can then be referenced to this tab. My loop is here........ For Each Worksheet In Worksheets If Worksheet.Name Like "Outstanding*" Then sName = Worksheet.CodeName Set sName = Worksheet Set mycell = sName.Range("A4") j = mycell.CurrentRegion.Rows.Count + 2 aRng = sName.Cells.Range("A4:A" & j) For Each mycell In aRng oEvent.rngnum = i oEvent.name = mycell oEvent.New_Event i = i + 1 Next mycell End If Next Worksheet Feeding ino the class module here............ Public Sub New_Event() Dim sURL As String Dim i As Integer Dim mycell As Excel.Range Dim j As Integer, iOffset As Integer With Sheet3 Sheet3.Cells.Range("A3:A250").ClearContents sURL = "http://brain.fserver.federal.com" With .QueryTables.Add(Connection:= _ "URL;" & sURL, Destination:=Range("DA3")) .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebFormatting = xlWebFormattingNone .WebSelectionType = xlAllTables .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End With Set mycell = .Range("DB6") iOffset = -1 If mycell.Value < "Event" Then Set mycell = .Range("DC6") iOffset = -2 If mycell.Value < "Event" Then Set mycell = .Range("DD6") iOffset = -3 End If End If j = mycell.CurrentRegion.Rows.Count For i = 0 To j If mycell.Offset(i, 0).Value = "" Then GoTo datanotavail If InStr(mycell.Offset(i, 0), SrchText) < 0 Then .Cells.Range("R4").Offset(iRow, 0).Value = mycell.Offset(i, 0) .Cells.Range("Q4").Offset(iRow, 0).Value = mycell.Offset(i, iOffset) GoTo datanotavail End If Next i End With datanotavail: End Sub In a nutshell, what I want to do is change the Sheet3 to any sheet tab depending on where the list of data is held. Does anyone have any ideas on how to pass the sheet tab name as a variable, I have tried many ways but always erroring with missing object variable etc. Thanks very much |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enabling the Filter button in a class module/protected worksheet | Excel Programming | |||
To access a previous object via class module | Excel Programming | |||
URGENT : How to get CHECKBOX object value from a Excel Worksheet by a C# Class | Excel Programming | |||
Variable from a sheet module in a class module in XL XP | Excel Programming | |||
Pass a variable from a class module | Excel Programming |