Need formula or VBA code to find integer
 

Please help. I need an Excel formula or a VBA code that will find the first (and only the first) integer greater than the number 10 in a set of numbers 1-26 A1:A26, any help would be very much appreciated. Thanks in advance.

Need formula or VBA code to find integer
 

Hi,

Am Tue, 3 Jun 2014 13:47:39 +0100 schrieb jackie2828:

Please help. I need an Excel formula or a VBA code that will find the
first (and only the first) integer greater than the number 10 in a set
of numbers 1-26 A1:A26, any help would be very much appreciated. Thanks
in advance.

try:
=ADDRESS(MATCH(MIN(IF(A1:A2610,A1:A26)),A1:A26,0) ,1,4)
and enter the formula with CTRL+Shift+Enter


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Need formula or VBA code to find integer
 

On Tuesday, June 3, 2014 8:47:39 AM UTC-4, jackie2828 wrote:
Please help. I need an Excel formula or a VBA code that will find the

first (and only the first) integer greater than the number 10 in a set

of numbers 1-26 A1:A26, any help would be very much appreciated. Thanks

in advance.









--

jackie2828

Jackie please try this code.

Sub test()


For i = 1 To 26
If Cells(i, 1).Value = 10 Then
MsgBox Cells(i, 1).Address
Cells(i, 1).Select
End If
Next

End Sub

Need formula or VBA code to find integer
 

I can't get Claus's formula to work. But here's a variant on Claus's suggestion: use this formula, also entered with Ctrl+Shift+Enter:

=INDEX(A1:A26,MIN(IF(A1:A2610, ROW(A1:A26),100)))

(It will show #REF! if there's no number 10 in the range.)

Howard

On Sunday, 22 June 2014 23:35:01 UTC+10, wrote:
On Tuesday, June 3, 2014 8:47:39 AM UTC-4, jackie2828 wrote:

Please help. I need an Excel formula or a VBA code that will find the



first (and only the first) integer greater than the number 10 in a set



of numbers 1-26 A1:A26, any help would be very much appreciated. Thanks



in advance.



















--



jackie2828



Jackie please try this code.



Sub test()





For i = 1 To 26

If Cells(i, 1).Value = 10 Then

MsgBox Cells(i, 1).Address

Cells(i, 1).Select

End If

Next



End Sub