Consulting

Results 1 to 3 of 3

Thread: Need help with this Function code

  1. #1
    VBAX Regular
    Joined
    Jan 2008
    Posts
    75
    Location

    Wink Need help with this Function code

    Hi All,

    Thanks so much for your help. You all are making my hand on in VBA better. I am having problem with a function I wrote which should only accept, A, B, Q as valid entries. For the purpose of reusability, I am required to use array as a parameter in the function.
    I wrote the following code, but is having problem and errors.

    Function MaintenanceFreq(columnname As Integer, myArray As String)
    Dim rowcount
    Dim R
    Dim myArray(1)
    rowcount = Range("A65536").End(xlUp).Row
    For R = 2 To rowcount

    strVal = Sheet1.Cells(R, columnname).Value
    If strVal <> myArray And strVal <> myArray Then
    Sheet1.Cells(R, columnname).Interior.ColorIndex = 5
    End If

    Next

    End Function

    Sub MaintenanceFreq ()
    MaintenanceFreq 17, "A", "B", "Q"
    End Sub

    Please, I would be very grateful if someone can help me out.

    Thanks

    Lucpian

  2. #2
    Hey Man, this is real messy. First, I would create an array, and then pass it. I'm pretty sure you're just passing 4 arguments rather than 1 argument and an array into your function. Secondly, change the name of your sub and function so that they're different. Third, I would use a nested to check for values in your array versus the cell value, so that your array can be of a variable length. It seems as if this is for class, so I think it would be best if you figured out how to do this rather than having one of us give you code. See where you get with that and if you have problems from there let me know.

    -B

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I answered in the other thread that has the same question.
    ____________________________________________
    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

Posting Permissions

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