Search:

Type: Posts; User: Rick Rothstein; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.88 seconds.

  1. Re: Extract and arrange the numbers small to larger values

    Am I remembering correctly from past postings of yours... except for the zero values, aren't your numbers in each row of Columns E:N always in sorted numerical order from left to right? If so, this...
  2. Replies
    11
    Views
    377

    Re: Problem with VBA Loop

    Try changing that line of code to this and see if it works correctly...

    sumall = sumall + Val(alpha_1y(i, 1))
  3. Replies
    18
    Views
    464

    Re: formatting a date

    The key to the TEXT function (and cell formatting) is that in the pattern string certain characters have special meaning (for example, the "d" standing for the day number in a date as opposed to...
  4. Replies
    18
    Views
    464

    Re: formatting a date

    I think this formula will do what you want...

    =UPPER(TEXT(D4+F4-SIGN(B5&1)*C5/24,"\B\/ddhhmm\Z mmm\/\/"))
  5. Replies
    18
    Views
    464

    Re: formatting a date

    If your formula is works for the OP, then I think this one will also...

    =TEXT(SUBSTITUTE(A1,"-",""),"00000")
  6. Replies
    5
    Views
    138

    Re: Change how date is converted.

    How would you type in the date October 17, 2019 into a cell using only numbers and slashes?
  7. Replies
    5
    Views
    138

    Re: Change how date is converted.

    So your system's natural date order is d/m/y but you are getting dates with m/d/y date order and you want to preserve them, is that correct? If so, I do not think you will be able to do that with...
  8. Re: Adding a +1 to a Row when dragging across Columns

    This formula should produce the displayed results that you want when dragged across...

    =COLUMNS($B1:B1)
  9. Replies
    4
    Views
    64

    Re: Numbering fixed amount of Rows

    Actually, using Arthur's formula as a base, we can do the macro without using a loop...

    Sub Deciles()
    Range("B1:B62208") = [IF({1},CEILING(ROW(A1:A62208)/7776,1))]
    Range("B62209:B77757") =...
  10. Replies
    4
    Views
    64

    Re: Numbering fixed amount of Rows

    Here is a macro that will fill your range with constants rather than formulas...

    Sub Deciles()
    Dim R As Long
    For R = 1 To 69985 Step 7776
    Cells(R, "B").Resize(7776).Value = Int((R +...
  11. Re: excel 2002 convert numbers to mathematical

    Are your "numbers" left justified? If so, they are text values. In order for that to be the case, I suspect you have some trailing non-breaking space attached to the numbers. Give this a try...

    1)...
  12. Replies
    3
    Views
    147

    Re: Adding Numbers with Text in Cell

    Here is a way to do this without using Regular Expressions...

    Function AddNums(ParamArray NumOrRng())
    Dim V As Variant, VV As Variant
    For Each V In NumOrRng
    If VarType(V) < vbArray Then...
  13. Re: If value is 12 then fill range 1-12 etc..

    This macro should do what you want...

    Sub EnterNumberSeries()
    [C1].Resize([A1]) = Evaluate("ROW(1:" & [A1] & ")")
    End Sub
  14. Re: making excel recognize it's 20XX not 19XX

    No, if you entered a date and the code "corrected" it for the year, your colleague's computer would show the corrected year (2045); however, if your colleague put a date into the worksheet using her...
  15. Re: making excel recognize it's 20XX not 19XX

    They certainly did bury it deep; however, the OP said in his opening post that...
  16. Re: making excel recognize it's 20XX not 19XX

    Give this workbook event code a try (it will automatically make all date years be in the 2000's when they are entered into a cell in Column F or G on the indicated sheets)...

    Private Sub...
  17. Re: making excel recognize it's 20XX not 19XX

    In addition to Joe4's question above, is there a particular sheet and range that your dates can be in or are you wanting a general fix for any sheet in the workbook inside of any cell on the sheet?
  18. Replies
    2
    Views
    46

    Re: Prevent first row to be deleted

    Maybe this in place of what you posted...

    With .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
    .AutoFilter Field:=1, Criteria1:="xxxx"
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End...
  19. Re: Cell recognize positive or negative value then adds certain texts to the 2 possibilities

    Assuming your value (percentage) is in cell A1, give this formula a try...

    =TEXT(A1,"""Overestimate"" 0%;""Underestimate"" 0%;""Spot On""")
  20. Re: Replacing an On Error with an If, regarding copying cells that may not exist after a filter

    If your filter is hiding every row in the range being examined, the you code will stop with a "No Cells Found" error. See what I wrote in Message #4 .
  21. Re: Replacing an On Error with an If, regarding copying cells that may not exist after a filter

    The On Error code lines in Norie's code do not persist and should not affect any other code. With that said, if you can guarantee that at least one row will be visible when you run the macro, you can...
  22. Re: VBA Code - Delete rows once column A no longer numbers

    Does this macro do what you want...

    Sub DeleteRubbishRows()
    Dim LastNumberRow As Long, LastRow As Long
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    LastNumberRow =...
  23. Replies
    8
    Views
    201

    Re: find last occurence of x

    Will a cell equal to TODAY() appear only once in Column A or could today's date appear more than once in Column A?

    If more than once, are we supposed to put the "X" in Column G for only the last...
  24. Replies
    8
    Views
    201

    Re: find last occurence of x

    Make what cell "X"... the one with in Column A with the date in it?
  25. Replies
    8
    Views
    201

    Re: find last occurence of x

    Try this in your macro...

    Sheets("Sheet2").Columns("G").Find("X", , , xlWhole, , xlPrevious, False, , False).Offset(7) = "X"
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4