YellowLabPro
08-05-2007, 05:13 PM
I have a procedure that currently resides in its native workbook. I am moving all my code to one separate inclusive workbook.
The following procedure will not run when I move it to the new book
What do I need to do to get this setup properly?
Currently it does not recognize that Worksheets(sTablesheet) is declared?
Option Explicit
Public Const sCompareSheet1 As String = "PCCombined_FF"
Public Const sCompareSheet2 As String = "PCCombined_VB"
Public Const sTablesheet As String = "Table"
Public Const sVariationsColumn As String = "N"
Public Const sCorrectColumn As String = "N"
Sub ColorTable()
Dim wsTable As Worksheet
Set wsTable = Worksheets(sTablesheet)
DeleteEntries FromSheet:=sCompareSheet1, TableSheet:=wsTable
DeleteEntries FromSheet:=sCompareSheet2, TableSheet:=wsTable
End Sub
Private Sub DeleteEntries(ByVal FromSheet As String, _
ByVal TableSheet As Worksheet)
Dim lRowEnd As Long, Lrow As Long, lResult As Long
Dim rMatch1 As Range, rMatch2 As Range
Dim sCurValue As String
Dim WS As Worksheet
Set WS = Sheets(FromSheet)
Set rMatch1 = TableSheet.Columns(sVariationsColumn)
Set rMatch2 = TableSheet.Columns(sCorrectColumn)
lRowEnd = WS.Cells(Rows.Count, sVariationsColumn).End(xlUp).Row
For Lrow = lRowEnd To 4 Step -1
sCurValue = CStr(WS.Cells(Lrow, sVariationsColumn).Value)
lResult = 0
With WorksheetFunction
On Error Resume Next
lResult = .Match(sCurValue, rMatch1, 0)
If lResult = 0 Then lResult = .Match(sCurValue, rMatch2, 0)
On Error GoTo 0
End With
If lResult = 0 Then WS.Cells(Lrow, "n").ClearContents
Next Lrow
End Sub
The following procedure will not run when I move it to the new book
What do I need to do to get this setup properly?
Currently it does not recognize that Worksheets(sTablesheet) is declared?
Option Explicit
Public Const sCompareSheet1 As String = "PCCombined_FF"
Public Const sCompareSheet2 As String = "PCCombined_VB"
Public Const sTablesheet As String = "Table"
Public Const sVariationsColumn As String = "N"
Public Const sCorrectColumn As String = "N"
Sub ColorTable()
Dim wsTable As Worksheet
Set wsTable = Worksheets(sTablesheet)
DeleteEntries FromSheet:=sCompareSheet1, TableSheet:=wsTable
DeleteEntries FromSheet:=sCompareSheet2, TableSheet:=wsTable
End Sub
Private Sub DeleteEntries(ByVal FromSheet As String, _
ByVal TableSheet As Worksheet)
Dim lRowEnd As Long, Lrow As Long, lResult As Long
Dim rMatch1 As Range, rMatch2 As Range
Dim sCurValue As String
Dim WS As Worksheet
Set WS = Sheets(FromSheet)
Set rMatch1 = TableSheet.Columns(sVariationsColumn)
Set rMatch2 = TableSheet.Columns(sCorrectColumn)
lRowEnd = WS.Cells(Rows.Count, sVariationsColumn).End(xlUp).Row
For Lrow = lRowEnd To 4 Step -1
sCurValue = CStr(WS.Cells(Lrow, sVariationsColumn).Value)
lResult = 0
With WorksheetFunction
On Error Resume Next
lResult = .Match(sCurValue, rMatch1, 0)
If lResult = 0 Then lResult = .Match(sCurValue, rMatch2, 0)
On Error GoTo 0
End With
If lResult = 0 Then WS.Cells(Lrow, "n").ClearContents
Next Lrow
End Sub