Search:

Type: Posts; User: Peter_SSs; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.84 seconds.

  1. Re: Loop through a range of cells and skip blank cells.

    Here is another approach. In particular, this would be much faster if the data is large.
    I have assumed that data starts in cell A1 of "Source" and that "ET Target" is empty when the code is run. If...
  2. Re: Create / Consolidate Data Range from Full Values

    I understand that you already have a suitable solution, but in case it is of interest to you, in columns D:E below are some alternative formulas that do not require the Ctrl+Shift+Enter confirmation....
  3. Re: How do I subtitute the name of a sheet with a cell number

    Try
    =INDIRECT(B2&"!C2")

    Note though that INDIRECT is a volatile function so can slow you sheet if over-used.
  4. Thread: Last Month

    by Peter_SSs
    Replies
    7
    Views
    133

    Re: Last Month

    :oops: I rushed in there Eric didn't I? (I often do with date questions :))

    So an alternative, but no better than yours, might be?
    =MONTH(EDATE(TODAY(),-1))
  5. Thread: Last Month

    by Peter_SSs
    Replies
    7
    Views
    133

    Re: Last Month

    Just this should do it too, shouldn't it?

    =MONTH(TODAY())-1
  6. Re: Concatenate a string of text from one col., from corresponding text in another col. that matches a cell

    Is this any use? I have only used one sheet for simplicity here but columns D:E could be put on your second sheet.
    This is an array formula so should be entered without the {} but confirmed with...
  7. Replies
    35
    Views
    4,948

    Re: Extract multiple keywords from text string

    Did you try the user-defined function from post #7 ?

    <b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt;...
  8. Replies
    3
    Views
    82

    Re: Sorting Row-Paired Data (Macro?)

    Glad you something to work for you. :)
  9. Re: Cut row to new sheet if text is found within multiple columns

    Good point. :)
    Another issue with the code is that it copies the data in the helper columns I use in Sheet1 to Sheet2

    Try this version which should address both the above issues. Changed lines...
  10. Thread: Formula Help

    by Peter_SSs
    Replies
    9
    Views
    167

    Re: Formula Help

    You're welcome. Glad to help. :)


    Based on the OP's samples I made the assumption that such a circumstance would not occur. If it can, I would certainly want to modify the code.
  11. Re: Cut row to new sheet if text is found within multiple columns

    Give this a try in a copy of your workbook.


    Sub Copy_Rows()
    Dim a As Variant, b As Variant
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim nc As Long, lr As Long, i As Long, j As Long, k As...
  12. Re: How to create a range from a cell value and repeat time in another cell ?

    Try in A1, Copied down as far as you might ever need

    =IF(ROWS(A$1:A1)>C$1,"",B$1)

    Now try changing B1 and/or C1 and look at the new values in column A.
  13. Replies
    5
    Views
    218

    Re: Code to replace formula

    You are very welcome. Thanks for letting us know.
  14. Thread: Formula Help

    by Peter_SSs
    Replies
    9
    Views
    167

    Re: Formula Help

    Actually, if all your values are single digits per your examples, then you could use a formula using standard worksheet functions like this, copied across.

    <b>Count Back</b><br /><br /><table...
  15. Thread: Formula Help

    by Peter_SSs
    Replies
    9
    Views
    167

    Re: Formula Help

    I am assuming this example is a mistake and that F1 should be 3 since there is a 5 in row 4 (5-0-8)

    If that is the case then you could try this UDF


    Function HowFarBack(MyRange As Range, myCol...
  16. Re: Cut row to new sheet if text is found within multiple columns

    You are now looking for the search text in any one of 18 columns instead of just 2?


    What is your ultimate goal, copy or cut?
    When I get to suggest some code, I will want to know which, as the...
  17. Replies
    3
    Views
    82

    Re: Sorting Row-Paired Data (Macro?)

    This could be done with a macro, but would this manual approach be any use?

    1. Use a vacant column (I have inserted a new column B) & insert a formula like my B2 and copy down.

    <b>Excel...
  18. Re: Cut row to new sheet if text is found within multiple columns

    Welcome to the MrExcel board!

    1. Does the (partial) text need to occur in both column C and column K on a particular row for that row to be copied?

    2. Can you confirm that row 1 of Sheet1...
  19. Replies
    5
    Views
    218

    Re: Code to replace formula

    Before moving to code, you could try a formula like this. For me, it is about 5 times faster than your existing formula.
    I have assumed that the formula quoted actually starts in row 12 with...
  20. Replies
    21
    Views
    526

    Re: Create a summary list

    No problem. Glad to help.
  21. Thread: Formula Help

    by Peter_SSs
    Replies
    9
    Views
    167

    Re: Formula Help

    1. Could you give a couple more examples of sample data and expected results?

    2. Is the 7-7-2 in your sample above all in one cell or does that mean A10=7, B10=7, C10=2?
  22. Replies
    21
    Views
    526

    Re: Create a summary list

    Would this be sufficient?

    Sub Build_Table_v2()
    Dim rData As Range

    Set rData = Range("A1", Range("B" & Rows.Count).End(xlUp))
    rData.AdvancedFilter Action:=xlFilterCopy,...
  23. Replies
    35
    Views
    4,948

    Re: Extract multiple keywords from text string

    Your sample data is not entirely clear and you have not shown the expected results and their layout. Can you clarify both the sample data and the expected results?
  24. Re: cap Excel 2010 rows and columns in new spreadsheets

    You are welcome. Hope it all works out for you. :)
  25. Re: Format the 3 numbers in a column closest to ZERO

    Cheers :)
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4