+ Reply to Thread
Results 1 to 7 of 7

Change Macro?

  1. #1
    Alen32
    Guest

    Change Macro?

    I got this macro here which works well:
    Private Sub ListBox1_Change()
    With Me.ListBox1
    If .ListIndex > -1 Then
    'MsgBox .List(.ListIndex, 1)
    ThisWorkbook.FollowHyperlink _
    Address:="Http://" & .List(.ListIndex, 1)
    End If
    End With
    End Sub

    Private Sub UserForm_Initialize()
    Dim cell As Range
    Dim sh As Worksheet

    With Me
    .ListBox1.RowSource = ""
    .ListBox1.ColumnCount = 3
    .ListBox1.Clear
    End With

    Set sh = Worksheets("Ark1")

    For Each cell In sh.Range("A10:A250")
    If cell.Value = sh.Range("A5").Value Then
    With UserForm
    .ListBox1.AddItem cell.Value
    .ListBox1.List(.ListBox1.ListCount - 1, 1) _
    = cell.Offset(0, 2).Value
    .ListBox1.List(.ListBox1.ListCount - 1, 2) _
    = cell.Offset(0, 5).Value
    End With
    End If
    Next

    End Sub


    I want to change macro becauseof this:
    I have made Useform with 2 frame and inside of each frame two option
    button. I have also one textbox.
    At the end I have one commandbutton (FIND). I want to do this:
    When USER make choise in this two frames and make input in textbox. And
    push commanbutton(FIND) then if this 3 words appear in samme row then I
    want row display in listbox. It sholud be searched in a whole workbook.


  2. #2
    Tom Ogilvy
    Guest

    Re: Change Macro?

    Look in help at the FindNext method of the range object. The sample code
    shows how to search for multiple occurances of a target. Look for one of
    your cells. Then each time you find it, check the two other cells in that
    row for the specific condition which must be satisfied. If the conditions
    are met, do the additem. continue the search.

    --
    Regards,
    Tom Ogilvy

    "Alen32" <[email protected]> wrote in message
    news:[email protected]...
    > I got this macro here which works well:
    > Private Sub ListBox1_Change()
    > With Me.ListBox1
    > If .ListIndex > -1 Then
    > 'MsgBox .List(.ListIndex, 1)
    > ThisWorkbook.FollowHyperlink _
    > Address:="Http://" & .List(.ListIndex, 1)
    > End If
    > End With
    > End Sub
    >
    > Private Sub UserForm_Initialize()
    > Dim cell As Range
    > Dim sh As Worksheet
    >
    > With Me
    > .ListBox1.RowSource = ""
    > .ListBox1.ColumnCount = 3
    > .ListBox1.Clear
    > End With
    >
    > Set sh = Worksheets("Ark1")
    >
    > For Each cell In sh.Range("A10:A250")
    > If cell.Value = sh.Range("A5").Value Then
    > With UserForm
    > .ListBox1.AddItem cell.Value
    > .ListBox1.List(.ListBox1.ListCount - 1, 1) _
    > = cell.Offset(0, 2).Value
    > .ListBox1.List(.ListBox1.ListCount - 1, 2) _
    > = cell.Offset(0, 5).Value
    > End With
    > End If
    > Next
    >
    > End Sub
    >
    >
    > I want to change macro becauseof this:
    > I have made Useform with 2 frame and inside of each frame two option
    > button. I have also one textbox.
    > At the end I have one commandbutton (FIND). I want to do this:
    > When USER make choise in this two frames and make input in textbox. And
    > push commanbutton(FIND) then if this 3 words appear in samme row then I
    > want row display in listbox. It sholud be searched in a whole workbook.
    >




  3. #3
    Alen32
    Guest

    Re: Change Macro?

    I found this in help but how to insert 3 words from my userform instead of
    number 2?

    This example finds all cells in the range A1:A500 that contain the value 2
    and makes those cells gray.

    With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
    firstAddress = c.Address
    Do
    c.Interior.Pattern = xlPatternGray50
    Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    End With



  4. #4
    Bob Phillips
    Guest

    Re: Change Macro?

    Is that search for an amalgam of the 3 words, one after the other, all three
    in related cells (if so, what is the relationship), or any one of the three?

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Alen32" <[email protected]> wrote in message
    news:[email protected]...
    > I found this in help but how to insert 3 words from my userform instead of
    > number 2?
    >
    > This example finds all cells in the range A1:A500 that contain the value 2
    > and makes those cells gray.
    >
    > With Worksheets(1).Range("a1:a500")
    > Set c = .Find(2, lookin:=xlValues)
    > If Not c Is Nothing Then
    > firstAddress = c.Address
    > Do
    > c.Interior.Pattern = xlPatternGray50
    > Set c = .FindNext(c)
    > Loop While Not c Is Nothing And c.Address <> firstAddress
    > End If
    > End With
    >
    >




  5. #5
    Alen32
    Guest

    Re: Change Macro?

    I make database of my articles in excell. User can choose with
    optionbutton.
    frame 1
    horse
    pig
    frame2
    danish
    foreign
    and
    text box is free but only one word.
    Now you have tree words ex. horse, danish and Aminoacids (input ex. from
    textbox).
    this three words can be found in samme row and I want to display these row
    in listbox.
    Words are in samme row but in different columns.


  6. #6
    Tom Ogilvy
    Guest

    Re: Change Macro?

    With userform1
    if .optionButton1 then
    sOne = "Horse"
    else
    sOne = "Pig"
    end if
    if .optionButton3 then
    sTwo = "Danish"
    else
    sTwo = "Foreign"
    end if
    sThree = .Textbox1.Text
    End With
    With Worksheets(1).Cells
    Set c = .Find(sOne, lookin:=xlValues)
    If Not c Is Nothing Then
    firstAddress = c.Address
    Do
    if application.countif(c.EntireRow,"*" & sTwo & "*") and _
    application.Countif(c.EntireRow,"*" & sThree & "*") then
    ' add row to listbox
    end if
    Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    End With

    --
    Regards,
    Tom Ogilvy


    "Alen32" <[email protected]> wrote in message
    news:[email protected]...
    > I make database of my articles in excell. User can choose with
    > optionbutton.
    > frame 1
    > horse
    > pig
    > frame2
    > danish
    > foreign
    > and
    > text box is free but only one word.
    > Now you have tree words ex. horse, danish and Aminoacids (input ex. from
    > textbox).
    > this three words can be found in samme row and I want to display these row
    > in listbox.
    > Words are in samme row but in different columns.
    >




  7. #7
    Alen32
    Guest

    Re: Change Macro?

    I tried everything but I can't add rows to listbox.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1