Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.83 seconds.

  1. Replies
    26
    Views
    543

    Re: VBA Formula

    I have modified the code I posted earlier and I believe it will now do what you want correctly...

    Sub YesLast4Max()
    Dim LR As Long, C As Long
    For C = 2 To Cells(4,...
  2. Replies
    11
    Views
    148

    Re: Autofill dates for specified year

    Hmm! The code works perfectly for me. I guess the problem has to do with the fact that you, along with most of the rest of the world, uses the wrong date order when displaying...
  3. Replies
    11
    Views
    148

    Re: Autofill dates for specified year

    I missed that as I had run your code beforehand to see exactly what was being placed in the cells and doing that changed the cells to Date format.:banghead:

    I can save my one-liner with the...
  4. Replies
    11
    Views
    148

    Re: Autofill dates for specified year

    Here is alternate one-liner that you can consider (assumes as Peter did that the starting date is in cell A1)...

    Sub FillDates()
    [A1].Resize(DateAdd("yyyy", 1, [A1]) - [A1]) = [A1+ROW(1:366)-1]...
  5. Re: Defining a name for more than one range

    I am hardly an expert on Defined Names so others who are will hopefully chime in, but I could not find a way to create a disjointed Defined Name that is usable. The closest I could come to what you...
  6. Re: Defining a name for more than one range

    With those defined names, you could also write that formula this way...

    =SUM(Apple,Pie)
  7. Replies
    29
    Views
    459

    Re: Reverse Hex Byte Pairs

    Mike changed the name of his reversal function that the above function is calling (instead of ending in "Rev" here it ends in "Reverse"). Change what I highlighted in red to the name of the reversal...
  8. Replies
    29
    Views
    459

    Re: Reverse Hex Byte Pairs

    Here is another way to write this function...

    Function Hex_Pairs_Rev(ByVal HexStr As String) As String
    Dim N As Long, Arr() As String
    Arr = Split(Trim(Format(HexStr, Application.Rept("@@ ",...
  9. Replies
    5
    Views
    83

    Re: If cell is Empty!

    If I am not mistaken, this non-looping event code should also work...

    Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Evaluate("COUNTA(" & Sheets(1).Name & ":" &...
  10. Re: Shorten Formula =IF(D8=5,L8,0)+IF(D9=5,L9,0) repeat unto row 209 +IF(D209=5,L209,0)

    Try using the SUMIF formula...

    =SUMIF(D8:D209,5,L8:L209)
  11. Re: Defining a name for more than one range

    Select the two ranges (select the first then hold down the CTRL key while selecting the second) and then type the Defined Name for this discontinuous range in the Name Box (located to the left of the...
  12. Replies
    20
    Views
    604

    Re: Stripping text in Excel

    I think you will need to tighten up the above rule somewhat or else explain why message.[1] was not outputted from this sentence in your cell A1 example text...

    "It is a coherent set of signs that...
  13. Replies
    26
    Views
    543

    Re: VBA Formula

    Here is another macro that should work...

    Sub YesLast4()
    Dim LR As Long, C As Long
    For C = 2 To Range("A1").End(xlToRight).Column
    LR = Columns(C).Find("*", , xlValues, , xlRows,...
  14. Replies
    6
    Views
    119

    Re: VBA: concatenate data

    Yikes! I completely missed that. :banghead: Thanks for pointing that out.

    Simple fix though...

    Since you indicated A1:A100 and B1:B100 all had data in them, this macro should work for you...
    ...
  15. Replies
    6
    Views
    119

    Re: VBA: concatenate data

    Since you indicated A1:A100 and B1:B100 all had data in them, this macro should work for you...

    Sub ConcatAandB()
    [C1:C100] = [A1:A100&CHAR(10)&B1:B100]
    End Sub

    If the cells will not be...
  16. Re: Returning the 2nd, 3rd, 4th values from a cell based on a list

    Does this formula do what you want where I have assumed your ingredients are delimited by a comma (shown in red below)...

    =TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",300)),COLUMNS($B:B)*300,300))
  17. Re: Displaying time as 1:00PM (etc) for a Label Value?

    In what way was it a "no-go"? Please describe happened...

    Did you get an error? If so, what was it?

    If not, what actually happened and what were you expecting to happen?

    What was in the cell...
  18. Replies
    17
    Views
    204

    Re: adding time issue

    The SUMPRODUCT function evaluates each cell of a range one cell at a time (you could also use SUM but then you would have to commit it with CTRL+SHIFT+ENTER) before performing the summation... adding...
  19. Replies
    17
    Views
    204

    Re: adding time issue

    Does this formula work for you (using the original range you posted with your values remaining as Text)...

    =SUMPRODUCT(C15:O15+0)

    Format the cell you put this in using [h]:mm:ss
  20. Re: Two numbers to left of * and two numbers to right of *

    Another way to consider...

    Sub test()
    Dim Response As String, Parts() As String
    Response = "CEQ"
    Parts = Split(ActiveSheet.Name, Response)
    If UBound(Parts) Then
    ' Parts(0) will...
  21. Re: VBA to fill array with delimited data from text file

    Just reminding you both that this violates Forum Rule #4 . The reason for that rule is those of us who want to help can't because the information we need is only being shared with one person. The...
  22. Re: VBA to fill array with delimited data from text file

    I am unclear about the output you want. For the above, is it this in each cell of Column A...

    CA
    0
    258
    21122
    0
    0
    0
    0
  23. Re: VBA to find the day number of the year from today's date?

    You can eliminate the concatenations by moving those numbers into the format pattern taking care to put a backslash in front of any 0's that are to remain constant within the pattern...

    dn =...
  24. Re: VBA to find the day number of the year from today's date?

    :confused: I thought you wanted a VBA solution... is that no longer the case?
  25. Re: Search in TXT file and return line to Excel

    jphumani,

    I have a question I am not too clear on... can the Identification Number appear more than once in any single text file?
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4