Re: $$Excel-Macros$$ Text to number format while opening a file
Thanks Ravinder,
Actually my query was, While opening the excel file, can be convert
every text format error checking in green colur to number.
Note: if the green color are actually a number but intext format
With warm regards,
Rajan Sharma
On Wed, Aug 7, 2013 at 1:22 PM, ravinder negi ravi_colw...@yahoo.comwrote:
PFA..
Private Sub Workbook_Open()
'MsgBox Sheets(Report1).UsedRange.Rows.Count
For i = 10 To Sheets(Report1).UsedRange.Rows.Count - 1
Sheets(Report1).Range(e i).Activate
Application.SendKeys {f2}
Application.SendKeys ~
Next
End Sub
--
*From:* Rajan sharma rajansharma9...@gmail.com
*To:* excel-macros@googlegroups.com excel-macros@googlegroups.com
*Sent:* Tuesday, August 6, 2013 5:41 PM
*Subject:* $$Excel-Macros$$ Text to number format while opening a file
Dear experts,
i have a excel file which is extracted from a application as seen in
attached sample file. but the problem is that the numbers are in text
format, i want it to open as number format automatically, can be done like
that.
With warm regards,
Rajan Sharma
--
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.
--
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.
Re: $$Excel-Macros$$ Help needed in Excel
pls provide excel or explain more about your query... From: Pankaj pankajdhamij...@gmail.com To: excel-macros@googlegroups.com Sent: Wednesday, August 7, 2013 9:23 AM Subject: $$Excel-Macros$$ Help needed in Excel I have 2 columns in excel - A and B. I want to merge the contents of a horizontal row one below the other as: A B please tell me a formula to do this as the columns contain 900 cells each and I cannnot merge the contents manually using Alt + Enter. -- 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: Macro Require
On Thursday, June 27, 2013 6:30:36 PM UTC+5:30, vikas khen wrote: Dear All, Please find the excel sheet. I need macro for the same Regards -- 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. Copy of Query.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Re: Macro Require
see if it helps On Wed, Aug 7, 2013 at 4:33 PM, sandeepsharma4...@gmail.com wrote: On Thursday, June 27, 2013 6:30:36 PM UTC+5:30, vikas khen wrote: Dear All, Please find the excel sheet. I need macro for the same Regards -- 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. -- *Regards* * * *Ashish Koul* *Visit* *My Excel Blog http://www.excelvbamacros.com/* Like Us on Facebookhttp://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 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/groups/opt_out. Copy of Query (1) _pivot table.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ How can I call a very long list into an array?
In my excel sheet, I have an array but the amount of entries is close to 1,000 separate items. Would calling a file or creating a dictionary/collection be best to implement into the array? Sub longfunction() Dim Rng As Range, Str Range(A1).Select Do For Each Str In Array(too many, items to fit, in one array, need dictionary or, need to call file) If InStr(1, Selection, Str) 0 Then Selection.EntireRow.Delete Next Selection.Offset(1, 0).Select Loop Until Selection.Offset(0, 0) = End Sub How would I implement this? -- 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.
Re: $$Excel-Macros$$ Macro to delete row with specific phrase
Save those list of entries in another sheet, say Sheet2. Then use Dim varArray varArray = Worksheets(Sheet2).Range(A1:A1000).Value2 Then do For Each Str In varArray On Wed, Aug 7, 2013 at 11:40 PM, Spencer Patterson williamspencerpatter...@gmail.com wrote: In my excel sheet, I have an array but the amount of entries is close to 1,000 separate items. I am limited to 255 / too many line continuations. Would calling a file or creating a dictionary/collection be best to implement into the array? Sub longfunction() Dim Rng As Range, Str Range(A1).Select Do For Each Str In Array(too many, items to fit, in one array, need dictionary or, need to call file) If InStr(1, Selection, Str) 0 Then Selection.EntireRow.Delete Next Selection.Offset(1, 0).Select Loop Until Selection.Offset(0, 0) = End Sub How would I implement this? -- 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. -- Sam Mathai Chacko -- 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$$ Macro that will leave data and new data from query when ran
I have a macro that works perfectly except for everytime it's ran it wipes the spreadsheet clean and inserts the new values. I need it to insert the new data while leaving the old data without giving me a stackover flow error :-/ Any help would be greatly appreciated. Dim state As String Dim startdate As String Dim enddate As String Sub btn1_click() state = Me.Cells(3, 3) startdate = Me.Cells(4, 3) enddate = Me.Cells(5, 3) MsgBox Input parameters - State = state Date = startdate to enddate For row = 14 To 100 For col = 2 To 100 Me.Cells(row, col) = Next col Next row MsgBox Depending on the volume of data, this report would take 5-10 minutes to fetch results, vbInformation callDB End Sub Sub callDB() On Error GoTo ErrHandler Dim con As ADODB.Connection Dim rs As ADODB.Recordset Dim query As String Dim strqry As String Dim txt As String Dim row As Integer Dim col As Integer Set con = CreateObject(ADODB.Connection) Set rs = CreateObject(ADODB.Recordset) strCon = Provider=OraOLEDB.Oracle;Data Source=enCPR2;User Id=***;Password=***!! con.ConnectionString = strCon con.Open strqry = strqry SELECT strqry = strqry DATES, rs.Open strqry, con If Not rs.EOF Then rs.MoveFirst End If row = 14 col = 2 Do While Not rs.EOF For I = 0 To rs.Fields.Count - 1 Me.Cells(row, col) = rs.Fields(I) col = col + 1 Next I row = row + 1 col = 2 rs.MoveNext Loop ExitHandler: rs.Close con.Close Set rs = Nothing Set con = Nothing MsgBox Report generation is completed Exit 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/groups/opt_out.
Re: $$Excel-Macros$$ Macro to delete row with specific phrase
Sub longfunction() Dim varArray varArray = Worksheets(Sheet2).Range(A1:A1000).Value2 Do For Each Str In varArray If InStr(1, Selection, Str) 0 Then Selection.EntireRow.Delete If InStr(1, UCase(Selection), Ucase(Str)) 0 Then Selection.EntireRow.Delete Next Selection.Offset(1, 0).Select Loop Until Selection.Offset(0, 0) = End Sub --- This gives me a For Each *Str* In varArray Argument Not Optional Error. -- 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$$ Convert number of days to month and day (ie, 035 = 2/4)
Greetings, I work at a hospital laboratory and we have an expiration date barcode on blood transfusion units that formats the month and day as a number; the number of days since the beginning of the year. We use a barcode scanner to read in the expiration date, but need to convert the number of days to month and day. For example for this year (2013), 015 Would be 1/15, 275 would be 10/2. The expiration date includes year and time as well, but I can pull out the numbers of days using the MID function. I've looked through several websites as well as this Group, but haven't found any solutions yet. Thanks, Don -- 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.
Re: $$Excel-Macros$$ Macro that will leave data and new data from query when ran
Replace your code with below one... This will be great if you could provide your original file. Dim state As String Dim startdate As String Dim enddate As String, Erow as long Sub btn1_click() state = Me.Cells(3, 3) startdate = Me.Cells(4, 3) enddate = Me.Cells(5, 3) MsgBox Input parameters - State = state Date = startdate to enddate *Erow=Me.range(A1).End(xlDown).row+1* MsgBox Depending on the volume of data, this report would take 5-10 minutes to fetch results, vbInformation callDB End Sub Sub callDB() On Error GoTo ErrHandler Dim con As ADODB.Connection Dim rs As ADODB.Recordset Dim query As String Dim strqry As String Dim txt As String Dim row As Integer Dim col As Integer Set con = CreateObject(ADODB.Connection) Set rs = CreateObject(ADODB.Recordset) strCon = Provider=OraOLEDB.Oracle;Data Source=enCPR2;User Id=***;Password=***!! con.ConnectionString = strCon con.Open strqry = strqry SELECT strqry = strqry DATES, rs.Open strqry, con If Not rs.EOF Then rs.MoveFirst End If row = 14 col = 2 Do While Not rs.EOF For I = 0 To rs.Fields.Count - 1 Me.Cells(Erow, col) = rs.Fields(I) col = col + 1 Next I Erow = Erow + 1 col = 2 rs.MoveNext Loop ExitHandler: rs.Close con.Close Set rs = Nothing Set con = Nothing MsgBox Report generation is completed Exit Sub On Thu, Aug 8, 2013 at 12:54 AM, christy palmer christy4...@gmail.comwrote: I have a macro that works perfectly except for everytime it's ran it wipes the spreadsheet clean and inserts the new values. I need it to insert the new data while leaving the old data without giving me a stackover flow error :-/ Any help would be greatly appreciated. Dim state As String** Dim startdate As String Dim enddate As String ** ** ** ** Sub btn1_click() state = Me.Cells(3, 3) startdate = Me.Cells(4, 3) enddate = Me.Cells(5, 3) MsgBox Input parameters - State = state Date = startdate to enddate For row = 14 To 100 For col = 2 To 100 Me.Cells(row, col) = Next col Next row MsgBox Depending on the volume of data, this report would take 5-10 minutes to fetch results, vbInformation callDB End Sub ** ** Sub callDB() On Error GoTo ErrHandler ** ** Dim con As ADODB.Connection Dim rs As ADODB.Recordset Dim query As String Dim strqry As String Dim txt As String Dim row As Integer Dim col As Integer ** ** Set con = CreateObject(ADODB.Connection) Set rs = CreateObject(ADODB.Recordset) ** ** strCon = Provider=OraOLEDB.Oracle;Data Source=enCPR2;User Id=***;Password=***!! ** ** con.ConnectionString = strCon con.Open ** ** ** ** strqry = strqry SELECT strqry = strqry DATES, ** ** rs.Open strqry, con ** ** If Not rs.EOF Then rs.MoveFirst End If row = 14 col = 2 Do While Not rs.EOF ** ** For I = 0 To rs.Fields.Count - 1 Me.Cells(row, col) = rs.Fields(I) col = col + 1 Next I row = row + 1 col = 2 rs.MoveNext Loop ** ** ExitHandler: rs.Close con.Close Set rs = Nothing Set con = Nothing MsgBox Report generation is completed Exit 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/groups/opt_out. -- Regards, Anoop Sr. Developer Facebook ID - https://www.facebook.com/anooop.k.sharma -- 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 @
Re: $$Excel-Macros$$ Convert number of days to month and day (ie, 035 = 2/4)
Don, try passing the number of days to the date function like this. =TEXT(DATE(2013,1,No. Of Days - 1), m\/d) Sam MATHAI CHACKO On Aug 8, 2013 4:11 AM, Don Barton donaldb...@gmail.com wrote: Greetings, I work at a hospital laboratory and we have an expiration date barcode on blood transfusion units that formats the month and day as a number; the number of days since the beginning of the year. We use a barcode scanner to read in the expiration date, but need to convert the number of days to month and day. For example for this year (2013), 015 Would be 1/15, 275 would be 10/2. The expiration date includes year and time as well, but I can pull out the numbers of days using the MID function. I've looked through several websites as well as this Group, but haven't found any solutions yet. Thanks, Don -- 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.
Re: $$Excel-Macros$$ Convert number of days to month and day (ie, 035 = 2/4)
In fact you probably don't need that - 1. Remove it. I'm just typing this on my handset, so please don't mind. On Aug 8, 2013 6:50 AM, Sam Mathai Chacko samde...@gmail.com wrote: Don, try passing the number of days to the date function like this. =TEXT(DATE(2013,1,No. Of Days - 1), m\/d) Sam MATHAI CHACKO On Aug 8, 2013 4:11 AM, Don Barton donaldb...@gmail.com wrote: Greetings, I work at a hospital laboratory and we have an expiration date barcode on blood transfusion units that formats the month and day as a number; the number of days since the beginning of the year. We use a barcode scanner to read in the expiration date, but need to convert the number of days to month and day. For example for this year (2013), 015 Would be 1/15, 275 would be 10/2. The expiration date includes year and time as well, but I can pull out the numbers of days using the MID function. I've looked through several websites as well as this Group, but haven't found any solutions yet. Thanks, Don -- 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.
Re: $$Excel-Macros$$ VBA code require
Thanks a Sir lot. Actually, i am learning the same . I was able to write the code for single column but i didnot understand how to write for second column Regards, Prafull Jadhav. 9920553518 On Wed, Aug 7, 2013 at 9:05 PM, ashish koul koul.ash...@gmail.com wrote: Sub test() For j = 1 To Range(a2:c4).Columns.Count For i = 1 To Range(a2:c4).Rows.Count Range(h65356).End(xlUp).Offset(1, 0) = Range(a2:c4).Cells(i, j).Value Next Next End Sub see if it helps On Wed, Aug 7, 2013 at 10:11 AM, Prafull Jadhav prafulltjad...@gmail.comwrote: Dear All, Very Good Morning. Please provide me VBA code as i am not able to write it . Thanks in Advance. Regards, Prafull -- 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. -- *Regards* * * *Ashish Koul* *Visit* *My Excel Blog http://www.excelvbamacros.com/* Like Us on Facebookhttp://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 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/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.
Re: $$Excel-Macros$$ Macro to delete row with specific phrase
To accomodate that, the easiest way is to put your keyword to a sheet, Let say your array placed in sheet named MyArrayInSheet starting on range A1 to A1000, then use this function : Sub Button1_Click() Dim Rng As Range ActiveSheet.Range(A1).Select Do For Each Rng In Sheets(MyArrayInSheet).Range(A1).CurrentRegion If InStr(1, Selection, Rng) 0 Then Selection.EntireRow.Delete Selection.Offset(-1, 0).Select Exit For End If Next Selection.Offset(1, 0).Select Loop Until Selection.Offset(0, 0) = End Sub Pada 08/08/2013 1:10, Spencer Patterson menulis: In my excel sheet, I have an array but the amount of entries is close to 1,000 separate items. I am limited to 255 / too many line continuations. Would calling a file or creating a dictionary/collection be best to implement into the array? Sub longfunction() Dim Rng As Range, Str Range(A1).Select Do For Each Str In Array(too many, items to fit, in one array, need dictionary or, need to call file) If InStr(1, Selection, Str) 0 Then Selection.EntireRow.Delete Next Selection.Offset(1, 0).Select Loop Until Selection.Offset(0, 0) = End Sub How would I implement this? -- 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.
