Consulting

Results 1 to 3 of 3

Thread: Solved: Assist. Placing Code in Correct Location

  1. #1

    Solved: Assist. Placing Code in Correct Location

    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?

    [vba]
    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
    [/vba]
    Last edited by YellowLabPro; 08-05-2007 at 05:26 PM.
    my site: www.ecboardco.com
    was built w/ a majority of the assistance from the board members here... thanks VBAX.

    Just because I see something, doesn't mean that what's actually happening is what I see.

    You don't get from 0-90 by standing still!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    IS this not a problem anymore Doug?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    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)
    my site: www.ecboardco.com
    was built w/ a majority of the assistance from the board members here... thanks VBAX.

    Just because I see something, doesn't mean that what's actually happening is what I see.

    You don't get from 0-90 by standing still!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •