View Full Version : Solved: Assist. Placing Code in Correct Location

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

Bob Phillips
08-06-2007, 12:21 AM
IS this not a problem anymore Doug?

08-06-2007, 05:08 AM
No not a problem any longer, thanks though.
I had to place my Public Const in the Declaration area of the module. It took me a bit to figure this out.
(You will never know how much the things you have passed on to me have helped, even the small bits- like this one, was a huge help. Our first session helped me solve for this one)