Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.84 seconds.

  1. Replies
    2
    Views
    33

    Re: Another Newb question

    I think this should do it...

    =0.07*MIN(100000,A1)+0.025*MAX(0,A1-100000)
  2. Replies
    11
    Views
    66

    Re: Remove all Numbers from list

    You can compact your code slightly by putting the R1C1 formula inside the Evaluate function call and removing the SpecialCells code line completely (the Value property can receive a formula value...
  3. Re: Formula for Separating Digits after nth Digit

    And just to prove there is almost always more than one way to do things in Excel, here are three more formula that will return those trailing characters (if any)...

    =MID(A2,6+(MID(A2,6,1)="-"),99)...
  4. Re: How to include double quotation marks in a formula in vba

    Why the double closing square brackets?
  5. Replies
    21
    Views
    412

    Re: Cell Formatting

    Here is another way to write the Change event procedure...

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim IsDigit As Boolean
    Application.EnableEvents = False
    If Target.Address(0,...
  6. Replies
    9
    Views
    216

    Re: Adding a Minus Sign with a macros

    Weird... when I do the same thing, I get -36 in both cells. :confused:

    Maybe it is version related... I am using XL2010, what version are you using?
  7. Replies
    9
    Views
    216

    Re: Adding a Minus Sign with a macros

    Yes, it is necessary. Without it, if you put two different numbers in G8 and H8, the number in G8 ends up in both cells.
  8. Replies
    9
    Views
    216

    Re: Adding a Minus Sign with a macros

    Here is another macro (a one-liner) that will also do what you want...

    Sub Main()
    [G8:H8] = [IF({1},-ABS(G8:H8))]
    End Sub
  9. Re: Random Highlight only 1 in the each row

    I cannot get it to move the colors back and forth in any reasonable time frame, but maybe this will be acceptable to you...

    Sub RandomHighlight()
    Dim R As Long, X As Long, Z As Long, LastRow As...
  10. Re: Random Highlight only 1 in the each row

    Kishan lists his version at the top of his first messages... he is using XL2000.
  11. Replies
    2
    Views
    151

    Re: custom format, M has to be "M"

    The Custom Formatter thinks the M is a shortcut pattern for the word Month, so you must put it in quotes (or, alternately, precede it with a backslash) so it sees it as a plain character and not a...
  12. Re: Random Highlight only 1 in the each row

    Does this macro do what you want...

    Sub RandomHighlight()
    Dim R As Long, LastRow As Long
    Randomize
    LastRow = Cells(Rows.Count, "C").End(xlUp).Row
    Range("C6:E" & LastRow).ClearFormats
    ...
  13. Replies
    8
    Views
    110

    Re: Change column order

    It occurs to me that you may give more tests later on and, in the future, you may have more or less students, so here is my code fully generalized so that it will adapt automatically to the number of...
  14. Replies
    8
    Views
    110

    Re: Change column order

    Here is a VBA macro that will randomly mix your columns...

    Sub RandomizeStudentColumns()
    Dim Cnt As Long, RndIndx As Long, Cols As Variant, Tmp As Variant
    Cols = [TRANSPOSE(ROW(2:7))]
    For...
  15. Replies
    8
    Views
    110

    Re: Change column order

    Question: Did you always want to keep the same Identifier # associated with their same scores the way Eric's solution does? I ask because your original post seemed to disassociate the Identifier #...
  16. Re: Applying Conditional Formatting to only the first 10 strings in a cell

    Did you want to try my idea of replacing the formulas with event code (you and your users would not notice any functional difference)? If so, show us the formula that is in each of the cells (B63,...
  17. Re: Applying Conditional Formatting to only the first 10 strings in a cell

    Dante... that won't work because the cells still has formulas in them. If you set each cell equal to its value first, then it would work, but then the formula would be gone. The only way I can think...
  18. Re: Is there any way to change range's values more efficiently than looping?

    You do? :eek: I am honored... and speechless at the thought! :bow:




    Thank you so much for your kind words above and for this recommendation... you are far too kind. :oops:
  19. Re: Is there any way to change range's values more efficiently than looping?

    First off, if you have not seen it yet, I would direct you to footoo's solution (Message #11 ) for your original problem as it is much better than the Evaluate method Jaafar gave you even with the...
  20. Re: Is there any way to change range's values more efficiently than looping?

    I cannot give you a technical reason... it is just something I discovered when playing around with the Evaluate function when I first learned about it a few years back. The non-technical reason that...
  21. Re: Is there any way to change range's values more efficiently than looping?

    String functions do not natively process arrays, so it must be "induced". If there are no blanks within your range, these (from Jaafar's code in Message #4 ...

    Range("A1:A10") =...
  22. Re: VBA clear contents (number and date formats) but keep format

    I don't work with SQL, but as far as I know, importing data is the same as entering data and the cell adopts the format of the data being entered.
  23. Re: VBA clear contents (number and date formats) but keep format

    What does happen for them? For example, if cell L2 is custom formatted as "$0.00" and set its value equal to "", then go back and enter 98.76, what displays in the cell?
  24. Re: For Each Visible cell in range, get me the left 6 characters

    Another way to do it...

    Sub GetLeftSixCharactersFromVisibleCellsInColumnA()
    Dim Ar As Range
    For Each Ar In Columns("A").SpecialCells(xlVisible).Areas
    Ar.Offset(, 22) =...
  25. Re: VBA clear contents (number and date formats) but keep format

    It does for me. If the date format was currency for a cell before assign "" to it and I then enter a number back into the cell, that number gets formatted as currency.... if the cell was formatted as...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4