Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Help with Do While Loop

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Chicago, IL USA
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    the following code should keep looking until FirstValue does not equal SecondValue or until the cell in column A of sheet2 is empty.

    It isn't working. I'm a bit of a novice and the book I have is not helping to any great extent.

    Anything you can suggest is greatly appreciated.

    Sub ProductServiceBoth()
    Dim FirstValue, SecondValue As String
    Dim Cel As Range
    For Each Cel In Selection
    Set c = Worksheets("Sheet2").Columns("A:A").Find(Worksheets("Sheet1").Range(Cel.Address).Value, LookIn:=xlWhole)
    FirstValue = Worksheets("Sheet2").Range(c.Address).Offset(0, 3).Value
    Set b = Worksheets("Sheet2").Columns("A:A").FindNext(c)
    SecondValue = Worksheets("Sheet2").Range(b.Address).Offset(0, 3).Value
    If FirstValue <> SecondValue Then
    Worksheets("Sheet1").Range(Cel.Address).Offset(0, 3).Value = "Both"
    Else
    Do While FirstValue = SecondValue
    Set b = Worksheets("Sheet2").Columns("A:A").FindNext(c)
    SecondValue = Worksheets("Sheet2").Range(b.Address).Offset(0, 3).Value
    If FirstValue <> SecondValue Then
    Worksheets("Sheet1").Range(Cel.Address).Offset(0, 3).Value = "Both"
    Else
    End If
    Loop
    End If
    Next Cel
    End Sub

    Thanks

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    this does seem a little overcomplicated, what is it you are trying to do?

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Chicago, IL USA
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Please see my previous post in which I explain, if a bit unclearly, what I am trying to do. Any help at all would be greatly appreciated. I'll be dealing with some 45,000 records (rows). Thanks

    http://www.mrexcel.com/board/viewtop...4075&forum=2&8

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I answered your first post, like I said, its not clean, because I didn't feel like starting over. Hope you are happy with it.

  5. #5
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    I may not have understood what you were trying to do, it would help if you said what you had as data and what you wanted to happen!

    This code will put a "Both" to the right of a matched value from sheet1 and sheet2 on sheet1. JSW

    Sub ProductServiceBoth()
    Dim FirstValue, SecondValue As String
    Dim Cel As Range

    For Each Cel In Worksheets("Sheet1").Range("A:A")
    FirstValue = Worksheets("Sheet1").Range(Cel.Address).Value
    SecondValue = Worksheets("Sheet2").Range(Cel.Address).Value

    If IsEmpty(FirstValue) Then GoTo Kill

    If FirstValue = SecondValue Then
    Worksheets("Sheet1").Range(Cel.Address).Offset(0, 1).Value = "Both"
    End If
    Next Cel
    Kill:
    End
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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