can this formula be coded?

[colwyn]

I'm using Excel 2007 and s/s is 325501 rows deep. It consists of serie
of ranges between 4 and 30 rows deep.

What I want to do is locate the next appearance of a name and copy it
accompanying number.
Doing this manully is not feasible, given the large size of the s/s .

I have the following formula but have had it running for 5 hours no
and it is only down to row 22300:


Code
-------------------

=IF(C3="","",IF(ISNUMBER(MATCH(1,(C4:$C$140=C3)*(L4:$L$140>0),0)),INDEX(L4:$L$140,MATCH(1,(C4:$C$140=C3)*(L4:$L$140>0),0)),""))

-------------------


Can anyone offer anything quicker or code??

I enclose a small attachment showing what I am trying to achieve bu
for those who don't like opening attachments the wording in it is :

The desired objective is to place in column Q the next appearing numbe
in column L of the name in column C.
The VLOOKUP formula in column Q presents the desired number bu
(problem!) presents a zero when next appearance = blank.
When this happens I want the formula/code to repeatedly lookup the nex
appearance until it finds a number.
Examples of where next numbers appear are given here in column R.


If anyone can help me to this end I would be most grateful.
Big thanks.
Colwyn

+-------------------------------------------------------------------
|Filename: example.xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=60
+-------------------------------------------------------------------

 

[Joel]

I can easily be coded in a macro in the formula is correct. First make sure
you formula is corrrect by reviewing the formula using the Efvaluate formula
function.

Tools - formula Auditing - Evaluate Formula.

You are first checking if C3 has a Comma "","" then comparing C3 with
C4:$C$140. Does this make sense?

 

[colwyn]

Joel Guest - thanks.

I've taken out the first part where C3 checks for comma and have als
put the formula in braces. The rest is correct and any help you can giv
will be greatly appreciated.

The formula now reads:

Code

 

[Joel]

the code will look something like the code below. I'm putting the Next
Number in column S since you didn't specify.

for Row 3 the code will produce this statement
Set c = Range("C4:C325501").Find(what:=Range("C3"), _
LookIn:=xlValues, lookat:=xlWhole)

for Row 4 the code will produce this statement
Set c = Range("C5:C325501").Find(what:=Range("C4"), _
LookIn:=xlValues, lookat:=xlWhole)


Sub GetNext()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
For RowCount = 3 To (LastRow - 1)
Data = Range("C" & RowCount)
Set c = Range("C" & (RowCount + 1) & ":C" & LastRow).Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Num = Range("L" & c.Row)
Range("S" & RowCount) = Num
End If

Next RowCount

End Sub

 

[colwyn]

Joel Guest- thanks. That's very good and full appreciation for you
efforts

Problem with it is: it doesn't provide the next value when there is a
occasion where a name occurs without a value but later does so
For instance, a name mayoccur on row 1, re-occur on row 20 without
value and again on row 40 with a value
I need row 1 to retrieve the value on row 40

I'll enclose attachment with your code and its product as it wil
better illustrate
Big thanks
Colwyn

+-------------------------------------------------------------------
|Filename: example 2.xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=61
+-------------------------------------------------------------------

 

[Joel]

try these changes

Sub GetNext()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
For RowCount = 3 To (LastRow - 1)
Data = Range("C" & RowCount)
With Range("C" & (RowCount + 1) & ":C" & LastRow)
Set c = .Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Num = Range("L" & c.Row)
Do While Num = "" And _
Not c Is Nothing And _
c.Row > 3

Set c = .FindNext()
Num = Range("L" & c.Row)
Loop

Range("S" & RowCount) = Num
End If
End With
Next RowCount

End Sub

 

[Joel]

I needed to add C to the findnext

Sub GetNext()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
For RowCount = 3 To (LastRow - 1)
Data = Range("C" & RowCount)
With Range("C" & (RowCount + 1) & ":C" & LastRow)
Set c = .Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Num = Range("L" & c.Row)
Do While Num = "" And _
Not c Is Nothing And _
c.Row > 3

Set c = .FindNext(after:=c)
Num = Range("L" & c.Row)
Loop

Range("S" & RowCount) = Num
End If
End With
Next RowCount

End Sub

 

[colwyn]

Joel Guest - thanks. Still not working. It works on the first range the
freezes. On 'debug' it highlights the line "Num = Range("L" & c.Row)"

???
Can it be fixed?
Big thanks.
Colwyn

 

[Joel]

I fixed the problem. the reason the code hung was if you were on row 10 and
the data was 8 in column C the macro would find 8 also in row 20 and 30 but
column L was empty in both cases. the code would get hung in a loop finding
8 in row 20, then 30, then 20, then 30 and never getting out of the code.
The problem with find and find next is it wraps and when it gets to the last
row goes back to the 1st row in the search field.


The code right now wraps that if it doesn't find the number at a row higher
than then current row it goes back to row 3 and searches until it gets to the
current row. So the result is it will put the data from column L on a lower
row instead of a higher row If you don't want it to wrap and put and leave a
blank if it doesn't find the number in a higher row then make this change

from:
Range("S" & RowCount) = Num
to:
if c.Row > RowCount then
Range("S" & RowCount) = Num
end if

Sub GetNext()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
For RowCount = 3 To (LastRow - 1)
Data = Range("C" & RowCount)
With Range("C3:C" & LastRow)
Set c = .Find(what:=Data, after:=Range("C" & RowCount), _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Num = Range("L" & c.Row)
Do While Num = "" And _
Not c Is Nothing And _
c.Row > RowCount

Set c = .FindNext(after:=c)
Num = Range("L" & c.Row)
Loop

Range("S" & RowCount) = Num
End If
End With
Next RowCount

End Sub

 

[Simon Lloyd]

Crossposting
You have crossposted 'here
(http://www.mrexcel.com/forum/showthread.php?t=362455

--
Simon Lloy

Regards
Simon Lloy
'The Code Cage' (http://www.thecodecage.com