Posts by nilem

  • Filter and delete duplicates

    Re: Filter and delete duplicates


    Hi Angadpatil,
    try it

  • Filter and delete

    Re: Filter and delete


    Hi angadpatil,
    also try it

  • Automatically create a list of column contents based on criteria

    Re: Automatically create a list of column contents based on criteria


    Hi danielstone121,
    maybe UDF

    Code
    Function Daniel(rngAttendance As Range, rngDates As Range) As String
Dim x, i As Long, s As String: x = rngAttendance.Value
For i = 1 To UBound(x, 2)
    If x(1, i) = vbNullString Or x(1, i) = "WR" Then s = s & ", " & rngDates(1, i)
Next
Daniel = Mid(s, 3)
End Function


    In the worksheet, it will look like
    =Daniel(V2:AC2;$V$1:$AC$1)

  • Find any dates in a string?

    Re: Find any dates in a string?


    Quote from stildawn;695574

    ...So year in full, year in 2 digit, year first, year last etc. ...


    I think in this case, can not do without manual input. Something like

    Code
    Sub ertert()
Dim r As Range
With CreateObject("vbscript.regexp")
    .Pattern = "\d+.\d+.\d+"
    For Each r In Range("A1:A5")
        r.Value = .Replace(r, Application.InputBox("Change a file date", , .Execute(r)(0)))
    Next
End With
End Sub

  • Find any dates in a string?

    Re: Find any dates in a string?


    Hi Stildawn,
    maybe something like this

    Code
    Sub ertert()
Dim r As Range
With CreateObject("vbscript.regexp")
    .Pattern = "\d+.\d+.\d+"
    For Each r In Range("A1:A5")
        r.Value = .Replace(r, Format(Date, "dd.mm.yyyy"))
    Next
End With
End Sub

  • Convert into editable data

    Re: Convert into editable data


    Hi Howard,
    There is a picture (not grouped form) in your second file. And in this case I think you need not an Excel, but something like FineReader. Sorry

  • Convert into editable data

    Re: Convert into editable data


    Hi HowardC,
    try it

    Code
    Sub ertert()
Dim shp As Shape
ActiveSheet.Shapes("Group 204").Ungroup
For Each shp In ActiveSheet.Shapes
    If Len(shp.TextFrame2.TextRange) Then
        shp.TopLeftCell.Value = shp.TextFrame2.TextRange
    End If
    shp.Delete
Next shp
End Sub

  • Auto populate date to seperate tables in one sheet

    Re: Auto populate date to seperate tables in one sheet


    Hipaeych,
    maybe so

    Code
    Sub Find_Week2()
Dim r As Range, wk As Range
With Range("D1", Cells(Rows.Count, 4).End(xlUp))
    Set wk = .Cells(1)
    For Each r In Range("A1", Cells(Rows.Count, 1).End(xlUp))
        Set wk = .Find(r, after:=wk, lookat:=xlWhole)
        wk(1, 2) = r(1, 2)
    Next
End With
End Sub

  • Separate cell with "Comma" in different row & multiple each row "n" times

    Re: Separate cell with "Comma" in different row & multiple each row "n" times


    Hi arun_ebl,
    try it

  • MAC VBA to delete partial cell content and sum all unique values

    Re: Macro to delete partial cell content and sum all pertinent values in an unique ro


    Hi evt,
    try it

  • remove some word + split text

    Re: remove some word + split text


    try

    Code
    Sub ttt()
With Sheets("Sheet2")
    Sheets("Sheet1").Columns(1).SpecialCells(2).Copy .Range("A1")
    .Columns(1).Replace " -", "~"
    .Columns(1).TextToColumns DataType:=xlDelimited, Other:=True, OtherChar:="~"
    .Columns(2).Replace "Total", ""
End With
End Sub