Hi Every One

I am relatively new to programming and have benefited greatly from this
forum. I have got another very basic issue which I am unable to address
myself.

I am comparing the smallest value from an array which is stored in variable
"Least" and then, look for the corresponding value in another array with the
following code:

Least = Application.WorksheetFunction.Small(Array, 1)


A = Application.WorksheetFunction.HLookup(Least, Range, 6, False)

I would also like to obtain the cell addresses of the two (The smallest of
"Array" and its correspodning value in "Range".

Kindly help.

Many Thanks

Mallick

Try this:
(In my example I replaced "Array" with Range("A1:B6"),
"Range" with Range("C1:G6"))

Least = WorksheetFunction.Small(Range("A1:B6"), 1)
Range("A1:B6").Select
LAddr = Selection.Find(What:=Least, After:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Address
A = Application.WorksheetFunction.HLookup(Least, Range("C1:G6"), 6, False)
Range("C1:G6").Select
AAddr = Selection.Find(What:=A, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Address

Regards,
Stefi

€žMallick€� ezt Ã*rta:

Hi Every One

I am relatively new to programming and have benefited greatly from this
forum. I have got another very basic issue which I am unable to address
myself.

I am comparing the smallest value from an array which is stored in variable
"Least" and then, look for the corresponding value in another array with the
following code:

Least = Application.WorksheetFunction.Small(Array, 1)


A = Application.WorksheetFunction.HLookup(Least, Range, 6, False)

I would also like to obtain the cell addresses of the two (The smallest of
"Array" and its correspodning value in "Range".

Kindly help.

Many Thanks

Mallick

Thanks Steffi for your quick reply.

Its not as simple as I expected it to be. As I mentioned earlier that I am a
bit of novice in VBA, can you please explain your code?

Thanks

Mallick
"Stefi" wrote:

Try this:
(In my example I replaced "Array" with Range("A1:B6"),
"Range" with Range("C1:G6"))

Least = WorksheetFunction.Small(Range("A1:B6"), 1)
Range("A1:B6").Select
LAddr = Selection.Find(What:=Least, After:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Address
A = Application.WorksheetFunction.HLookup(Least, Range("C1:G6"), 6, False)
Range("C1:G6").Select
AAddr = Selection.Find(What:=A, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Address

Regards,
Stefi

€žMallick€� ezt Ã*rta:

Hi Every One

I am relatively new to programming and have benefited greatly from this
forum. I have got another very basic issue which I am unable to address
myself.

I am comparing the smallest value from an array which is stored in variable
"Least" and then, look for the corresponding value in another array with the
following code:

Least = Application.WorksheetFunction.Small(Array, 1)


A = Application.WorksheetFunction.HLookup(Least, Range, 6, False)

I would also like to obtain the cell addresses of the two (The smallest of
"Array" and its correspodning value in "Range".

Kindly help.

Many Thanks

Mallick

Stefi

It was unable to differentiate between integer and double. Forexample, Least
value was 1 and when searched through the Array, it gave the address of the
cell with 1.6 value.

Kindly help to remove this bug. Thanks

Mallick



"Mallick" wrote:


Thanks Steffi for your quick reply.

Its not as simple as I expected it to be. As I mentioned earlier that I am a
bit of novice in VBA, can you please explain your code?

Thanks

Mallick
"Stefi" wrote:

Try this:
(In my example I replaced "Array" with Range("A1:B6"),
"Range" with Range("C1:G6"))

Least = WorksheetFunction.Small(Range("A1:B6"), 1)
Range("A1:B6").Select
LAddr = Selection.Find(What:=Least, After:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Address
A = Application.WorksheetFunction.HLookup(Least, Range("C1:G6"), 6, False)
Range("C1:G6").Select
AAddr = Selection.Find(What:=A, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Address

Regards,
Stefi

€žMallick€� ezt Ã*rta:

Hi Every One

I am relatively new to programming and have benefited greatly from this
forum. I have got another very basic issue which I am unable to address
myself.

I am comparing the smallest value from an array which is stored in variable
"Least" and then, look for the corresponding value in another array with the
following code:

Least = Application.WorksheetFunction.Small(Array, 1)


A = Application.WorksheetFunction.HLookup(Least, Range, 6, False)

I would also like to obtain the cell addresses of the two (The smallest of
"Array" and its correspodning value in "Range".

Kindly help.

Many Thanks

Mallick