$$Excel-Macros$$ Re: VBA Password
How is it that you came to misplace the password? If this is not a commercial excel file and something you lost or misplaced I would have no issues removing the VBA password for you and returning the file to you when complete. Nathan On Thursday, January 16, 2014 6:57:35 AM UTC-5, Pavan Valluru wrote: Do we have VBA code to un protect or crack VBA password? -Pavan -- 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: Filling Data based on cells above
Thanks again for all of the replies, below is the code I put together to do this task in case anyone is interested... LastRow = Range(C Rows.Count).End(xlUp).row Range(A1:B LastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = =R[-1]C Columns(A:B).Copy Columns(A:B).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False -- 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?hl=en.
$$Excel-Macros$$ Re: Filling Data based on cells above
Thanks all, the issue has been resolved! You are all great! Natron -- 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$$ Filling Data based on cells above
I'm looking for the most efficient way to take the data from my spreadsheet and manipulate it into more of a database format. I've posted my file at the below link with an example of the data and what I'd like it to look like after a macro has been run. I tried to use the CurrentRegion collection and then jump to the next but am not sure if that is really the best way to go about this. https://docs.google.com/file/d/0B7Wrlvw2fV31WGtLbUVycXdaT1k/edit Thank you to any and all responses! Natron -- 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.
Re: $$Excel-Macros$$ EMAIL AUTOMATION BY VBA OUTLOOK
*Ashish, you are awesome!* -- 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: Please Open the Excel file
Breaking passwords is not allowed in this forum. Thanks. Natron -- 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.
Re: $$Excel-Macros$$ Please help - How to export specific range by ID to other excel file.
Glad it worked for you. On Tuesday, November 20, 2012 1:29:45 PM UTC-5, Mangesh wrote: Thank you so much Natron. You have made my work very easy. Thanks again. -- 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.
Re: $$Excel-Macros$$ Re: Please help - How to export specific range by ID to other excel file.
My apologies Anoop, I hope you can understand my code, I tried to comment it at key locations. I attached a workbook the second go around. On Tuesday, November 20, 2012 9:38:32 PM UTC-5, Enrique Martin wrote: It's better to attach the file and put you query there. No one gonna study this code as different people have different logic. Regards, Anoop -- 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.
Re: $$Excel-Macros$$ Please help - How to export specific range by ID to other excel file.
See attached, with the code I used below...there are many other ways to do this. 'This is hooked to your Export Specific Data Button on your form Private Sub cmdExport_Click() Dim intStart As Integer, intEnd As Integer On Error GoTo whoops intStart = Me.txtStart intEnd = Me.txtEnd If intStart intEnd Then GoTo whoops exportme Me.Hide Exit Sub whoops: MsgBox Please enter two integers and try again End Sub 'This is put into a Module Sub exportme() '' 'Original Script Written by www.ozgrid.com '' Dim rRange As Range Dim strCriteria As String, strCriteria2 As String Dim lCol As Long Dim rHeaderCol As Range Dim xlCalc As XlCalculation On Error Resume Next Step1: Set rRange = Range(A1).CurrentRegion 'Cancelled or non valid rage If rRange Is Nothing Then Exit Sub 'Awlays use GoTo when selecting range so doesn't matter which Worksheet Application.Goto rRange.Rows(1), True step2: lCol = 1 Step3: strCriteria = frmExport.txtStart strCriteria2 = frmExport.txtEnd 'Store current Calculation then switch to manual. 'Turn off events and screen updating With Application xlCalc = .Calculation .Calculation = xlCalculationManual .EnableEvents = False .ScreenUpdating = False End With 'Remove any filters ActiveSheet.AutoFilterMode = False Dim s As Range With rRange 'Filter, offset(to exclude headers) .AutoFilter Field:=lCol, Criteria1:== strCriteria, Operator:=XlAutoFilterOperator.xlAnd, Criteria2:== strCriteria2 .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy End With Workbooks.Add ActiveSheet.Paste ChDir C:\ ActiveWorkbook.SaveAs Filename:=C:\YourFile.xlsx, FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False ActiveWorkbook.Save ActiveWindow.Close 'Remove any filters ActiveSheet.AutoFilterMode = False 'Revert back With Application .Calculation = xlCalc .EnableEvents = True .ScreenUpdating = True End With On Error GoTo 0 End Sub -- 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. ExportRange.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
$$Excel-Macros$$ Re: Mandatory Field
Not possible to disable closing the file without a Macro. -- 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$$ Formula to grab last two words from string
I have a string such as the following two examples and want a formula to grab the last two words. The following formula works for me but I'm looking for other ways to do the same. =MID(A1,FIND(-Date,A1)+6,LEN(A1)) Any help would be appreciated Cell A1 contains: Reporting Period: Month-To-Date November 2012 Reporting Period: Month-To-Date April 2012 Formula in cell B1 should produce: November 2012 April 2012 Thank you! -- 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.
Re: $$Excel-Macros$$ Formula to grab last two words from string
Works Perfectly, Thanks! Rajan On Monday, November 19, 2012 11:40:26 AM UTC-5, Rajan_Verma wrote: *If words are separated by Space use this* *=TRIM(RIGHT(SUBSTITUTE(A1, ,REPT( ,LEN(A1))),LEN(A1)*2))** * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* -- 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: Formula to grab last two words from string
Prince, thank you for the attempt, this does not work as I intended. I'm interested to learn more about the technique you came back with. If you have the time can you do a quick explaination of the functions you are using to arrrive at the result? Thank you again! On Monday, November 19, 2012 11:26:07 AM UTC-5, Prince wrote: Hi Natron, I hope this will help you. =MID(A1,FIND(*,SUBSTITUTE(A1, ,*,1),1),LEN(A1)) Regards Prince -- 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: Please help - How to export specific range by ID to other excel file.
This is close and might get you a kickstart. Sub selectme() '' 'Original Script Written by www.ozgrid.com '' Dim rRange As Range Dim strCriteria As String, strCriteria2 As String Dim lCol As Long Dim rHeaderCol As Range Dim xlCalc As XlCalculation On Error Resume Next Step1: Set rRange = Range(A1).CurrentRegion 'Cancelled or non valid rage If rRange Is Nothing Then Exit Sub 'Awlays use GoTo when selecting range so doesn't matter which Worksheet Application.Goto rRange.Rows(1), True step2: lCol = 1 'Cancelled If lCol = 0 Then Exit Sub Step3: strCriteria = frmExport.txtStart strCriteria2 = frmExport.txtEnd 'Store current Calculation then switch to manual. 'Turn off events and screen updating With Application xlCalc = .Calculation .Calculation = xlCalculationManual .EnableEvents = False .ScreenUpdating = False End With 'Remove any filters ActiveSheet.AutoFilterMode = False With rRange 'Filter, offset(to exclude headers) .AutoFilter Field:=lCol, Criteria1:== strCriteria, Operator:=XlAutoFilterOperator.xlAnd, Criteria2:== strCriteria2 .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Select End With 'Remove any filters 'ActiveSheet.AutoFilterMode = False 'Revert back With Application .Calculation = xlCalc .EnableEvents = True .ScreenUpdating = True End With On Error GoTo 0 End Sub On Monday, November 19, 2012 1:55:03 PM UTC-5, Mangesh wrote: Hi Friends, I have attached the file where I need to export specific range to another excel file which to be save on C: drive. I have created userform for this as well. Need vba code to perform this task. Waiting for the solution. Please help !!! -- With regards, *MaNgEsH* -- 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.
Re: $$Excel-Macros$$ VBA Conditional Format Offset
Thanks Daniel that works, but I'm still trying to wrap my head around applying this format to various ranges on the worksheet. My data I'm comparing is in Column D and F, E and Getc all the way up to Column EK. Any pointers on skipping around with this formula? Thanks again! Natron Try : Sub ConFormatOffset() With Range([A1], Cells(Rows.Count, 1).End(xlUp)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Operator:=xlGreater, _ Formula1:==A1B1 .FormatConditions(1).Interior.Color = RGB(255, 0, 0) End With End Sub Daniel -- -- 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$$ Looping an array
Thanks to both Ashish and GoldenLance. Problem solved. -- -- 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$$ VBA Conditional Format Offset
I have multiple columns (around 140) of data and need to conditionally format the data as apposed to looping through. For instance I have the following data in column A and B. A B 1 10 2 1 8 4 4 3 5 6 Here is basically what I'm trying to do: If Column A has a value Greater than Column B then change Column A cell interior color to red My attempt in a Macro to perform this task is Below...not quite working Sub ConFormatOffset() With Selection Debug.Print ActiveCell Debug.Print ActiveCell.Offset(0, 2) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Operator:=xlGreater, Formula1:==A1Offset(ActiveCell,0,2) .FormatConditions(1).Interior.Color = RGB(255, 0, 0) End With 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Looping an array
Thanks for the reply Ashish, However I need to skip around a bit since not all columns need summed. For instance Columns 4, 7, 9 need summed but those inbetween do not. -- -- 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$$ Looping an array
I have a spreadsheet I need to have multiple items summed at the first blank row available...the following macro works great for Column 4 as shown below, but I need to have it run through multiple columns up to 20...I tried using an array using a for next loop on the array, but couldn't get it figured out. I commented out some of what I was attemptign. Any suggestions or a push in the right direction would be appreciated! Sub enterTotals() 'myArr = Array(4, 5, 7) 'For i = LBound(myArr) To UBound(myArr) For Each rngArea In Columns(4).SpecialCells(xlCellTypeConstants, xlNumbers).Areas With rngArea.Offset(rngArea.Rows.Count).Resize(1, 1) .FormulaR1C1 = =SUM( rngArea.Address(1, 1, xlR1C1) ) .Interior.ColorIndex = 6 End With Next rngArea 'Next i End Sub As a bonus I need to have some simple percentages figured on the same blank total row the sumations are on I'd figure I would build that into the loop as well unless there is a faster method. Thanks you are all great! -- -- 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$$ Sorting a list; adding blanks when either side is not present
Very easy to understand, and just what I needed. Thank you very much for your time and knowledge! On Feb 2, 12:40 am, ashish koul koul.ash...@gmail.com wrote: see if this helps Sub sort1() Sheets(1).Range(a1:a Range(a1).End(xlDown).Row).Sort key1:=Sheets(1).Range(a:a), order1:=xlAscending, Header:=xlYes Sheets(1).Range(b1:b Range(b1).End(xlDown).Row).Sort key1:=Sheets(1).Range(b:b), order1:=xlAscending, Header:=xlYes Dim i As Long For i = 2 To Sheets(1).UsedRange.Rows.Count If UCase(Sheets(1).Cells(i, 1).Value) UCase(Sheets(1).Cells(i, 2).Value) And Sheets(1).Cells(i, 1).Value Then If Sheets(1).Cells(i, 1).Value Sheets(1).Cells(i, 2).Value Then Sheets(1).Cells(i, 1).Insert Shift:=xlDown Else Sheets(1).Cells(i, 2).Insert Shift:=xlDown End If End If Next i End Sub On Tue, Feb 1, 2011 at 9:48 AM, Nathan protoc...@gmail.com wrote: Hello all, I'm hoping you will have time to assist me understanding how to best tackle this comparison list shown at the following link. https://docs.google.com/leaf?id=0B7Wrlvw2fV31ODliYzkwMWYtMzRhMi00OGU4... I'm given a range similar to (A1:B5) and need it to end up like range (D1:E6). Basically I have two list and need to sort out and add in blank cells to anything that does not match from either side. Thank you in advance for any bits of VBA or places to start my seach. -- ------ 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 -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. Book1.xls 43KViewDownload- Hide quoted text - - Show quoted text - -- -- 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$$ Sorting a list; adding blanks when either side is not present
Wow, I like this VBA example quite a bit, the problem I provided was indeed simplistic and would need to be modified for my intended need. I'm fairly new at VBA and very much appreciate your skill and knowledge. I was wondering if an array was the way to go! Thank you Again! On Feb 2, 3:51 am, siti Vi villager.g...@gmail.com wrote: i think my code is too big (no a simple code) but your refferred-table can be extended (add columns / add rows) without editing this code Sub Re_Arrange() '- ' coded by: siti Vi villager.g...@gmail.com ' jakarta, feb 1, 2011 '- '- declaring some variables... Dim sItem As String ' consist of unique-items text separated by \ Dim dArr() As Variant ' dynamic array consist of Unique Item only Dim TBLV() As Range ' dynamic array consist of each Vector / ' [OneColumnTable] of Old table Dim dTBL As Range ' the Old Table Dim dNEW As Range ' the New Table Dim xCell As Range ' each cell in Old Table Dim i As Long, n As Long ' counters in some looping Dim r As Long, c As Integer ' number of rows and columns of the Old Tbl '--assignments... Set dTBL = Cells(1).CurrentRegion c = dTBL.Columns.Count r = dTBL.Rows.Count - 1 Set dNEW = dTBL.Offset(1, c + 1) Set dTBL = dTBL.Offset(1, 0).Resize(r, c) dNEW.CurrentRegion.Clear ' copying the header dTBL.Offset(-1, 0).Resize(1, c).Copy dNEW(0, 1) '--assign some [OneColumnTable] in array ' depends on the width of the Old Table For i = 1 To c ReDim Preserve TBLV(1 To i) Set TBLV(i) = dTBL.Offset(0, i - 1).Resize(r, 1) Next i '--items storred in an array (the unique item only) For Each xCell In dTBL If Len(xCell) 0 Then If InStr(1, sItem, xCell \) = 0 Then sItem = sItem xCell \ n = n + 1 ReDim Preserve dArr(1 To n) dArr(n) = xCell.Value End If End If Next xCell ' elemens in array is sorted ascending dArr = SortArray(dArr) '-- reconstruct a new table based on Old table With Application.WorksheetFunction For i = 1 To UBound(dArr) For n = 1 To c If .CountIf(TBLV(n), dArr(i)) 0 Then dNEW(i, n) = dArr(i) End If Next n Next i End With End Sub Private Function SortArray(Ar) ' bubble sorter (ascending order) ' [one-dimension-array] to [one-dimension-array] ' by siti Vi// jakarta, feb 1, 2011 '--- Dim a As Long, b As Long, c As Long, z As Long, t z = UBound(Ar) For a = LBound(Ar) To z - 1 For b = z To (a + 1) Step -1 c = b - 1 If Ar(b) Ar(c) Then t = Ar(b): Ar(b) = Ar(c): Ar(c) = t End If Next b Next a SortArray = Ar End Function thank you and best regards, siti On Tue, Feb 1, 2011 at 11:18 AM, Nathan protoc...@gmail.com wrote: Hello all, I'm hoping you will have time to assist me understanding how to best tackle this comparison list shown at the following link. https://docs.google.com/leaf?id=0B7Wrlvw2fV31ODliYzkwMWYtMzRhMi00OGU4... I'm given a range similar to (A1:B5) and need it to end up like range (D1:E6). Basically I have two list and need to sort out and add in blank cells to anything that does not match from either side. Thank you in advance for any bits of VBA or places to start my seach. ctv_ReArrange for comparison.xls 75KViewDownload- Hide quoted text - - Show quoted text - -- -- 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
