A Loop...I think?!?

G

Guest

Guest
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top