1. W

    VBA - evaluate if a cell is formatted as a date (of *any* date type)

    I have a Task List workbook that launches a calendar app (Userform Control called MonthView) whenever a "date cell" is double-clicked. "Date Cells" are identified by their NumberFormat (mm/dd/yyyy). So the function tests the cell NumberFormat, and if it is "mm/dd/yyyy" then it loads the...
  2. S

    Numberformat with many columns at one go

    Hello I want to numberformat with many columns at one go Can i ws.Columns(3, 5, 9, 11, 23, 27).NumberFormat = Trim(Replace(Format(String(Len(Int(Cell.value)) - 1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00" Also Cell.value in red requires proper syntax Will the above...
  3. D

    Unable to set the NumberFormat property of the Range class; Run-time error “1004”

    I have an excel workbook which downloads historical stocks price data. The VBA code has been working without any problems for at least couple of years. Today I get error message: Run-time error “1004” - Unable to set the NumberFormat property of the Range class part of VBA code as it follows...
  4. E

    Type Mismatch using NumberFormat with DataBodyRange

    In the past I've done .Range.NumberFormat no issue, but having a problem using with .DataBodyRange . The following is what I have (with generic labeling): 'Code defining variables, etc with ws.listobjects(1) .DataBodyRange(r,c).NumberFormat = "0" 'Type Mismatch Error occurs end with...
  5. B

    changing date format

    Hey, so i have to change the date format in an excel sheet from yy-mm-dd to dd-mm-yy. i tried using numberformat like this 'Range("A2", "A50000").NumberFormat = "dd-mm-yy"' but this doesnt change anything. i also tried using format(Date,"dd-mm-yy") but this just sets the cell to have the date of...
  6. E

    VBA numberformat doesn't get saved

    Hi guys I got a strange problem. Here is the code to change the columns into 'accounting' numberformat then tries to save and close the workbook. With ActiveSheet .Range("G:I,Q:T").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" End With ThisWorkbook.Save...
  7. A

    Use of NumberFormat function chnages cell type from "Number" to "Custom"

    Hi all, I am trying to build a simple number formatting macro that would display negative numbers in parentheses e.g., -24000 should be shown as (24,000.00) or something like that. While I am able to accomplish said formatting change using Numberformat, the command is forcing the cell type to...
  8. C

    Date Format works for one column, but not the other

    Hello one of two lines are not working in Macro Columns("C:C").NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy" Columns("D:D").NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy" Only works for column C. I have tried combining the lines "C:D", reversing order...
  9. T

    Copy formatted cell values to another range as text

    How can I copy a bunch of formatted values to a new range and have them as text values exactly the way they were formatted? for example suppose my Regional Settings are set to French-Canada(note that French-Canada uses a comma for decimal point instead of a period). Note that I choose...
  10. D

    VBA NumberFormat

    is there a way to combine the following two commands in one? Sums_range.NumberFormat = "[>=1000]#'##0;0" Sums_range.NumberFormat = "0;-0;-" the first formats numbers in the 0'000 format (with tone for thousands) and no decimals the second formats zeros to dashes
  11. D

    Format number 0.000,000 to 0'000 ?

    For example number 67,194 (194 are decimals). I'd like it to appear as 67. A bigger number e.g. 12345,194 would appear 12'345. I tried NumberFormat = "0'000" and = "#'##0", but they don't work. The first gives 0'067, the second give '67.
  12. E

    How to get the current week -1 week and CYTD -1 year (use a CYweek in an number format).

    Dear Access wizards, Currently I'm using Access to store weekly data of our market. Every week I receive a flatfile which I integrate in Access. As a timekey I'm using a CYWEEK (example: 201615). Now the thing is, when I make a query, I want to be able to see the latest week (highest value)...
  13. L

    VBA help: Nested If statement with IsNumeric and NumberFormat etc

    Hi, I have a large spreadsheet that I receive often from a lab that I need to perform some statistical analysis on. I have been able to get what I need from nested if statements with regular cell functions, but I fear they may be confusing for my colleagues and am hoping for something a bit...
  14. S

    100 separator in Excel

    Hi All, I need custom number format for 100 separator . can Anyone please provide me the Number format. Thanks, Sandeep
  15. T

    Get formatted value/text of a Range without looping

    I'd like to be able to efficiently read all of the formatted values of a range of cells into an array. When I say formatted values, I mean the values as they are shown on the screen. Range.Value and Range.Value2 can efficiently retrieve the values into an array. Range.Text seems to work only...
  16. H

    Replace NumberFormat VBA Code

    Hi - I want to create a code that searches column T for "1" and replace this with "0001" (so it must be custom formatted to "0000", otherwise the number will be shown as "1" instead). I've done a similar replace/format code for "û" which works perfectly, so I don't know why it keeps giving me a...
  17. Alf1956

    Application.Dialogs(xlDialogFormatNumber) - Ingreso de número personalizado desde macro vba

    Buenas tardes, Uso Excel 2007 Básico. Con Excel 2003 lo hacía sin problemas, pero con la versión 2007, no puedo ingresar el siguiente formato personalizado: ... Selection.NumberFormat = "#,##0.00_);(#,##0.00)" ... Agradeezco se sirvan indicarme si desde una macro, se puede...
  18. U

    Worksheet_Change event to convert percentages to whole numbers

    I have a page for data entry that I want to automatically convert any data that is a percentage to a whole number (50% to 50 - not to 0.50). I am in the Sheet code (not the workbook) and I can't seem to get this to work. Data will likely be pasted by the user in bulk to a range (B3:H32) though...
  19. J

    Trouble with changing cell numberformat with VBA

    Hi, I believe Range.Numbeformat is the property I am looking for. I have a cell with a dropdown picker. If the user chooses "Distractor" from the picker, Worksheet_Change triggers an Inputbox to open, asking what kind of distractor it is. We'll use "Gloves" for this example. I want the cell...
  20. R

    Setting Range.NumbeFormat(Local) on non-English Office installations

    <code>On this page: The example for NumberFormat goes like this: Worksheets("Sheet1").Range("A17").NumberFormat = "General" However the string "General" seems incorrect when you have installed a non-English Microsoft Office...

Some videos you may like

This Week's Hot Topics