A Loop...I think?!?

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.

[Dave Hawley]

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

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.

[SamS]

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

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!

[Dave Hawley]

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

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!

[Dave Hawley]

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

[Dave Hawley]

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

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?