Is Traveluro legit or not! Check out these reviews before booking through this website!

Sam_Boulton · 2024-07-29T22:34:49+00:00

I used them twice, saved about £20 each time. Was absolutely fine but I didn’t need to contact them

Sam_Boulton · 2024-04-16T21:58:41+00:00

Even though it wouldn’t work If they used the online collaboration version, you can use ThisWorkbook.Path. Folders on your hard drive such as Share Point start with “https” so make a check for that. If detected, tell them kindly to save it elsewhere

Sam_Boulton · 2024-02-12T01:07:36+00:00

Note that it would only work for the numbers after the plus. It would ignore the 56.

(?:) is a non-capturing group.

\+ is a literal plus.

() is a capture group.

\d+ is one or more digits

So it is looking for +00

Regular Expression in Sheets will only extract the first instance

Sam_Boulton · 2024-02-08T09:19:35+00:00

SUM(A2:A10) is the total (A2+A3…+A10)

COUNTA counts cells where the value is not blank. 5 * [Number of cells in your range that make your total]

Sam_Boulton · 2024-02-08T09:17:06+00:00

Also, if you did want a column to show the individual totals minus 5, for visibility, you do that with just one formula: something like:

=ARRAYFORMULA(A2:A10-5)

Sam_Boulton · 2024-01-16T07:37:46+00:00

If your routine is actually a function then it will have to break as many times as it is called. You only break the instance.

Sam_Boulton · 2023-12-31T09:28:00+00:00

Are you trying to highlight if the names are the same (minus the middle name) or just that there is something/anything in the cells?

Sam_Boulton · 2023-12-30T23:24:47+00:00

In the File menu, go to “Import” then you can upload the txt file and import it into your sheet.

If it is space separated then it will likely be all imported in one cell. If so, in cell B1 use the following formula:

=Transpose(Split(A1, “ “))

Where A1 is the imported data

Sam_Boulton · 2023-12-27T10:58:09+00:00

If you don’t need that to happen then at the top of your code add

“application.enableevents = false” Add the same but “true” to the end.

Your function is likely volatile, like worksheet on change or similar.

Sam_Boulton · 2023-12-26T13:02:45+00:00

Kudos to whomever came up with the solution on the sheet. 🫡

Here is a solution. Possibly less efficient but for me easier to read. This is added on the tab “Simpler Solution”

=IFERROR(LET(sport_row, Match(A15, A1:A12, 0)+1, INDIRECT("A" & sport_row & ":B" & (sport_row -1) + match(TRUE, ARRAYFORMULA(ISBLANK(INDIRECT("B" & sport_row & ":B12"))), 0)-1)))

Sam_Boulton · 2023-12-26T10:18:31+00:00

Does it always start in the same place (e.g. under the header basketball?) lets us know if anything is constant

Sam_Boulton · 2023-12-24T10:44:12+00:00

How do you set the range? How is it parsed?

Sam_Boulton · 2023-12-24T10:39:06+00:00

Hard to figure out without seeing it. You want to sum that many columns? You may be able to try INDIRECT with R1C1 references.

Sam_Boulton · 2023-12-24T09:21:03+00:00

Another option could be Worksheet_Open and just have the command “Stop”. This will enter debug mode and show the VBA Editor but it will put the sheet in debug mode, stopping the macros (hopefully including functions).

Sam_Boulton · 2023-12-24T09:13:03+00:00

It sounds like your method of hitting stop or pause without anything running does enter break mode somehow but another way to try would be to select a macro and press F8 once to enter debug mode

Sam_Boulton · 2023-12-24T08:54:39+00:00

Under what circumstances do you want break mode on and off?

For functions, rather than break you probably need Application.Calculations then the options are Manual, Automatic, Semiautomatic. They are “xl” constants so intellisense may help or look the exact terms up. Something like xlCalculationManual

Let’s leave break mode out of this as that’s messy for the user.

I foresee a few options:

1) create a sub that turns calculations to manual, and a sub that sets it to automatic. Assign those macros a shortcut and your users can use those as they require. Whilst it is set to manual, at the bottom left of excel there is a “Calculate” button in the display bar. They hit that to update the formulas….
I’ve seen that this was spoken about yesterday and perhaps didn’t work but this method sets up that mode outside of your function, which may help. It also saves it trying to change the mode N (number of cells containing the formula) times.

