select variable cell in fixed column

[superik84]

I am trying to select the first and last time and put it down in 2 columns. For this I am currently using:

' maximum tijd
'
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=R[-2]C"


'celeigenschap
Selection.NumberFormat = "h:mm"
'cel 1 naar beneden
ActiveCell.Offset(1, 0).Range("A1").Select
'
' minimum tijd
'
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=MIN(R2C:R3C)"
Selection.NumberFormat = "h:mm"
ActiveCell.Offset(1, 0).Range("A1").Select



This being part of a Macro.

However, When the first or last cells of the table are empty, I get an #Value#- mark! How do I select the first cell WITH a time in it?

When I have this right in my macro, I can finish it up! Would be great if anyone knew what to do!


thanks so much!

E

I can't get my table clear, but some columns start in A2, some start in A15 etc. Some end in A30, some end in A40 (for example)

my tables look like this:

HaltenaamRitnummers: 7001 7005 a7009 7013
Uithuizen, BusstationV 8:00 10:35
Uithuizen, Engersmastraat 8:01 10:36
Uithuizen, Hunsingoheerd 8:02 10:37
Uithuizen, Snik 8:03 10:37
Uithuizen, J Cohenstraat 8:04 10:38
Uithuizen, Wilgenbos 8:04 10:38
Uithuizen, John F Kennedylaan 8:05 10:39
Lage van de weg, Bovenhuizen 8:06 10:40
Lage van de weg, Streeksterweg 8:08 10:42
Usquert, Weg naar Lutjebos 8:10 10:43
Usquert, Sportpark 8:11 10:44
Usquert, Spoorwegovergang 8:12 10:45
Rottum, 't Lage Eind 8:16 10:49
Kantens, Kolpendestraat 8:18 10:51
Kantens, Oude Gemeentehuis 8:18 10:51
Kantens, Middelstumerweg 8:18 10:51
Middelstum, Weg naar Toornwerd 8:21 10:53
Middelstum, Brouwerslaan 8:22 10:54
Middelstum, Concordiaplein 8:23 9:57 10:55 10:57
Middelstum, Mentheda 8:23 9:57 10:57
Fraamklap, Fraamklap 8:25 9:59 10:59
Middelstum, Stitswerderweg 8:27 10:01 11:01
Onderdendam, Brug 8:28 10:02 11:02
Onderdendam, Kerk 8:28 10:02 11:02
Onderdendam, Bedumerweg 8:29 10:03 11:03
Bedum, Wroetende Mol 8:31 10:05 11:05
Bedum, Zuivelfabriek 8:32 10:06 11:06
Bedum, Wilhelminalaan 8:32 10:06 11:06
Bedum, van Heemskerckstraat 8:33 10:07 11:07
Bedum, Molenweg 8:33 10:07 11:07
Bedum, Schoolstraat 8:34 10:08 11:08
Bedum, Bedumerbos 8:36 10:10 11:10
Ellerhuizen, Brug 8:39 10:13 11:13
Zuidwolde, Plattenburg 8:40 10:14 11:14
Zuidwolde, Noordwolderweg 8:41 10:15 11:15
Zuidwolde, Gemaal 8:44 10:18 11:18
Noorderhoogebrug, Boterdiepsbrug 8:45 10:19 11:19
Groningen, Bedumerweg/Poortstraat 8:47 10:21 11:21
Groningen, Bedumerweg/Floresstraat 8:47 10:21 11:21
Groningen, Bedumerweg/Nw.Ebbingestr 8:48 10:22 11:22
Groningen, Nw Ebbingestr/Noorderplsn 8:49 10:23 11:23
Groningen, Nw Ebbingestr/Brouwerstr 8:49 10:23 11:23
Groningen, Nw Ebbingestr/Korenstraat 8:50 10:24 11:24
Groningen, Oude Ebbingestraat 8:51 10:25 11:25
Groningen, Grote Markt/Stud Soc 8:52 10:26 11:26
Groningen, Gelkingestraat 8:53 10:27 11:27
Groningen, Zuiderdiep 8:54 10:28 11:28
Groningen, Zuiderdiep/Schoolholm 8:55 10:29 11:29
Groningen, Emmaplein 8:56 10:30 11:30
Groningen, Busstation CSA 8:57 10:31 11:31

 

