Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Excel Problem (2003)

    Hi,


    I have a query regarding a macro.

    I have a sheet in which time is given (sheet is attached). In this sheet time is given in cells but before time some values are coming for ex. "09 - 17:53" where 17:53 is the time and "09 - " is the unwanted value. I have multiple columns in my sheet and I want that when I run the macro, only time( for ex. 17:53 ) would be there.

    Also, I have some merge cells in my sheet that contains values like "PL". I want that when I run a macro it will unmerge those cells that contains values"PL" and paste the same value(PL) in unmerge cells as well.


    I m attaching the workbook that contains sheets original and Desired. Original is original sheet that I have and Desired the sheet that contains the desired values.

    Any help would be highly appreciated.
    Thanks and Regards,
    Ankit
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Problem (2003)

    Try this macro. Change the constant c as needed - it's the number of the column to be handled.

    Sub ExcelProblem()
    Const c = 1 ' Column to be manipulated
    Dim r As Long
    Dim m As Long
    Dim p As Integer
    m = Cells(Rows.Count, c).End(xlUp).Row
    For r = 1 To m
    If Cells(r, c).MergeCells Then
    Cells(r, c).UnMerge
    End If
    p = InStr(Cells(r, c), "-")
    If p > 0 Then
    Cells(r, c) = TimeValue(Trim(Mid(Cells(r, c), p + 1)))
    End If
    Next r
    ' Optional - format cells
    Range(Cells(1, c), Cells(m, c)).NumberFormat = "h:mm"
    End Sub

  3. #3
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Excel Problem (2003)

    Hi again,

    Thanks for ur help.

    There are some modifications that I want. I want the the macro to run for the selected range of colums . Moreover, when it unmerge cells, its not pasting the value which is in blank unmerge cell( value which was present before unmerging).

    Also, its showing a type mismatch error 13.


    Thanks in advance.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Problem (2003)

    Here is a version that acts on the current selection:

    Sub ExcelProblem()
    Dim oCell As Range
    Dim p As Integer
    For Each oCell In Selection
    If oCell.MergeCells Then
    oCell.UnMerge
    End If
    p = InStr(oCell, "-")
    If p > 0 Then
    oCell = TimeValue(Trim(Mid(oCell, p + 1)))
    End If
    Next oCell
    ' Optional - format cells
    Selection.NumberFormat = "h:mm"
    End Sub

    This code works correctly on the sample that you attached. If it doesn't work on other data, I'd have to see a representative sample of those data.

  5. #5
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Excel Problem (2003)

    Hi Hans,


    Thanks for the macro you jus gave me. Its working absolutely perfect.

    I have attached a sample sheet in which i normally work. Kindly have a look at it .

    I want that macro to run on these type of sheets and on those types of data. It contains images as well.

    The original form as well as desired from is clearly shown in the attached sheet. Pls. check.

    Also , macro needed is for the selected range.


    Thanks and Regards,
    Ankit
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Problem (2003)

    Try this version:

    Sub ExcelProblem()
    Dim i As Integer
    Dim n As Integer
    Dim oCell As Range
    Dim rngMerge As Range
    Dim p As Integer
    n = ActiveSheet.Shapes.Count
    For i = n To 1 Step -1
    If Not Intersect(Selection, ActiveSheet.Shapes(i).TopLeftCell) Is Nothing Then
    ActiveSheet.Shapes(i).Delete
    End If
    Next i
    For Each oCell In Selection
    If oCell.MergeCells Then
    Set rngMerge = oCell.MergeArea
    oCell.UnMerge
    rngMerge = oCell
    End If
    p = InStr(oCell, "-")
    If p > 0 Then
    oCell = TimeValue(Trim(Mid(oCell, p + 1)))
    End If
    Next oCell
    ' Optional - format cells
    Selection.NumberFormat = "hh:mm"
    End Sub

  7. #7
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Excel Problem (2003)

    Thank u so much for ur patience and Time ...



    Hats off to u........

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •