Address of a Cell
 

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

Address of a Cell
 

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

Address of a Cell
 

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

Address of a Cell
 

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