[ClulessChris]

Format the cells as a time. the one's with no entry will then have a value of 00:00 (I think)
and thus not throw the error.

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[superik84]

this does not work.. Prob because it then selects 00:00 as lowest time..

should I do something with 'Minimum' and 'is not zero'? would that be possible?

 

[superik84]

PLEASE HELP!! I'M TOTALLY STUCK!!

 

[xlbo]

yes - do a check on whether the cell = "" or 0 as well as what you are checking for now

Please don't post in CAPS or with lots of !!!!!

It makes you look unprofessional

You answered your own question so that is probably why no-one has bothered responding

I would really avopid using ACTIVECELL and SELECTION as well. They are highly likely to catch you out and not be what you expect them to be. Explicitly reference cells / ranges rather than working with what may or may not be selected or active

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[superik84]

what should the macro look like?

I still can't succeed in making it work succesfully.

pretty obvious I started using VBA 2 days ago!

Problem however is that I am using with changing columns. For this reason I cannot work with reference cells and am, with my current knowledge, restricted to using the ActiveCell and Selection.

Thanks,

E

 

[xlbo]

what is it exactly that you are trying to do?

Are you trying to input a formula to determine the min and max time or can you calculate it in code and then just enter it onto the sheet as a value ?

Are you trying to get a max and min per row or per column or for the 2 columns?

Please be clear about the process you are trying to achieve

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[superik84]

late response..

ok, I need the first time (min?) from a column in say 4 cells beneath the column. 1 cell below I would like to have the last time (max?)

So the wanted results are per column, stated 4 and 5 rows below.

what I am trying to find is a formula I guess?

E

 

[xlbo]

Just been reviewing the whole thread here and I think there is a misunderstanding going on

If you have real times in your cells then it shouldn;t matter whether you have blank cells - they are discounted by the min formula

Maybe a slightly different approach - see if this works on 1 column

sub tester()

iCol = activecell.column
sRow = activecell.row
lRow = cells(65536,iCol).end(xlup).row

cells(lrow + 4,iCol).formula = "=MIN(" & range(cells(sRow,iCol),cells(lRow,iCol)).address & ")"
end sub


Paste that into a module and select the 1st cell with a value in it in 1 of your time columns

Run the sub and see whether that works for you as a start point...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

THis is pretty simple.

For column B...
[tt]
=MIN(B$7:B$65536)
=MAX(B$7:B$65536)
[/tt]
copy across.

All you need is the first row of data.

Here are my results obtained in a mater of seconds...
[tt]
8:00 9:57 10:55 10:57
8:57 10:54 11:31 10:57

[/tt]


Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[xlbo]

ah but Skip - the formulae need to be IN the columns themselves so can;t use 65536 (as per OPs specs)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

"I am trying to select the first and last time and put it down in 2 columns"

Mine are in B1 & B2 summing B7:B65536. (actually could be summing from row 4 if so desired, with one row of headings)

SURELY, you are not putting aggregations BELOW the list. That went out with paper, pencil and adding machines. As a matter of SOP, I even repeat PivotTable column aggregations, at the top of each report. Why make the poor consumer of your product, hunt for such important information?

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[ck1999]

xlbo,

Using your code can you do this to determine 1st time in column (assuming no spaces)

sub tester()
iCol = 2 ' for column B
lRow = cells(65536,iCol).end(xlup).row
sRow = cells(lrow,iCol).end(xlup).row

cells(lrow + 4,iCol).formula = "=MIN(" & range(cells(sRow,iCol),cells(lRow,iCol)).address & ")"
end sub

ck1999

 

[xlbo]

certainly could ck1999 but wanted to check whether the concept would work before ironing out the specifics of contiguous ranges and where data starts etc...


and Skip - yes, yes and yes. Couldn't agree with you more...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question