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

Thread: A Loop...I think?!?

  1. #1
    Guest

    Default

    I have a row of columns numbered 1-10 (Say A1-A10) and the cells below may have information in depending on the result of another formula. How can I get Excel to look at the information in row B, starting with column A10? If there is no entry in B10 I need it to look at B9, and so on until it finds a cell with some information in.

    Hope this makes sense to someone! Please help!

    Thanks.

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    I don't think a Loop would be best, try the Find method. This can easily be restricted to a certain range and also include/exclude others. I am not very clear on the specific ranges you want but the code below will only look in Range("A11:B20")

    Range("A11:B20").Find(What:="7", After:=Range("A11"), _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False).Activate

  3. #3
    Guest

    Default

    On 2002-03-10 07:04, Dave Hawley wrote:
    Hi

    I don't think a Loop would be best, try the Find method. This can easily be restricted to a certain range and also include/exclude others. I am not very clear on the specific ranges you want but the code below will only look in Range("A11:B20")

    Range("A11:B20").Find(What:="7", After:=Range("A11"), _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False).Activate

    One small point.
    If the first cell containing "7" needs to be located, would suggest using After:-Range("B20") instead of After:-Range("A11").
    If "7" is in cell A11 and also in B2(say), B2 will be "found" if After:-Range("A11") is used.


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not the best but this will find the next empty cell in column B and returns the Row number of the cell previous to an empty cell.

    Dim MTCell As Range
    Dim lrow
    For Each MTCell In Range("B1:B10")
    If MTCell = Empty Then
    lrow = ActiveCell.Row - 1
    'put your code here
    End If
    Next

  5. #5
    Guest

    Default

    Brilliant! Excuse my ignorance, I am extremely Excel illiterate! But...how do I get this bit of programming to return a value (true or false would do) to a specific cell when it finds the information it is looking for? I am using the Find method.

    Cheers!

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about:


    Sub FoundIt
    Dim bFound As Boolean
    On Error Resume Next
    bFound = Range("A11:B20").Find(What:="7", After:=Range("A11"), _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False)
    On Error GoTo 0
    Sheet1.Range("A1") = bFound
    End sub


  7. #7
    Guest

    Default

    Ok, got that to work, thanks.

    Is there any way it can now look for the column heading that I have set up and match that to a report number?

    For example, if it finds a "7" in the column headed "4" can it return that true or false response to a cell in report "4"?

    My spreadsheet looks a little like this...
    1 2 3 4 5 6 7 8(Report Numbers)

    1 3 4 5 7 2 5 (entries on s/sheet)
    2
    3

    I really appreciate this, I'm learning loads!

  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, here waht I THINK you want:


    Sub FoundIt()
    Dim bFound As Boolean
    Dim strMyMatch As String
    Dim strHead As String

    'Value to compare a 5 rows down
    strMyMatch = "Castle"
    'Heading in row 1
    strHead = Cells(1, 1)

    On Error Resume Next
    bFound = Range("A1:K1").Find(What:="7", After:=Range("A1"), _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False).Offset(5, 0) = strMyMatch
    On Error GoTo 0

    Cells(1, 1) = bFound
    End Sub

  9. #9
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    I have a feeling you will want to loop through each heading.


    Sub FoundIt()
    Dim iCol As Integer
    Dim strFind As String

    'Loop through all headings to and match to 5 rows down
    For iCol = 1 To WorksheetFunction.CountA(Range("A1:E1"))
    strFind = Choose(iCol, "H1LookFor", "H2LookFor", _
    "H3LookFor", "H4LookFor", "H5LookFor")
    Cells(1, iCol) = (Cells(1, iCol).Offset(5, 0) = strFind)
    Next iCol
    End Sub

  10. #10
    Guest

    Default

    Couple of queries...

    What does the bit about 'Value to compare a 5 rows down
    strMyMatch = "Castle" mean? Should I see the word "castle" somewhere if it finds a 7?

    Also what does Offset(5, 0) = strMyMatch mean?

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
  •