Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Get value from cells range (2000)

    I want to get the value in column B and fill TEST why not work correct ????
    In this case if cells=8501 get NORD EST and insert in TEST

    piece of code:

    Dim AllCells As Range, Cell As Range
    Dim NoDupes As New Collection
    Dim i As Integer, j As Integer
    Dim Swap1, Swap2, Item
    Dim TEST As String
    Set AllCells = Sheets("TABELLA").Range("A2:A6")

    On Error Resume Next

    For Each Cell In AllCells
    NoDupes.Add Cell.Value, CStr(Cell.Value)
    'TEST = Cell(3, CStr(Cell.Value))
    Next Cell

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Get value from cells range (2000)

    Use

    TEST = Cell.Offset(0, 1).Value

    Offset(0, 1) means: move 0 rows down (i.e. stay in the same row) and move 1 column to the right (i.e. to column

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Get value from cells range (2000)

    Not sure what you want exactly.

    If you just want TEST to give the item from B as you go:

    Dim AllCells As Range, Cell As Range
    Dim NoDupes As New Collection
    Dim TEST As String
    Set AllCells = Sheets("TABELLA").Range("A2:A6")
    On Error Resume Next
    For Each Cell In AllCells
    NoDupes.Add Cell.Value, CStr(Cell.Value)
    TEST = Cell.Offset(0, 1)
    Next Cell


    But if you want it to be when the value is "8501", adapting your code:

    Dim AllCells As Range, Cell As Range
    Dim NoDupes As New Collection
    Dim TEST As String
    Set AllCells = Sheets("TABELLA").Range("A2:A6")
    On Error Resume Next
    For Each Cell In AllCells
    NoDupes.Add Cell.Value, CStr(Cell.Value)
    If CStr(Cell.Value) = "8501" Then
    TEST = Cell.Offset(0, 1)
    End If
    Next Cell

    Though you don't need the NoDupes, I owuld do it like this:
    Dim AllCells As Range
    Dim i As Integer
    Dim TEST As String
    Set AllCells = Sheets("TABELLA").Range("A2:A6")
    i = 0
    On Error Resume Next
    i = Application.WorksheetFunction. _
    Match("8501", AllCells, 0)
    On Error GoTo 0
    If i = 0 Then
    TEST = "Not found"
    Else
    TEST = AllCells.Cells(i).Offset(0, 1)
    End If

    Steve

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get value from cells range (2000)

    Steve, Hans really my prob is to fill a combobox1 with the jont of cell in A and correspondent cell in B...
    similar:

    cellA2 " -" & cellB2
    cellA3 " -" & cellB3

    Without dupes naturally

    tks.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Get value from cells range (2000)

    Change

    For Each Cell In AllCells
    NoDupes.Add Cell.Value, CStr(Cell.Value)
    'TEST = Cell(3, CStr(Cell.Value))
    Next Cell

    to

    For Each Cell In AllCells
    NoDupes.Add Cell.Value & " -" & Cell.Offset(0, 1).Value, CStr(Cell.Value)
    Next Cell

    then fill the combo box with

    Dim itm
    For Each itm In NoDupes
    Me.ComboBox1.AddItem itm
    Next itm

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get value from cells range (2000)

    tks hans....
    But i dont see nothing in combobox2, after the code finish...!!!!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Get value from cells range (2000)

    Because you haven't copied the code correctly. In the lines

    For Each itm In NoDupes
    Sheets("STAT_NEW_GEN").ComboBox2.AddItem Item
    Next itm

    you're mixing itm and Item. You should either use itm twice, or use Item twice, but not itm and Item.

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get value from cells range (2000)

    OK TKS.

Posting Permissions

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