Re: $$Excel-Macros$$ Re: parsing time field
the problem you have here is that Excel doesn't recognize what you call time as actual time. Excel says that time is actually a fractional part of a day. since there are 24 hours in a day, 6:00am is 1/4 of a day, (or .25) 12:00 noon is 1/2 of a day, and 6:00pm is 3/4 of a day. If you were to change your sample cells to general format, you'll find that 7:58:31 becomes 0.332303240740741 but :00:00 and :07:36 are unchanged, because Excel doesn't recognize them as valid time formats. So they remain text strings. Functions like Hour(), Minute() and Second() expect to receive a time as a parameter. Not a text string. So the first thing we need to do is decide if we're wanting to convert the text strings to date/time format, or convert the date/time to text strings. To convert the time to a text string, you must first determine if the value IS a time value. I put this macro together. '=== Option Explicit Sub Parse_Time() Dim StrArray, LHour, LMinute, LSecond Dim TimeString, dtime As Date On Error Resume Next Err.Clear '--- ' Save the cell value '--- TimeString = Range(D2).Value '--- ' save the string in a date variable ' dtime = TimeString ' 'If saving it as a date produces an error, 'then it evidently wasn't a date! ' In that case, convert the date to a text string ' If (Err = 0) Then TimeString = Format(dtime, hh:mm:ss) End If '-- ' Split the string into an array '-- StrArray = Split(TimeString, :) '-- ' Store the individual array elements '-- LHour = StrArray(0) If LHour = Then LHour = 0 LMinute = StrArray(1) If LMinute = Then LMinute = 0 LSecond = StrArray(2) If LSecond = Then LSecond = 0 ' ' Display the values ' Range(L2).Value = LHour Range(M2).Value = LMinute Range(N2).Value = LSecond End Sub '== Now, if you have a whole bunch of these rows/columns to deal with, we could use put together a loop like: Option Explicit Sub Parse_Time() Dim R, C, C2, FirstCol, OffsVal Dim StrArray, LHour, LMinute, LSecond Dim TimeString, dtime As Date On Error Resume Next FirstCol = 4 OffsVal = 12 For R = 2 To 30 For C = 4 To 6 Err.Clear C2 = (C - FirstCol) * 3 + OffsVal '--- ' Save the cell value '--- TimeString = Cells(R, C).Value '--- ' save the string in a date variable ' dtime = TimeString ' 'If saving it as a date produces an error, 'then it evidently wasn't a date! ' In that case, convert the date to a text string ' If (Err = 0) Then TimeString = Format(dtime, hh:mm:ss) End If '-- ' Split the string into an array '-- StrArray = Split(TimeString, :) '-- ' Store the individual array elements '-- LHour = StrArray(0) If LHour = Then LHour = 0 LMinute = StrArray(1) If LMinute = Then LMinute = 0 LSecond = StrArray(2) If LSecond = Then LSecond = 0 ' ' Display the values ' Cells(R, C2).Value = LHour Cells(R, C2 + 1).Value = LMinute Cells(R, C2 + 2).Value = LSecond Next C Next R End Sub hope this helps, Paul S. From: Speilman_54 mbed...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Thu, April 7, 2011 1:49:12 PM Subject: $$Excel-Macros$$ Re: parsing time field Please forgive me I'm not the best programmer. The cells are initially a custom field but if you change them to text it isn't making a difference because of the : starting value Is it possible that you could break down what
Re: $$Excel-Macros$$ Compare strings in cells, return differences
Thanks STDEV(i) - very appreciated. On Apr 8, 6:29 am, STDEV(i) setiyowati.d...@gmail.com wrote: *=TextDif(A2,B2)* TextDif is an UDF, like this: Function TextDif(S1 As String, S2 As String) As String Dim Arr1, Arr2 Dim n As Integer, i As Integer, t As String Arr1 = Split(S1, ,): Arr2 = Split(S2, ,) For i = LBound(Arr2) To UBound(Arr2) For n = LBound(Arr1) To UBound(Arr1) If Arr1(n) = Arr2(i) Then Arr2(i) = Next n Next i For i = LBound(Arr2) To UBound(Arr2) If Len(Arr2(i)) 0 Then t = t Arr2(i) , Next i TextDif = t If Len(t) 0 Then TextDif = Left(t, Len(t) - 1) End Function On Fri, Apr 8, 2011 at 4:46 AM, SHC stuart.hallcoo...@googlemail.comwrote: I'd like a function script that compares the strings from two cells, and returns, in a third cell, the parts of the 2nd string that do not match parts of the 1st string (I don't need to return the parts of 1st string that do not match parts of the 2nd string). Examples: Cell1: Chris Cell2: Carl,Chris,Peter Result in Cell3: Carl,Peter Cell1: Chris,Carl Cell2: Carl,Peter,Chris Result in Cell3: Peter Cell1: Chris,Dave Cell2: Carl,Chris,Peter Result: Carl,Peter Cell1: Chris,Dave,Peter,Carl Cell2: Dave,Chris,Carl Cell3 is empty Note, parts of the strings (separated by commas) will not necessarily be in the same order. Any help would be most appreciated Thanks. ctv_TextDif about Chris and Peter.xls 29KViewDownload -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Data Chart
HI ALL, I want to draw a chart based on the data attached.Objective is to depict the implication of Coal Cost on Other than Coal Cost (Store Cost Water Cost etc) in Total Cost prominently. When i select Bar Graph/ other graph, only Coal Cost part is prominent from distance. Could any one suggest me any alternative manner. Regards, C.G.Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Book1.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Fwd: Data Chart
HI ALL, I want to draw a chart based on the data attached.Objective is to depict the implication of Coal Cost on Other than Coal Cost (Store Cost Water Cost etc) in Total Cost prominently. When i select Bar Graph/ other graph, only Coal Cost part is prominent from distance. Could any one suggest me any alternative manner. Regards, C.G.Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Book1.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Need Help!
As per my observation this is producing wrong result, please check it once again. On Fri, Apr 8, 2011 at 9:34 AM, §»VIPER«§ viper@gmail.com wrote: hi syed, find the attached without array. -- *Thanks Regards Thamu * On Fri, Apr 8, 2011 at 2:45 AM, syed aliya raza hashim hashim...@gmail.com wrote: Hi, Hi i have attached my file i want in sheet 2 there are column c in that i want the name from sheet 1 but there are two condition first is match by ID then match by Name as well then how can we do this -- syed aliya -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel * * -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ querry
Please reply. -- Nemi Gandhi 98204 92963 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel QUERRY.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Group Survey : Your feedback is important
Hi Sanjoo, Thanks for feedback. I also wanted to categorise the group into sub groups but we do not have such facility available in Google groups. Google Groups have been working to add new features in future but I am not sure what all will come. We need to live with this limitation. :) Rest of the group, Please participate in survey if you have not done yet. Thanks Regards Ayush Jain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Need Help!
Hi, Find the attachment. Thaks, Kishor On Fri, Apr 8, 2011 at 2:45 AM, syed aliya raza hashim hashim...@gmail.comwrote: Hi, Hi i have attached my file i want in sheet 2 there are column c in that i want the name from sheet 1 but there are two condition first is match by ID then match by Name as well then how can we do this -- syed aliya -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks, Kishor Kumar Ananthapalli -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel match_by_idname_then_pull_out_submitted_by_from_sheet_1(1).xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Re: parsing time field
Paul, That is exactly what I was looking for, I've been able to modify what you sent to do what I need and it works great, this will save a ton of manual inputting. Thanks so much for your help. On Apr 8, 8:49 am, Paul Schreiner schreiner_p...@att.net wrote: the problem you have here is that Excel doesn't recognize what you call time as actual time. Excel says that time is actually a fractional part of a day. since there are 24 hours in a day, 6:00am is 1/4 of a day, (or .25) 12:00 noon is 1/2 of a day, and 6:00pm is 3/4 of a day. If you were to change your sample cells to general format, you'll find that 7:58:31 becomes 0.332303240740741 but :00:00 and :07:36 are unchanged, because Excel doesn't recognize them as valid time formats. So they remain text strings. Functions like Hour(), Minute() and Second() expect to receive a time as a parameter. Not a text string. So the first thing we need to do is decide if we're wanting to convert the text strings to date/time format, or convert the date/time to text strings. To convert the time to a text string, you must first determine if the value IS a time value. I put this macro together. '=== Option Explicit Sub Parse_Time() Dim StrArray, LHour, LMinute, LSecond Dim TimeString, dtime As Date On Error Resume Next Err.Clear '--- ' Save the cell value '--- TimeString = Range(D2).Value '--- ' save the string in a date variable ' dtime = TimeString ' 'If saving it as a date produces an error, 'then it evidently wasn't a date! ' In that case, convert the date to a text string ' If (Err = 0) Then TimeString = Format(dtime, hh:mm:ss) End If '-- ' Split the string into an array '-- StrArray = Split(TimeString, :) '-- ' Store the individual array elements '-- LHour = StrArray(0) If LHour = Then LHour = 0 LMinute = StrArray(1) If LMinute = Then LMinute = 0 LSecond = StrArray(2) If LSecond = Then LSecond = 0 ' ' Display the values ' Range(L2).Value = LHour Range(M2).Value = LMinute Range(N2).Value = LSecond End Sub '== Now, if you have a whole bunch of these rows/columns to deal with, we could use put together a loop like: Option Explicit Sub Parse_Time() Dim R, C, C2, FirstCol, OffsVal Dim StrArray, LHour, LMinute, LSecond Dim TimeString, dtime As Date On Error Resume Next FirstCol = 4 OffsVal = 12 For R = 2 To 30 For C = 4 To 6 Err.Clear C2 = (C - FirstCol) * 3 + OffsVal '--- ' Save the cell value '--- TimeString = Cells(R, C).Value '--- ' save the string in a date variable ' dtime = TimeString ' 'If saving it as a date produces an error, 'then it evidently wasn't a date! ' In that case, convert the date to a text string ' If (Err = 0) Then TimeString = Format(dtime, hh:mm:ss) End If '-- ' Split the string into an array '-- StrArray = Split(TimeString, :) '-- ' Store the individual array elements '-- LHour = StrArray(0) If LHour = Then LHour = 0 LMinute = StrArray(1) If LMinute = Then LMinute = 0 LSecond = StrArray(2) If LSecond = Then LSecond = 0 ' ' Display the values ' Cells(R, C2).Value = LHour Cells(R, C2 + 1).Value = LMinute Cells(R, C2 + 2).Value = LSecond Next C Next R End Sub hope this helps, Paul S. From: Speilman_54
Re: $$Excel-Macros$$ Run - Copy / Paste - Repeat Macro
30,432 Thank you's --- On Fri, 4/8/11, Paul Schreiner schreiner_p...@att.net wrote: From: Paul Schreiner schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ Run - Copy / Paste - Repeat Macro To: excel-macros@googlegroups.com Date: Friday, April 8, 2011, 7:48 AM I've not tried it with charts, but whenever you do a find/replace (ctrl-h) there is an option to look in formulas. You could copy the sheets, then use find/replace to replace references to Info4 with Info3, etc. Paul From: David Lanne djl99_28...@yahoo.com To: excel-macros@googlegroups.com Sent: Thu, April 7, 2011 5:51:07 PM Subject: Re: $$Excel-Macros$$ Run - Copy / Paste - Repeat Macro I have a similar issue. I have 4 workbooks basically (dumb info 1, dumb info 2, etc). The layout/format of the four are the same. The first 76 sheets of each book contains the data. Another 5-10 sheets are use to consolidate. With dumb info 4 I got playing around with graphs and charts. So I have additional 16 pages (1 for each widget) that contains the 76 line item results of each. Since dumb info 4 has only used 30 of its 76 sheets I've been using dynamic cells to update my charts and stuff. Now I want to go back to the earlier 3 wookbooks and incorporate the new sheets I made in dumb info 4. When I go to copy and paste. All the formulas still reference dumb info 4. Without having to go into each formula and change the dumb info 4 line, there must be an easier way? --- On Wed, 4/6/11, ashish koul koul.ash...@gmail.com wrote: From: ashish koul koul.ash...@gmail.com Subject: Re: $$Excel-Macros$$ Run - Copy / Paste - Repeat Macro To: excel-macros@googlegroups.com Date: Wednesday, April 6, 2011, 11:36 PM see if it helps On Wed, Apr 6, 2011 at 11:03 PM, RON SMITH comeonove...@gmail.com wrote: Here is the file: On Wed, Apr 6, 2011 at 7:01 AM, qcan comeonove...@gmail.com wrote: Hi, I have this great Monte Carlo Simulation program for blackjack. I am not all that good in VBA, but would like a small macro to run this program, the copy and paste the results to another sheet, then repeat that task again for as many times as I wish. Any takers ? I can forward the spreadsheet to you as I cannot seem to attach it here. Thanks. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com/ To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Ashish Koul akoul.blogspot.com akoul.wordpress.com My Linkedin Profile P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links
Re: $$Excel-Macros$$ Compare strings in cells, return differences
Well done Seti ! Appreciate your knowledge Help in this forum On Fri, Apr 8, 2011 at 1:23 PM, SHC stuart.hallcoo...@googlemail.comwrote: Thanks STDEV(i) - very appreciated. On Apr 8, 6:29 am, STDEV(i) setiyowati.d...@gmail.com wrote: *=TextDif(A2,B2)* TextDif is an UDF, like this: Function TextDif(S1 As String, S2 As String) As String Dim Arr1, Arr2 Dim n As Integer, i As Integer, t As String Arr1 = Split(S1, ,): Arr2 = Split(S2, ,) For i = LBound(Arr2) To UBound(Arr2) For n = LBound(Arr1) To UBound(Arr1) If Arr1(n) = Arr2(i) Then Arr2(i) = Next n Next i For i = LBound(Arr2) To UBound(Arr2) If Len(Arr2(i)) 0 Then t = t Arr2(i) , Next i TextDif = t If Len(t) 0 Then TextDif = Left(t, Len(t) - 1) End Function On Fri, Apr 8, 2011 at 4:46 AM, SHC stuart.hallcoo...@googlemail.com wrote: I'd like a function script that compares the strings from two cells, and returns, in a third cell, the parts of the 2nd string that do not match parts of the 1st string (I don't need to return the parts of 1st string that do not match parts of the 2nd string). Examples: Cell1: Chris Cell2: Carl,Chris,Peter Result in Cell3: Carl,Peter Cell1: Chris,Carl Cell2: Carl,Peter,Chris Result in Cell3: Peter Cell1: Chris,Dave Cell2: Carl,Chris,Peter Result: Carl,Peter Cell1: Chris,Dave,Peter,Carl Cell2: Dave,Chris,Carl Cell3 is empty Note, parts of the strings (separated by commas) will not necessarily be in the same order. Any help would be most appreciated Thanks. ctv_TextDif about Chris and Peter.xls 29KViewDownload -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Re: parsing time field
Hi Paul, Explanation is awesome. i have been reading your replies most of the time as you explain whole thing perfectly and in a good way. i dont know n dont understand how do you know this much in detail theory. You rock man. please keep up the good work. From: Paul Schreiner schreiner_p...@att.net To: excel-macros@googlegroups.com Sent: Fri, 8 April, 2011 6:19:44 PM Subject: Re: $$Excel-Macros$$ Re: parsing time field the problem you have here is that Excel doesn't recognize what you call time as actual time. Excel says that time is actually a fractional part of a day. since there are 24 hours in a day, 6:00am is 1/4 of a day, (or .25) 12:00 noon is 1/2 of a day, and 6:00pm is 3/4 of a day. If you were to change your sample cells to general format, you'll find that 7:58:31 becomes 0.332303240740741 but :00:00 and :07:36 are unchanged, because Excel doesn't recognize them as valid time formats. So they remain text strings. Functions like Hour(), Minute() and Second() expect to receive a time as a parameter. Not a text string. So the first thing we need to do is decide if we're wanting to convert the text strings to date/time format, or convert the date/time to text strings. To convert the time to a text string, you must first determine if the value IS a time value. I put this macro together. '=== Option Explicit Sub Parse_Time() Dim StrArray, LHour, LMinute, LSecond Dim TimeString, dtime As Date On Error Resume Next Err.Clear '--- ' Save the cell value '--- TimeString = Range(D2).Value '--- ' save the string in a date variable ' dtime = TimeString ' 'If saving it as a date produces an error, 'then it evidently wasn't a date! ' In that case, convert the date to a text string ' If (Err = 0) Then TimeString = Format(dtime, hh:mm:ss) End If '-- ' Split the string into an array '-- StrArray = Split(TimeString, :) '-- ' Store the individual array elements '-- LHour = StrArray(0) If LHour = Then LHour = 0 LMinute = StrArray(1) If LMinute = Then LMinute = 0 LSecond = StrArray(2) If LSecond = Then LSecond = 0 ' ' Display the values ' Range(L2).Value = LHour Range(M2).Value = LMinute Range(N2).Value = LSecond End Sub '== Now, if you have a whole bunch of these rows/columns to deal with, we could use put together a loop like: Option Explicit Sub Parse_Time() Dim R, C, C2, FirstCol, OffsVal Dim StrArray, LHour, LMinute, LSecond Dim TimeString, dtime As Date On Error Resume Next FirstCol = 4 OffsVal = 12 For R = 2 To 30 For C = 4 To 6 Err.Clear C2 = (C - FirstCol) * 3 + OffsVal '--- ' Save the cell value '--- TimeString = Cells(R, C).Value '--- ' save the string in a date variable ' dtime = TimeString ' 'If saving it as a date produces an error, 'then it evidently wasn't a date! ' In that case, convert the date to a text string ' If (Err = 0) Then TimeString = Format(dtime, hh:mm:ss) End If '-- ' Split the string into an array '-- StrArray = Split(TimeString, :) '-- ' Store the individual array elements '-- LHour = StrArray(0) If LHour = Then LHour = 0 LMinute = StrArray(1) If LMinute = Then LMinute = 0 LSecond = StrArray(2) If LSecond = Then LSecond = 0 ' ' Display the values ' Cells(R, C2).Value = LHour Cells(R, C2 + 1).Value = LMinute Cells(R, C2 + 2).Value = LSecond Next C
$$Excel-Macros$$ Re: querry
Please explain clearly what u need exact? On Apr 8, 6:34 am, Nemi Gandhi nemigan...@gmail.com wrote: Please reply. -- Nemi Gandhi 98204 92963 QUERRY.xlsx 17KViewDownload -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
