Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.86 seconds.

  1. Re: odd cell behavior: can overwrite cell contents but not edit cell contents

    Is there any event code in the code module for the worksheet that cell is in (not a general code module... the worksheet code module)?
  2. Replies
    2
    Views
    35

    Re: Invalid Qualifier

    Which line of code was highlighted when the error occurred?

    Also, do you have procedures named get_Minus_Day and get_SessionDate?
  3. Re: Delete Rows Where Column A Does Not Equal a Number or Numeric Value

    This might work the way you want...

    Sub DeleteNonNumber()
    On Error Resume Next
    Columns("A").SpecialCells(xlConstants, xlTextValues).EntireRow.Delete
    On Error GoTo 0
    End Sub
  4. Re: Replacing a cell reference within a formula with the hard-coded reference

    I had written that code several years ago and forgot that the person I wrote it for wanted to preserve the original formulas and have the "replacement" formula next to it. In his case, the original...
  5. Re: Replacing a cell reference within a formula with the hard-coded reference

    Select the cells you want to process first, then run the macro. The macro will replace every cell reference with that cell reference's value within every formula within the selected cells.
  6. Re: Replacing a cell reference within a formula with the hard-coded reference

    This might do what you want. The following macro will replace all cell reference in with the value at the referenced cells within the formula of all cells within the current Selection. This code will...
  7. Re: Addig specif numbers within one cell

    That means you have two procedures both with the same name (BracketSum)... VBA does not know which one to use so it issues the error. Remove or comment out one of them (the older one if you want to...
  8. Re: Combining 2 lists (for each cell in first list assign all cells in second list)

    For those who might be interested, here is a macro that does what the OP asked for without using a loop...



    Sub NamesAndFruits()
    Dim List1 As Variant, List2 As Variant, Nme As String, Fruit...
  9. Replies
    2
    Views
    53

    Re: custom formatting

    If you won't mind exact number of kilos (e.g., 8000) displaying with a trailing decimal point (i.e., 8. k), then you can use this custom format...

    [>=1000]0.####, k

    where you would put as many...
  10. Re: Addig specif numbers within one cell

    Here is another UDF (user defined function) that you can consider...

    Function BracketSum(St As String) As Double
    Dim V As Variant
    For Each V In Split(St, "[")
    BracketSum = BracketSum +...
  11. Re: Array: how to populate it starting from a cell's value up to zero

    I am not sure if you asked for an array solution because you are taking a class and are currently studying arrays, but what you asked for can be done without using arrays in a lot less lines of...
  12. Re: VBA: Remove all items from Scripting Dictionary

    Untested but why can't you use .RemoveAll within the dictionary's With..EndWith block (just like you did for the .Item property)?
  13. Replies
    2
    Views
    82

    Re: Format Cells

    Given the formats you showed us, how do you know the missing dash belongs at the end instead of at the beginning? Since you would need event code to do what you asked, wouldn't it be better to have...
  14. Re: How to split units from numbers stored in a cell that is custom formatted

    So are you saying Column A has letters like A, B, C in it and Column B has your formatted numbers with each Column's values starting on Row 2 (headers being on Row 1)? If so, give this macro a try......
  15. Re: message box if no cells in range > 0

    Did you write this correctly? Shouldn't the second greater than symbol be an equal sign instead?
  16. Replies
    8
    Views
    135

    Re: Delete rows based on empty cell

    If you were to run the code when there were no blank cells for the SpecialCells function to find, it would raise an error... the On Error Resume Next stops VBA from raising that error and tell it to...
  17. Replies
    8
    Views
    135

    Re: Delete rows based on empty cell

    I do not think you need calculate and/or specify the last row as SpecialCells will not look below the bottom of the UsedRange on the sheet it is examining for blank rows. I believe this should do the...
  18. Replies
    25
    Views
    1,314

    Re: Find next whole number in list

    If you want the number of rows between the first real whole number and the second real whole number, you could use this array-entered** formula...
    ...
  19. Re: rearrange text in cell (more complex than a first name surname switch!)

    Actually, I had a perfectly good formula in Message #10 which could be used as the basis for a non-looping macro...

    Sub barley18()
    Dim Addr As String
    Addr = "A1:A" & Cells(Rows.Count,...
  20. Replies
    25
    Views
    1,314

    Re: Find next whole number in list

    I am not sure the OP is actually after the numbers themselves as opposed to the distance from each other; however, with that said, the following one-liner will output the same list as your code...
  21. Re: rearrange text in cell (more complex than a first name surname switch!)

    However, if you want a VBA macro solution, give this one a try...



    Sub barley18()
    Dim Arr As Variant, Cell As Range
    Application.ScreenUpdating = False
    For Each Cell In Range("A1",...
  22. Re: rearrange text in cell (more complex than a first name surname switch!)

    Here is another formula solution that you can consider...

    =SUBSTITUTE(MID(REPLACE(A1,FIND(",",A1),0,", "&LEFT(A1,FIND("-",A1)-2)),FIND("-",A1)+2,LEN(A1)),", ",CHAR(10))
  23. Re: Convert duration in text to HH:MM:SS format in excel

    Here is the UDF (user defined function) that I came up with (it will allow abbreviations for the Hours, Minutes and Seconds in case that should ever occur)...

    Function GetTime(ByVal S As String)...
  24. Re: VBA Code to drop values in cells at the same time

    So why Fluff13... were Fluff1 through Fluff12 really taken already? :laugh:
  25. Re: VBA Code to drop values in cells at the same time

    Yes, but are you really a blonde? :diablo:

    On an aside, I have been also volunteering on the "Excel Forum" forum and there is a volunteer there using the name Fluff13 and they are from...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4