# places

1. ### Converting number to fixed length field

Hi all, Not VBA related at this stage but I will be hoping to convert it into VBA in the long run. I need to convert a number, which will be of indeterminate length, into a text string of fixed length. The length is 14 characters long. To fill the length, I need to add leading zeros to the...
2. ### [VBA] Cut down a string to match another string, if applicable

Hi all, It was all going so well. In N12 I have a long string of place names, like this: Aberystwyth, Accrington, Alfreton, Altrincham, Banbury, Bangor, Barnsley, Barrow-in-Furness, Basingstoke, Bath, Bebington, Bedford, Belper, Beverley, Biggleswade, Birkenhead, Bishop Auckland, Blackburn...
3. ### Need help with this formula

Hi all, MY formula is this =IF(OR(ISBLANK(E15),ISBLANK(F15)), "", "\$" &E15/F15*100 &"/100g") and my result is this <colgroup><col><col><col></colgroup><tbody> \$3.20 175g \$1.82857142857143/100g </tbody> Issue I'm having is I would like to only have the multiplied number to 2 decimal...
4. ### If Statements Based on Number of Decimal Places?

Hello all, I was wondering if it is possible to create an if statement that does different things depending on how many decimal places appear in a number? For example if I have the numbers 27.23 and 30.496 can I do an if statement that multiples 27.23 by 4, but multiplies 30.496 by 5? What I...
5. ### Ranking lap times with 2 decimals

So im trying to rank my lap times but i keep getting the error that my lap time is a text value and not a number value because it has two decimal places. e.g 2.23.340 i get an error but 2.23 works. is there a way to rank numbers with two decimal places. Formula =RANK(D3:D27,LapTimes,1)...
6. ### How to clear Recent Places

How can "Recent Places" list be cleared with VBA? I have a hidden folder that I do not want to reveal.
7. ### VBA Get Function

Hi all I have a scenario where I need to truncate a number to 4 decimal places ( no rounding) . I have searched google and this site and found the Get() Function. This function works great IF the decimals to 4 places are greater 0 ( 515.1234678 gives me 515.1234) however if the number is to...
8. ### Set Cell Equal to Another Cell in VBA without Rounding

Hello, I am using the following code: Sheets("Sheet1").Range("A1").Value = Sheets("Sheet2").Range("A2").Value However, if "A1" is a value to 3 decimal places, the code is rounding it to 2 decimals. For example, if "A1" is \$0.437, after the code executes, "A2" on "Sheet2" is \$0.44 I used...
9. ### Remove a Dash from numbers in a column

Hello- I am running a macro and have a column with numbers that areformatted as such: 15-3282 401-3264 6-3282 The dash can be in different places, How can I run a line of code that will reformat the entirecolumn? Thank you,
10. ### #value!

hi, I inserted =IF(B16>0,B16*O10), but if B16 is blank i get '#value!' error , how can I resolve? FYI - cell 010 formatting = Currency (2 decimal places) KR Trevor3007
11. ### Difficulty with Roundup

I've created a formula: Roundup(H3*L3,0,). I also have the Numbers option in the Format/Cell dropdown list set to 2 decimal places. The formula is producing a number calculated to two decimal places. By the same token, if I set the Numbers option in the Format/Cell dropdown to 0 decimal...
12. ### Counting ascending and falling numbers in a row

Hi I would like to count ascending and falling numbers in a row from a row of 13 numbers. I want to count 3 numbers in a row and 4 numbers in a row. 3 numbers in a row can come in 11 different places and 4 numbers in a row in 10 different places. I just need the numbers and not the...
13. ### Formula needed!

I have been to the following places, the following number of times. A 4 B 3 C 2 D 2 E 2 F 2 G 2 H 1 I 1 J 1 I want to find a formula that tells me that i’ve been to x places, x amount of times. So at the moment x=2. If I go to C again, which makes C=3, then x=3. I have made a COUNTIF formula...
14. ### Custom format for decimal places

Is there a custom format that can be applied to the following? Results like in column B, but I'm needing to apply the custom format on column A. Column A is the result of a formula. AB 115.2815.28 215.0015 312.2312.23 40.020.02 514.0014 60.110.11
15. ### Rounding

Is it possible to have a cell within a spreadsheet that has greater then 30 decimal places?
16. ### Excel automatically rounding to 6 decimal places

Hi there, I am trying to us a SUMIFS to sum a range of numbers based upon a reference. The reference I am using has 12 decimal places (stored as text), i.e.: 120577342.663008205083 When Excel uses the reference it seems to automatically round it up to 6 decimal places i.e...
17. ### Remove trailing zeros

I am creating a sheet where numbers are raised to negative powers of ten. If I format the cells with the Number Type set to Number and the Decimal Places set to 0 all numbers less than 1 display as 0. I can format each cell individually to display the number of decimal places I want which...
18. ### Add into a formula Right & Len

My current formula is =SUMPRODUCT(--(A11>='Front End'!B\$25:B\$72),--('Front End'!C\$25:C\$72>=A11+"00:15"))+SUMPRODUCT(--(A11>='Combo EE'!B\$36:B\$69),--('Combo EE'!C\$36:C\$69>=A11+"00:15")) I'm having an issue because when I copy data in the range C25:C72 and B25:B72 the time now has a date in...
19. ### fixing distances to 3 decimal places when using Concatenate

Hi there, when using the Concatenate() command, was wondering how you go about making the distances only show 3 decimal places. EG at current I have CONCATENATE(BEARING,BEARING SYMBOL,MIN,,,SECS,"~10.000000112) Any help would be greatly appreciated, Warmest Regards, Rob
20. ### Formula Issue

I need a formula which as an example, when A1 is 6, then it multplies A2 by 8.0186 and puts this value in cell A3, however if A1 is 8 then it multiples A2 by 14.2554 and places the value in A3 Thanks Everyone

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...