Re: $$Excel-Macros$$ Re: Run time error...
Just a quick question guys... Do you know how I can change a value of a dropdown list using VBA? I tried to record a macro to see the code but with no luck. Thank you in advance -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Run time error...
Sorry I was not specific and sorry again I wish I could share the file... It is just a dropdown list on a cell. I have the VBA that replaces the values of cells but the thing is that there are a couple of cells that are populated via those lists and the values won't change. Unfortunately, I am not allowed to change the spread sheet at all. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Run time error...
if its a simple data validation then you can do it directly like range(a1).value =xyz On Fri, Feb 13, 2015 at 3:32 PM, Christos Alekopoulos christosalekopou...@gmail.com wrote: Sorry I was not specific and sorry again I wish I could share the file... It is just a dropdown list on a cell. I have the VBA that replaces the values of cells but the thing is that there are a couple of cells that are populated via those lists and the values won't change. Unfortunately, I am not allowed to change the spread sheet at all. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- *Regards* *Ashish Koul* *Visit* http://www.excelvbamacros.in Like Us on Facebook https://www.facebook.com/excelvbacodes Join Us on Facebook http://www.facebook.com/groups/163491717053198/ P Before printing, think about the environment. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Run time error...
Thank you Ashish, didn't think of that... sometimes the simplest answer is the best! Cheers, Christos -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Run time error...
It is more clear now... Cheers!! On Tue, Feb 10, 2015 at 7:00 PM, Christos Alekopoulos christosalekopou...@gmail.com wrote: Thanks Vabz but I don't want just a function that replaces any value. My post explains fully what I needed! Cheers -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Run time error...
Hi Michael, That works perfect thank you!!! Cheers, Christos -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Run time error...
Thanks Vabz but I don't want just a function that replaces any value. My post explains fully what I needed! Cheers -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Run time error...
Welcome Christos... On Tuesday, February 10, 2015 at 4:52:30 PM UTC+5:30, Christos Alekopoulos wrote: Hi Michael, That works perfect thank you!!! Cheers, Christos -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Run time error...
Christos, you could have achieved this by following simple line too: Sub Macro1() Prompt = What is the original value you want to replace? Title = Search Value Input Search = InputBox(Prompt, Title) Prompt = What is the replacement value? Title = Search Value Input Replacement = InputBox(Prompt, Title) Cells.Replace What:=Search, Replacement:=Replacement, LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub Cheers!! On Tue, Feb 10, 2015 at 4:52 PM, Christos Alekopoulos christosalekopou...@gmail.com wrote: Hi Michael, That works perfect thank you!!! Cheers, Christos -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Run time error...
In fact I used a slightly different code... It finds a row with a certain value (the risk id for my case) and replace another value in the same row that I set. I hope I this code helps others as well! Thank you again for all your help and feedback!!! Sorry for the comments but I think they could be useful to another beginner such as me! Sub replace() Dim WS As Worksheet Dim Search As String Dim Replacement As String Dim Prompt As String Dim Title As String Dim ID As String Set the unique value in each row Prompt = What is the risk ID? Title = Search Value Input ID = InputBox(Prompt, Title) ''Set the value in the specific row I want to replace Prompt = What is the original value you want to replace? Title = Search Value Input Search = InputBox(Prompt, Title) '''Set the new value Prompt = What is the replacement value? Title = Search Value Input Replacement = InputBox(Prompt, Title) For Each WS In Worksheets ' A 'for' command to search in all worksheets Cells.Find(What:=ID, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ Finds and activates the cell that contains the first value , SearchFormat:=False).Activate ActiveCell.EntireRow.Select ' Selects the entire row Selection.replace What:=Search, Replacement:=Replacement, LookAt:=xlWhole, _ ''Searches the row and replaces the value SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Run time error...
Hi Christos, Try this code... Sub Button1_Click() Dim WS As Worksheet Dim Search As String Dim Replacement As String Dim Prompt As String Dim Title As String Dim ID As String Dim mu As String Dim temp As Long Dim R As Long Dim C As Long Dim MatchCase As Boolean Prompt = What is the risk ID? Title = Search Value Input ID = InputBox(Prompt, Title) Prompt = What is the original value you want to replace? Title = Search Value Input Search = InputBox(Prompt, Title) Prompt = What is the replacement value? Title = Search Value Input Replacement = InputBox(Prompt, Title) If Search = Empty Or Replacement = Empty Then End End If For Each WS In Worksheets WS.Select WS.Activate R = WS.UsedRange.SpecialCells(xlCellTypeLastCell).Row C = WS.UsedRange.SpecialCells(xlCellTypeLastCell).Column WS.Range(Cells(1, 1), Cells(R, C)).Select On Error Resume Next Selection.replace What:=Search, Replacement:=Replacement, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False On Error GoTo 0 Next WS End Sub On Monday, February 9, 2015 at 3:48:24 PM UTC+5:30, Christos Alekopoulos wrote: Hi All, I have created this code that finds a value, then changes any pre-set value into a new value determined by the user as well. The problem is that I get an error 424 and a message item required every time I run it. I have highlighted the line where it seems to have the error. The code is the following. Every idea and any help is really appreciated. Thank you Christos Sub replace() Dim WS As Worksheet Dim Search As String Dim Replacement As String Dim Prompt As String Dim Title As String Dim ID As String Dim mu As String Dim temp As Long Dim MatchCase As Boolean Prompt = What is the risk ID? Title = Search Value Input ID = InputBox(Prompt, Title) Prompt = What is the original value you want to replace? Title = Search Value Input Search = InputBox(Prompt, Title) Prompt = What is the replacement value? Title = Search Value Input Replacement = InputBox(Prompt, Title) For Each WS In Worksheets Set r = WS.Cells.Find(myTask, , , 1) If Not r Is Nothing Then ff = r.Address 'temp = r.EntireRow.Value ' r.EntireRow.Value temp = r.EntireRow.Value.Cells.replace(What:=Search, Replacement:=Replacement) End If 'mu = temp.Cells.replace(What:=Search, Replacement:=Replacement) 'temp = r.EntireRow.Value.Cells.replace(What:=Search, Replacement:=Replacement) Next 'temp.Cells.replace What:=Search, Replacement:=Replacement, _ 'LookAt:=xlPart, MatchCase:=False 'mu = temp.Cells.replace(What:=Search, Replacement:=Replacement) End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: run-time error '1004': Reference is not valid.
Thank you very much, Paul. When I received the file, it was quickly obvious that this other party had introduced an error into the file that was producing a bunch of #0's and such and that the goal seek would never be able to find a solution. I guess these VBA error messages are just too generic for my tastes. It seemed more like the kind of messge you get when something isn't dimensioned properly, etc. I expected something a lot more subtle! Next time I will know to ask for the file first, before I go jumping to conclusions. Thanks again for your very thoughtful answer. Dean On Friday, July 18, 2014 6:18:06 PM UTC-7, deanalt wrote: I sent someone an EXCEL macro enabled workbook file (created in EXCEL 2010) and they ran some macros, saved the result, then later tried to run a different macro and got the error message in the subject line. I had no such problem in running it just the way they did it on my computer. It is possible they have an older or newer version of EXCEL than I do, i.e., not 2010. Below is the very short macro. Is there anything here that lends itself to not working on a different version of EXCEL? Even if there is no clue, is there a way to change the macro, maybe by declaring something or whatever, to ensure it will probably run on other computers better? I created this macro a long time ago, am a novice at macros, and don't recall if I recorded all of this macro or just some of it, then edited it. But it works for me just fine. Thanks so much! Dean Sub ComputeInterestRates() Application.MaxChange = 0.01 Application.Goto Reference:=GoalSeekPERSIR Range(GoalSeekPERSIR).GoalSeek Goal:=0, ChangingCell:=Range(CalPERSImpliedInterestRate) Application.Goto Reference:=GoalSeekIndexIR Range(GoalSeekIndexIR).GoalSeek Goal:=0, ChangingCell:=Range(BenchmarkImpliedInterestRate) Application.Goto Reference:=R16C1 End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Run time error 6
Hi, Got the solution for this problem I have declared variable to integer then I changed it to Long. Now macro is working fine. Thanks, Chandra On Wed, Aug 14, 2013 at 10:25 AM, Lalit Mohan Pandey mohan.pande...@gmail.com wrote: Instead of sharing Screen shot it would be nice if you can share file On Tuesday, 13 August 2013 20:01:06 UTC+5:30, Chandru wrote: Hi, While running macro am getting run time error 6 as shown in the attached file. I am using huge data which contains 9 rows. Could you please let me know how to run macro with hugre data. Thanks, Chandra -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: Run time error 6
Instead of sharing Screen shot it would be nice if you can share file On Tuesday, 13 August 2013 20:01:06 UTC+5:30, Chandru wrote: Hi, While running macro am getting run time error 6 as shown in the attached file. I am using huge data which contains 9 rows. Could you please let me know how to run macro with hugre data. Thanks, Chandra -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: date time subtraction
hi all thanks for your help!! On Monday, January 7, 2013 8:20:25 AM UTC-6, Nitesh Sharma wrote: Hi all, need your help i need a vba code which able to subtract user given DateTime in any cell(say a1 = 07-01-2012 19:44) with the current system time. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: All time high Price
Hi Hilary please use this formula hope this will helpe u.
=MAX(INDEX('Monthly End Prices'!C2:AK169,,MATCH(Sheet2!B1,'Monthly End
Prices'!C1:AK1,0)))
Regards
Prince dueby
On Thursday, September 20, 2012 9:28:51 PM UTC+5:30, hilary lomotey wrote:
Hello Experts,
i need a formula to calculate the all time highest price if i select an
equity in the attached sheet. kindly help with the formula. thanks
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups MS
EXCEL AND VBA MACROS group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ Re: All time high Price *
thanks Prince, Effort appreciated
On Mon, Sep 24, 2012 at 10:10 AM, Prince Dubey prince141...@gmail.comwrote:
[image: Boxbe] https://www.boxbe.com/overview This message is eligible
for Automatic Cleanup! (prince141...@gmail.com) Add cleanup
rulehttps://www.boxbe.com/popup?url=https%3A%2F%2Fwww.boxbe.com%2Fcleanup%3Ftoken%3DsJrFvJKRThQk%252Fix%252BEyaqsvo0dM5oG88jsjcvvvEwcdRcpxwyEd4%252FvCjxn659z0D12trPRMjxIfvdEwwfQkR%252BNqTjwBl7mohZWGJC%252F2LcyE5F43D6t50TZaZ8h8lRQsdoNIzgqyhf3PR9QLWGfitvsw%253D%253D%26key%3DuKsiF9BLUXQuS07nVB54s16%252Bh7N%252F%252FhnTRvLCm4Pblbs%253Dtc=12477235279_1594681215|
More
infohttp://blog.boxbe.com/general/boxbe-automatic-cleanup?tc=12477235279_1594681215
Hi Hilary please use this formula hope this will helpe u.
=MAX(INDEX('Monthly End Prices'!C2:AK169,,MATCH(Sheet2!B1,'Monthly End
Prices'!C1:AK1,0)))
Regards
Prince dueby
On Thursday, September 20, 2012 9:28:51 PM UTC+5:30, hilary lomotey wrote:
Hello Experts,
i need a formula to calculate the all time highest price if i select an
equity in the attached sheet. kindly help with the formula. thanks
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups
MS EXCEL AND VBA MACROS group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups MS
EXCEL AND VBA MACROS group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
$$Excel-Macros$$ Re: Run time error out of memory 7
Hello, Give more details. At what stage, you are getting the error. When opening the file, When opening a particular form/ running a query / executing a report etc. Also provide The size of access file. Once thing you can try is to condense the access database using compact repair database option. Regards, Kishan Reddy, K On Tuesday, August 14, 2012 2:18:44 PM UTC+5:30, Prince Dubey wrote: Hi Group, I have a project which is running fine on my system with XP OS but the same project giving error of Out of Memory 7 on other system with XP platform. I am using VBA and Acess as data source. I tried my level best to track the source of error but not able to find it.So can you pls come up with your suggetions what may wrong with project. regards Prince -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
$$Excel-Macros$$ Re: Date Time picker on a userform
Use Date and Time Picker Control 6.0 (SP6) from the tool box. That would list the control in the toolbox window. Click on it, and use it on your userform. On Jul 24, 6:13 pm, alisha malhotra alisha.malhotr...@gmail.com wrote: Hi, I am using one userform, In that I need Date time Picker in excel 2003. But when I click on Additional controls Nothing Happens. I need to add some reference for this? How can I add the Date time Picker on a userform? I am attaching the file also. Pls help. Thanks Regards, Alisha Trng Hall Bookings.xls 62KViewDownload -- -- 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: Date Time picker on a userform
Generally it comes in toolbox after right click selecting additional controls or From tool menu. But it is not appearing in my PC. I think some of the reference has not been added. So which reference should I add to get the additional controls? Regards, Alisha On Sun, Jul 24, 2011 at 7:52 PM, GoldenLance samde...@gmail.com wrote: Use Date and Time Picker Control 6.0 (SP6) from the tool box. That would list the control in the toolbox window. Click on it, and use it on your userform. On Jul 24, 6:13 pm, alisha malhotra alisha.malhotr...@gmail.com wrote: Hi, I am using one userform, In that I need Date time Picker in excel 2003. But when I click on Additional controls Nothing Happens. I need to add some reference for this? How can I add the Date time Picker on a userform? I am attaching the file also. Pls help. Thanks Regards, Alisha Trng Hall Bookings.xls 62KViewDownload -- -- 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: Date Time picker on a userform
It is the same control, just need to change the format. I have sent an attachment, with a new control, and changed the format. Sam Mathai Chacko On Jul 24, 9:11 pm, alisha malhotra alisha.malhotr...@gmail.com wrote: Generally it comes in toolbox after right click selecting additional controls or From tool menu. But it is not appearing in my PC. I think some of the reference has not been added. So which reference should I add to get the additional controls? Regards, Alisha On Sun, Jul 24, 2011 at 7:52 PM, GoldenLance samde...@gmail.com wrote: Use Date and Time Picker Control 6.0 (SP6) from the tool box. That would list the control in the toolbox window. Click on it, and use it on your userform. On Jul 24, 6:13 pm, alisha malhotra alisha.malhotr...@gmail.com wrote: Hi, I am using one userform, In that I need Date time Picker in excel 2003. But when I click on Additional controls Nothing Happens. I need to add some reference for this? How can I add the Date time Picker on a userform? I am attaching the file also. Pls help. Thanks Regards, Alisha Trng Hall Bookings.xls 62KViewDownload -- -- 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 athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://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$$ Re: Run time error '13'... Type mismatch error
ActiveCell.Rows(1: nrow).EntireRow.Select On Jun 15, 4:11 pm, Hiren Sheth hiren.sh...@viteos.com wrote: Hi I am getting following error while running below mention code. Please help. [cid:image001@01CC2B7B.1B6C0EA0] Dim nrow As Long ActiveCell.Select Cells(8, 1).Select ActiveCell.Offset(1, 0).Range(A1).Select Range(Selection, Selection.End(xlDown)).Select nrow = Range(Selection, Selection.End(xlDown)).Count MsgBox nrow ActiveCell.Rows(1:nrow).EntireRow.Select This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mis-transmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Viteos Capital Market Services Ltd.and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity image001.png 7KViewDownload -- -- 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
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$$ Re: parsing time field
...@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 this is doing Range(L2).offset(0,1).resize(1,3).value = split(Range(cellalpha).value,:) This does work but not entirely of what I'm trying to do Here is more of an example of what' I'm trying to do data comes is as D E F 7:58:31 :00:00 :07:36 And what I want is L M N O P Q R S T 7 58 31 0 0 0 0 7 36 as soon as it tries any cell beginning with : I get a type mismatch on the first line LHour = Hour(Range(cellalpha)), Maybe I'm not going about this the right way. On Apr 7, 12:35 pm, GoldenLance samde...@gmail.com wrote: Assuming the time is a text, and not a time value, use Range(L2).offset(0,1).resize(1,3).value = split(Range(cellalpha).value,:) On Apr 7, 5:11 pm, Speilman_54 mbed...@gmail.com wrote: I'm having an issue where I'm trying to separate a cell with time in it, for example the cell would conta 8:56:45 and I need each one of those numbers into it's on cell so A1 = 8 A2 =56 A3=45. So far I've been able to ge this to work however the system sends the information will send data like :45:45 if there work time is less than 1 hour which is where I'm receiving Type Mismatch error. If I could somehow insert a 0 when this would solve this issue, but I've tried to but unsuccessfully. Any help would be much appreciated. Code is below 'staffed time ' hour separator LHour = Hour(Range(cellalpha)) Range(L2) = LHour ' minute separator LMinute = Minute(Range(cellalpha)) Range(M2) = LMinute ' Seconds separator LSecond = Second(Range(cellalpha)) Range(N2) = LSecond -- -- 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 athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below linkhttp://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: parsing time field
When I ran your code I got an error on this line: Range(L2) = LHour ... and had to add parens around the L2, comme ca: Range(L2) = LHour If that still doesn't run, you might try this construct: If IsError(Hour(Range(CellAlpha))) Then LHour = 0 Range(L2) = LHour Else LHour = Hour(Range(CellAlpha)) Range(L2) = LHour End If LMinute = Minute(Range(CellAlpha)) Range(M2) = LMinute LSecond = Second(Range(CellAlpha)) Range(N2) = LSecond Do you need to store LHour, LMinute, and LSecond as variables for use later in the code? You store those as variables and use them on the very next line, which is a little chunky _unless_ you need to use them later. If you _don't_ need to use them later, you can combine the range assignment line and the variable assignment line, like this: Range(M2) = Minute(Range(CellAlpha)) My apologies if you know that already- I don't mean to be critical- Dave O Eschew Obfuscation -- -- 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$$ Re: parsing time field
Assuming the time is a text, and not a time value, use Range(L2).offset(0,1).resize(1,3).value = split(Range(cellalpha).value,:) On Apr 7, 5:11 pm, Speilman_54 mbed...@gmail.com wrote: I'm having an issue where I'm trying to separate a cell with time in it, for example the cell would conta 8:56:45 and I need each one of those numbers into it's on cell so A1 = 8 A2 =56 A3=45. So far I've been able to ge this to work however the system sends the information will send data like :45:45 if there work time is less than 1 hour which is where I'm receiving Type Mismatch error. If I could somehow insert a 0 when this would solve this issue, but I've tried to but unsuccessfully. Any help would be much appreciated. Code is below 'staffed time ' hour separator LHour = Hour(Range(cellalpha)) Range(L2) = LHour ' minute separator LMinute = Minute(Range(cellalpha)) Range(M2) = LMinute ' Seconds separator LSecond = Second(Range(cellalpha)) Range(N2) = LSecond -- -- 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$$ 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 this is doing Range(L2).offset(0,1).resize(1,3).value = split(Range(cellalpha).value,:) This does work but not entirely of what I'm trying to do Here is more of an example of what' I'm trying to do data comes is as D E F 7:58:31 :00:00 :07:36 And what I want is LMN OPQ RS T 7 58 3100 007 36 as soon as it tries any cell beginning with : I get a type mismatch on the first line LHour = Hour(Range(cellalpha)), Maybe I'm not going about this the right way. On Apr 7, 12:35 pm, GoldenLance samde...@gmail.com wrote: Assuming the time is a text, and not a time value, use Range(L2).offset(0,1).resize(1,3).value = split(Range(cellalpha).value,:) On Apr 7, 5:11 pm, Speilman_54 mbed...@gmail.com wrote: I'm having an issue where I'm trying to separate a cell with time in it, for example the cell would conta 8:56:45 and I need each one of those numbers into it's on cell so A1 = 8 A2 =56 A3=45. So far I've been able to ge this to work however the system sends the information will send data like :45:45 if there work time is less than 1 hour which is where I'm receiving Type Mismatch error. If I could somehow insert a 0 when this would solve this issue, but I've tried to but unsuccessfully. Any help would be much appreciated. Code is below 'staffed time ' hour separator LHour = Hour(Range(cellalpha)) Range(L2) = LHour ' minute separator LMinute = Minute(Range(cellalpha)) Range(M2) = LMinute ' Seconds separator LSecond = Second(Range(cellalpha)) Range(N2) = LSecond -- -- 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: On Time
Hi, I believe this query has been answered in some other posting having different subject line. Best Regards, DILIPandey On 11/22/10, Chandru chandrashekarb@gmail.com wrote: Hi, Any help please Chandra Shekar wrote: Hi, When I run the program excel starts flicekring how to avoid this. Thanks Chandra Shekar -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- Thanks Regards, DILIP KUMAR PANDEY MBA-HR,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Re: On Time
Hi Dilip, This is my first post in this forum. I have posted this query in other forum but I did'nt get reply for that. Please let me know how to do this. Thanks On Tue, Nov 23, 2010 at 8:53 PM, Dilip Pandey dilipan...@gmail.com wrote: Hi, I believe this query has been answered in some other posting having different subject line. Best Regards, DILIPandey On 11/22/10, Chandru chandrashekarb@gmail.com wrote: Hi, Any help please Chandra Shekar wrote: Hi, When I run the program excel starts flicekring how to avoid this. Thanks Chandra Shekar -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- Thanks Regards, DILIP KUMAR PANDEY MBA-HR,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Re: On Time
Hi, I mean that this query has been solved n replied in this forum itself. Please search in this group to get the solution. Best regards, DILIPandey On 11/23/10, Chandra Shekar chandrashekarb@gmail.com wrote: Hi Dilip, This is my first post in this forum. I have posted this query in other forum but I did'nt get reply for that. Please let me know how to do this. Thanks On Tue, Nov 23, 2010 at 8:53 PM, Dilip Pandey dilipan...@gmail.com wrote: Hi, I believe this query has been answered in some other posting having different subject line. Best Regards, DILIPandey On 11/22/10, Chandru chandrashekarb@gmail.com wrote: Hi, Any help please Chandra Shekar wrote: Hi, When I run the program excel starts flicekring how to avoid this. Thanks Chandra Shekar -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- Thanks Regards, DILIP KUMAR PANDEY MBA-HR,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- Sent from my mobile device Thanks Regards, DILIP KUMAR PANDEY MBA-HR,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ Re: On Time
Hi, Any help please Chandra Shekar wrote: Hi, When I run the program excel starts flicekring how to avoid this. Thanks Chandra Shekar -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ Re: Run time error 1004
Getting rid of the .text and ,value cleared up the run error. However, the activate didn't work as expected. It started writing in the input table which is what I was trying to avoid in the first place. Is this an excel vs vba command issue? What is the easiest way to get the activate to switch. Should I try select sheet? On Jul 9, 2:22 pm, larry laurence.tha...@navy.mil wrote: The following macro should activate worksheet Matrix Table then print out some text and then print out the values in the Nstep_tran array. Since I don't have a row counter, I am in the process of working out the formula for the Else case. What follows Else is a place holder. I cannot get the initial Cnt=1 case to work without hitting a run time error. I don't see any obvious typos. Do you? Call Mat_table(Cnt, Nstep_Trans) Sub Mat_table(Cnt, Nstep_Trans) Worksheets(Matrix Table).Activate If Cnt = 1 Then Cells(Cnt, 1).Text = Matrix Raised to a Power Cells(Cnt, 2).Value = 1 Else Cells(Cnt, 1).Text = Matrix Raised to a Power Cells(Cnt, 2).Value = Cnt End If For I = 1 To No_States For J = 1 To No_States Cells(Cnt + I, J).Value = Nstep_Trans(I, J) Next J Next I End Sub -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Run Time Error 3050 - Could Not Lock File - ERROR?
It could be the access registry lock in access. You should use a less conservative lock. Fabio L. Lemos Em 19/05/2009, às 08:30, Alokeshwar Tiwary alokeshwar.tiw...@yahoo.com escreveu: Hi All, Does anyone know why this error occurs? I am using DAO in a Excel Userform to save data in MS Access. Approx 40 users are using the userform and its working fine however sometimes it shows following error message and crashes: Run Time Error 3050 - Could Not Lock File What could be the reason? Regards, Alokeshwar _ There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we know we don't know. But there are also unknown unknowns. There are things we don't know we don't know. Explore and discover exciting holidays and getaways with Yahoo! India Travel Click here! --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: run time ERROR 9
Hi Shrinivas, Mr. Powell has rightly said, please debug the same way. And If it helps please also check, if you have larger amount of columns or rows than your friend, since this also cause Subscript Out of Range. Run on lesser or same amount of rows , also ensure correct sheet/file names in the vba macro. For more tips, tricks, downloads and problem solutions: visit -- http://www.excelitems.com Regards *Ashish **Jain* Analyst, CSC Microsoft Certified Application (Excel) Specialist Excel and VBA Trainer Author - www.excelitems.com +91--40-48-43 ----- ----- On Apr 1, 2:54 pm, shrinivas shevde shrinivas...@hotmail.com wrote: Dear All I am having vba code not written by me but one of my friend.Now I am facing a problem while running the same. This I am using to make a monthly report ,this programe open a indivisual file from the given folsder and copy paste required data in given excel sheet. Now when I am try to run this it shows the Run-time error9' Subscript out of range Please help me to solve this problem shrinivas _ So many new options, so little time. Windows Live Messenger.http://www.microsoft.com/india/windows/windowslive/messenger.aspx --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: run time ERROR 9
Shrinivas -- My guess is that the Excel sheet you are trying to reference is either misnamed or does not exist When the error appears, click the Debug button -- this will take you to the offending line of code in the VB Editor This will enable you do identify the cause of the error Hope it helps you ! - Rodney POWELL [ Microsoft MVP - Excel, 1997-2009 ] www.BeyondTechnology.com On Apr 1, 4:54 am, shrinivas shevde shrinivas...@hotmail.com wrote: Dear All I am having vba code not written by me but one of my friend.Now I am facing a problem while running the same. This I am using to make a monthly report ,this programe open a indivisual file from the given folsder and copy paste required data in given excel sheet. Now when I am try to run this it shows the Run-time error9' Subscript out of range Please help me to solve this problem shrinivas _ So many new options, so little time. Windows Live Messenger.http://www.microsoft.com/india/windows/windowslive/messenger.aspx --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Run-time error '-2147352571 (80020005) - Type Mismatch
Kindly ignore the request.. I figured it out myself.. _ There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we know we don't know. But there are also unknown unknowns. There are things we don't know we don't know. From: Alokeshwar Tiwary alokeshwar.tiw...@yahoo.com To: excel-macros@googlegroups.com Sent: Monday, 9 February, 2009 5:52:54 AM Subject: $$Excel-Macros$$ Run-time error '-2147352571 (80020005) - Type Mismatch Hi, I am trying to populate an excel userform combo box from a table in access database using DAO. When I pass arguments as following: AddItemtoDropDownList Me.cboUserName, LookupLists, UserId VBA returns following error: === Run-time error '-2147352571 (80020005) Type Mismatch=== When I click on debug VBA highlights below line from the code: cboBox.AddItem rs.Fields(cboField).value Here is my code: === Sub AddItemtoDropDownList(cboBox As Object, cboSourceTable As String, cboField As String) Dim db As Database, rs As Recordset Set db = OpenDatabase(CurrentDB(), False, False, DBPassword()) Set rs = db.OpenRecordset(SELECT cboField FROM cboSourceTable Order by cboField, dbReadOnly) Do cboBox.AddItem rs.Fields(cboField).value rs.MoveNext Loop Until rs.EOF rs.Close Set rs = Nothing db.Close Set db = Nothing End Sub === If hardcode field name and tablename in my code as following it works pefectly fine. What could be the problem? with the code mentioned above while the code mentioned below is working. Also i will be grateful if you could suggest some alternate method to populate combobox is excel userform from a field in an access database table. Sub AddItemtoComboBox Dim db As Database, rs As Recordset Set db = OpenDatabase(CurrentDB(), False, False, DBPassword()) Set rs = db.OpenRecordset(SELECT UserId FROM LookupLists Order by UserId, dbReadOnly) Do Me.cboUserId.AddItem rs.Fields(UserId).value rs.MoveNext Loop Until rs.EOF rs.Close Set rs = Nothing db.Close Set db = Nothing End SUb _ There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we know we don't know. But there are also unknown unknowns. There are things we don't know we don't know. Add more friends to your messenger and enjoy! Invite them now. Connect with friends all over the world. Get Yahoo! India Messenger at http://in.messenger.yahoo.com/?wm=n/ --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com If you find any spam message in the group, please send an email to Ayush @ jainayus...@gmail.com -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Run-time error '-2147352571 (80020005) - Type Mismatch
So why you don't want to share with group mates??? - Original Message - From: Alokeshwar Tiwary To: excel-macros@googlegroups.com Sent: Monday, February 09, 2009 2:46 PM Subject: $$Excel-Macros$$ Re: Run-time error '-2147352571 (80020005) - Type Mismatch Kindly ignore the request.. I figured it out myself.. _ There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we know we don't know. But there are also unknown unknowns. There are things we don't know we don't know. -- From: Alokeshwar Tiwary alokeshwar.tiw...@yahoo.com To: excel-macros@googlegroups.com Sent: Monday, 9 February, 2009 5:52:54 AM Subject: $$Excel-Macros$$ Run-time error '-2147352571 (80020005) - Type Mismatch Hi, I am trying to populate an excel userform combo box from a table in access database using DAO. When I pass arguments as following: AddItemtoDropDownList Me.cboUserName, LookupLists, UserId VBA returns following error: === Run-time error '-2147352571 (80020005) Type Mismatch === When I click on debug VBA highlights below line from the code: cboBox.AddItem rs.Fields(cboField).value Here is my code: === Sub AddItemtoDropDownList(cboBox As Object, cboSourceTable As String, cboField As String) Dim db As Database, rs As Recordset Set db = OpenDatabase(CurrentDB(), False, False, DBPassword()) Set rs = db.OpenRecordset(SELECT cboField FROM cboSourceTable Order by cboField, dbReadOnly) Do cboBox.AddItem rs.Fields(cboField).value rs.MoveNext Loop Until rs.EOF rs.Close Set rs = Nothing db.Close Set db = Nothing End Sub === If hardcode field name and tablename in my code as following it works pefectly fine. What could be the problem? with the code mentioned above while the code mentioned below is working. Also i will be grateful if you could suggest some alternate method to populate combobox is excel userform from a field in an access database table. Sub AddItemtoComboBox Dim db As Database, rs As Recordset Set db = OpenDatabase(CurrentDB(), False, False, DBPassword()) Set rs = db.OpenRecordset(SELECT UserId FROM LookupLists Order by UserId, dbReadOnly) Do Me.cboUserId.AddItem rs.Fields(UserId).value rs.MoveNext Loop Until rs.EOF rs.Close Set rs = Nothing db.Close Set db = Nothing End SUb _ There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we know we don't know. But there are also unknown unknowns. There are things we don't know we don't know. -- Add more friends to your messenger and enjoy! Invite them now. -- Connect with friends all over the world. Get Yahoo! India Messenger. -- No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.0.233 / Virus Database: 270.10.19/1941 - Release Date: 02/06/09 17:28:00 --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com If you find any spam message in the group, please send an email to Ayush @ jainayus...@gmail.com -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: run time type mismatch/other improvements
Why dont you use formula=DAYS360(Start Date,End Date) You dont need to code anything for this. On Fri, Dec 5, 2008 at 1:21 AM, larry laurence.tha...@navy.mil wrote: I am trying to creat a code for caculating the number of days between two dates. N=A-N causes a type mismacth run time error. Also looking for any other improvements. Nested if may need to be cleared up. Thanks for any help. Option Explicit Public M1 As Integer, M2 As Integer, D1 As Integer, D2 As Integer, Y1 As Variant, Y2 As Variant Public N As Integer, A As Integer Sub DayDelta() M1 = Cells(2, 2).Value D1 = Cells(3, 2).Value Y1 = Cells(4, 2).Value M2 = Cells(2, 4).Value D2 = Cells(3, 4).Value Y2 = Cells(4, 4).Value Call Daycalc(M1, D1, Y1, A) N = A Call Daycalc(M2, D2, Y2, A) N = A - N N = Cells(3, 5).Value End Sub Sub Daycalc(M, D, Y, A) Public M As Integer, D As Integer, Y As Variant, A As Integer Select Case M Case 1 A = 0 Case 2 A = 31 Case 3 A = 59 Case 4 A = 90 Case 5 A = 120 Case 6 A = 151 Case 7 A = 181 Case 8 A = 212 Case 9 A = 243 Case 10 A = 273 Case 11 A = 304 Case 12 A = 334 End Select A = A + Y * 365 + Int(Y / 4) + D + 1 - Int(Y / 100) + Int(Y / 400) If Int(Y / 4) = Y / 4 Then If Y / 400 = Int(Y / 400) Then If M 3 Then A = A - 1 End If Else If Y / 100 Int(Y / 100) Then If M 3 Then A = A - 1 End If End If End Sub --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: run time type mismatch/other improvements
Thanks for the tips. When I checked the code, I found I had reversed the row and column in the Cell(). There were also a few other issues. I like the Date - Date but that would not teach me anything about programming. I still need to cehck the if then sequence but it does run with some of the test problems. On Dec 5, 2:13 am, lohith [EMAIL PROTECTED] wrote: Hi Larry, Try Using this formula instead. =DATE(B4,B3,B2)-DATE(D4,D3,D2) Cheers, Lohith On Dec 5, 12:51 am, larry [EMAIL PROTECTED] wrote: I am trying to creat a code for caculating the number of days between two dates. N=A-N causes a type mismacth run time error. Also looking for any other improvements. Nested if may need to be cleared up. Thanks for any help. Option Explicit Public M1 As Integer, M2 As Integer, D1 As Integer, D2 As Integer, Y1 As Variant, Y2 As Variant Public N As Integer, A As Integer Sub DayDelta() M1 = Cells(2, 2).Value D1 = Cells(3, 2).Value Y1 = Cells(4, 2).Value M2 = Cells(2, 4).Value D2 = Cells(3, 4).Value Y2 = Cells(4, 4).Value Call Daycalc(M1, D1, Y1, A) N = A Call Daycalc(M2, D2, Y2, A) N = A - N N = Cells(3, 5).Value End Sub Sub Daycalc(M, D, Y, A) Public M As Integer, D As Integer, Y As Variant, A As Integer Select Case M Case 1 A = 0 Case 2 A = 31 Case 3 A = 59 Case 4 A = 90 Case 5 A = 120 Case 6 A = 151 Case 7 A = 181 Case 8 A = 212 Case 9 A = 243 Case 10 A = 273 Case 11 A = 304 Case 12 A = 334 End Select A = A + Y * 365 + Int(Y / 4) + D + 1 - Int(Y / 100) + Int(Y / 400) If Int(Y / 4) = Y / 4 Then If Y / 400 = Int(Y / 400) Then If M 3 Then A = A - 1 End If Else If Y / 100 Int(Y / 100) Then If M 3 Then A = A - 1 End If End If End Sub- Hide quoted text - - Show quoted text - --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: run time type mismatch/other improvements
VBA has a function called DateDiff: DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]]) The DateDiff function syntax has these named arguments: Part Description interval Required. String expression that is the interval of time you use to calculate the difference between date1 and date2. date1, date2 Required; Variant (Date). Two dates you want to use in the calculation. firstdayofweek Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed. firstweekofyear Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs. Settings The interval argument has these settings: Setting Description Year q Quarter m Month y Day of year d Day w Weekday ww Week h Hour n Minute s Second If you're stuck with the month/day/year in separate cells, You can use: D1 = DateValue(Cells(32, 2) / Cells(33, 2) / Cells(34, 2)) D2 = DateValue(Cells(32, 3) / Cells(33, 3) / Cells(34, 3)) MsgBox DateDiff(d, D1, D2) If you REALLY, REALLY, REALLY want to use your M1,D1,Y1, you can use: M1 = Cells(32, 2).Value D1 = Cells(33, 2).Value Y1 = Cells(34, 2).Value M2 = Cells(32, 4).Value D2 = Cells(33, 4).Value Y2 = Cells(34, 4).Value D1 = DateValue(M1 / D1 / Y1) D2 = DateValue(M2 / D2 / Y2) MsgBox DateDiff(d, D1, D2) HTH Paul - Original Message From: larry [EMAIL PROTECTED] To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Monday, December 8, 2008 8:18:36 AM Subject: $$Excel-Macros$$ Re: run time type mismatch/other improvements Thanks for the tips. When I checked the code, I found I had reversed the row and column in the Cell(). There were also a few other issues. I like the Date - Date but that would not teach me anything about programming. I still need to cehck the if then sequence but it does run with some of the test problems. On Dec 5, 2:13 am, lohith wrote: Hi Larry, Try Using this formula instead. =DATE(B4,B3,B2)-DATE(D4,D3,D2) Cheers, Lohith On Dec 5, 12:51 am, larry wrote: I am trying to creat a code for caculating the number of days between two dates. N=A-N causes a type mismacth run time error. Also looking for any other improvements. Nested if may need to be cleared up. Thanks for any help. Option Explicit Public M1 As Integer, M2 As Integer, D1 As Integer, D2 As Integer, Y1 As Variant, Y2 As Variant Public N As Integer, A As Integer Sub DayDelta() M1 = Cells(2, 2).Value D1 = Cells(3, 2).Value Y1 = Cells(4, 2).Value M2 = Cells(2, 4).Value D2 = Cells(3, 4).Value Y2 = Cells(4, 4).Value Call Daycalc(M1, D1, Y1, A) N = A Call Daycalc(M2, D2, Y2, A) N = A - N N = Cells(3, 5).Value End Sub Sub Daycalc(M, D, Y, A) Public M As Integer, D As Integer, Y As Variant, A As Integer Select Case M Case 1 A = 0 Case 2 A = 31 Case 3 A = 59 Case 4 A = 90 Case 5 A = 120 Case 6 A = 151 Case 7 A = 181 Case 8 A = 212 Case 9 A = 243 Case 10 A = 273 Case 11 A = 304 Case 12 A = 334 End Select A = A + Y * 365 + Int(Y / 4) + D + 1 - Int(Y / 100) + Int(Y / 400) If Int(Y / 4) = Y / 4 Then If Y / 400 = Int(Y / 400) Then If M 3 Then A = A - 1 End If Else If Y / 100 Int(Y / 100) Then If M 3 Then A = A - 1 End If End If End Sub- Hide quoted text - - Show quoted text - --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: run time type mismatch/other improvements
Before assigning variables values, use on error goto label_name or on error resume next. When you use on error goto label_name, you can stop executing further and directly go to end giving some error information in msgbox, and when you use on error resume next, the line with error will not be executed and the next executable code after that will be executed. For more help you can google on this. Regards, Aditya Mundra On Fri, Dec 5, 2008 at 1:21 AM, larry [EMAIL PROTECTED] wrote: I am trying to creat a code for caculating the number of days between two dates. N=A-N causes a type mismacth run time error. Also looking for any other improvements. Nested if may need to be cleared up. Thanks for any help. Option Explicit Public M1 As Integer, M2 As Integer, D1 As Integer, D2 As Integer, Y1 As Variant, Y2 As Variant Public N As Integer, A As Integer Sub DayDelta() M1 = Cells(2, 2).Value D1 = Cells(3, 2).Value Y1 = Cells(4, 2).Value M2 = Cells(2, 4).Value D2 = Cells(3, 4).Value Y2 = Cells(4, 4).Value Call Daycalc(M1, D1, Y1, A) N = A Call Daycalc(M2, D2, Y2, A) N = A - N N = Cells(3, 5).Value End Sub Sub Daycalc(M, D, Y, A) Public M As Integer, D As Integer, Y As Variant, A As Integer Select Case M Case 1 A = 0 Case 2 A = 31 Case 3 A = 59 Case 4 A = 90 Case 5 A = 120 Case 6 A = 151 Case 7 A = 181 Case 8 A = 212 Case 9 A = 243 Case 10 A = 273 Case 11 A = 304 Case 12 A = 334 End Select A = A + Y * 365 + Int(Y / 4) + D + 1 - Int(Y / 100) + Int(Y / 400) If Int(Y / 4) = Y / 4 Then If Y / 400 = Int(Y / 400) Then If M 3 Then A = A - 1 End If Else If Y / 100 Int(Y / 100) Then If M 3 Then A = A - 1 End If End If End Sub --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: run time type mismatch/other improvements
Hi Larry, Try Using this formula instead. =DATE(B4,B3,B2)-DATE(D4,D3,D2) Cheers, Lohith On Dec 5, 12:51 am, larry [EMAIL PROTECTED] wrote: I am trying to creat a code for caculating the number of days between two dates. N=A-N causes a type mismacth run time error. Also looking for any other improvements. Nested if may need to be cleared up. Thanks for any help. Option Explicit Public M1 As Integer, M2 As Integer, D1 As Integer, D2 As Integer, Y1 As Variant, Y2 As Variant Public N As Integer, A As Integer Sub DayDelta() M1 = Cells(2, 2).Value D1 = Cells(3, 2).Value Y1 = Cells(4, 2).Value M2 = Cells(2, 4).Value D2 = Cells(3, 4).Value Y2 = Cells(4, 4).Value Call Daycalc(M1, D1, Y1, A) N = A Call Daycalc(M2, D2, Y2, A) N = A - N N = Cells(3, 5).Value End Sub Sub Daycalc(M, D, Y, A) Public M As Integer, D As Integer, Y As Variant, A As Integer Select Case M Case 1 A = 0 Case 2 A = 31 Case 3 A = 59 Case 4 A = 90 Case 5 A = 120 Case 6 A = 151 Case 7 A = 181 Case 8 A = 212 Case 9 A = 243 Case 10 A = 273 Case 11 A = 304 Case 12 A = 334 End Select A = A + Y * 365 + Int(Y / 4) + D + 1 - Int(Y / 100) + Int(Y / 400) If Int(Y / 4) = Y / 4 Then If Y / 400 = Int(Y / 400) Then If M 3 Then A = A - 1 End If Else If Y / 100 Int(Y / 100) Then If M 3 Then A = A - 1 End If End If End Sub --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~--~~~~--~~--~--~---