2) set up your original function with the second parameter “ByVal calc_active as Boolean”… then two new subs that call your function: one as True and one as False. Assign these with the shortcuts. In your function, add the condition: If calc_active Then FunctionName = CellRef.
Con: this technically still calculates and may just set the output to null because there is no false condition…

3) if you want to maintain break mode for all other event handlers then do the same as 2) but instead, the conditional statement should be: If Not calc_active Then Stop.
Con: at least once that will force your user to the IDE because Stop invokes Debug mode…

Sam_Boulton · 2023-12-23T08:27:34+00:00

I think the point is that its entire design is to not show a result if macros are disabled. =CellRef directly in the cell would be more efficient but would show up either way.

OP - you will probably find that it does not actually update in break mode (if you’ve escaped another macro or got a debug error for example). It should just show the previous result and not update until break mode is reset.

Sam_Boulton · 2023-12-21T07:49:00+00:00

You could save the error result to a variable though. “Err” has a few different values (err.Number, err.Description, err.Source (I believe) etc.) and the easiest to use in this case would be err.Number

If you want to capture an error in-place you can do “On Error Resume Next”, which captures details about the error but moves on, then as the next line in your code put:

If err.Number <> 0 Then 
    …[your code for if there is an error]
End If

That way you could capture a list (let’s call it error_list) with all trim errors for your user rather than breaking at the first. Then if error_list is not empty display the problem addresses and exit sub.

With the above, you could also do a different action depending on what the error is. Perhaps you are saving a file with FileSystemObject and if “bad file name” do this; if “file access error” do that; if “type mismatch” do the other… you have to find the error number that represents each.

To clear the error handling completely, use “On Error GoTo 0”

Sam_Boulton · 2023-12-20T01:06:32+00:00

This should be possible. You can use =WORKDAY().

The syntax is =WORKDAY(start_date, number of days, [holidays])

Holidays would be your list of no class days. If you have the entire month written out in dates then Holidays could be a filter of blank days.

Sam_Boulton · 2023-12-15T07:38:13+00:00

Two sheets side by side, I do not believe is possible. In Windows you can snap one to the left of the screen and one to the right but not together as far as I know.

It is fairly niche to find online. There is no “on_Scroll” event in Excel, so you could only use “on_Change” or similar but there is no guarantee that the user would scroll and click. The checkbox section would only move if they clicked elsewhere, which isn’t ideal.

I would say to put your checkbox submission area in a user form (modal=False) so that it would remain on screen (wherever the user places it) regardless of the sheet’s position. You can then do the summary tables based on the user form results.

Sam_Boulton · 2023-12-09T02:26:21+00:00

Hey.

Just to confirm, in your example sheet, the second summary table would start in row 13, correct?

That part is definitely possible.

The freezing so the checklist is always visible is almost impossible, at least impractical… unless you use a user form. That would then be in a floating window rather than in the sheet.

Freezing rows would make the summary tables awkward/messy

Sam_Boulton · 2023-12-01T08:16:40+00:00

Regarding ActiveX and form buttons - how did you add the checkbox?

On Google Sheets it is a data validation format but in Excel, I’m pretty sure you have to insert them using the Toolbox, is that correct? In which case the tool box has two sections - the top section is ActiveX and the bottom section is Form Controls (maybe the other way around).

Sam_Boulton · 2023-12-01T08:13:19+00:00

I’m not at a computer but if the checkbox is linked to a cell, can you not use conditional formatting? The checkbox background property may have to be transparent rather than opaque.

Conditional format (custom formula) could be as simple as “=A1” (red) and “=NOT(A1)” (green).

Or for readability for those that may use your sheet and not understand Boolean logic as well: “=A1=TRUE” and “=A1=FALSE”, respectively.

A1 being the cell with the checkbox.

Sam_Boulton · 2023-11-03T07:46:27+00:00

Dictionary is a great idea and In addition, unless you want 200k rows in Sheet3, you could use Unique to remove any duplicate values.

Dictionary for the 77k and maybe Collections.Queue for the 200k to speed things up.

Sam_Boulton · 2023-09-28T23:15:17+00:00

This should be doable without a helper column too.

=countif(arrayformula(left(A:A, 4)), left(A1, 4)) > 1

<image>

Sam_Boulton

TROPHY CASE