Results 1 to 7 of 7
Thread: Excel Problem (2003)

20081005, 16:47 #1
 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

20081005, 17:45 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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

20081005, 19:22 #3
 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.

20081005, 19:41 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20081005, 19:54 #5
 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

20081005, 20:22 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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

20081005, 20:35 #7
 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........