Consulting

Results 1 to 9 of 9

Thread: code problem

  1. #1

    code problem

    Hey all,

    I get a bug when i try to alter this piece of code so that instead of "A2" in rng1 there should be "A5". when i run it, the code is not able to call the userform called udvalg..

    [VBA]
    Private Sub userform_initialize()

    Dim UniqueList() As String
    Dim x As Long
    Dim c As range
    Dim Unique As Boolean
    Dim y As Long
    Dim Rng1 As range
    Dim Rng2 As range
    Dim Rng3 As range
    Dim Rng4 As range

    'Opgavenavn
    Set Rng1 = range(Sheets("Indtastning af ny opgave").range("A2"), Sheets("Indtastning af ny opgave").range("A" & Rows.Count).End(xlUp))
    y = 1

    ReDim UniqueList(1 To Rng1.Rows.Count)

    For Each c In Rng1
    If Not c.Value = vbNullString Then
    Unique = True
    For x = 1 To y
    If UniqueList(x) = c.Text Then
    Unique = False
    End If
    Next
    If Unique Then
    y = y + 1
    Me.lstopg.AddItem (c.Text)
    UniqueList(y) = c.Text
    End If
    End If
    Next[/VBA]

  2. #2
    any ideas??

  3. #3
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    What error do you get? (and how does that 'set rng1 ...' line end as I can't see it?)
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    Maybe try this [VBA]Set Rng1 = Range("A2:A" & Worksheets("Indtastning af ny opgave").Range("A" & _
    Rows.Count).End(xlUp).Row)[/VBA]

  5. #5
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    Just noticed you specified the sheet for the start and end cells, but not for the range as a whole - try:
    [vba]With Sheets("Indtastning af ny opgave")
    Set Rng1 = .range(.range("A2"), .range("A" & .Rows.Count).End(xlUp))
    End With[/vba]
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    [VBA]With Worksheets("Indtastning af ny opgave")
    Set Rng1 = .Range("A2:A" & .Range("A" & _
    Rows.Count).End(xlUp).Row)
    End With[/VBA]

  7. #7
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    the code is not able to call the userform called udvalg
    Where are you actually 'calling' the userform in that code?

    As far as I can see that is code for a userform initialize event and would be triggered when the userform is shown and shouldn't be 'calling' the userform.

  8. #8
    sorry - this piece of code is runing when the userform initializes. But i call the userform from a button, and then it just does this as it is shown.

    i have similar pieces of code for rng2, 3, and 4 where it works fine. The first 3 rows of the sheets are empty, and whenever i try to start this from the first row (actually the second one, since the whole point is that i dont wanna have column names) in column "A" it dies .. (this is no problem in column "B"

  9. #9
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    And what is it that the code is meant to do?

    And what do you actually mean by this?
    the code is not able to call the userform called udvalg

Posting Permissions

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