$$Excel-Macros$$ Re: Combining mulitple rows into one row
Yes there is, but it seems to me that it would falsify the records. On Jan 11, 2:57 pm, AlwaysLearing wrote: > Hey Guys, > I have a spreadsheet that has multiple rows of data for each employee > with each row containing the date they worked and for how long. For > example, Mr. Smith worked on 7/1/09 for 8 hrs and on 7/2/09 for 8 hrs > and 7/3/09 for 8 hrs. Is there a way to combine all 3 rows as Mr. > Smith worded on 7/1/09 for 24 hrs? > > thanks! -- -- Some important links for excel users: 1. Follow us in TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 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 If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 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
Re: $$Excel-Macros$$ Help on Date Function
Since I am firm believer in writing concise code whenever possible,
especially when assisting others in learning the power of VBA, I
thought it would be helpful to post a shorter version of your
Move_Only_Date_To_Result routine (3 statements instead of 9):
Sub Move_Only_Date_to_Result()
Sheets("Sheet1").Range("B7:D12").Copy Sheets("Results").Range
("A1")
Sheets("Results").Range("C2:C6").Copy
Sheets("Results").Range("C2").PasteSpecial Paste:=xlPasteValues
End Sub
Best wishes,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Dec 3, 10:38 pm, rf1234 rf1234 wrote:
> Your are trying to copy and paste.
> if u will use copy and paste then it will copy
> formula.
>
> use code
>
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
> :=False, Transpose:=False
>
> for you i have attached the file
>
>
>
> On Thu, Dec 3, 2009 at 8:41 PM, krishnan Gopi wrote:
> > Hello All,
>
> > Facing problem with date function, the same is explained in the attachment.
>
> > Regards
> > Gopi Krishnan
>
> > --
>
> > ------
> > 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 athttp://www.excel-macros.blogspot.com
> > 3. Learn VBA Macros athttp://www.vbamacros.blogspot.com
> > 4. Excel Tips and Tricks athttp://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
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
>
> > We reach over 6,500 subscribers worldwide and receive many nice notes about
> > the learning and support from the group. Our goal is to have 10,000
> > subscribers by the end of 2009. Let friends and co-workers know they can
> > subscribe to group at
> >http://groups.google.com/group/excel-macros/subscribe
>
>
>
> Date Function.xls
> 56KViewDownload- Hide quoted text -
>
> - Show quoted text -
--
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Help required in repeating calculation using macro (This is urgent, can anyone help me)
Here are a couple of questions I have: customer Billable% = Customer Billable hours/Potential hours (H17/ F17): what do you want to see in H17? I am afraid you are thinking SUM (H7:H16), but since all the values are percentages this simply makes no sense. Might it be possible that H17 should be G17/F17? Cust.Non Billable% = Cust Non Billable hours/Potential hours(L17/F17): similar for L17. Should it rather be K17/F17? Total Cust% = Total Cust Hours/Potential hours(N17/F17): likewise. Should N17 = M17/F17? The other formulae you would like to see calculated seem correct to me. Please let me know if you are familiar with macros, i.e. VBA. Unless you are your asking for a solution to your workbook problem might be a bit misplaced in this forum. After all 'this Google group is is dedicated to unleash the POWER of MS EXCEL & VBA MACROS,one of the best Programming ever created for Mankind' which I interpret as Excel VBA experts helping others unleash their own VBA programming potential. Therefore unless you are indeed trying to learn VBA you might be better off looking for a paid-for contractor to develop a solution for you. Best wishes, Rolf Jaeger SoarentComputing http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm On Dec 3, 7:51 am, RolfJ wrote: > I'd be happy to help, but would need a bit more information. > > Rolf Jaeger > SoarentComputinghttp://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm > > On Dec 2, 4:52 am, bala Subrahmanyam puligadda > wrote: > > > > > Hi ALL, > > > Please help me in the repeating some calculations by using the macro. > > > Please find the attachment. > > > the following are the details for that > > > we have to do calculation on the Employee Summary Rep tab. > > Please look into > > A17 row i.e., the subtotal for Organisation name ABC > > now what we have to do is to calculate the > > sum of potential hours for ABC (F7:F16) > > customer Billable% = Customer Billable hours/Potential hours (H17/F17) > > Cust.Non Billable% = Cust Non Billable hours/Potential hours(L17/F17) > > Total Cust% = Total Cust Hours/Potential hours(N17/F17) > > Non Cust Productive% = Non Cust Productive hours/Potential hours.(017/F17) > > Total Productive % = Total Productive hours/Potential hours(Q17/F17) > > Overhead% = Overhead hours/Potential hours(S17/F17) > > Total All%= Total All Hours/Potential hours(U17/F17) > > > This if for ABC organisation, like this we have to repeat this calculation > > for all the organizations and leave the grand totals at the bottom. > > > This is a sample report, normally no,of organizations and details regarding > > the organization will vary from the report. I need a solution which will fit > > for all these changes. please help. > > but the columns are fixed for every report. > > > Thank you.. > > > Macro Help.xlsx > > 42KViewDownload- Hide quoted text - > > - Show quoted text - -- -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Help on Date Function
You could place the following function into a standard VBA module of your workbook: Public Function LastUpdated() As Date LastUpdated = FileDateTime(ActiveWorkbook.FullName) End Function And then use it in the cells you would like to show the date of the last saved update by entering the formula: =LastUpdated() Hope this helped, Rolf Jaeger SoarentComputing http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm On Dec 3, 7:11 am, krishnan Gopi wrote: > Hello All, > > Facing problem with date function, the same is explained in the attachment. > > Regards > Gopi Krishnan > > Date Function.xls > 28KViewDownload -- -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Help required in repeating calculation using macro (This is urgent, can anyone help me)
I'd be happy to help, but would need a bit more information. Rolf Jaeger SoarentComputing http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm On Dec 2, 4:52 am, bala Subrahmanyam puligadda wrote: > Hi ALL, > > Please help me in the repeating some calculations by using the macro. > > Please find the attachment. > > the following are the details for that > > we have to do calculation on the Employee Summary Rep tab. > Please look into > A17 row i.e., the subtotal for Organisation name ABC > now what we have to do is to calculate the > sum of potential hours for ABC (F7:F16) > customer Billable% = Customer Billable hours/Potential hours (H17/F17) > Cust.Non Billable% = Cust Non Billable hours/Potential hours(L17/F17) > Total Cust% = Total Cust Hours/Potential hours(N17/F17) > Non Cust Productive% = Non Cust Productive hours/Potential hours.(017/F17) > Total Productive % = Total Productive hours/Potential hours(Q17/F17) > Overhead% = Overhead hours/Potential hours(S17/F17) > Total All%= Total All Hours/Potential hours(U17/F17) > > This if for ABC organisation, like this we have to repeat this calculation > for all the organizations and leave the grand totals at the bottom. > > This is a sample report, normally no,of organizations and details regarding > the organization will vary from the report. I need a solution which will fit > for all these changes. please help. > but the columns are fixed for every report. > > Thank you.. > > Macro Help.xlsx > 42KViewDownload -- -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Named range in macro
The syntax for referring to named ranges is Range("{Name of Named
Range}"). Also, I would strong recommend to work with Option Explicit
which will require you to explicitly declare your variables. That
makes your code easier to read and maintain. Give this a try:
Option Explicit
Private Sub Worksheet_Activate()
Dim c As Range
Dim a As String
With Range("CompNames")
Set c = .Find(Range("A1"))
If Not c Is Nothing Then a = Sheets("Names").Cells(c.Row, 3)
End With
If ActiveSheet.Name <> a Then ActiveSheet.Name = a
End Sub
Hope this helped,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Dec 2, 8:59 pm, Dave Bonallack wrote:
> Hi friends,
>
> I am using XL2003.
>
> I am having trouble using a named range in a macro, even after refering the
> Help.
> The named range lives in the active workbook.
> My code is this:
>
> Private Sub Worksheet_Activate()
> With [CompNames]
> Set c = .Find(Range("A1"))
> If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3)
> End With
> If ActiveSheet.Name <> A Then ActiveSheet.Name = A
> End Sub
>
> You will see that I have had to resort to using [ ] around the named range,
> which is the only way I could get the code to run.
> I tried With Range("CompNames") which is what the Help seemed to indicate,
> and tried including the sheet name and the workbook name, but none of those
> worked.
>
> I have checked the spelling of the named range, even copied and pasted it
> straight from the name box.
>
> When I use With Range("CompNames") it returns the following error:
>
> Runtime error '1004':
> Method 'Range' of object '_Worksheet' failed
>
> Anyone had a problem with this before?
>
> Regards - Dave.
>
> _
> Use Messenger in your Hotmail inbox Find out
> howhttp://windowslive.ninemsn.com.au/hotmail/article/823454/web-im-for-h...
--
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Add a SUMPRODUCT formula to a cell with Criteria Referenced in ComboBoxes in a Userform
Try this: .Formula = "=SUMPRODUCT((C1:H1= " & UserForm1.ComboBox2.Value & ")* (A2:A13= " & UserForm1.ComboBox1.Value & ")*(C2:H13))" Hope this helped, Rolf Jaeger SoarentComputing http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm On Dec 2, 9:54 pm, sgltaylor wrote: > Hi All, > > I would appreciate help with the following: > > I am trying to insert a sumproduct formula in a cell > but the criteria options are selected by the user > from comboBoxes in a user form. > > My code looks like this: > > With ActiveCell > .Formula = "=SUMPRODUCT((C1:H1= UserForm1.ComboBox2.value)* > (A2:A13= UserForm1.ComboBox1.Value )*(C2:H13))" > End With > > Any help would be appreciated. > > Thanks, > > Steve -- -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Hide/Unhide and Print selected sheets
This code might do the trick for you:
Const SUMMARY_SHEETNAME As String = "Summary"
Sub HideUnhideSelectedSheets()
Dim rCell As Range
For Each rCell In Range(Worksheets(SUMMARY_SHEETNAME).Range("A2"),
Worksheets(SUMMARY_SHEETNAME).Range("A" & Rows.Count).End(xlUp))
Dim sh As Worksheet
On Error Resume Next
Set sh = Worksheets(rCell.Value)
If Not sh Is Nothing Then
If UCase(rCell.Offset(, 1).Value) = "YES" Then
sh.Visible = xlSheetHidden
Else
sh.Visible = xlSheetVisible
End If
End If
Set sh = Nothing
Next rCell
End Sub
Sub PrintSelectedSheets()
Dim rCell As Range
For Each rCell In Range(Worksheets(SUMMARY_SHEETNAME).Range("A2"),
Worksheets(SUMMARY_SHEETNAME).Range("A" & Rows.Count).End(xlUp))
Dim sh As Worksheet
On Error Resume Next
Set sh = Worksheets(rCell.Value)
If Not sh Is Nothing Then
If UCase(rCell.Offset(, 2).Value) = "YES" Then
sh.PrintOut
End If
End If
Set sh = Nothing
Next rCell
End Sub
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Nov 26, 4:33 am, Manish Pansari wrote:
> Dear All,
>
> I want to create a macro by which I want to print relevant sheet and
> want to hide/unhide irrelevant sheets. I want to list out all working
> sheet in one sheet and in column next to the name of the worksheet i
> want to put Yes/No for hide the sheet. Once i decide the whole sheet
> which i want to hide i want to run the macro. e.g.
>
> Column A Column B Column C
> Hide Print
> Sheet1 Yes No
> Sheet2 NO Yes
> Sheet3 No Yes
> Sheet4 Yes Yes
>
> How I can do this, pls help me. I want to assign separate macro
> commend for hide/Unhide and print sheet.
>
> Thnxs in advance.
>
> -
> Manish
--
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Autofill Based on a Changing Column
If I correctly understand what your are trying to do this code might do the trick: Sub CopyFormulaToRowBeyondLastOfColumnToTheLeft() Dim targetRange As Range Set targetRange = Range(ActiveCell.Offset(1), Cells (ActiveCell.Offset(, -1).End(xlDown).Row, ActiveCell.Column)) ActiveCell.Copy targetRange End Sub Hope this helped, Rolf Jaeger SoarentComputing http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm On Nov 26, 9:36 pm, sgltaylor wrote: > Hi > > Any assistance with the following would be most welcome. > > I am trying to find some VBA code which will allow me to copy a > formula from an ActiveCell down to the last row in the ActiveCell > column based on the non blank cells in the column to the left of the > ActiveCell column. > > For example, if the ActiveCell is equal to C5 and the range B5 to B20 > contains entries. > I would like to autofill the formula from cell C5 down to C20. > > To further complicate matters, the ActiveCell may be in column C in > one instance and in another it could be column D etc (in other words, > it can be in any column depending on the source data). The column to > the immediate left will always have entries in it but the number of > rows will change as well. > > Thanks, > > Steve > > PS I am using Microsoft Office 2003 with windows XP -- -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Need help
I can't think of any Excel-intrinsic method that would do this.
However a VBA macro could. If I was to assume that your colored cells
are the ONLY data on active worksheet, give this macro a try:
Sub ExtractNonZeroCells()
Dim db As Range
Set db = ActiveSheet.UsedRange
'First results of clear previous extraction
Columns("A").ClearContents
Dim nextRow As Long
nextRow = 8
Dim rCell As Range
For Each rCell In db.Cells
If rCell.Value <> "" And rCell.Value <> 0 Then
Cells(nextRow, 1).Value = rCell.Value
nextRow = nextRow + 1
End If
Next rCell
End Sub
Hope this helped,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Nov 23, 8:08 am, Kaustubh K wrote:
> Hi,
>
> In the attached file the basic data is in coloured cells.
>
> What I want to achieve is that I want to list all items from the basic data
> that are not zero in column A and that too alphabetically i.e A1, then A6
> then A8 and so on.
>
> Is there any method in excel so that I can do that.
>
> Thanks
> KK
>
> PROBLEM.xlsx
> 11KViewDownload
--
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Replace contents in a formula used for whole sheet.
You should provide a bit more detail. What's the formula? Which characters do you need to change? On Nov 24, 2:51 am, nikhil deshpande wrote: > Dear all, > > I need to change characters used to show results in (IF ELSE ) formula. > > I tried ctrl F- replace. but it is disturbing mu sheet and askin g to save > some where else. > > Kindly guide. > -- > Nikhil -- -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Excel check list
Here is a slight correction to my previous response. The eventhandlers
in the ThisWorkbook VBA module need to be modified as follows:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
UserForm1.cmdButton.Caption = "Close Workbook"
UserForm1.Show vbModal
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
UserForm1.cmdButton.Caption = "Save Workbook"
UserForm1.Show vbModal
End Sub
Best wishes,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Nov 23, 5:53 pm, RolfJ wrote:
> If I correctly understand what you are trying to do you could add a
> UserForm to your workbook, add five checkboxes and one command button
> and then place the following code in its VBA module:
>
> Option Explicit
>
> Dim b_UserIsFinished As Boolean
>
> Private Sub CheckBox1_Click()
> cmdButton.Enabled = AllTasksFinished
> End Sub
>
> Private Sub CheckBox2_Click()
> cmdButton.Enabled = AllTasksFinished
> End Sub
>
> Private Sub CheckBox3_Click()
> cmdButton.Enabled = AllTasksFinished
> End Sub
>
> Private Sub CheckBox4_Click()
> cmdButton.Enabled = AllTasksFinished
> End Sub
>
> Private Sub CheckBox5_Click()
> cmdButton.Enabled = AllTasksFinished
> End Sub
>
> Private Sub cmdButton_Click()
> b_UserIsFinished = True
> Unload Me
> End Sub
>
> Private Sub UserForm_Initialize()
> Me.Caption = "Task List"
> cmdButton.Enabled = False
> b_UserIsFinished = False
> End Sub
>
> Function AllTasksFinished() As Boolean
> If CheckBox1 And CheckBox2 And CheckBox3 And CheckBox4 And
> CheckBox5 Then
> AllTasksFinished = True
> Else
> AllTasksFinished = False
> End If
> End Function
>
> Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
> Integer)
> If Not b_UserIsFinished Then Cancel = True
> End Sub
>
> and then place the folloing event handlers in the ThisWorkbook VBA
> module:
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> MsgBox ("Can't close without saving")
> Cancel = True
> End Sub
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> UserForm1.Show vbModal
> End Sub
>
> Hope this will get you started in the right direction,
> Rolf Jaeger
> SoarentComputinghttp://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
>
> On Nov 20, 3:00 am, Hiren Sheth wrote:
>
>
>
> > Hi Excel Gurus,
>
> > Thanks a lot for sharing your knowledge and solving queries.
>
> > I am planning to prepare a check list for my team member for a particular
> > task.
>
> > I mean once they finish the working on the excel sheet and try to close
> > that, a pop up of check list will appear which state the steps to be
> > perform to complete the task (max 5 steps).
>
> > In order to close the file, user must be forced to mark each task.
>
> > Regards
> > Hiren
>
> > 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-
> > Hide quoted text -
>
> - Show quoted text -
--
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Excel check list
If I correctly understand what you are trying to do you could add a
UserForm to your workbook, add five checkboxes and one command button
and then place the following code in its VBA module:
Option Explicit
Dim b_UserIsFinished As Boolean
Private Sub CheckBox1_Click()
cmdButton.Enabled = AllTasksFinished
End Sub
Private Sub CheckBox2_Click()
cmdButton.Enabled = AllTasksFinished
End Sub
Private Sub CheckBox3_Click()
cmdButton.Enabled = AllTasksFinished
End Sub
Private Sub CheckBox4_Click()
cmdButton.Enabled = AllTasksFinished
End Sub
Private Sub CheckBox5_Click()
cmdButton.Enabled = AllTasksFinished
End Sub
Private Sub cmdButton_Click()
b_UserIsFinished = True
Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.Caption = "Task List"
cmdButton.Enabled = False
b_UserIsFinished = False
End Sub
Function AllTasksFinished() As Boolean
If CheckBox1 And CheckBox2 And CheckBox3 And CheckBox4 And
CheckBox5 Then
AllTasksFinished = True
Else
AllTasksFinished = False
End If
End Function
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
If Not b_UserIsFinished Then Cancel = True
End Sub
and then place the folloing event handlers in the ThisWorkbook VBA
module:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox ("Can't close without saving")
Cancel = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
UserForm1.Show vbModal
End Sub
Hope this will get you started in the right direction,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Nov 20, 3:00 am, Hiren Sheth wrote:
> Hi Excel Gurus,
>
> Thanks a lot for sharing your knowledge and solving queries.
>
> I am planning to prepare a check list for my team member for a particular
> task.
>
> I mean once they finish the working on the excel sheet and try to close that,
> a pop up of check list will appear which state the steps to be perform to
> complete the task (max 5 steps).
>
> In order to close the file, user must be forced to mark each task.
>
> Regards
> Hiren
>
> 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
--
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Add a column of matching numbers
Give this alternative a try. It labels all non-empty cells all the way
to the last cell non-empty cell in the column:
Dim aCol As Variant
Dim bCol As Variant
bCol = ActiveSheet.UsedRange.Columns("A")
ReDim aCol(1 To UBound(bCol, 1), 1 To 1)
Dim i As Long
i = 1
Dim irow As Long
For irow = 1 To UBound(bCol, 1)
If bCol(irow, 1) <> "" Then
aCol(irow, 1) = i
i = i + 1
Else
aCol(irow, 1) = ""
End If
Next irow
Columns("A:A").Insert Shift:=xlToRight
Range(Cells(1, 1), Cells(UBound(bCol, 1), 1)) = aCol
It is of course a little less speedy than the routine proposed by
Dave.
On Nov 21, 12:00 am, Dave Bonallack wrote:
> Hi Mike,
> Try this code. It may be quicker. But it won't go to the end of Col B if Col
> B has any blank cells. Let me know if this is a problem.
>
> Sub NumColA()
> Columns("A:A").Insert Shift:=xlToRight
> Range("A1") = 1: Range("A2") = 2
> Range("A1:A2").AutoFill Destination:=Range(Cells(1, 1),
> Cells(Range("B1").End(xlDown).Row, 1)), Type:=xlFillDefault
> End Sub
>
> Regards - Dave.
>
>
>
>
>
> > Date: Fri, 20 Nov 2009 16:28:50 -0800
> > Subject: $$Excel-Macros$$ Add a column of matching numbers
> > From: mwillia...@socal.rr.com
> > To: excel-macros@googlegroups.com
>
> > What I need to do is insert a column at A:A
> > if there is data in B1 number A:1 as 1
> > down one to B2 number A:2 as 2
> > and so on to the end of data in column B
>
> > start with this
>
> > Col A
> > aaa
> > bbb
> > ccc
>
> > end with this
>
> > Col A Col B
> > 1.aaa
> > 2.bbb
> > 3.ccc
> > (dots shown here as spacers)
>
> > The code I wrote below works perfectly, but it is SLOW
> > Does anyone have any suuggestions to speed it up?
> > (10,000+ rows)
>
> > Columns("A:A").Select
> > Selection.Insert Shift:=xlToRight
>
> > myrow = 1
>
> > Do Until IsEmpty(Range("B" & myrow))
>
> > Range("A" & myrow).Select
> > ActiveCell.FormulaR1C1 = myrow
>
> > myrow = myrow + 1
> > Loop
>
> > --
> > ------
> > Some important links for excel users:
> > 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads
> > athttp://www.excelitems.com
> > 2. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 3. Learn VBA Macros athttp://www.vbamacros.blogspot.com
> > 4. Excel Tips and Tricks athttp://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
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
>
> > We reach over 6,500 subscribers worldwide and receive many nice notes about
> > the learning and support from the group. Our goal is to have 10,000
> > subscribers by the end of 2009. Let friends and co-workers know they can
> > subscribe to group athttp://groups.google.com/group/excel-macros/subscribe
>
> _
> Want to know what your boss is paid? Check out The Great Australian Pay Check
> nowhttp://clk.atdmt.com/NMN/go/157639755/direct/01/- Hide quoted text -
>
> - Show quoted text -
--
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Change FROM email address
Sorry I steered you in the wrong direction: there is no From property.
There is however a SentOnBehalfOfName property. You should be able to
assign your team's e-mail address to this property and obtain the
result you are hoping for.
Best wishes,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Nov 20, 2:53 pm, Hanum wrote:
> I domt see .From property defined anywhere. I did try with SendName
> property but the code errors out.
> Any further help is greatly appreciated.
>
> Dim myMail As MailItem
>
> 'All
> 'my
> 'code
>
> With myMail
> .To = "myn...@myadd.com"
> '.From =
> .Subject = "My files"
> .Body = ActiveSheet.Range("A2").Text & vbCrLf
> .Attachments.Add CurrFile
> .Display '.Send
> End With
>
> On Nov 17, 10:16 pm, RolfJ wrote:
>
>
>
> > Did you already try to simply assign your team's groupemailaddress
> > to the .From property of the mail item?
>
> > On Nov 16, 9:01 am, Hanum wrote:
>
> > > I have some VBA code that sends anemailautomatically after attaching
> > > an Excel file.
> > > Theemailgets sent using my corporateemailaccount. Is there a way I
> > > can make the FROMemailmy team's groupemailaddress rather than
> > > mine?- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
--
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Fwd: Urgent Help required
Ooops. I forgot to mention that you need to explicitly reference the
MS Outlook library for the code I posted to be able to run by
selecting Tools | References from the VBE main menu and then select
'Microsoft Outlook 11.0 (or 12.0) Object Library' from the 'Available
References' list.
Best wishes,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Nov 20, 4:01 pm, RolfJ wrote:
> I am not quite clear exactly when you would like an email to be sent.
> Is it when you change a cell in column A ('Subject Line') or when you
> update an email address in column D. Anyhow here is code that you
> could place in the VBA module associated with the worksheet you would
> like to respond to changes by sending email. This code assumes that
> you want email to be sent when you change the subject line, but it
> would be straightforward to change that by modifying the event
> handler. I hope the rest is sufficiently clear.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Row = 1 Then Exit Sub
> If Target.Column > 1 Then Exit Sub
> If Target.Offset(, 3).Value <> "" Then
> Call SendMail(Target.Offset(, 3).Value, Target.Value)
> End If
> End Sub
>
> Sub SendMail(strTo As String, strSubject As String)
>
> Dim objOut As Outlook.Application
> Dim objMail As Outlook.MailItem
> Dim strBody As String
>
> On Error Resume Next
>
> Application.DisplayAlerts = False
>
> Set objOut = New Outlook.Application
> Set objMail = objOut.CreateItem(olMailItem)
>
> strBody = "Hi Team:"
> strBody = strBody & vbCrLf & vbCrLf & "Pls provide the email id
> for the email with above subject line."
> strBody = strBody & vbCrLf & vbCrLf & "Thanks."
>
> With objMail
> .To = strTo
> .Body = strBody
> .Subject = strSubject
> .Send
> End With
>
> Set objOut = Nothing
> Application.DisplayAlerts = True
>
> End Sub
>
> Rolf Jaeger
> SoarentComputinghttp://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
>
> On Nov 18, 8:52 am, hemant shah wrote:
>
>
>
> > Hello Everyone,
>
> > Can you help me with my query. I have updated everything i require in
> > the attached excel.
>
> > Regards,
>
> > Hemant Shah
>
> > Please do not print this email unless it is absolutely necessary.
>
> > The information contained in this electronic message and any
> > attachments to this message are intended for the exclusive use of the
> > addressee(s) and may contain proprietary, confidential or privileged
> > information. If you are not the intended recipient, you should not
> > disseminate, distribute or copy this e-mail. Please notify the sender
> > immediately and destroy all copies of this message and any
> > attachments.
>
> > WARNING: Computer viruses can be transmitted via email. The recipient
> > should check this email and any attachments for the presence of
> > viruses. The company accepts no liability for any damage caused by any
> > virus transmitted by this email.
>
> >www.wipro.com
>
> > Book1.xls
> > 29KViewDownload- Hide quoted text -
>
> - Show quoted text -
--
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Fwd: Urgent Help required
I am not quite clear exactly when you would like an email to be sent.
Is it when you change a cell in column A ('Subject Line') or when you
update an email address in column D. Anyhow here is code that you
could place in the VBA module associated with the worksheet you would
like to respond to changes by sending email. This code assumes that
you want email to be sent when you change the subject line, but it
would be straightforward to change that by modifying the event
handler. I hope the rest is sufficiently clear.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Then Exit Sub
If Target.Column > 1 Then Exit Sub
If Target.Offset(, 3).Value <> "" Then
Call SendMail(Target.Offset(, 3).Value, Target.Value)
End If
End Sub
Sub SendMail(strTo As String, strSubject As String)
Dim objOut As Outlook.Application
Dim objMail As Outlook.MailItem
Dim strBody As String
On Error Resume Next
Application.DisplayAlerts = False
Set objOut = New Outlook.Application
Set objMail = objOut.CreateItem(olMailItem)
strBody = "Hi Team:"
strBody = strBody & vbCrLf & vbCrLf & "Pls provide the email id
for the email with above subject line."
strBody = strBody & vbCrLf & vbCrLf & "Thanks."
With objMail
.To = strTo
.Body = strBody
.Subject = strSubject
.Send
End With
Set objOut = Nothing
Application.DisplayAlerts = True
End Sub
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Nov 18, 8:52 am, hemant shah wrote:
> Hello Everyone,
>
> Can you help me with my query. I have updated everything i require in
> the attached excel.
>
> Regards,
>
> Hemant Shah
>
> Please do not print this email unless it is absolutely necessary.
>
> The information contained in this electronic message and any
> attachments to this message are intended for the exclusive use of the
> addressee(s) and may contain proprietary, confidential or privileged
> information. If you are not the intended recipient, you should not
> disseminate, distribute or copy this e-mail. Please notify the sender
> immediately and destroy all copies of this message and any
> attachments.
>
> WARNING: Computer viruses can be transmitted via email. The recipient
> should check this email and any attachments for the presence of
> viruses. The company accepts no liability for any damage caused by any
> virus transmitted by this email.
>
> www.wipro.com
>
> Book1.xls
> 29KViewDownload
--
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Urgent Need help on formula.
This might work for you. Let's assume that the total numbers of minutes is located in cell A2. The following formulae would calculate the number of whole days, hours of next fractional day and minutes of next fractional hour: Number of days (Cell B2): =INT(A2/60/24) -> 22 for your example Hours of next fractional day (Cell C2): =INT((A2-B2*24*60)/60) -> 9 for your example Minutes of next fractional hour (Cell D2): =A2-B2*24*60-C2*60 -> 49 for your example Hope this helped, Rolf Jaeger SoarentComputing http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm On Nov 19, 8:47 am, vinod rao wrote: > HI Friends, > > I have a question in excel formula, > > I have a number which gives total numbers of minuts, example 32269 and it is > minutes total. Can excel find numbe of days and hours and minutes in that > total. > > This is very urgent. kindly help > Regards, > Vr -- -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Change FROM email address
Did you already try to simply assign your team's group email address to the .From property of the mail item? On Nov 16, 9:01 am, Hanum wrote: > I have some VBA code that sends an email automatically after attaching > an Excel file. > The email gets sent using my corporate email account. Is there a way I > can make the FROM email my team's group email address rather than > mine? -- -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Excel VBA dynamically fill ComboBox?
Assuming that your country column is column A, place the following
code into the VBA section of the form containing your three ComboBoxes
(referred to as cmbBoxCountry, cmbBoxState and cmbBoxProduct) and give
it a try:
Private Sub cmbBoxCountry_Change()
Call UpdateComboBox("B", cmbBoxState, cmbBoxCountry)
End Sub
Private Sub cmbBoxState_Change()
Call UpdateComboBox("C", cmbBoxProduct, cmbBoxState)
End Sub
Private Sub UserForm_Activate()
Call UpdateComboBox("A", cmbBoxCountry)
End Sub
Private Sub UpdateComboBox(sourceCol As String, ByRef c As
MSForms.ComboBox, Optional cRef As MSForms.ComboBox)
c.Clear
Dim r As Range
Set r = Range(Range(sourceCol & 2), Range(sourceCol &
Rows.Count).End(xlUp))
Dim rCell As Range
If cRef Is Nothing Then
For Each rCell In r.Cells
If Not IsInComboBox(rCell.Value, c) Then c.AddItem
(rCell.Value)
Next rCell
Else
For Each rCell In r.Cells
If rCell.Offset(, -1).Value = cRef Then
If Not IsInComboBox(rCell.Value, c) Then c.AddItem
(rCell.Value)
End If
Next rCell
End If
If c.ListCount > 0 Then c.ListIndex = 0
End Sub
Private Function IsInComboBox(s As String, c As MSForms.ComboBox) As
Boolean
Dim i As Integer
For i = 0 To c.ListCount - 1
If c.List(i) = s Then
IsInComboBox = True
Exit Function
End If
Next i
IsInComboBox = False
End Function
Hope this helped,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Nov 8, 7:47 am, Meimei wrote:
> Hello,
>
> I have a question on using VBA to programme several ComboBoxes whose
> items are dynamically filled?
> I have a worksheet containing country names, states, and product, such
> as
> Country State Product
> 1 US MA Banana
> 2 US MA Orange
> 3 US WV Apple
> 4 US WV Apple
> 5 US CA Banana
> 6 Canada Quebec Orange
> 7 Canada Quebec Orange
> 8 Canada Albert Apple
> 9 Canada Albert Banana
>
> I want to the 1st ComboBox to read the list of country names and
> filled by the unique country names, in this, I have two country names,
> US and Canada. After the 1st ComboBox is selected, I want the 2nd
> ComboBox to be dynamically filled with the appropriate state/province
> names, i.e., if the user chooses US, then the 2nd ComboBox only have
> the choices of MA, WV, and CA. Similarly for the 3rd ComboBox, if the
> user chooses US and then WV, the 3rd ComboBox should list Apple.
>
> So I figure that the code should dynamically search for unique country
> names, state names, and product names, and also be able to link the
> appropriate list of states to country, and appropriate list of product
> to state or provinces. But I don't have a clear idea how to do this.
> Can someone help me with some sample codes?
>
> Thanks a lot!
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Store Cell formatting
Please explain why you don't save the worksheet resulting from your calculations in the intrinsic Excel format (i.e. as an Excel workbook). On Nov 8, 12:20 pm, Hemant Hegde wrote: > Hi > > After a lot of coding and lengthy calculations (takes up to 2 minutes) I get > a sheet (actually a report) with lot of numbers and different cell > formatting. > > Now I need to store the cell values together with cell formatting in a > binary file to avoid repeated lengthy calculations. > > The only option I able to think is to store all the formatting values one > after another in the binary file eg. For cell's background colour, I will > have to store the value of cells(1,1).interior.colorindex as a number and > write it to the binary file. While opening the binary file, it has to read > it and apply it back to the cell. > > Any better Idea? > > Im sorry if I failed to explain correctly what i want to do! > Can I get all the formatting of a cell as a single number or a string by any > means? > > Professional programmers help me please > > -- > Hemant Hegde --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re:
Copy the macro listed below into a standard VBA module in your
workbook and give it a try. The macro will parse the data on the
'Data' worksheet and create another worksheet named 'CleanedUpData'.
You can then setup the PivotTable you want on that worksheet.
Sub ExtractData()
Dim strTransactions() As String
Dim shRawData As Worksheet
Set shRawData = Worksheets("Data")
'*
'Add a new worksheet named "CleanedUpData"
'*
Dim shCleanData As Worksheet
On Error Resume Next
Set shCleanData = Worksheets("CleanedUpData")
If shCleanData Is Nothing Then
Worksheets.Add After:=Worksheets("Data")
Set shCleanData = Worksheets(Worksheets.Count)
shCleanData.Name = "CleanedUpData"
End If
On Error GoTo 0
Dim db As Range
Set db = shRawData.UsedRange.Columns("A")
shCleanData.Range("A1") = "Customer Name"
shCleanData.Range("B1") = "Reference"
shCleanData.Range("C1") = "Amount"
shRawData.Activate
Dim iTrans As Integer
Dim iRow As Integer
Dim bDone As Boolean
Do
ReDim Preserve strTransactions(iTrans)
Do
strTransactions(iTrans) = strTransactions(iTrans) & " " &
Range("A2").Offset(iRow).Value
iRow = iRow + 1
If Range("A2").Offset(iRow).Value = "required" Then
iRow = iRow + 1
'Check is last 'required'
If Range(Range("A2").Offset(iRow), Cells(Rows.Count,
1).End(xlUp)).Find("required") Is Nothing Then
bDone = True
End If
Exit Do
End If
Loop
If bDone Or Range("A2").Offset(iRow).Value = "" Then Exit Do
iTrans = iTrans + 1
Loop
For iTrans = 0 To UBound(strTransactions)
shCleanData.Range("B1").Offset(iTrans + 1).Formula = "'" &
RefNumber(strTransactions(iTrans))
Dim customerName As String
shCleanData.Range("C1").Offset(iTrans + 1) =
ExtractTransactionAmountAndName(strTransactions(iTrans), customerName)
shCleanData.Range("A1").Offset(iTrans + 1).Value =
customerName
Next iTrans
End Sub
Function RefNumber(s As String) As String
Dim refNo As String
Dim strTarget As String
If InStr(s, "Transfer") > 0 Then
strTarget = "Transfer"
ElseIf InStr(s, "Check") > 0 Then
strTarget = "Check"
End If
refNo = Mid(s, InStr(s, strTarget) + Len(strTarget) + 1)
refNo = Left(refNo, InStr(refNo, " ") - 1)
RefNumber = refNo
End Function
Function ExtractTransactionAmountAndName(s As String, ByRef n As
String) As String
Dim a As String
a = Left(s, InStr(s, "INR") - 2)
a = Mid(a, InStrRev(a, " ") + 1)
n = Trim(Mid(s, 1, InStr(s, a) - 2))
ExtractTransactionAmountAndName = a
End Function
Please contact me if you are not familiar with macros.
Hope this helped,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Nov 1, 1:50 am, karthikeyan sankaran
wrote:
> I have download this data from PDF.Now I want to create pivotTable through
> this data.
>
> Book1.xls
> 76KViewDownload
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Help for formula
Not clear what you mean. Please provide an example. On Nov 2, 1:00 am, Nagendra Modupalli wrote: > Hi Team, > > Is there any formula to give cells continuous number for filter one.Thanks > in advance. > > -- > Thanks&Regards > Nagendra.M > > "Beautiful photos are developed by negatives in a dark room, so if U see > darkness in Ur life believe that God is making a beautiful future for U !!! --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Extracting Specific string and corresponding and so on.
I am not sure what you mean by fetch, but the easiest way to obtain a
list of just the items that contain "PIPE" or "ELL" would be the
following:
Step 1: Enter the following formula into Cell E4
=IF(ISNUMBER(SEARCH("PIPE",A4)),"PIPE",IF(ISNUMBER(SEARCH
("ELL",A4)),"ELL",""))
Step 2: Copy the formula to the cells below cell E4
Step 3: Select the range A4:E13
Step 4: From the Main Menu select Data | Filter | Autofilter
Step 5: Click on the down arrow in cell E3 and select either PIPE or
ELL
Hope this helped,
Rolf
On Oct 31, 11:49 pm, Aligahk06 wrote:
> Dear All,
>
> Greetings of the day!
> I have an excel file with bulk of contents in following format.
> Col A contains description, Colc Contains (Size inches to the
> corresponding description)
> Col D contains the Qty. to the corresponding Description.
>
> I want to fetch form ColA i.e( Only Pipe and ELL as a text from entire
> string.)
> i.e if pipe is available in string then fetch it or if ELL is
> available in same string then fetch it.
> But in each string only either pipe will bet there or ELL will be
> there or nothing.
> Possibilty of finding the string is One( i.e either pipe or ELL not
> both in same string.)
>
> From column C the corersponding inches i want to fetch.
> From column D the corersponding Qty. i want to fetch.
>
> From All three columns appear contents respectively in ColE, COlF and Col G.
>
> Basically this is for compilation of requisite data only.
> Note:- The excel file is converted from Pdf file.
>
> Please assist with any formula or macro or the logic you can share.
> Rgds,
> Aligahk06
>
> Query.xlsx
> 13KViewDownload
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Macro for providing Multiple Passward in a single worksheet.
Place this code in the VBA module for the worksheet you would like to
restrict and give it a try (needless to say: for this password
strategy to be bullet-proof you would have to password-protect the VBA
project, so that none uf your users can have access to the passwords):
Const RANGE1_PWD = "abc"
Const RANGE2_PWD = "def"
Const RANGE3_PWD = "xyz"
Const RESTRICTED_RANGE1 = "A1:K15"
Const RESTRICTED_RANGE2 = "C3:D6"
Const RESTRICTED_RANGE3 = "D5:E8"
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static b_UserPassedAuthentication(2) As Boolean
If Not Intersect(Target, Range(RESTRICTED_RANGE1)) Is Nothing Then
If Not Intersect(Target, Range(RESTRICTED_RANGE2)) Is Nothing
Then
If b_UserPassedAuthentication(1) Then Exit Sub
b_UserPassedAuthentication(1) = UserAuthenticatedForRange
(Range(RESTRICTED_RANGE2), RANGE2_PWD)
ElseIf Not Intersect(Target, Range(RESTRICTED_RANGE3)) Is
Nothing Then
If b_UserPassedAuthentication(2) Then Exit Sub
b_UserPassedAuthentication(2) = UserAuthenticatedForRange
(Range(RESTRICTED_RANGE3), RANGE3_PWD)
Else
If b_UserPassedAuthentication(0) Then Exit Sub
b_UserPassedAuthentication(0) = UserAuthenticatedForRange
(Range(RESTRICTED_RANGE1), RANGE1_PWD)
End If
ElseIf Not Intersect(Target, Range(RESTRICTED_RANGE2)) Is Nothing
Then
If Not Intersect(Target, Range(RESTRICTED_RANGE1)) Is Nothing
Then
If b_UserPassedAuthentication(0) Then Exit Sub
b_UserPassedAuthentication(0) = UserAuthenticatedForRange
(Range(RESTRICTED_RANGE1), RANGE1_PWD)
ElseIf Not Intersect(Target, Range(RESTRICTED_RANGE3)) Is
Nothing Then
If b_UserPassedAuthentication(2) Then Exit Sub
b_UserPassedAuthentication(2) = UserAuthenticatedForRange
(Range(RESTRICTED_RANGE3), RANGE3_PWD)
Else
If b_UserPassedAuthentication(1) Then Exit Sub
b_UserPassedAuthentication(1) = UserAuthenticatedForRange
(Range(RESTRICTED_RANGE2), RANGE2_PWD)
End If
ElseIf Not Intersect(Target, Range(RESTRICTED_RANGE3)) Is Nothing
Then
If Not Intersect(Target, Range(RESTRICTED_RANGE1)) Is Nothing
Then
If b_UserPassedAuthentication(0) Then Exit Sub
b_UserPassedAuthentication(0) = UserAuthenticatedForRange
(Range(RESTRICTED_RANGE1), RANGE1_PWD)
ElseIf Not Intersect(Target, Range(RESTRICTED_RANGE2)) Is
Nothing Then
If b_UserPassedAuthentication(1) Then Exit Sub
b_UserPassedAuthentication(1) = UserAuthenticatedForRange
(Range(RESTRICTED_RANGE2), RANGE2_PWD)
Else
If b_UserPassedAuthentication(2) Then Exit Sub
b_UserPassedAuthentication(2) = UserAuthenticatedForRange
(Range(RESTRICTED_RANGE3), RANGE3_PWD)
End If
End If
End Sub
Function UserAuthenticatedForRange(r As Range, rPwd As String) As
Boolean
If InputBox("Enter the password required to edit cells in range "
& r.Address) = rPwd Then
UserAuthenticatedForRange = True
Else
MsgBox "Incorrect password!", vbExclamation
UserAuthenticatedForRange = False
End If
End Function
This may not be the most elegant code, but it seems to do the job.
Hope this helped,
Rolf
On Oct 30, 3:45 am, DEBASHIS BANERJEE wrote:
> Dear Friends,
>
> I need help in writing macro to provide multiple protection of a work
> sheet .
>
> ie two or three passward can be provided in a single work sheet and
> every user would be restricted to edit the area where he is not
> permitted to edit .
>
> kindly help .
>
> thanks and regards .
>
> debashis.
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: For Excel Book
John Walkenbach's "Excel VBA Programming for Dummies' (this is not intended as an insult. it's REALLY a great book to get started): http://spreadsheetpage.com/ Hope this helped, Rolf On Oct 29, 10:42 pm, khalid khan wrote: > Dear friends > > Please suggest me book for Excel and VBA coding > > thank you in advance > > from > Khalid khan --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Macro required for consolidation
Place this code into a standard VBA module of your workbook XYZ.xls
and give it a try (you will still need to add a button that invokes
the macro, and I would suggest to let the user decide where to save it
by using the standard File | Save As command) :
Sub ConsolidateFilesIntoMasterWorkbook()
Dim folderName As String
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please selected the folder containing the files you
would like to consolidate."
.Show
If .SelectedItems.Count = 0 Then
Exit Sub
Else
folderName = .SelectedItems(1)
End If
End With
Dim masterWb As Workbook, sourceWb As Workbook
Set masterWb = ActiveWorkbook
Dim fs As Object
Dim objFolder As Object
Dim wbFile As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Set objFolder = fs.GetFolder(folderName)
Application.ScreenUpdating = False
Dim sh As Worksheet
Dim rSource As Range, rTarget As Range
For Each wbFile In objFolder.Files
Set sourceWb = Workbooks.Open(wbFile.Path)
For Each sh In sourceWb.Worksheets
Set rSource = sh.UsedRange.Rows("2:" &
sh.UsedRange.Rows.Count)
Set rTarget = masterWb.Sheets(1).Cells(Rows.Count,
1).End(xlUp).Offset(1)
rSource.Copy Destination:=rTarget
Next sh
sourceWb.Close (False)
Next wbFile
Application.ScreenUpdating = True
End Sub
It is IMPORTANT that only the files you want to consolidate are in the
selected folder, and no other files!!!
Hope this helped,
Rolf
On Oct 30, 4:33 am, nitin gupta wrote:
> Hi All
>
> I am an excel user with negligible knowledge if VBA codes.
> I want to know if following is possible by any generic macro:
>
> -->My objective is to consolidate few number of files (say 20 files) where
> data is there in just two fields i.e "Country" and "Sales in USD". To
> clarify more: There are 20 files with 2 sheets each and each sheet have 2
> headings "Country" and "Sales in USD" with data in it . So in nutshell I
> have 40 sheets in 20 files.
> Now I want alll these data to come in One file (say XYZ.xls) one-by-one, no
> futher formatting is required
>
> ---> Above macro should be in a excel file which should have buttons to ask
> for Location of Dumps(i.e location of 20 files) and one more button to ask
> for Location of new file (XYZ). And this macro can be used in any machine/PC
>
> Any help on this is highly appreciated
>
> Regards
> Nitin
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Separating 1 Worksheet into Multiple Tabs
In principle that should be possible, but without knowing how the main worksheet is structured it is difficult to make any specific suggestions. It would help if you were to post a sample workbook or described the structure in some other way. On Oct 30, 7:13 am, Leo wrote: > Hello! noob here :) > > I have a project where I am trying to separate out a huge spreadsheet > into multiple tabs. > So, the main worksheet has unique Profile Numbers, and then data > associated with each profile #. > Is there an easy way where each new profile # is split into a new tab? > > Please advise & do let me know if I can give any more info that will > be helpful. > > Thx! --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: How do I create a drop down list to select which macro to run
Let's assume that your 3 macros are located in a standard VBA module
and look like this:
Sub Macro1()
MsgBox ("Macro1")
End Sub
Sub Macro2()
MsgBox ("Macro2")
End Sub
Sub Macro3()
MsgBox ("Macro3")
End Sub
Now insert a UserForm in your VBA project then drop a ListBox (named
ListBox1) onto it. In the code section for this UserForm enter the
following code:
Private Sub ListBox1_Click()
Run (ListBox1.Value)
End Sub
Private Sub UserForm_Initialize()
ListBox1.AddItem ("Macro1")
ListBox1.AddItem ("Macro2")
ListBox1.AddItem ("Macro3")
End Sub
Hope this helped,
Rolf
On Oct 30, 11:24 am, CPGH wrote:
> I have 3 different macros that work, and as opposed to giving them
> each their own ctrl+ key combinations to run, I'd like to create a
> kind of drop down msgbox or something like that that would list the
> different options available to the user so that they can select the
> appropriate macro to run.
>
> I'm assuming it needs some kind of listbox or combobox, but I have no
> idea how to do this.
>
> Any help would be greatly appreciated.
>
> Thanks,
> Chris
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: macro sometimes working sometimes not!
I was able to reproduce your problem and can offer a solution, however
regrettably without being able to explain why it works. Replace the
section of your code that deals with the unprotecting, copying and
pasting, and re-protecting:
Worksheets("Computation").Range("compute").Copy
Sheets("PayDbase").Select
ActiveSheet.Unprotect Password:="tkh701"
Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial paste:=xlPasteValues,
Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False ' <<< the code shows error here
says
" PasteSpecial Range method class failed!"
Application.CutCopyMode = False
MsgBox "Posting Payroll Successful, you may print payslips
now!"
ActiveSheet.Protect Password:="tkh701"
with this code:
Sheets("PayDbase").Unprotect Password:="tkh701"
Dim targetCell As Range
Set targetCell = Worksheets("PayDbase").Range("A" &
Rows.Count).End(xlUp).Offset(1)
Dim sourceRange As Range
Set sourceRange = Worksheets("Computation").Range
("Compute")
sourceRange.Copy
targetCell.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, _
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
MsgBox "Posting Payroll Successful, you may print payslips
now!"
Sheets("PayDbase").Protect Password:="tkh701"
and give it another try.
Hope this helped,
Rolf
On Oct 27, 10:39 pm, OSAVentures Calamba wrote:
> Hello Excel Gurus, Im not sure why this macro works and sometimes not.
> Could you please comment.
>
> Sub Find_First_before_ Copy_Paste()
> With Application
> .ScreenUpdating = False
> .DisplayAlerts = False
> End With
> Dim FindString As Integer
> Dim Rng As Range
> FindString = Sheets("Computation").Range("F7").Value
> If Trim(FindString) <> "" Then
> With Sheets("PayDbase").Range("E:E")
> Set Rng = .find(What:=FindString, _
> After:=.Cells(.Cells.Count), _
> LookIn:=xlValues, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> If Not Rng Is Nothing Then
> MsgBox "THE PAYROLL PERIOD YOU ARE TYRING TO POST IS ALREADY
> CLOSED! "
> Else
> 'paste the entire ranged named to dbase worksheet
> ActiveSheet.Calculate
> Worksheets("Computation").Range("compute").Copy
> Sheets("PayDbase").Select
> ActiveSheet.Unprotect Password:="tkh701"
> Range("A65536").Select
> Selection.End(xlUp).Select
> ActiveCell.Offset(1, 0).Select
> Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False ' <<< the code shows error here says
> " PasteSpecial Range method class failed!"
> Application.CutCopyMode = False
> MsgBox "Posting Payroll Successful, you may print payslips now!"
> ActiveSheet.Protect Password:="tkh701"
> 'MsgBox "Nothing found"
> End If
> End With
> End If
> End Sub
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: copy paste while copycell is not empty
You are welcome!
On Oct 28, 7:06 pm, OSAVentures Calamba wrote:
> @Rolf ... Thank you very much... You were a BIG BROTHER to me, teaching me
> in detailed patience how to make my first steps in walking to macros. I
> tested my codes with ALL your suggestions and it went fine. May you be
> blessed with good health and long life.
>
>
>
> On Wed, Oct 28, 2009 at 11:20 PM, RolfJ wrote:
>
> > Please find below a revised and annotated version of your Post_to_Dbase
> > () routine. I corrected what seemed to me a couple of logic errors
> > (which I explained in my annotations):
>
> > Sub Post_to_Dbase()
>
> > 'Suppressing screen updating and Excel alerts
> > With Application
> > .ScreenUpdating = False
> > .DisplayAlerts = False
> > End With
>
> > 'Declaring variables
> > 'Dim PayPeriod As Integer
>
> > 'It really doesn't matter whether this is declared as String or
> > Integer
> > '(VBA is very forgiving that way, for the most part)
> > 'String typically happens to be safer.
> > 'It does however matter when you later in your code make the
> > 'assumption that PayPeriod is a String variable after all,
> > 'as you do in your statement 'If PayPeriod <> "" Then'.
> > 'Therefore I changed this declaration to:
> > Dim PayPeriod As String
> > Dim Rng As Range
>
> > 'In case of error what ever reason
> > On Error GoTo Err_Execute
> > 'This is a good idea in general,
> > 'but can be improved on by displaying the error code.
> > 'However I recommend to not use it while you are debugging your
> > code,
> > 'because you will never find out where your code went wrong
> > 'unless you are stepping through it (using the F8 key) or are
> > using
> > 'breakpoints.
>
> > 'Retrieve date value to search for
>
> > 'PayPeriod = Sheets("Computation").Range("B7").Value
>
> > 'ARE YOU SURE THIS IS WHAT YOU WANT?
> > 'This statement would assign the IDNo not the Period to PayPeriod.
> > 'In addition your original code points to the cell in the second
> > row
> > 'of your Compute range not the first.
> > 'I changed it to
> > '(slightly modified from my original code
> > ' using the column letter instead of the column number):
>
> > PayPeriod = Worksheets("Computation").Range("Compute").Cells(1,
> > PERIOD_COLUMN).Value
>
> > 'Comment: using the Worksheets collection instead of the Sheets
> > collection is
> > ' generally better practice (although it requires a bit
> > more typing).
> > ' The reason: the Worksheets collection really contains
> > only the
> > ' worksheets in your workbook. The Sheets collection
> > also includes other
> > ' sheets, e.g. Chart sheets. That can lead to unexpected
> > results.
> > ' This is not a problem in your particular case. I just
> > thought you should
> > ' be aware of the distinction.
>
> > 'Select worksheet to paste append from if the variable is exiting
> > If PayPeriod <> "" Then
> > 'With Sheets("PayDbase").Range("B:B")
> > 'ARE YOU SURE THIS IS WHAT YOU WANT?
> > 'This statement would look for the period in the IDNo
> > column
> > 'and of course not find it.
> > 'I changed it to:
> > With Sheets("PayDbase").Range("E:E")
> > Set Rng = .Find(What:=PayPeriod, _
> > After:=.Cells(.Cells.Count), _
> > LookIn:=xlValues, _
> > LookAt:=xlWhole, _
> > SearchOrder:=xlByRows, _
> > SearchDirection:=xlNext, _
> > MatchCase:=False)
> > 'If Not Rng Is Nothing Then
> > 'WATCH OUT: I thought you only wanted to copy the data if
> > there was no
> > 'data for this period present.
> > 'That is the case when Rng comes up as nothing after you
> > look for the
> > 'specific period.
> > 'Hence I changed this to:
> > If Rng Is Nothing Then
> > 'Paste the entire range
$$Excel-Macros$$ Re: copy paste while copycell is not empty
Ooops! I forgot to mention that I introduced another constant for the
'Period' column (PERIOD_COLUMN) in your Computation worksheet. Please
add this declaration at the top of your VBA module:
Const PERIOD_COLUMN As String = "E"
Doing this is not really required, but it typically simplifies code
management when later decide to insert a column in your worksheet.
Rather than having to thieve through your macros to find out where you
used "E", you could just change the assignment of the constant in one
place. Just a suggestion.
You will of course notice that I didn't follow my own guideline when
referring to column E in your 'PayDbase' worksheet...
Best wishes,
Rolf
On Oct 27, 8:54 pm, OSAVentures Calamba wrote:
> @Rolf Thank you very much for your help. You are sent by angels.
> I read your code and tried to self understand the logic because im just a
> beginner. I made some remarks. I also pasted here my revised code which i
> adopted from many sites to suit my actual situation. I rename
> Post_to_summary TO Post_to_Dbase ( second before your email came in). Is it
> better if you can comment your logic flow for beginners like me.
>
> Sub Post_to_Dbase()
> 'suppressing screen
> With Application
> .ScreenUpdating = False
> .DisplayAlerts = False
> End With
> 'declaring variable
> Dim PayPeriod As Integer
> Dim Rng As Range
> 'in case of error what ever reason
> On Error GoTo Err_Execute
> 'Retrieve date value to search for
> PayPeriod = Sheets("Computation").Range("B7").Value
> 'select worksheet to paste append from if the variable is exiting
> If PayPeriod <> "" Then
> With Sheets("PayDbase").Range("B:B")
> Set Rng = .Find(What:=PayPeriod, _
> After:=.Cells(.Cells.Count), _
> LookIn:=xlValues, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> If Not Rng Is Nothing Then
> 'paste the entire ranged named to dbase worksheet
> ActiveSheet.Calculate
> Worksheets("Computation").Range("compute").Copy
> Sheets("PayDbase").Select
> Range("A65536").Select
> Selection.End(xlUp).Select
> 'one empty row down to paste the values
> ActiveCell(1, 0).Select
> Worksheets("PayDbase").Range("a6").PasteSpecial
> Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> MsgBox "Payroll closed and posted, you may print payslips now!"
> Else
> MsgBox "Payroll Period is already posted!"
> End If
> End With
> With Application
> .ScreenUpdating = True
> .DisplayAlerts = True
> End With
> Err_Execute:
> MsgBox "An error occurred."
> End Sub
>
>
>
>
>
> On Wed, Oct 28, 2009 at 4:00 AM, RolfJ wrote:
>
> > I took another look at your workbook and think I have a reasonable
> > solution for your Post_to_Summary routine:
>
> > Sub Post_to_Summary()
>
> > With Application
> > .ScreenUpdating = False
> > .DisplayAlerts = False
> > End With
>
> > Dim nPeriod As String ' my period is an integer coz of some math
> > operations somewhere
> > nPeriod = Range("Compute").Cells(1, 5).Value
>
> > Dim targetColumn As Range
> > 'this code pasting the range "compute" already? ' compute is dynamic
> > named ranged already?"*
> > *
>
> * *
>
>
>
>
>
> > Set targetColumn = Worksheets("DBase").Columns("E")
> > Dim targetCell As Range
>
> > Application.CutCopyMode = False
>
> > Set targetCell = targetColumn.Find(What:=nPeriod,
> > After:=targetColumn.Cells(1, 1), LookIn:=xlFormulas, LookAt _
> > :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
> > MatchCase:= _
> > False, SearchFormat:=False)
>
> > If targetCell Is Nothing Then
> > Set targetCell = Worksheets("DBase").Range("E" &
> > Rows.Count).End(xlUp).Offset(1, -4)
> > Dim sourceRange As Range
> > Set sourceRange = Worksheets("Computation").Range("Compute")
> >
$$Excel-Macros$$ Re: copy paste while copycell is not empty
7; Worksheets("PayDbase").Range("a6").PasteSpecial _
' Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, _
' Transpose:=False
' Application.CutCopyMode = False
'Again declaring and setting the sourceRange adn
targetCell this way
'simplifies the code.
'Notice that I am not using the Select method at all.
Dim targetCell As Range
Set targetCell = Worksheets("PayDbase").Range("A" &
Rows.Count).End(xlUp).Offset(1)
Dim sourceRange As Range
Set sourceRange = Worksheets("Computation").Range
("Compute")
sourceRange.Copy
targetCell.PasteSpecial Paste:=xlPasteValues
MsgBox "Payroll closed and posted, you may print
payslips now!"
Else
MsgBox "Payroll Period is already posted!"
End If
End With
End If
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
Err_Execute:
MsgBox "An error occurred." & vbCr & vbCr & "Error: " & Error$
End Sub
Hope this helped,
Rolf
On Oct 27, 8:54 pm, OSAVentures Calamba wrote:
> @Rolf Thank you very much for your help. You are sent by angels.
> I read your code and tried to self understand the logic because im just a
> beginner. I made some remarks. I also pasted here my revised code which i
> adopted from many sites to suit my actual situation. I rename
> Post_to_summary TO Post_to_Dbase ( second before your email came in). Is it
> better if you can comment your logic flow for beginners like me.
>
> Sub Post_to_Dbase()
> 'suppressing screen
> With Application
> .ScreenUpdating = False
> .DisplayAlerts = False
> End With
> 'declaring variable
> Dim PayPeriod As Integer
> Dim Rng As Range
> 'in case of error what ever reason
> On Error GoTo Err_Execute
> 'Retrieve date value to search for
> PayPeriod = Sheets("Computation").Range("B7").Value
> 'select worksheet to paste append from if the variable is exiting
> If PayPeriod <> "" Then
> With Sheets("PayDbase").Range("B:B")
> Set Rng = .Find(What:=PayPeriod, _
> After:=.Cells(.Cells.Count), _
> LookIn:=xlValues, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> If Not Rng Is Nothing Then
> 'paste the entire ranged named to dbase worksheet
> ActiveSheet.Calculate
> Worksheets("Computation").Range("compute").Copy
> Sheets("PayDbase").Select
> Range("A65536").Select
> Selection.End(xlUp).Select
> 'one empty row down to paste the values
> ActiveCell(1, 0).Select
> Worksheets("PayDbase").Range("a6").PasteSpecial
> Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> MsgBox "Payroll closed and posted, you may print payslips now!"
> Else
> MsgBox "Payroll Period is already posted!"
> End If
> End With
> With Application
> .ScreenUpdating = True
> .DisplayAlerts = True
> End With
> Err_Execute:
> MsgBox "An error occurred."
> End Sub
>
>
>
>
>
> On Wed, Oct 28, 2009 at 4:00 AM, RolfJ wrote:
>
> > I took another look at your workbook and think I have a reasonable
> > solution for your Post_to_Summary routine:
>
> > Sub Post_to_Summary()
>
> > With Application
> > .ScreenUpdating = False
> > .DisplayAlerts = False
> > End With
>
> > Dim nPeriod As String ' my period is an integer coz of some math
> > operations somewhere
> > nPeriod = Range("Compute").Cells(1, 5).Value
>
> > Dim targetColumn As Range
> > 'this code pasting the range "compute" already? ' compute is dynamic
> > named ranged already?"*
> > *
>
> * *
>
>
>
>
>
> > Set targetColumn = Worksheets("DBase").Columns("E")
> > Dim targetCell As Range
>
> > Application.CutCopyMode = False
>
&
$$Excel-Macros$$ Re: Conditional Formating of more than one cell
What do you mean by 'Any color?'
On Oct 27, 6:01 am, Bajji wrote:
> Hi Rolf,
>
> Let me put an example:
>
> 1. Assume sheet1 has some information from Cells ranging A1 : G20
> 2. When I run the macro, it should pop user form with 2 input fields
> asking for cell range (example A1 and C6) to color
> 3. Based on the above cell ranges A1:C6, the macro should color(Any
> color) the cells.
>
> Hope you got my question.
>
> Thanks
>
> On Oct 27, 2:03 am, RolfJ wrote:
>
>
>
> > I am sorry, but I don't understand what you mean. Please try again.
>
> > On Oct 24, 5:08 am, Bajji wrote:
>
> > > Hi Rolf,
> > > I need similar help, little different way
>
> > > When I run the Macro, user form should pop up allowing to put the
> > > array (a1: j10) details to color the rows
>
> > > Regards,
> > > Sri
>
> > > On Oct 24, 4:53 am, RolfJ wrote:
>
> > > > I can't figure a way to do this by way of conditional formating, but I
> > > > can offer some VBA code that would do the trick. Place the following
> > > > code into the a standard VBA module of your workbook:
>
> > > > Public Sub ColorTable()
> > > > Dim rRow As Range
> > > > For Each rRow In ActiveSheet.UsedRange.Rows
> > > > If rRow.Row >= 4 Then
> > > > Select Case rRow.Cells(1, "M").Value
> > > > Case "Green"
> > > > Call ColorCells(rRow.Row, vbGreen)
> > > > Case "Orange"
> > > > Call ColorCells(rRow.Row, RGB(255, 140, 0))
> > > > Case "Red"
> > > > Call ColorCells(rRow.Row, vbRed)
> > > > End Select
> > > > End If
> > > > Next rRow
> > > > End Sub
>
> > > > Public Sub ColorCells(iRow As Long, c As Long)
> > > > Dim r As Range
> > > > Set r = Rows(iRow)
> > > > Dim col As Integer
> > > > For col = Asc("A") To Asc("L")
> > > > r.Cells(1, Chr(col)).Interior.Color = c
> > > > Next col
> > > > End Sub
>
> > > > Place the following code into the VBA module associated with the
> > > > worksheet containing the table:
>
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > If Target.Column <> Columns("M").Column Then Exit Sub
> > > > Select Case Target.Value
> > > > Case "Green"
> > > > Call ColorCells(Target.Row, vbGreen)
> > > > Case "Orange"
> > > > Call ColorCells(Target.Row, RGB(255, 140, 0))
> > > > Case "Red"
> > > > Call ColorCells(Target.Row, vbRed)
> > > > End Select
> > > > End Sub
>
> > > > And finally place this code into the ThisWorkbook VBA module:
>
> > > > Private Sub Workbook_Open()
> > > > ColorTable
> > > > End Sub
>
> > > > That should do it.
>
> > > > Hope this helped,
> > > > Rolf
>
> > > > On Oct 22, 10:18 pm, ASHUTOSH AGARWAL wrote:
>
> > > > > Dear All,
>
> > > > > In the attached file, I want to format cells (a4 to l14) in a manner
> > > > > where row colour would be same as appearing in coloumn M. In other
> > > > > words, in row number 5, I want cells from A to L to be fileld in Green
> > > > > color, in row 6 in red color, etc.
>
> > > > > Is it possible to do so using conditional formating?
>
> > > > > Thanks in advance,
> > > > > Ashutosh
>
> > > > > Sample.xls
> > > > > 42KViewDownload- Hide quoted text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: copy paste while copycell is not empty
> 'ActiveCell.Offset(1, 0).Select
> Sheets("PAYSLIP").Select
> Range("D4").Select
> Selection.PasteSpecial Paste:=xlPasteValues
> Application.CutCopyMode = False
> Do Until IsEmpty(ActiveCell)
> Sheets("Computation").Select
> ActiveCell.Offset(1, 0).Select
> Selection.Copy
> Sheets("PAYSLIP").Select
> Range("D41").Select
> Selection.PasteSpecial Paste:=xlPasteValues
> Application.CutCopyMode = False
> 'Print_All_Pay
> Range("B1:N68").Select
> Selection.PrintOut Copies:=1
> 'Print_All_Pay
> Sheets("Computation").Select
> ActiveCell.Offset(1, 0).Select
> Selection.Copy
> Sheets("PAYSLIP").Select
> Range("D4").Select
> Selection.PasteSpecial Paste:=xlPasteValues
> Application.CutCopyMode = False
> With Selection
> .HorizontalAlignment = xlGeneral
> .VerticalAlignment = xlBottom
> End With
> Loop
> With Application
> .ScreenUpdating = True
> .DisplayAlerts = True
> End With
> End If
> End Sub
>
>
>
> On Tue, Oct 27, 2009 at 12:15 PM, RolfJ wrote:
>
> > I am having difficulties following what you would like to do. In
> > particular it is not clear to me what you mean when you say 'it
> > stops'. Also, what to you mean to indicate with the -<<<< debugs here
> > flag?
>
> > Please post or e-mail example worksheets and I'll take a closer look.
>
> > On Oct 26, 6:28 pm, OSAVentures Calamba wrote:
> > > Dear Macro Experts,
>
> > > I'm just starting to write a macro. What I'm trying to do is I want to
> > copy
> > > a value from 'computation' sheet.column B and posting it as values in
> > 'Slip'
> > > range "D4" and do that while sheet.columnB is not empty. Below is the
> > code
> > > i made but stops.
>
> > > Sub copyIDtopay()
> > > With Application
> > > .ScreenUpdating = False
> > > .DisplayAlerts = False
> > > End With
> > > ' Select cell b7, first line of data computation sheet.
> > > Do Until IsEmpty(ActiveCell)
> > > Worksheets("Computation").Range("B7").Copy -<<<< > > Worksheets("SLIP").Range("D4").PasteSpecial Paste:=xlPasteValues
> > > Sheets("Computation").Select
> > > ActiveCell.Offset(1, 0).Select
> > > Selection.Copy
> > > Sheets("SLIP").Select
> > > Range("D41").Select
> > > ActiveSheet.Paste
> > > Application.CutCopyMode = False
> > > Loop
> > > .ScreenUpdating = False
> > > .DisplayAlerts = False
>
> > > End Sub
>
> > > Please help me.
>
>
>
> Ptemplate.xls
> 102KViewDownload- Hide quoted text -
>
> - Show quoted text -
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Conditional Formating of more than one cell
I am sorry, but I am still not quite clear. What do you now mean by
'any color'?
On Oct 27, 6:01 am, Bajji wrote:
> Hi Rolf,
>
> Let me put an example:
>
> 1. Assume sheet1 has some information from Cells ranging A1 : G20
> 2. When I run the macro, it should pop user form with 2 input fields
> asking for cell range (example A1 and C6) to color
> 3. Based on the above cell ranges A1:C6, the macro should color(Any
> color) the cells.
>
> Hope you got my question.
>
> Thanks
>
> On Oct 27, 2:03 am, RolfJ wrote:
>
>
>
> > I am sorry, but I don't understand what you mean. Please try again.
>
> > On Oct 24, 5:08 am, Bajji wrote:
>
> > > Hi Rolf,
> > > I need similar help, little different way
>
> > > When I run the Macro, user form should pop up allowing to put the
> > > array (a1: j10) details to color the rows
>
> > > Regards,
> > > Sri
>
> > > On Oct 24, 4:53 am, RolfJ wrote:
>
> > > > I can't figure a way to do this by way of conditional formating, but I
> > > > can offer some VBA code that would do the trick. Place the following
> > > > code into the a standard VBA module of your workbook:
>
> > > > Public Sub ColorTable()
> > > > Dim rRow As Range
> > > > For Each rRow In ActiveSheet.UsedRange.Rows
> > > > If rRow.Row >= 4 Then
> > > > Select Case rRow.Cells(1, "M").Value
> > > > Case "Green"
> > > > Call ColorCells(rRow.Row, vbGreen)
> > > > Case "Orange"
> > > > Call ColorCells(rRow.Row, RGB(255, 140, 0))
> > > > Case "Red"
> > > > Call ColorCells(rRow.Row, vbRed)
> > > > End Select
> > > > End If
> > > > Next rRow
> > > > End Sub
>
> > > > Public Sub ColorCells(iRow As Long, c As Long)
> > > > Dim r As Range
> > > > Set r = Rows(iRow)
> > > > Dim col As Integer
> > > > For col = Asc("A") To Asc("L")
> > > > r.Cells(1, Chr(col)).Interior.Color = c
> > > > Next col
> > > > End Sub
>
> > > > Place the following code into the VBA module associated with the
> > > > worksheet containing the table:
>
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > If Target.Column <> Columns("M").Column Then Exit Sub
> > > > Select Case Target.Value
> > > > Case "Green"
> > > > Call ColorCells(Target.Row, vbGreen)
> > > > Case "Orange"
> > > > Call ColorCells(Target.Row, RGB(255, 140, 0))
> > > > Case "Red"
> > > > Call ColorCells(Target.Row, vbRed)
> > > > End Select
> > > > End Sub
>
> > > > And finally place this code into the ThisWorkbook VBA module:
>
> > > > Private Sub Workbook_Open()
> > > > ColorTable
> > > > End Sub
>
> > > > That should do it.
>
> > > > Hope this helped,
> > > > Rolf
>
> > > > On Oct 22, 10:18 pm, ASHUTOSH AGARWAL wrote:
>
> > > > > Dear All,
>
> > > > > In the attached file, I want to format cells (a4 to l14) in a manner
> > > > > where row colour would be same as appearing in coloumn M. In other
> > > > > words, in row number 5, I want cells from A to L to be fileld in Green
> > > > > color, in row 6 in red color, etc.
>
> > > > > Is it possible to do so using conditional formating?
>
> > > > > Thanks in advance,
> > > > > Ashutosh
>
> > > > > Sample.xls
> > > > > 42KViewDownload- Hide quoted text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Plz see the attached sheet solv it
Place this code into the VBA module associated with worksheet Sheet1
of your workbook and give it a try:
Private Sub GO_Click()
TransferClaimData
ClearForm
End Sub
Sub TransferClaimData()
Dim r As Range
Set r = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset
(1)
If r.Row = 2 Then
r.Value = 1
Else
r.Value = r.Offset(-1).Value + 1
End If
r.Offset(0, 1).Value = Sheets("Sheet1").Range("C4").Value
r.Offset(0, 2).Value = Sheets("Sheet1").Range("C5").Value
r.Offset(0, 3).Value = Sheets("Sheet1").Range("C6").Value
r.Offset(0, 4).Value = Sheets("Sheet1").Range("C7").Value
r.Offset(0, 5).Value = Sheets("Sheet1").Range("C8").Value
r.Offset(0, 6).Value = Sheets("Sheet1").Range("C11").Value
r.Offset(0, 7).Value = Sheets("Sheet1").Range("C12").Value
End Sub
Sub ClearForm()
Range("C4").Value = ""
Range("C5").Value = ""
Range("C6").Value = ""
Range("C7").Value = ""
Range("C8").Value = ""
Range("C11").Value = ""
Range("C12").Value = ""
End Sub
Hope this helped,
Rolf
On Oct 25, 9:45 am, ╚►♥ѕαη∂єєρ♥◄╝ wrote:
> I want when i fill the from and click the GO command button all the info
> automaticlly move to sheet2 and the sheet 1 give a blank from again
> NOTE : the Claim number will be as string not integer
>
> --
> Regards: ╚►♥ѕαη∂єєρ♥◄╝
>
> Data sheet.xls
> 56KViewDownload
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Conditional Formating of more than one cell
I am sorry, but I don't understand what you mean. Please try again.
On Oct 24, 5:08 am, Bajji wrote:
> Hi Rolf,
> I need similar help, little different way
>
> When I run the Macro, user form should pop up allowing to put the
> array (a1: j10) details to color the rows
>
> Regards,
> Sri
>
> On Oct 24, 4:53 am, RolfJ wrote:
>
>
>
> > I can't figure a way to do this by way of conditional formating, but I
> > can offer some VBA code that would do the trick. Place the following
> > code into the a standard VBA module of your workbook:
>
> > Public Sub ColorTable()
> > Dim rRow As Range
> > For Each rRow In ActiveSheet.UsedRange.Rows
> > If rRow.Row >= 4 Then
> > Select Case rRow.Cells(1, "M").Value
> > Case "Green"
> > Call ColorCells(rRow.Row, vbGreen)
> > Case "Orange"
> > Call ColorCells(rRow.Row, RGB(255, 140, 0))
> > Case "Red"
> > Call ColorCells(rRow.Row, vbRed)
> > End Select
> > End If
> > Next rRow
> > End Sub
>
> > Public Sub ColorCells(iRow As Long, c As Long)
> > Dim r As Range
> > Set r = Rows(iRow)
> > Dim col As Integer
> > For col = Asc("A") To Asc("L")
> > r.Cells(1, Chr(col)).Interior.Color = c
> > Next col
> > End Sub
>
> > Place the following code into the VBA module associated with the
> > worksheet containing the table:
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Column <> Columns("M").Column Then Exit Sub
> > Select Case Target.Value
> > Case "Green"
> > Call ColorCells(Target.Row, vbGreen)
> > Case "Orange"
> > Call ColorCells(Target.Row, RGB(255, 140, 0))
> > Case "Red"
> > Call ColorCells(Target.Row, vbRed)
> > End Select
> > End Sub
>
> > And finally place this code into the ThisWorkbook VBA module:
>
> > Private Sub Workbook_Open()
> > ColorTable
> > End Sub
>
> > That should do it.
>
> > Hope this helped,
> > Rolf
>
> > On Oct 22, 10:18 pm, ASHUTOSH AGARWAL wrote:
>
> > > Dear All,
>
> > > In the attached file, I want to format cells (a4 to l14) in a manner
> > > where row colour would be same as appearing in coloumn M. In other
> > > words, in row number 5, I want cells from A to L to be fileld in Green
> > > color, in row 6 in red color, etc.
>
> > > Is it possible to do so using conditional formating?
>
> > > Thanks in advance,
> > > Ashutosh
>
> > > Sample.xls
> > > 42KViewDownload- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: TO PASTE THE DATE IN ATTACHED FORMAT & VALIDATE IT
Hi Rahul: I didn't say that your WORKBOOK is password-protected. However the MACROS that are associated with it ARE password-protected!!! It would help to know whether you are familiar with Excel macros. Thanks for bearing with me, Rolf On Oct 26, 4:15 am, RAHUL GARG wrote: > HI ROLF, > > THE WORKBOOK IS NOT PROTECTED BY MY PASSWORD, THE PVAT-18.XLS FILE IS A > SALE-TAX FILE IN WHICH WE HAVE TO SUBMIT OUR E-RETURNS.I DOWNLOAD IT FROM THE > WWW.PEXTAX.COM OTHER THINGS LIKE BILL NO. PARTY NAME PASTE EASILY & VALIDATE > OPTOIN GIVEN AT THE END VALIDATE THE SAME, WHICH IS ESSENTAIL FOR UPLOADING > THE FILE TO THE SALE-TAX SITE. BUT WHEN WE PASTE THE DATE IN THE ABOVE SAID > FILE .THE VALIDATE OPTION DOES NOT VALIDATE THE DATA. SO I HAVE ASKED TO YOU > & DEAR EXCEL MACROS FRIEND IS THERE ANY WAY TO PASTE THE DATE IN THE FILE & > VALIDATE THE SAME. > > THANKS FOR YOUR REPLY. > > RAHUL GARG > > --- On Fri, 23/10/09, RolfJ wrote: > > From: RolfJ > Subject: $$Excel-Macros$$ Re: TO PASTE THE DATE IN ATTACHED FORMAT & VALIDATE > IT > To: "MS EXCEL AND VBA MACROS" > Date: Friday, 23 October, 2009, 10:46 PM > > Hi Rahul: > > you will need to provide a LOT more information than this if you want > anyone to assist you with this problem. The first thing I noticed when > I opened your workbook was that you password protected your macros. > That's not a great start, because it prevented me from trying to > understand what you mean by 'IT'S VALIDATE OPTION'. > > Best wishes, > Rolf > > On Oct 21, 12:33 am, RAHUL GARG wrote: > > > DEAR ALL, > > > > > > PLEASE HELP ME HOW I CAN PASTE THE DATE FROM AN ORDIANRY EXCEL FILE IN TO > > THE ATTACHED FILE. ALL OTHER THINGS SUCH AS PARTY NAME,B.NO. PASTE EASILY , > > BUT WHEN WE PASTE THE DATE IT'S VALIDATE OPTION DOES NOT VALIDATE IT WHICH > > IS ESSENTIAL. SO, PLEASE DO THE FAVOR FOR ME TO REDUCE MY WORK. > > > > THANKS & REGARDS, > > RAHUL GARG, > > > > > From cricket scores to your friends. Try the Yahoo! India > > Homepage!http://in.yahoo.com/trynew > > > PVAT-18.xls > > 640KViewDownload > > Keep up with people you care about with Yahoo! India Mail. Learn > how.http://in.overview.mail.yahoo.com/connectmore > > PVAT-18.xls > 640KViewDownload --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Need help in Excel formula
It would help to see an example of what you are trying to accomplish. On Oct 26, 4:51 am, Anantha wrote: > Hello Folks, > > For one of my assignments I'm trying to write a formula that searches > a list rom a search space. > > Here the list is a set of cells might have same or different values, > search space is a single cell where in these values are given with > comma seperated this may or maynot contain the values in the list. > > the formula am trying to write should just flag if any value from the > list exist in the search space. > > I'm trying out the following 2 options > 1. Using a formula if (find(list[i],searchspace,1)>0,Yes,if (find(list > [i+1],searchspace,1)>0,Yes,if (find(list[i+2],searchspace,1)>0 ) > > this one is very basic method and another problem is the list element > is dynamic here and the complexity grows with the List. > > 2. Writing a Macro that finds and flags the search space. but > > Please let me know if there is any other better way of doing it. > > Thanks in Advance, > > Anantha --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: copy paste while copycell is not empty
I am having difficulties following what you would like to do. In
particular it is not clear to me what you mean when you say 'it
stops'. Also, what to you mean to indicate with the - debugs here
flag?
Please post or e-mail example worksheets and I'll take a closer look.
On Oct 26, 6:28 pm, OSAVentures Calamba wrote:
> Dear Macro Experts,
>
> I'm just starting to write a macro. What I'm trying to do is I want to copy
> a value from 'computation' sheet.column B and posting it as values in 'Slip'
> range "D4" and do that while sheet.columnB is not empty. Below is the code
> i made but stops.
>
> Sub copyIDtopay()
> With Application
> .ScreenUpdating = False
> .DisplayAlerts = False
> End With
> ' Select cell b7, first line of data computation sheet.
> Do Until IsEmpty(ActiveCell)
> Worksheets("Computation").Range("B7").Copy - Worksheets("SLIP").Range("D4").PasteSpecial Paste:=xlPasteValues
> Sheets("Computation").Select
> ActiveCell.Offset(1, 0).Select
> Selection.Copy
> Sheets("SLIP").Select
> Range("D41").Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Loop
> .ScreenUpdating = False
> .DisplayAlerts = False
>
> End Sub
>
> Please help me.
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Simultaneous Equations VBA
Microsoft requires the parameter of the MInverse method of data type Variant (http://msdn.microsoft.com/en-us/library/bb239421.aspx). Try declaring CoArrInv as Variant (as in Dim CoArrInv(3,3) As Variant). Hope this helped, Rolf On Oct 18, 10:36 pm, seryozha wrote: > Hello all, > > I am attempting to write code for a Macro that will solve for > simultaneous equations. I Have 4 equations and 4 unknowns. I really > want (need) to do this using the Matrix operations (MInverse and > MMult) and therefore arrays. > > I created a user form so the user may input the coefficients and then > a button to calculate the unknowns. > The problem I am having, is i get a "Can't assign to array" and it > highlights the 'CoArrinv' Line. I have an inkling that it has to do > with Dim statements. > Thanks in advance for any help received, > Seryozha > > The code for the Sub button is below, but some notes first: > eq1x1,eq1x2.eq2x4 relate to a textbox in the userform. eq stands > for equation, x1 stands for unkown. > Labelx1 and so forth are labels that correspond to the unkown variable > (x1,x2,x3, and x4) > > Private Sub CommandButton1_Click() > Dim CoArray(3, 3) As Integer, Zarray(3) As Integer, CoArrInv(3, 3) As > Integer, xArray(3) As Integer > Dim x1, x2, x3, lblx1, blbx2, lblx3 As Long > CoArray(0, 0) = Val(eq1x1.Value) > CoArray(0, 1) = Val(eq1x2.Value) > CoArray(0, 2) = Val(eq1x3.Value) > CoArray(0, 3) = Val(eq1x4.Value) > > CoArray(1, 0) = Val(eq1x1.Value) > CoArray(1, 1) = Val(eq1x2.Value) > CoArray(1, 2) = Val(eq1x3.Value) > CoArray(1, 3) = Val(eq1x4.Value) > CoArray(2, 0) = Val(eq2x1.Value) > > CoArray(2, 1) = Val(eq2x2.Value) > CoArray(2, 2) = Val(eq2x3.Value) > CoArray(2, 3) = Val(eq2x4.Value) > CoArray(3, 0) = Val(eq3x1.Value) > > CoArray(3, 1) = Val(eq3x2.Value) > CoArray(3, 2) = Val(eq3x3.Value) > CoArray(3, 3) = Val(eq3x4.Value) > > Zarray(0) = Val(eq1z.Value) > Zarray(1) = Val(eq2z.Value) > Zarray(2) = Val(eq3z.Value) > Zarray(3) = Val(eq4z.Value) > > CoArrInv = Application.WorksheetFunction.MInverse(CoArray) ' > Get "Can't assign to array" > > xArray = Application.WorksheetFunction.MMult(CoArray, Zarray) > > Labelx1 = xArray(0) > Labelx2 = xArray(1) > Labelx3 = xArray(2) > Labelx4 = xArray(3) > End Sub --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: To insert new row if
If I correctly understand your requirement this code should do:
Sub InsertRowsAfter8DigitRow()
Dim r As Range
Set r = ActiveSheet.UsedRange.Columns("B")
Dim rCell As Range
For Each rCell In r.Cells
If rCell.Row >= 3 Then
If rCell.Offset(-1).Value = "" And Is8DigitCode
(rCell.Offset(-2).Value) Then
rCell.Rows.EntireRow.Insert
End If
End If
Next rCell
End Sub
Function Is8DigitCode(s As String) As Boolean
If IsNumeric(Left(s, 4)) And Mid(s, 5, 1) = "." And IsNumeric(Right
(s, 4)) Then
Is8DigitCode = True
Else
Is8DigitCode = False
End If
End Function
Hope this helped,
Rolf
On Oct 22, 4:00 am, Hassan Tariq wrote:
> Dear All,
>
> Please help is it possible for
>
> Macro to insert a new row if column B of previous row is blank and the
> row before of Column B includes 8-digit code.
>
> For Example
>
> Column B
> Row 8
> Row 9
> Row 10 .
> Row 11
> Row 12
> Row 13
> Row 14
> Row 15 .
> Row 16
> Row 17
> Row 18
> Row 19
> Row 20
>
> I am looking for help for macro to insert a new row after Row 11 & 16
> and other such rows
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Conditional Formating of more than one cell
I can't figure a way to do this by way of conditional formating, but I
can offer some VBA code that would do the trick. Place the following
code into the a standard VBA module of your workbook:
Public Sub ColorTable()
Dim rRow As Range
For Each rRow In ActiveSheet.UsedRange.Rows
If rRow.Row >= 4 Then
Select Case rRow.Cells(1, "M").Value
Case "Green"
Call ColorCells(rRow.Row, vbGreen)
Case "Orange"
Call ColorCells(rRow.Row, RGB(255, 140, 0))
Case "Red"
Call ColorCells(rRow.Row, vbRed)
End Select
End If
Next rRow
End Sub
Public Sub ColorCells(iRow As Long, c As Long)
Dim r As Range
Set r = Rows(iRow)
Dim col As Integer
For col = Asc("A") To Asc("L")
r.Cells(1, Chr(col)).Interior.Color = c
Next col
End Sub
Place the following code into the VBA module associated with the
worksheet containing the table:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> Columns("M").Column Then Exit Sub
Select Case Target.Value
Case "Green"
Call ColorCells(Target.Row, vbGreen)
Case "Orange"
Call ColorCells(Target.Row, RGB(255, 140, 0))
Case "Red"
Call ColorCells(Target.Row, vbRed)
End Select
End Sub
And finally place this code into the ThisWorkbook VBA module:
Private Sub Workbook_Open()
ColorTable
End Sub
That should do it.
Hope this helped,
Rolf
On Oct 22, 10:18 pm, ASHUTOSH AGARWAL wrote:
> Dear All,
>
> In the attached file, I want to format cells (a4 to l14) in a manner
> where row colour would be same as appearing in coloumn M. In other
> words, in row number 5, I want cells from A to L to be fileld in Green
> color, in row 6 in red color, etc.
>
> Is it possible to do so using conditional formating?
>
> Thanks in advance,
> Ashutosh
>
> Sample.xls
> 42KViewDownload
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: - AUTOMATICALLY RUN AFTER FILE OPEN
I think I can help you with item 1, but don't understand item 2.
Here's my suggestion for item 1. Let's assume you have a macro named
macro1 then the code for what you are trying to do would be:
Activate Workbook
macro1
End Sub
There were two problems with your code:
1) Spaces are not allowed in macro names
2) The syntax for the Run command is different: Run("macro1") should
have worked.
Hope this helped,
Rolf
On Oct 23, 4:58 am, big smile wrote:
> Hellow friends
> Please guide me in writing the macro
>
> 1] Whenever i open a particular file --- it start with urn macro 1 store
> inside this file
>
> i have try the below mentioned macro - but it does not work out.
>
> Activate Workbook
> run macro 1
> End Sub
>
> 2] The another macro -that do the following
>
> which ever i select the raw -- then if i run macro -- say 1 - by short cut
> key
>
> it automatically do for me -- select right key of keyboard --near ctrl on
> right side
>
> or right side mouse -- filter -- then filter by select by sell value.
>
> Thanks
>
>
>
>
>
> > On Sat, Oct 17, 2009 at 4:22 PM, big smile wrote:
>
> >> Hellow all excel guru's & friends.
>
> >> I have some query -- or i want to know the shortcuts.
>
> >> 1] Can we delete the name given to particular cell in name box - if
> >> we type F5 - the name box appear
>
> >> I would like to know how to change the name and delete the name given to
> >> some reference.
>
> >> 2] There are 3 sheets in one file -- i have hide one sheet -- i want to
> >> know can we give the password for opening
>
> >> of such hide sheet ? -- if yes how
>
> >> 3] I have created one macro no. 7 -- in a particular file - i want to know
> >> can we define one another macro so that
>
> >> whenever i open that file -- macro no. 7 automatically run -- give me the
> >> result created in macro 7.
>
> >> 4] whenever we run a particular macro -- we can not go back by ctrl + Z [
> >> undo ] -- or we can not restore the old things which were there before
>
> >> macro run -- is there any solution to such thing.
>
> >> 5] I have created certain custom view in file --- is there any shortcuts
> >> to change the name of that custom view ---
>
> >> at present i am using the technique to select the particular custom view
> >> -- then delete the same & then giving another name
>
> >> this is perhaps the long procedure.-- is there any shortcuts of changing
> >> the custom view name
>
> >> Thanks & have a nice time- Hide quoted text -
>
> - Show quoted text -
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Run Excel Macro?
Following Hernant's suggestion I would consider to encapsulate this process in a totally stand-alone C# application which instantiates a separate Excel session that wouldn't interfere with your regular Excel session. I would have to see the current VBA code to see how easily this could be created. Best wishes, Rolf On Oct 20, 9:56 pm, Holsten wrote: > Hi All, > > First let me say that I love reading through the suggestions and > examples that have been posted for other peoples problem's and have > picked up some great formulas and macros and for that I am already > thankful to you all. Now for my question. I have been searching for > a way to automate some reports that I run. I use excel 2007 and the > main problem I have is that the reports are updated bi-weekly via > refresh of external qry to an access database on my server. When I > click "refresh all" the qrys run and excel becomes unusable to me for > several minutes. I am sure that It doesn't sound that bad but I have > to repeat the process for 6-10 reports. The time adds up and I need > to find a way to report faster. > > Please tell me if I am just a dreamer but in my mind it seems like > there should be a way for me to activate the "refresh all" and email > myself the finished product without locking up my computer or excel > for 2 hours. I do have a macro to run the refresh then email me the > product but again I still have the problem of excel locking up. Is > there a way for me to have the refresh (or macro to email self) run > without opening excel? I have punch and pie for anyone with a > promising suggestion. :) > > I am sure that that I have not explained details in full and I welcome > any questions. > > Thanks for your time. --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: TO PASTE THE DATE IN ATTACHED FORMAT & VALIDATE IT
Hi Rahul: you will need to provide a LOT more information than this if you want anyone to assist you with this problem. The first thing I noticed when I opened your workbook was that you password protected your macros. That's not a great start, because it prevented me from trying to understand what you mean by 'IT'S VALIDATE OPTION'. Best wishes, Rolf On Oct 21, 12:33 am, RAHUL GARG wrote: > DEAR ALL, > > > PLEASE HELP ME HOW I CAN PASTE THE DATE FROM AN ORDIANRY EXCEL FILE IN TO THE > ATTACHED FILE. ALL OTHER THINGS SUCH AS PARTY NAME,B.NO. PASTE EASILY , BUT > WHEN WE PASTE THE DATE IT'S VALIDATE OPTION DOES NOT VALIDATE IT WHICH IS > ESSENTIAL. SO, PLEASE DO THE FAVOR FOR ME TO REDUCE MY WORK. > > THANKS & REGARDS, > RAHUL GARG, > > > From cricket scores to your friends. Try the Yahoo! India > Homepage!http://in.yahoo.com/trynew > > PVAT-18.xls > 640KViewDownload --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Need help in Excle macro
If you are REALLY concerned about the users of your workbook using
specific commands you could also turn them off by using the OnKey
method in the Workbook_Open event handler. Here's the code on how to
do that for "CTRL+X":
Private Sub Workbook_Open()
Application.OnKey "^{x}", ""
End Sub
Check out http://msdn.microsoft.com/en-us/library/aa195807(office.11).aspx
to find out how to use the OnKey method for other keys.
Hope this helped,
Rolf
On Oct 8, 10:00 pm, Manish Pansari wrote:
> Hello Friends,
>
> I am very new in Excel Macros...
>
> I am triying to devlope a ms excel file with some useful macros..
>
> I want a pop-up window whenever I open the ms excel file. In tht pop-
> up window i want to highlight the instruction like dont use command
> like "Ctrl+X" or edit in "green field only".
>
> Any one can pls help me tht how can I devlope the same. If any one can
> attach a sample file, than it will be more easy for me to learn and
> understand.
>
> Thanks a ton...
>
> -
> Manish
> pansari.man...@gmail.com
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Managing decimal places
As long as you DON'T try to do any calculations with cell B5 (for the
reason explained by Paul) you would use the following formula in cell
B5:
=IF(B1="USD K",CONCATENATE("USD ",TEXT(B11,"#,###")),CONCATENATE("INR
",TEXT(B10,"#.#")))
If you do need to do calculations with the value in cell B5 I would
suggestion using a different cell to do the calculation with. You
could e.g. use cell C5 and place your own formula in it:
=If(B1="INR Crs",B10,B11)
In case it troubles you (or the users of your workbook) to see cell C5
you could turn it invisible e.g. by choosing white as the font
color...
Hope this helped,
Rolf
On Oct 9, 4:09 am, Radhe Sham L wrote:
> Hi ,
>
> I have a requirement for a solution regarding the display of decimal place
>
> I have a report which displays data in 2 currencies - USD K & INR Crores
>
> Based on the Currency selected from the drop box. The Value in the
> respective currency is required to be displayed in the same cell
>
> for example
> The drop Box for currency selection is in cell B1 (with options "INR Crs" &
> "USD K"
>
> The data is to be displayed in cell B5
>
> The INR value(INR Crs) is stored in cell B10 -> 6.1
> The USD value(USD K) is stored in cell B11 -> 1220
>
> The formula i would use is =If(B1="INR Crs",B10,B11).
>
> Now, what is required is when the currency is INR Crs, the display has to be
> 1.6 (ie with one decimal place) else if it is USD the the display should be
> 1,220 (ie without a decimal place)
>
> Tried using "Fixed" formula , but though it seems to solve my problem by
> what is displayed, the data is in text format due to which calculations are
> not possible
>
> Can any one kindly help me in giving a formula which would display a number
> with one decimal place for "INR Crs" and with no decimal place for USD K
>
> Many thanks in advance
>
> Best regards
> Radhe
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: filtering dates within this week
If you don't have Excel2007 it gets a bit more complicated. Here is one way to accomplish what you would like to do (for the sake of simplicity I am assuming that your dates are in column A, and that column B can be used as helper column and that you have a header in the first row (cell A1)): Step 1: Enter the following formula in cell B2 which checks whether the date in A2 falls within the same workweek (it's ugly, I know; can't wait to see a simpler solution posted): =IF(ABS(A2-DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW(>7,FALSE(),IF (A2>NOW(),IF(WEEKDAY(A2)>WEEKDAY(NOW()),TRUE(),FALSE()),IF(A2<=NOW(),IF (WEEKDAY(A2) wrote: > I have a series of dates. How can i filter dates that are within this > week? > > thanks --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: heyerlinking many cells
If I correctly understand Nader's requirement he would like to hyperlink each cell in one sheet to the corresponding cell in a second sheet (not all cells to cell A1 of the second sheet). I am not aware of a simple copy and paste route that would accomplish that, but can offer the following macro which accomplishes this for the selected range: Const TARGETSHEET = "Sheet1" Sub LinkToCellsInAnotherSheet() If ActiveSheet.Name = TARGETSHEET Then Exit Sub Dim rCell As Range Dim txtToDisplay As String For Each rCell In Selection If IsEmpty(rCell) Then txtToDisplay = " " Else txtToDisplay = rCell.Value End If ActiveSheet.Hyperlinks.Add Anchor:=rCell, Address:="", SubAddress:=TARGETSHEET & "!" & rCell.Address, TextToDisplay:=txtToDisplay Next rCell End Sub Hope this helped, Rolf On Oct 9, 8:51 pm, "Upendra Singh" wrote: > Hi Nader, > > You can do this in one Go. > Activate the second sheet. In the Cell A1, write "=" without double quotes > and then go to Sheet 1 and select A1. > Press enter. > Press F5 and enter the last cell till where you want to hyperlink (say M500) > Press Shift Enter. > Press F2 and Ctrl+Enter. > This will hyperlink all the cell from A1 to M500 > > Thanks and Regards, > > Upendra Singh > +91-9910227325 > > > > -Original Message- > From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] > > On Behalf Of nader > Sent: Friday, October 09, 2009 7:54 PM > To: MS EXCEL AND VBA MACROS > Subject: $$Excel-Macros$$ heyerlinking many cells > > Hello, > > I am trying to hyperlink many cells from one sheet to another one in > the same excel file but I can not copy and paste the first hyperlinked > cell to other cell because it always goes to the first cell. > E.g. I want to hyperlink cell A1 in first sheet to cell A1 in second > sheet and then A2 to A2, .. A500 to A500. > > Anybody knows how to do this? > I really appreciate your help > Nader- Hide quoted text - > > - Show quoted text - --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Hyperlinking to a text (Not the Cell) in a worksheet of same workbook
The simplest solution to this is to define names for your target cells. This is how you do that, e.g. for 'Ann': highlight the cell Ann in your Master sheet and then select Insert | Name | Define; the name for this cell automatically defaults to 'Ann' which is probably what you want; now go to the sheet you want to link to the cell 'Ann' and when editing the hyperlink select 'Ann' from the list of 'Defined Names'. Hope this helped, Rolf On Oct 9, 12:36 pm, Yahya wrote: > Hi > I have a work book having a lot of worksheets. There is one Master sheet > which contains the index with the names of all these worksheets. I have > created hyperlinks on this master sheet (By Insert Hyperlink >>> Cell Ref.) > to each of these worksheets. I have also created hyperlinks on each work > sheet in a particular cell (Say C1) to navigate to the particular cell in > the Master Sheet with the Name of the Worksheet. Everything works fine > unless I do not sort or insert any rows/columns in the master sheet after > creating the hyperlink. In such cases, the click on hyperlink takes me to > the previous cell where it was before (based on that cell ref). What I need > is to hyperlink the text so that even if the cell ref of that text changes, > the hyperlink will take me to it. > > Attached a sample file. > > Any solutions, please? > > Yahya > > Sample.xls > 18KViewDownload --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Running Mecro automatically
This pointer should help: http://www.ozgrid.com/Excel/run-macro-on-time.htm On Oct 11, 1:59 am, Manish Pansari wrote: > Hello Friends, > > I want to run a macro continuously after every five minutes. Is there > any option, by using that I can set macro run time and can run macro > automatically? > > Thanks, > Manish --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Mail through MS Excel more than one person - Help
Once you include add a reference to the Microsoft Outlook library to your VBA project (using Tools | References in VBE), you can send messages with code like this: Sub SendMail() Dim objOut As Outlook.Application Dim objMail As Outlook.MailItem Dim strTo As String Dim strCC As String Dim strSubject As String Dim strBody As String Dim firstTo As String, secondTo As String firstTo = "johnsm...@usa.com" secondTo = "john...@usa.com" Dim firstCC As String, secondCC As String firstCC = "johnad...@usa.com" secondCC = "jimsm...@usa.com" On Error Resume Next Application.DisplayAlerts = False Set objOut = New Outlook.Application Set objMail = objOut.CreateItem(olMailItem) strTo = firstTo & ";" & secondTo strCC = firstCC & ";" & secondCC strSubject = "Test" strBody = "Your call" strBody = strBody & vbCrLf & "Second line" strBody = strBody & vbCrLf & vbCrLf & "Best regards" With objMail .To = strTo .CC = strCC .Body = strBody .Subject = strSubject .Send End With Set objOut = Nothing Application.DisplayAlerts = True End Sub You can of course modify this code to read the e-mail addresses from cells in your workbook. Hope this helped, Rolf On Oct 11, 2:18 am, Manish Pansari wrote: > Hello Friends, > > I want to set a macro, by using that I want to mail some data sheet on > regular basis to my team members. > The format is always same. > > So I want to devolve a macro, but I am facing a problem that how can I > define body text of the mail. > Further, I want to put mail IDs in a worksheet in different category > like "To" & "Cc". Can through macro I can mark a single mail to more > than one person? How its possible? Can anyone please suggest me the > CODE or upload a sample file for code. > > Thanks in advance. > Manish --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Macro Help
I am not clear on what you want to write code for. Do you want to eliminate the space or do you want to intercept Excel's response to the (invalid) space in the e-mail address? On Oct 11, 3:44 am, karthikeyan wrote: > Hi, > I have enter E-mail id in one cell with space:e.g(karthi keyan > sankar...@gmail.com). > Excel automatically validate when i click enter button popup message : > don't use space enter E-mail id. How to write coding.pls let me know. > Thanks --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Is there code that when and Excel file opens it closes any open PowerPoint files?
It appears that you cross-posted this also at
http://www.excelforum.com/excel-programming/702626-excel-macro-to-close-powerpoint.html
For the benefit of the members of this forum, here the solution I sent
you:
Sub Workbook_Open()
Dim PPApp As PowerPoint.Application
Set PPApp = GetObject(, "PowerPoint.Application.11") 'or .12'
If PPApp Is Nothing Then Exit Sub 'no active PowerPoint session
Dim OpenPpt As PowerPoint.Presentation
For Each OpenPpt In PPApp.Presentations
OpenPpt.Close
Next OpenPpt
PPApp.Quit
End Sub
On Oct 8, 7:59 pm, Tony Bender wrote:
> I have an Excel application that enables the user to export an Exccel
> chart directly into PowerPoint. The macro to do this works fine
> provided PowerPoint was not already open.
>
> I am trying to figure the VB code so when the application opens it
> automatically closes PowerPoint - if in fact PowerPoint was open.
>
> Here's what I have so far but I'm getting errors ("ActiveX component
> can't create object):
>
> Sub Workbook_Open()
> Dim PPApp as PowerPoint.Application
> Set PPApp = PowerPoint.ActivePresentation
>
> With PPApp
> .Saved = msoTrue
> .Close
> end with
>
> UF1.show
> End Sub
>
> Can anyone point me in the right direction?
>
> Thank you,
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: fill in the nos
There are of course keyboard/menu equivalents for both (but I am probably telling you the obvious, hence maybe am missing the point of your question): - Filling: Edit | Fill | Series (choose either rows or columns, then enter stop value and click on OK - Copying of a Formula: - Step 1: copy the formual using Edit | Copy (or CTRL + C) - Step 2: highlight the cells you want the formula copied to - Step 3: paste the formula using Edit | Paste (or CTRL + V) Hope this helped, Rolf On Oct 8, 7:16 pm, subs wrote: > if i want to fill in a number or a forumla for rest of the cells in a > column , is there a short cut to do that or the only way is to pull in > the mouse pointer?pls help --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Cap Gains calculation on shares, on FIFO basis
You are describing exactly what I had proposed. Should be straightforward to implement. Have fun, Rolf On Oct 8, 7:45 am, chinmay kamat wrote: > Thank you sir, but unfortunately this does not solve the problem. > > The data is in the following format (example): > > For purchases: > Date Quantity Amount Rate > 01/04/08 150/- 85050/- 567/- > 05/04/08 100/- 61000/- 610/- > 09/04/08 25/- 15125/- 605/- > 10/04/09 200/- 13/- 650/- > > For sales: > Date Quantity Amount Rate > 10/04/08 100/- 59000/- 590/- > 07/04/09 175/- 117250/- 670/- > 10/04/09 200/- 16/- 800/- > > > > Analysis of the data: > > the sale made of 10/04/08 is entirely short term. The cost of 100 > shares sold are selected as follows: 100 out of purchases of 01/04/08. > Profit is (100*590) - (100*567)= 2300/- > > > > The sale made on 02/04/09 is one year later, and the cost for the 175 > shares sold is selected as follows: 50 out of purchases made on > 01/04/08 and 100 out of purchases made on 05/04/08 and 25 out of > purchases made on 06/04/08. > > Profit is (175*670) - ((50*567) + (100*610)+(25*605))= 12775/- > > out of which (150*670) - ((50*567)+(100*610))= 11150/- is long term > > and > > (25*670)-(25*605)=1625/- is short term. > > > > > The sale made on 10/04/09 is speculative because both the sale and > purchased occurred on the same day for all the shares. (as all the > earlier purchases were exhausted) > > > > so the program should not only calculate the profit by subtracting the > cost of shares on FIFO basis (that is identify the earliest existing > stock) but also tell if that was short term, long term or speculative. > > the second problem can easily be solved using the DATEIF function as > explained by you. but since the data does not readily give me the > correlation between the purchases and sales on FIFO basis, the first > problem remains. > > Interesting problem, isnt it..?? > > <><><><><><><><><><><><><><><><> > I have an idea of how to go about the problem, but i do not know how > to implement it. > > See, the data is as follows: > Date of each transaction, Quantity bought/sold in each transaction, > total amount > > we convert that data into: > Date of purchase/sale of each share, Quantity=1 (always), Rate per > share. > > So we will end up with data spreading across many rows, as each > transaction is split into several rows depending on the number of > shares involved. > > We do this for both purchase data and sale data. > > then we place the data side by side, and voila.. just subtract the > selling rate from purchase rate to get profit, then compare the dates > to categorise the transaction. > > You think it may be possible in excel?? or would it be possible in any > other manner??? --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Problems re-opening userform
Are you sure the ExitApp macro you placed in your workbook Rpt1.xls actually works without error? It seems to me that the UserForm UF1 in that context... Anyhow I think you can accomplish what you are trying to do simply by placing the following event handler into the ThisWorkbook VBA module of your workbook Wkbk1: Private Sub Workbook_Activate() UF1.Show vbModeless End Sub Once you did that you can remove the Workbook_Open event handler you posted and UF1 will show after you closed Rpt1. Hope this helped, Rolf On Oct 7, 9:03 am, Tony Bender wrote: > I have an application consisting of a report menu and the > corresponding templates the menu points to. > > The menu is a UserForm (UF1) and resides on a separate workbook > (Wkbk1). When the user opens Wkbk1 it automatically opens the > UserForm (UF1). The > user then selects a report template from one of 4 OptionButtons. The > resulting template (example; Rpt1.xls) opens up. The user can exit > this report by clicking on the Exit button (triggers the ExitApp > macro > which closes the activewindow). > > Here is the ExitApp macro: > Private Sub ExitApp() > Application.DisplayAlerts = False > Application.ActiveWorkbook.Saved = False > Application.ActiveWorkbook.Close > UF1.Show > End Sub > > My problem is when I click on the Exit button on Rpt1 it closes the > workbook but instead of opening the menu (UF1) it simply opens > the sole empty worksheet in Wkbk1. I want this to automatically open > the UF1 instead of displaying the empty worksheet. > > I've tried the following code but with no luck, and I appreciate any > ideas as I am losing patience! > > In Wkbk1 I have this code: > > Private Sub Workbook_Open() > Load UF1 > UF1.Show vbModeless > End Sub > > Also, in the UserForm itself when the user selects one of the 4 > OptionButtons here is the corresponding code: > > Private Sub OB1_Click() > Workbooks.Open Filename:="C:\Data\Rpt1.xls" > UF1.Hide > End Sub > > Thank you, --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Cap Gains calculation on shares, on FIFO basis
You could use the DATEDIF function (see e.g. http://www.cpearson.com/excel/datedif.aspx). Let's assume that your purchase date is in cell A2 and you sell date in cell B2. The formula =DATEDIF(A2,B2,"d") returns the number of days between the two dates. One way to use this function for your scenario could be to enter the formula for each of the three transaction types into additional cells on the same row (including the tax considerations), e.g.: - cell H2: long-term gain - cell I2: short-term gain - cell K2: speculative gain You could now enter the following formula in cell L2: =IF(DATEDIF(A2,B2,"d")=0,K2,IF(DATEDIF(A2,B2,"d")<365,I2,H2)) If this does what you were hoping for, you could now copy cells H2, I2, K2 and L2 down to the other rows in your table and hopefully be done. Hope this helped,Rolf On Oct 8, 1:31 am, chinmay kamat wrote: > Hello friends, > > I'm interested in using excel to do calculation for gains/losses in > shares. > > As per law, there are 3 kinds of transactions which can be entered > into in shares. The types are determined on the basis of the period of > holding of each share by using FIFO method. FIFO method assumes that > out of several shares purchased of multiple dates, the earliest shares > are sold first. Thus for 10 shares sold on a day at the same price, > the gains might be completely different as 5 shares might have been > purchased first at some price and the rest might have been purchased > on a later date. thus the date of purchase decides the category of > transaction and the price decides the gain/loss. > > If the period of holding is 365 days or more, it is long term. if it > is more that 1 day but less than 365 days it is short term. and if it > is sold on d same day than the transaction is teated as a speculative > transaction. > > The tax treatment for each type is different. > > I have data in the following format for purchase and sale separately. > > Date, quantity and total amount > > Is there any way that the FIFO algorithm can be implemented in excel?? > > Thanks in advance --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Macro to hide blank or zero value rows
It seems that the code you posted is dealing with only one of the two
year columns you mentioned in your post. Give this code a try (after
modifying the constant COL2 to match the range for the second year
column) and see whether it accomplishes what you would like to see
happening:
Const COL1 As String = "AF13:AF85"
Const COL2 As String = "AH13:AH85"
Sub hiderow()
Dim rng1 As Range, rng2 As Range
On Error Resume Next
Range(COL1).EntireRow.Hidden = False
Dim c As Range
Set rng1 = Range(COL1).SpecialCells(xlBlanks)
Set rng2 = Range(COL2).SpecialCells(xlBlanks)
On Error GoTo 0
If rng2 Is Nothing Then Exit Sub
For Each c In rng1
Dim r As Range
Set r = Intersect(Rows(c.Row), rng2)
If Not r Is Nothing Then
c.EntireRow.Hidden = True
End If
Next c
End Sub
Hope this helped,
Rolf
On Oct 7, 7:28 pm, Heather wrote:
> This is my first post, and I hope it works! I need to hide non-used
> rows that either have zero values or blanks. I have two total columns
> for two years, and if they are both zero, then that row can be hidden.
>
> I am using the following macro, but is is hiding too much.
>
> Sub hiderow()
> Dim rng As Range
> On Error Resume Next
> Range("af13:af85").EntireRow.Hidden = False
> Set rng = Range("af13:af85").SpecialCells(xlBlanks)
> On Error GoTo 0
> If Not rng Is Nothing Then
> rng.EntireRow.Hidden = True
> End If
> End Sub
>
> I have a sample file that I can attach.
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: need help with excel macro
Assuming that you already populated the listbox (named ListBox1) with the file path names of Excel workbooks you could place this ListBox click event handler in the VBA module of the UserForm containing your listbox: Private Sub ListBox1_Click() If Dir(ListBox1.Value) <> "" Then Workbooks.Open (ListBox1.Value) End Sub Hope this helped, Rolf On Oct 3, 8:50 am, karthikeyan wrote: > I have put one listbox add some '.xls' file path through inputbox and > store as a button in a toolbar. > now want to open listindex files.e.g : listbox contains 5 '.xls'-files > when am clicking file path name its opening in new workbook.can any > body help me about this?. --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: need help with excel macro
Assuming that you already populated the listbox (named ListBox1) with the file path names you would need to add this ListBox click event handler to the VBA module of the UserForm containing the listbox: Private Sub ListBox1_Click() If Dir(ListBox1.Value) <> "" Then Workbooks.Open (ListBox1.Value) End Sub Hope this helped, Rolf On Oct 3, 8:50 am, karthikeyan wrote: > I have put one listbox add some '.xls' file path through inputbox and > store as a button in a toolbar. > now want to open listindex files.e.g : listbox contains 5 '.xls'-files > when am clicking file path name its opening in new workbook.can any > body help me about this?. --~--~-~--~~~---~--~~ -- 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Appending multiple excel files into one
It's difficult to comment on this without seeing your whole code.
Please post it and I promise to take a look,
Rolf
On Oct 2, 10:47 pm, suyog_linux wrote:
> Hi Rolf,
>
> Thanks for responding.
>
> I made the changes as described. Now I am getting an error "Duplicate
> declaration in current scope." for statement --> Dim path as string
>
> Please help.
>
> Suyog
>
> On Oct 2, 1:49 am, RolfJ wrote:
>
>
>
> > Got it. Try declaring fso this way:
>
> > Dim fso As Object
> > Set fso = CreateObject("Scripting.FileSystemObject")
>
> > On Oct 1, 10:34 pm, suyog_linux wrote:
>
> > > Hi Rolf,
>
> > > Thanks for responding.
>
> > > Please find the answers below :
>
> > > 1. I am getting the error which says:
> > > "User defined type - not defined" This error is related to first
> > > Dim statement in the code above.
>
> > > Dim fso As Scripting.FileSystemObject
>
> > > 2. Obviously it is critical that you specify the range of cells you
> > > want to copy (in the code you showed it is set to "B28:F28". Is that
> > > correct?
> > > Ans:- Yes
>
> > > 3. Also the code assumed that your daily reports contain the date you
> > > want to copy on the first worksheet. Is that the case?
> > > Ans: Yes
>
> > > 4. The code also assumes that all your daily reports, and only your
> > > daily reports (!), are located in the folder "C:\test". Is that
> > > correct?
> > > Ans: Yes
>
> > > 5. The code also assumes that the format of your weekly report is
> > > identical to the format of the daily reports. Is that the case?
> > > Ans: Yes
>
> > > Thanks,
> > > Suyog
>
> > > On Oct 1, 8:03 am, RolfJ wrote:
>
> > > > You need to be a little bit more specific in HOW this code is not
> > > > working for you. Obviously it is critical that you specify the range
> > > > of cells you want to copy (in the code you showed it is set to
> > > > "B28:F28". Is that correct? Also the code assumed that your daily
> > > > reports contain the date you want to copy on the first worksheet. Is
> > > > that the case? The code also assumes that all your daily reports, and
> > > > only your daily reports (!), are located in the folder "C:\test". Is
> > > > that correct? The code also assumes that the format of your weekly
> > > > report is identical to the format of the daily reports. Is that the
> > > > case?
>
> > > > On Oct 1, 12:43 am, suyog_linux wrote:
>
> > > > > Hi,
>
> > > > > I am trying to append multiple excel workbooks into. Basically to
> > > > > merge daily reports into a weekly one.
>
> > > > > I found the code below somewhere on internet but it is not working for
> > > > > me.
>
> > > > > Can someone please look and let me know how I need to change this in
> > > > > order to make it work ?
>
> > > > > Sub AppendData()
>
> > > > > Dim fso As Scripting.FileSystemObject
> > > > > Dim folder As Scripting.folder
> > > > > Dim file As Scripting.file
> > > > > Dim rngEntry As Range
> > > > > Set rngEntry = Range("a1")
> > > > > Set fso = New FileSystemObject
> > > > > Set folder = fso.GetFolder("C:\test\")
> > > > > Dim wbkMaster As Workbook
> > > > > Dim shtMaster As Worksheet
> > > > > Dim rngMaster As Range
> > > > > Dim wbkData As Workbook
> > > > > Dim shtData As Worksheet
> > > > > Dim rngData As Range
>
> > > > > ' change path and file name to suit
> > > > > Set wbkData = Workbooks.Open(path)
> > > > > Set shtMaster = wbkMaster.Worksheets(1)
>
> > > > > For Each file In folder.Files
> > > > > If LCase(Right(file.Name, 4)) = ".xls" Then
> > > > > Dim path As String
> > > > > path = file.path
> > > > > MsgBox path
> > > > > Set wbkData = Workbooks.Open(path)
> > > > > Set shtData = wbkData.Worksheets(1)
>
> > > > > ' get end of master
> > > > > Set rngMast
$$Excel-Macros$$ Re: Help with CountIf in Macro
I have two comments.
1) If I correctly understand what you are trying to accomplish, you
could simply put the formula COUNTIF(AR2:CE2,1) in cell K2 and copy it
down to K3:K250.
2) It seems to me that the problem with your code is that your COUNIF
formula references the whole range (arange) not such a single row.
Give this modified version a try:
Dim aRange As Range
Dim WS1 As Worksheet
Set WS1 = Worksheets("Dept")
Set aRange = WS1.Range("$AR$2:$CE$250")
Dim row As Range
For Each row In aRange.Rows
WS1.Cells(row.Cells(1, 1).row, "K").Value =
Application.WorksheetFunction.CountIf(row, ">0")
Next row
Hope this helped.
On Oct 2, 7:01 am, c wrote:
> Hi Everyone,
>
> Need some help with this macro. Basically, I am searching through
> rows and looking for any values in the cells that are >0 in columns AR
> - CE.
> After I find these cells in that range, I need to count the number of
> columns. This can be done with the COUNTIF function. I also do not
> want to count blank cells.
>
> I want to put the result of the countif in the K column.
>
> Here is what I have so far and it's not working. Go figure. Please
> help. I think I'm close.
>
> Dim j, k As Long
> Dim iRow As Long
> Dim aRange As Range
> Dim NumberOfMonths As Long
>
> Dim WS1 As Worksheet
> Set WS1 = Worksheets("Dept")
>
> NumberOfMonths = 0
>
> Sheets("Dept").Select
> With WS1.Range("AR2:CE250")
> Set aRange = WS1.Range("$AR$2:$CE$250")
> For j = 2 To aRange.Rows.Count
> For k = 44 To 90
> If ((WS1.Cells(j, k) > 0)) Then
> NumberOfMonths = Application.WorksheetFunction.CountIf
> (aRange, ">0")
> WS1.Cells(j,11).Value = NumberOfMonths
> End If
> Next k
> Next j
> End With
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Appending multiple excel files into one
Got it. Try declaring fso this way:
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
On Oct 1, 10:34 pm, suyog_linux wrote:
> Hi Rolf,
>
> Thanks for responding.
>
> Please find the answers below :
>
> 1. I am getting the error which says:
> "User defined type - not defined" This error is related to first
> Dim statement in the code above.
>
> Dim fso As Scripting.FileSystemObject
>
> 2. Obviously it is critical that you specify the range of cells you
> want to copy (in the code you showed it is set to "B28:F28". Is that
> correct?
> Ans:- Yes
>
> 3. Also the code assumed that your daily reports contain the date you
> want to copy on the first worksheet. Is that the case?
> Ans: Yes
>
> 4. The code also assumes that all your daily reports, and only your
> daily reports (!), are located in the folder "C:\test". Is that
> correct?
> Ans: Yes
>
> 5. The code also assumes that the format of your weekly report is
> identical to the format of the daily reports. Is that the case?
> Ans: Yes
>
> Thanks,
> Suyog
>
> On Oct 1, 8:03 am, RolfJ wrote:
>
>
>
> > You need to be a little bit more specific in HOW this code is not
> > working for you. Obviously it is critical that you specify the range
> > of cells you want to copy (in the code you showed it is set to
> > "B28:F28". Is that correct? Also the code assumed that your daily
> > reports contain the date you want to copy on the first worksheet. Is
> > that the case? The code also assumes that all your daily reports, and
> > only your daily reports (!), are located in the folder "C:\test". Is
> > that correct? The code also assumes that the format of your weekly
> > report is identical to the format of the daily reports. Is that the
> > case?
>
> > On Oct 1, 12:43 am, suyog_linux wrote:
>
> > > Hi,
>
> > > I am trying to append multiple excel workbooks into. Basically to
> > > merge daily reports into a weekly one.
>
> > > I found the code below somewhere on internet but it is not working for
> > > me.
>
> > > Can someone please look and let me know how I need to change this in
> > > order to make it work ?
>
> > > Sub AppendData()
>
> > > Dim fso As Scripting.FileSystemObject
> > > Dim folder As Scripting.folder
> > > Dim file As Scripting.file
> > > Dim rngEntry As Range
> > > Set rngEntry = Range("a1")
> > > Set fso = New FileSystemObject
> > > Set folder = fso.GetFolder("C:\test\")
> > > Dim wbkMaster As Workbook
> > > Dim shtMaster As Worksheet
> > > Dim rngMaster As Range
> > > Dim wbkData As Workbook
> > > Dim shtData As Worksheet
> > > Dim rngData As Range
>
> > > ' change path and file name to suit
> > > Set wbkData = Workbooks.Open(path)
> > > Set shtMaster = wbkMaster.Worksheets(1)
>
> > > For Each file In folder.Files
> > > If LCase(Right(file.Name, 4)) = ".xls" Then
> > > Dim path As String
> > > path = file.path
> > > MsgBox path
> > > Set wbkData = Workbooks.Open(path)
> > > Set shtData = wbkData.Worksheets(1)
>
> > > ' get end of master
> > > Set rngMaster = shtMaster.Range("A65536").End(xlUp).Offset
> > > (1)
> > > 'MsgBox "Address = " & rngMaster.Address
> > > ' get all data cells
> > > Set rngData = shtData.Range("B28:F28")
> > > ' copy data across
> > > rngData.Copy rngMaster
>
> > > MsgBox "Appended " & rngData.Rows.Count & " rows of data
> > > to Master data", vbInformation
>
> > > ' simply close data
> > > wbkData.Close False
> > > ' save and close master
> > > wbkMaster.Close True
> > > End If
> > > Next file
> > > ' release objects
> > > Set rngData = Nothing
> > > Set shtData = Nothing
> > > Set wbkData = Nothing
> > > Set rngMaster = Nothing
> > > Set shtMaster = Nothing
> > > Set wbkMaster = Nothing
> > > End Sub- Hide quoted text -
>
> > - Show quoted
$$Excel-Macros$$ Re: Find & Clear
Alternatively, but still along the lines proposed by Paul Schreiner,
you could make your code more concise by using this approach which
avoids all the activating and selecting that will slow your code down
once you apply your code to larger ranges:
Dim searchRange As Range
Set searchRange = Columns("B:B")
Dim foundCell As Range
On Error Resume Next
Set foundCell = searchRange.Find("zz", searchRange.Cells
(Rows.Count, 1), xlFormulas, xlPart, xlByRows, xlNext, False, False)
If Not foundCell Is Nothing Then
foundCell.Clear
Range("D1").Select 'I would recommend to avoid this
statement for the reasons I mentioned above
End If
Please note that I changed the After:= cell to the last cell in the
search range. If you don't do this your code will ignore cell B1.
Hope this helped,
Rolf
On Sep 30, 5:15 pm, Tommy wrote:
> I am trying to find the first cell in a column containing the text
> "zz" and then clearing that cell and moving on to accomplish other
> things. When a cell is found with :zz" in it,
> all is well, but if there are no cells in the column that contain
> "zz", the macro stops running and generates an error. what do i have
> to do to correct that situation. below is the macro,
>
> Sub Find_zz()
> '
> ' Find_zz Macro
> ' Macro recorded 9/30/2009 by Tommy
> '
>
> '
> Columns("B:B").Select
> Selection.Find(What:="zz", After:=ActiveCell, LookIn:=xlFormulas,
> LookAt _
> :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
> False, SearchFormat:=False).Activate
> ActiveCell.Select
> ActiveCell.Clear
> Range("D1").Select
> End Sub
>
> Any help would be appreciated
>
> Thanks, Tommy
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Error 91 - Run Time
It seems to me that you skipped over the setting of the database
itself, i.e. the Set dbs = statement is missing. Therefore your VBA
code doesn't know what dbs is.
On Oct 1, 12:05 am, kuhrty wrote:
> I am trying to create a table in Access 2003 through VBA. I keep
> getting an error 91 where the Object variable or With Block is not
> set. If appears like I have all the right code to create this table
> but I can't get beyond the Set statement.
>
> Thanks in advance.
>
> Dim dbs As Database
> Dim tdf As TableDef
>
> Set tdf = dbs.CreateTableDef("tbl_OPRiskMK")
> tdf.Fields.Append tdf.CreateField("Region", dbText)
> tdf.Fields.Append tdf.CreateField("Sector", dbText)
> tdf.Fields.Append tdf.CreateField("Business", dbText)
> tdf.Fields.Append tdf.CreateField("EventCategory", dbText)
> tdf.Fields.Append tdf.CreateField("Qtr_Yr", dbText)
> tdf.Fields.Append tdf.CreateField("NetAmount_USD", dbDouble)
> tdf.Fields.Append tdf.CreateField("EventRank", dbByte)
> tdf.Fields.Append tdf.CreateField("BusinessRank", dbByte)
> dbs.TableDefs.Append tdf
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Appending multiple excel files into one
You need to be a little bit more specific in HOW this code is not
working for you. Obviously it is critical that you specify the range
of cells you want to copy (in the code you showed it is set to
"B28:F28". Is that correct? Also the code assumed that your daily
reports contain the date you want to copy on the first worksheet. Is
that the case? The code also assumes that all your daily reports, and
only your daily reports (!), are located in the folder "C:\test". Is
that correct? The code also assumes that the format of your weekly
report is identical to the format of the daily reports. Is that the
case?
On Oct 1, 12:43 am, suyog_linux wrote:
> Hi,
>
> I am trying to append multiple excel workbooks into. Basically to
> merge daily reports into a weekly one.
>
> I found the code below somewhere on internet but it is not working for
> me.
>
> Can someone please look and let me know how I need to change this in
> order to make it work ?
>
> Sub AppendData()
>
> Dim fso As Scripting.FileSystemObject
> Dim folder As Scripting.folder
> Dim file As Scripting.file
> Dim rngEntry As Range
> Set rngEntry = Range("a1")
> Set fso = New FileSystemObject
> Set folder = fso.GetFolder("C:\test\")
> Dim wbkMaster As Workbook
> Dim shtMaster As Worksheet
> Dim rngMaster As Range
> Dim wbkData As Workbook
> Dim shtData As Worksheet
> Dim rngData As Range
>
> ' change path and file name to suit
> Set wbkData = Workbooks.Open(path)
> Set shtMaster = wbkMaster.Worksheets(1)
>
> For Each file In folder.Files
> If LCase(Right(file.Name, 4)) = ".xls" Then
> Dim path As String
> path = file.path
> MsgBox path
> Set wbkData = Workbooks.Open(path)
> Set shtData = wbkData.Worksheets(1)
>
> ' get end of master
> Set rngMaster = shtMaster.Range("A65536").End(xlUp).Offset
> (1)
> 'MsgBox "Address = " & rngMaster.Address
> ' get all data cells
> Set rngData = shtData.Range("B28:F28")
> ' copy data across
> rngData.Copy rngMaster
>
> MsgBox "Appended " & rngData.Rows.Count & " rows of data
> to Master data", vbInformation
>
> ' simply close data
> wbkData.Close False
> ' save and close master
> wbkMaster.Close True
> End If
> Next file
> ' release objects
> Set rngData = Nothing
> Set shtData = Nothing
> Set wbkData = Nothing
> Set rngMaster = Nothing
> Set shtMaster = Nothing
> Set wbkMaster = Nothing
> End Sub
--~--~-~--~~~---~--~~
--
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---
