1. ## 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

2. ## 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. ## 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.

4. ## 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. ## 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

6. ## 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. ## 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
•