Locating Cell Number

[khawarameer]

Hello All,

Take a look at these columns below;

<table style="border-collapse: collapse; width: 134pt;" width="178" border="0" cellpadding="0" cellspacing="0"><col style="width: 67pt;" span="2" width="89"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 67pt;" width="89" align="right" height="17">.00</td> <td class="xl65" style="width: 67pt;" width="89" align="right">810.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">.00</td> <td class="xl65" align="right">1,729.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">.00</td> <td class="xl65" align="right">1,594.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">.00</td> <td class="xl65" align="right">1,599.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">.00</td> <td class="xl65" align="right">498.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">498.00</td> <td class="xl65" align="right">.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">.00</td> <td class="xl65" align="right">1,852.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">.00</td> <td class="xl65" align="right">454.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">.00</td> <td class="xl65" align="right">50.00</td> </tr> </tbody></table>
I want to find the cell reference number of any value of column A (which is greater than 0) in column B. I could only manage to find values with my formula i.e.

=IF(A1>0,VLOOKUP(A1,$B$1:$B$9,1,0),0)

Your help is required.

Regards

 

[GTO]

I doubt that this is it, but maybe close enough you can clarify.

For a range like:

Excel Workbook
	A	B
1
2	0	810
3	0	1,729.00
4	546	1,594.00
5	0	1,599.00
6	0	498
7	498	0
8	0	1,852.00
9	2.5	454
10	0	50
Sheet2

Excel 2003

Run this, which will return the addresses...<font face=Courier New><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> exa1()<br><SPAN style="color:#00007F">Dim</SPAN> ary<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> rngCell<SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> i<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> s<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">ReDim</SPAN> ary(0<SPAN style="color:#00007F">To</SPAN> 0)<br>    <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> rngCell<SPAN style="color:#00007F">In</SPAN> Range("A2:A10")<br>        <SPAN style="color:#00007F">If</SPAN> rngCell.Value > 0 And IsNumeric(rngCell.Value)<SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">ReDim</SPAN><SPAN style="color:#00007F">Preserve</SPAN> ary(1<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(ary) + 1)<br>            ary(UBound(ary)) = rngCell.Offset(, 1).Address<br>        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> i =<SPAN style="color:#00007F">LBound</SPAN>(ary)<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(ary)<br>        s = s & ary(i) & vbCrLf<br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    MsgBox s<br>    <br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Is that any closer?

Mark

 

[MyExcel]

anothr Array Formula

=ADDRESS(MATCH(9.99999999999999E+307,IF(A1:A9>0,A1:A9)),2)

 

[khawarameer]

Hello Mark,

If the results are displayed in cells instead of message box then it will be easier to find.

You see i have almost 6000 rows to apply that code on.

can you rewrite the code so it may display results in sheet instead of Messagebox.

Regards

 

[GTO]

Hi there,

Transpose the array and place the addresses where you want.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> exa1()<br>Dim _<br>ary         <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, _<br>rngCell     <SPAN style="color:#00007F">As</SPAN> Range, _<br>i           <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">ReDim</SPAN> ary(0 <SPAN style="color:#00007F">To</SPAN> 0)<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rngCell <SPAN style="color:#00007F">In</SPAN> Range("A2:A6000")<br>        <SPAN style="color:#00007F">If</SPAN> rngCell.Value > 0 And IsNumeric(rngCell.Value) <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> ary(1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(ary) + 1)<br>            ary(UBound(ary)) = rngCell.Offset(, 1).Address<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    Range("D2").Resize(UBound(ary)).Value = Application.Transpose(ary)<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Hope that helps,

Mark