Excel Help Thread ๐Ÿ“Š

Just wanna say I love macros

1 Like

Absolutely amazing! That has worked perfectly. Thanks for taking the time to look at that.

2 Likes

Such a dumbass thing to want to do with a spreadsheet anyway!

2 Likes

would love it if it was a test and they wanted you to just use access instead :smiley:

2 Likes

Another query from me!

Iโ€™m looking to save a workbook to a location based on tbe value of a cell on another workbook.

I have the below code:

ActiveWorkbook.SaveAs Filename:=โ€œC:\Temp\Exercise Output\Feb Order - Customer โ€œ & Cells (2, 2) .Value

Itโ€™s the value in cell 2, 2 I want to have in the file name but how do I write into the code that itโ€™s cell 2, 2 on sheet 2?

Any help is much appreciated.

Might be easiest to copy across to hidden sheet on active workbook first?

Do you really mean itโ€™s on another workbook and not just another worksheet? And, if so, which workbook is the code running in? The one with the location info or not?

SFileName = workbooks(โ€œwhatever.xlsxโ€).worksheets(โ€œsheet2โ€).Range(โ€œB2โ€).text

OR

sFilename = Range(โ€œ[whatever.xlsx]Sheet2!B2โ€).value

Google Apps Script query

Iโ€™ve got a sheet with a script assigned to a button. Itโ€™s been working fine since I created it last year and hasnโ€™t been changed at all

Itโ€™s now producing a โ€˜script function could not be foundโ€™ error message

Any ideas why it might now be doing that?

I tried making a copy of the document and it worked in that
I tried renaming the script and reassigning it and that didnโ€™t work
Iโ€™m not at all confident with Google Apps Script and googling hasnโ€™t produced anything helpful

Hey Excel geniuses, does anyone know if itโ€™s possible to create a formula to check if a cell contains any characters that are NOT in a defined range?

How big is the range of characters? Can you post an example?
I can think of a fairly long-winded way of doing it if itโ€™s half a dozen characters but it wouldnโ€™t be very dynamic or scaleable.

Doing it the other way round (testing if all or none of the characters in the defined range are in a value) is annoyingly quite straightforward.

1 Like

What do you mean by defined range?

I think excel can cope with ascii which might help

probably something involving substitute, but definitely need a bit more info

Yeah, this was my idea. If itโ€™s five or ten possible characters then you can just nest the SUBSTITUTE calls, but I couldnโ€™t think of a way to do it array style.

Hereโ€™s an example. If you have a list of characters in range A1:A5, this will return how many characters in the value in C6 are not in that list.

=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C6,$A$1,โ€œโ€),$A$2,โ€œโ€),$A$3,โ€œโ€),$A$4,โ€œโ€),$A$5,โ€œโ€))

Alternatively just add helper formulae to count the instance of each unwanted character, then subtract from the length of the entire string.

Itโ€™s not pretty, but it works.

Thanks guys, so I have about 85 characters I need to look up against, I can do this as values in a single cell or in a list, but itโ€™s basically a list of โ€˜safeโ€™ characters to be used in URLs

Then I need to look up whether a list (around 3k in this instance) of URLs contains any characters outside of this set of characters

So my hypothetical thing would be as follows:

So my hypothetical thing would be as follows:

Country Overall Length A C Excl. Length
Afghanistan =LEN(A2) =$B2-LEN(SUBSTITUTE(UPPER($A2),C$1,)) =$B2-LEN(SUBSTITUTE(UPPER($A2),D$1,)) =B2-SUM(C2:D2)
Albania 7 3 0 4
Algeria 7 2 0 5

What I like about it is that you can essentially paste the same formula (i.e. the โ€œ=$B2-LEN(โ€ฆโ€ one) into as many columns as you like, as long as you have the forbidden/allowed character there. Then the last column is just a way of checking whether or not it appears, and can be tweaked quite easily, I think.

None of these functions should be too time-consuming either

2 Likes

So my hypothetical thing would be as follows:

OK, this looks interesting, Iโ€™m going to have a play around with it, cheers!