Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.81 seconds.

  1. Replies
    1
    Views
    35

    Re: Macro for Message Pop-up

    You would not do what you asked with macros, rather, you would do it with event code.

    For the MessageBox when going into or leaving a worksheet, right click that worksheet's tab and select "View...
  2. Re: Join all row cells in a single cell by vertical bar

    Here is a formula solution...




    =SUBSTITUTE(TRIM(IF(A2="","",TEXT(A2,"00 "))&IF(B2="","",TEXT(B2,"00 "))&IF(C2="","",TEXT(C2,"00 "))&IF(D2="","",TEXT(D2,"00 "))&IF(E2="","",TEXT(E2,"00...
  3. Re: Join all row cells in a single cell by vertical bar

    Here is a formula solution...

    =SUBSTITUTE(TRIM(A2&" "&B2&" "&C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2)," ","|")
  4. 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...
  5. Replies
    12
    Views
    409

    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))
  6. Replies
    18
    Views
    474

    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...
  7. Replies
    18
    Views
    474

    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\/\/"))
  8. Replies
    18
    Views
    474

    Re: formatting a date

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

    =TEXT(SUBSTITUTE(A1,"-",""),"00000")
  9. Replies
    5
    Views
    170

    Re: Change how date is converted.

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

    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...
  11. 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)
  12. Replies
    4
    Views
    65

    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") =...
  13. Replies
    4
    Views
    65

    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 +...
  14. 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)...
  15. Replies
    3
    Views
    149

    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...
  16. 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
  17. 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...
  18. Re: making excel recognize it's 20XX not 19XX

    They certainly did bury it deep; however, the OP said in his opening post that...
  19. 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...
  20. 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?
  21. Replies
    2
    Views
    49

    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...
  22. 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""")
  23. 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 .
  24. 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...
  25. 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 =...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4