MS Excel / VBA help thread

Posted on
of 47
First Prev
/ 47
  • Sounds familiar!
    Just enough to do one or two things, then when it gets too big....

    I'll go and read geocomputing in R by Lovelace and see where I get to!

  • Don't want to downplay what you do.

  • Not at all! The nature of the work is such that I don't really get the opportunity to dig into any dirty stuff even if I could find the motivation to learn it..! :)

  • I want to get the distance and traveling time between two locations in a Google Sheet.

    I have copy and pasted a script I found and it is running well and returning the right results.

    However, it is erroring frequently and intermittently with

    Service invoked too many times in a short time: URL protected host rate Max. Try Utilities.sleep(1000) between calls.

    and also

    Service invoked too many times in one day

    I know what they mean but don't know how to reduce the frequency that the sheet calls for the data

  • Anyone any good at field codes in Word?

    I'm doing a mailmerge and one of the ranges is a number. At the moment it's returning everything with two decimal places using the field code

    \# "#.00"

    i.e 70 -> 70.00
    70.2 -> 70.20
    70.23 -> 70.23
    70.234 -> 70.23


    \# "#.##"

    I can get this to a max of 2 d.p. and fewer if it has fewer. However, the decimal point still shows up.

    i.e 70 -> 70.
    70.2 -> 70.2
    70.23 -> 70.23
    70.234 -> 70.23

    Any ideas on how I can get rid of that decimal point for integers?

  • Cheers, that looks like what I'm looking for. Now to understand it!

  • Any of you made the transition from analyst who uses excel to crack every nut to well rounded analyst with python/R/tableau/ stats knowledge? Did you manage mainly self taught? Has it opened doors?

  • Sort of.
    I've been making a stab of it. After drifting in a job, I started doing more in excel.

    Then I got into public health and doing "information". Then I got into doing maps and stuff in R. Now I'm at a local authority doing excel, arcgis, bit of r, some SQL planned and a lot of power bi. Iteration is what I need to sort.
    I've not wanted to take a course because finances. But I've tried to do things that I think will be useful to my development.
    Power BI is going to help with the ETL side of things and understanding that better.
    I'm nowhere near the level of some on here.

  • Using =now() as my reference point I'd like to check if cell A1 is >or< now. A1 has 10:56:00, for example.

    =now() doesn't return a very user friendly number

  • NOW() returns a time-date value.

    It sounds like your 'time' data is stored as text strings. You should make sure it's also a time-date value (irrespective of how it actually displays via format codes). If you can't fix this in your source data, you could do this with a new column using DATEVALUE to convert the text.

    Then simple comparison will work fine.

  • Thanks. Can now build a countdown until 4pm checker.

  • You can also store the time as a number. e.g. 43658.6666666667 is 4pm today

  • That is what I said

  • Bored as

    1 Attachment

    • IMG_20190712_123854.jpg
  • Now make it recalc automatically every X seconds

  • Ah yes. I thought you were saying to store it as 12/07/2019 16:00 which obviously gives all kinds of hassle when you're trying to work with it.

  • Hello!
    I've been asked to find an SQL class to go on. In london.

    I can write SQL but I am going to be involved in migrating data into SQL databases. Yeah. Me neither.

    So. I need to go on a course for beginners in london for a day or two.
    Any recommendations?

  • Sounds like you should maybe be looking for a Microsoft SQL Server administration course, not a SQL course?

  • I think my role is:
    Pulling data out of SQL databases we have access to
    Moving stuff we have to new places

    So yeah admin is probably a thing

  • Anyone use Power Query/M?

    Looking to pull out the contents of a column using wildcards. I want a column containing time in the format xx:xx:xx (e.g. 17:04:01) so trying to do it using Text.Contains but I can't find what the wildcard characters are.

    I've tried


    and a few others but none seem to work. A quick google isn't bringing anything up.

  • I have a column containing numbers, I want them all to be the same length with zeros padding them from the left.
    So 1,23,456 becomes 0001,0023,0456 etc

    How? I'm using our friends If and Len, but I know there's a better way.

  • Could you do something like:


    which would find the first comma

    Then for your true statement you want

    "000"& whatever code goes here to return the characters before the first comma

    Then for your false statement you want a nested if returning

    "00" & code for between two commas
    "0" and code for after second

    or is that what you've written already?

  • OBVS depends on how many flippin rows of data we're talking here.
    If loads

  • Post a reply
    • Bold
    • Italics
    • Link
    • Image
    • List
    • Quote
    • code
    • Preview

MS Excel / VBA help thread

Posted by Avatar for mattty @mattty