VBA: Find the adress of the cell within a certain row with the value closest to 1

[Mrbengek]

Hi all!

This is my very first post here, I am very new at VBA. I am looking forward to be of service to this community, so I can enhance my own VBA skills.

However I have now run into a problem which I cannot seem to be able to solve:

At my work we print the times of people coming in and out. The times can be anywhere from 0:00 to 23:59 as most of you probably know the times are saved in excel as an number (double) between 0 and 1.

I want to know the adress of the cell which is closest to 23:59 (seen by excel as value 1).

example:

19:45

21:46

22:02

23:47

00:02

01:44

01:59

03:45

I hope you can help me!

With Kind regards,

MrBengek

[Mrbengek]

This is what I currently have, it works as intended maybe usefull for the next person. (my data is in column 11 - max column count)
and reports the column which is closest to 1

Please Login or Register  to view this content.

[JBeaucaire]

I would think you can do it without looping. The MAX function will present the highest value in the range, which should the value you're looking for. Then you can jump straight to that value with a FIND command and create the address.

Here it is in one line of code:

Please Login or Register  to view this content.