Re: $$Excel-Macros$$ How to disable all macros initially when i open the workbook
You probably have your macro in the ThisWorkbook workbook open or a sheet module activate. Move the body of the macro to a regular Sub linked to your command button or a shape. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Mangesh Dayne Sent: Tuesday, June 05, 2012 9:52 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ How to disable all macros initially when i open the workbook Hi Friends, I am working with the file containing macros. I want all macros should be disable when i opened the file and should be enable when i click on command button in file. My intention is to stop executing the macro just after opening the file. It should be run only when i click on command button. Please help. MAnGesh -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Extract Names from different sheet into one sheet
Why not use a macro to gather all and then do a unique on the gathered list? Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: hilary lomotey Sent: Wednesday, June 06, 2012 10:39 AM To: excel-macros Subject: $$Excel-Macros$$ Extract Names from different sheet into one sheet Hello Experts, i have data of names sitting in sheet 2 to sheet 4 in the attached. is it possible to have a formula to extract all these names into one column in sheet 1. thanks -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
I would have written it a bit more efficiently but what it is doing is copying
all of current region of each sheet to a new sheet.
What do you want to do???
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: David Grugeon
Sent: Wednesday, June 06, 2012 5:49 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
You can use a formula like
=sum(Sheet1:sheet17!A1)
and it will add all the cells A1 in the sheets called Sheet1, Sheet17 and all
sheets physically between them.
On 7 June 2012 05:52, Greg wrote:
Hello,
I am trying to process an Excel macro I found online from the following
weblink:
http://excel.tips.net/T003005_Condensing_Multiple_Worksheets_Into_One.html
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"
' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets
' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End SubAccording to the description, I can either use the Excel "Consolidate"
tool or the above macro. Unfortunately, I didn't have much luck with the
"Consolidate" tool because I don't understand how to reference each of the
worksheet ranges.
Once I run the macro, it appears to be doing something because I can see it
trans-versing through each worksheet. When the macro has completed, the first
sheet in the workbook is not named Combined, and it contains no data from the
other worksheets.
Thanks in advance for any assistance you can provide.
Kind regards,
Greg
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
mailto:excel-macros%2bunsubscr...@googlegroups.com
--
David Grugeon
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a
Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
I adhere to the KISS principle so here is a simple macro.
If you want the second row change to rows(2)
Option Explicit
Sub copytoprowofallsheets()
'fire from the First sheet(sheet to far left)
Dim i As Long
For i = 2 To Sheets.Count
Sheets(i).Rows(1).Copy Cells(Rows.Count, 1).End(xlUp)(2)
Next i
End Sub
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: Greg
Sent: Wednesday, June 06, 2012 6:09 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
Hi Don,
Thanks for your reply. I want to do exactly what it says. I have identically
structured data in each worksheet, and wish to combine the multiple worksheets
into a single, large worksheet. The names of each worksheet is different.
However, they all have content in the first row. I wish to copy the first row
content one after the other into one large worksheet.
Regards,
Greg
On Wednesday, June 6, 2012 6:59:27 PM UTC-4, Don Guillett wrote:
I would have written it a bit more efficiently but what it is doing is
copying all of current region of each sheet to a new sheet.
What do you want to do???
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: David Grugeon
Sent: Wednesday, June 06, 2012 5:49 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
You can use a formula like
=sum(Sheet1:sheet17!A1)
and it will add all the cells A1 in the sheets called Sheet1, Sheet17 and all
sheets physically between them.
On 7 June 2012 05:52, Greg wrote:
Hello,
I am trying to process an Excel macro I found online from the following
weblink:
http://excel.tips.net/T003005_Condensing_Multiple_Worksheets_Into_One.html
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"
' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets
' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End SubAccording to the description, I can either use the Excel "Consolidate"
tool or the above macro. Unfortunately, I didn't have much luck with the
"Consolidate" tool because I don't understand how to reference each of the
worksheet ranges.
Once I run the macro, it appears to be doing something because I can see it
trans-versing through each worksheet. When the macro has completed, the first
sheet in the workbook is not named Combined, and it contains no data from the
other worksheets.
Thanks in advance for any assistance you can provide.
Kind regards,
Greg
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
mailto:excel-macros%2bunsubscr...@googlegroups.com
--
David Grugeon
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
I understood you wanted the first or second row only. If you want all but the
header row.
Option Explicit
Sub copytoprowofallsheets()
'fire from the First sheet(sheet to far left)
Dim i As Long
For i = 2 To Sheets.Count
Sheets(i).UsedRange.Offset(1).Copy Cells(Rows.Count, 1).End(xlUp)(2)
Next i
End Sub
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: Greg
Sent: Wednesday, June 06, 2012 6:09 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
Hi Don,
Thanks for your reply. I want to do exactly what it says. I have identically
structured data in each worksheet, and wish to combine the multiple worksheets
into a single, large worksheet. The names of each worksheet is different.
However, they all have content in the first row. I wish to copy the first row
content one after the other into one large worksheet.
Regards,
Greg
On Wednesday, June 6, 2012 6:59:27 PM UTC-4, Don Guillett wrote:
I would have written it a bit more efficiently but what it is doing is
copying all of current region of each sheet to a new sheet.
What do you want to do???
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: David Grugeon
Sent: Wednesday, June 06, 2012 5:49 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
You can use a formula like
=sum(Sheet1:sheet17!A1)
and it will add all the cells A1 in the sheets called Sheet1, Sheet17 and all
sheets physically between them.
On 7 June 2012 05:52, Greg wrote:
Hello,
I am trying to process an Excel macro I found online from the following
weblink:
http://excel.tips.net/T003005_Condensing_Multiple_Worksheets_Into_One.html
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"
' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets
' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End SubAccording to the description, I can either use the Excel "Consolidate"
tool or the above macro. Unfortunately, I didn't have much luck with the
"Consolidate" tool because I don't understand how to reference each of the
worksheet ranges.
Once I run the macro, it appears to be doing something because I can see it
trans-versing through each worksheet. When the macro has completed, the first
sheet in the workbook is not named Combined, and it contains no data from the
other worksheets.
Thanks in advance for any assistance you can provide.
Kind regards,
Greg
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
mailto:excel-macros%2bunsubscr...@googlegroups.com
--
David Grugeon
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
For the entire used range simply remove the .offset(1)
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: Greg
Sent: Thursday, June 07, 2012 1:54 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
Hi Don,
This macro works and accomplishes what I need. And I agree with your KISS
principle whenever possible.
BTW, if I want to use this same macro and keep the headers what must I change?
Thanks,
Greg
On Thursday, June 7, 2012 12:52:25 PM UTC-4, Don Guillett wrote:
I understood you wanted the first or second row only. If you want all but the
header row.
Option Explicit
Sub copytoprowofallsheets()
'fire from the First sheet(sheet to far left)
Dim i As Long
For i = 2 To Sheets.Count
Sheets(i).UsedRange.Offset(1).Copy Cells(Rows.Count, 1).End(xlUp)(2)
Next i
End Sub
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: Greg
Sent: Wednesday, June 06, 2012 6:09 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
Hi Don,
Thanks for your reply. I want to do exactly what it says. I have identically
structured data in each worksheet, and wish to combine the multiple worksheets
into a single, large worksheet. The names of each worksheet is different.
However, they all have content in the first row. I wish to copy the first row
content one after the other into one large worksheet.
Regards,
Greg
On Wednesday, June 6, 2012 6:59:27 PM UTC-4, Don Guillett wrote:
I would have written it a bit more efficiently but what it is doing is
copying all of current region of each sheet to a new sheet.
What do you want to do???
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: David Grugeon
Sent: Wednesday, June 06, 2012 5:49 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
You can use a formula like
=sum(Sheet1:sheet17!A1)
and it will add all the cells A1 in the sheets called Sheet1, Sheet17 and
all sheets physically between them.
On 7 June 2012 05:52, Greg wrote:
Hello,
I am trying to process an Excel macro I found online from the following
weblink:
http://excel.tips.net/T003005_Condensing_Multiple_Worksheets_Into_One.html
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"
' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets
' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End SubAccording to the description, I can either use the Excel "Consolidate"
tool or the above macro. Unfortunately, I didn't have much luck with the
"Consolidate" tool because I don't understand how to reference each of the
worksheet ranges.
Once I run the macro, it appears to be doing something because I can see
it trans-versing through each worksheet. When the macro has completed, the
first sheet in the workbook is not named Combined, and it contains no data from
the other worksheets.
Thanks in advance for any assistance you can provide.
Kind regards,
Greg
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
mailto:excel-macros%2bunsubscr...@googlegroups.com
--
David Grugeon
--
FORUM RULES (986+ members already BANNED
Re: $$Excel-Macros$$ How to add combo box in a sheet.....Need Help.....
Create combobox1 in sheet1 and then put this code in the SHEET module Private Sub Worksheet_Activate() Sheet1.ComboBox1.Clear Dim sh As Worksheet For Each sh In Worksheets Sheet1.ComboBox1.AddItem (sh.Name) Next End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Mangesh Dayne Sent: Friday, June 08, 2012 6:17 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ How to add combo box in a sheet.Need Help. Hi Friends, I have to add combo box in a sheet that will contain all sheets name and when i select the any sheet name in combo box, it will activate in the workbook, means I can able to see the contents of sheet simply by selecting the sheet from combo box list. Moreover, combo box should automatically update the sheet name, means if i deleted any sheet from workbook the entry from the combo box should also be deleted. Thanks a lot. Please help Mangesh -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Edit Cell
Unlock all you want to allow lock that one protect sheet Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Deba Ranjan Sent: Friday, June 08, 2012 9:07 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Edit Cell Dear Its is possible to Lock a particular cell only, I mean user cant able to edit this cell only, rest they can able to edit. Even the E3 cell should not be able to edit, Rest can be edit. Please see the attached. thank you. Thanks & Regards, Deba Ranjan P -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Introduce Yourself !!
BBA University of Texas 1959, Graduate studies at UT law school and NYU. Ex US Air Force Officer. I am a retired Regional Manager for ING and held a stock brokers series 7 license and insurance license. To keep myself busy, I am an independent Excel Developer for projects large and small and Excel MVP for years on other forums For many years I was also a road race car driver holding an International FIA racing license and race driving instructor. driving SCCA EP MGB, Formula Ford, and Formula 2 open wheel single seat race cars in the US and Mexico. I live in beautiful Austin Texas on the top of a hill with a view of the lake and at age 76 I do only what I want to do. It is really nice Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Ayush Jain Sent: Sunday, June 10, 2012 11:47 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Introduce Yourself !! Dear all, Good to know you all and feels proud to be part of this forum. Let me introduce myself. I am Ayush Jain from Delhi, India. Presently working as senior analyst in Ernst & Young and my work includes bulding Excel & Access based models for clients for analysis, reporting, planning etc. I started this forum 5 years back with the aim to provide quality solutions for excel and vba queries & I am quite happy to see what we are today. This is one of the largest forum which is spam free and always buzzing with activity. I have received Microsoft MVP award in 2010 & 2011 for my voluntry contribution to various communities in excel. For more details:-Please visit my MVP profile I really appreciate the forum members for their active paticipation and voluntry help to others. If you have any feedback, click here. Keep introducing yourself!! Many Thanks. -Ayush Jain Group Manager / Microsoft MVP On Sun, Jun 10, 2012 at 2:23 PM, David Grugeon wrote: Hi all 'Excel'lent people This is David Grugeon from Brisbane Australia. I work as an accountant/consultant for local governments - mainly one in Thargomindah which is 1000 Km west of Brisbane and one in Thursday Island which is 2000 Km North. I use excel all the time for work and am currently working on the Budget for Bulloo Shire Council (Thargomindah) with about ten interlinked workbooks with many sheets each. I use VBA a lot where it saves me time. I think I have quite good Excel skills developed by helping others on groups like this one. If you try to answer the questions you learn a lot which you can use in the future. If oyu are interested Bulloo Shire council has an area of about 83000 sq Km (about half the size of England) and a population of about 600. Thursday island is the administrative centre for four islands (Thursday, Horn, Prince of Wales, and Goods Islands) in the Torres Strait between Australia and Papua New Guinea. I am 69 in a few days and lived in England until I was 47. I hope I am able to help you guys and I am sure I will learn a lot as well. Best regards David Grugeon On 10 June 2012 16:47, wrote: Hi Excel Buddies! My name is Pooja Sharma, I'm from New Delhi. Working as Lead into Financial Closing and Reporting with a Gurgaon based MNC. Apart from my work, I like to travel, enjoy most forms of music, playing Table Tennis, Gardening. Excel plays major supportive role in accounting and reporting. I am really feel good to be a part of this forum. Regards, Pooja Sharma Sent from BlackBerry® on Airtel From: amar.gur...@gmail.com Sender: excel-macros@googlegroups.com Date: Sun, 10 Jun 2012 06:38:09 + To: ReplyTo: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Introduce Yourself !! Hi Everyone, I am Amar, an insurance professional working in non-life insurance. I love interacting with people, working in excel and food with different specialities. I am from Mumbai. Regards, Amar Sent on my BlackBerry® from Vodafone From: Rajesh Janardanan Sender: excel-macros@googlegroups.com Date: Sun, 10 Jun 2012 10:31:29 +0400 To: ReplyTo: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Introduce Yourself !! Hello All, I am Rajesh Janardhanan , a Chartered Accountant working in the oil field service industry, aged 34. I live in UAE. interest includes travelling , movies etc and Excel and presentations are a necessity for business for me and this forum has helped me many time's in last many year's. Thanks to all. Best regards Rajesh On Sun, Jun 10, 2012 at 10:27 AM, wrote: Hi everyone, My name is Joseph. I live in Mumbai, age 34 years. I work as a Presentation Specialist. I work only on PowerPoint, Excel and Word. Thanks an
Re: $$Excel-Macros$$ Date format change by running the macro
Try
Sub FormulaToEndOffset1SAS()
Dim lr As Long
With ActiveCell
lr = Cells(.Row, .Column).End(xlDown).Row
With Range(Cells(.Row, .Column + 1), Cells(lr, .Column + 1))
.Formula = _
"=IF(ISERR(DATE(LEFT(A" & .Row & ",4),MID(A" & .Row & ",5,2)," & _
"RIGHT(A" & .Row & ",2))),,DATE(LEFT(A" & .Row & ",4), " & _
"MID(A" & .Row & ",5,2),RIGHT(A" & .Row & ",2)))"
.NumberFormat = "dd/mmm/"
.Value = .Value
End With
End With
End Sub
‘
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: ankur
Sent: Tuesday, June 12, 2012 5:50 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Date format change by running the macro
hi Experts
i use this code to solve the query with slight modification,i want date format
in adjacent cell..
i used Macro for this..
But problem is ,this code can run upto specific no. of row i.e 475
i want to be it a dynamic ...i.e it should work with based on selection of
range onlylike solution of Krishna Sir is doing..what modification is
needed in my codes
File is attached for your reference
thanks in advance
Sub ChgMyFormat()
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERR(DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2))),,DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2)))"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A475")
ActiveCell.Range("A1:A475").Select
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Selection.NumberFormat = "dd/mmm/"
ActiveCell.Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Application.CutCopyMode = False
ActiveCell.Offset(0, -1).Range("A1").Select
End Sub
Regards:
CMA Ankur Pandey
On Tue, Jun 12, 2012 at 2:32 PM, Ashish Bhalara
wrote:
Thank you Mr.Krish it is exactly what i want.
On Tue, Jun 12, 2012 at 2:21 PM, ankur wrote:
Hi Ahmed
Sorry for this ...Now i had changed the settings.Thanks for correcting
me..
Regards:
CMA Ankur Pandey
On Tue, Jun 12, 2012 at 2:20 PM, ankur wrote:
Hi Ahmed
Sorry for this ...Now i had changed the settings.Thanks for
correcting me..
Regards:
CMA Ankur Pandey
On Tue, Jun 12, 2012 at 2:11 PM, Ahmed Honest
wrote:
Dear Ankur,
Please if you don't mind will you make your font sytle and size as a
simple Or plain one. It looks too ODD.
Thanks for your understanding.
Regards,
On Tue, Jun 12, 2012 at 11:38 AM, ankur wrote:
HI ASHISH
I TRIED THIS CODE FOR YOUR SOLUTION ,BUT IT IS NOT SO GOODbecause
i dont know how to stop the code after the blank cellsExperts will guide me
in this issue how to remove this flawfile is attached for your reference
Sub chgDateFormat()
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2))"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A500")
ActiveCell.Range("A1:A1").Select
End Sub
Regards:
CMA Ankur Pandey
Visit My Site
On Tue, Jun 12, 2012 at 1:04 PM, Ashish Bhalara
wrote:
Dear sir,
I have data in which date written in 20120412 format and i want to
make macro to which transfer the format to 12-Apr-2012 by running the macro on
selection range. Please help me in my below code.
Sub Macro1()
Dim selct As Range
selct = Range.Select
Range = WorksheetFunction.TEXT(TEXT(selct, "-00-00"),
"DD-MMM-")
End Sub
Thanks & regards.
Ashish Bhalara
9624111822
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like
Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any
security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this
forum in signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook.
Forum owners and members are not responsible for any loss.
-
Re: $$Excel-Macros$$ Sheet Requirements
Forgive me but as I recall you have been a part of this group for awhile. If so, you should have learned enough to do this yourself. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Deba Ranjan Sent: Tuesday, June 12, 2012 6:45 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Sheet Requirements Dear Experts, i have attached a file which contains many sheets. in every sheets there contains data. i want a button which on clicking the whole sheet data consolidated. Please find the attached. thanks... Thanks & Regards, Deba Ranjan P -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Date format change by running the macro
Fully tested on provided file.
The lr is the last populated row in column A and was based on the file
presented. It is designed so that whatever cell you select in col A then column
B ONLY to the lr will be filled with the formula and the formatting. If not,
send your file personally to me...
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: ankur
Sent: Tuesday, June 12, 2012 11:22 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Date format change by running the macro
Thanks Don Sir
Very Nice Code
But it is not limited to Selected Rangefull column is converted to desired
format...
is there any way to limit the code upto selected range only
Regards:
CMA Ankur Pandey
On Tue, Jun 12, 2012 at 6:30 PM, dguillett1 wrote:
Try
Sub FormulaToEndOffset1SAS()
Dim lr As Long
With ActiveCell
lr = Cells(.Row, .Column).End(xlDown).Row
With Range(Cells(.Row, .Column + 1), Cells(lr, .Column + 1))
.Formula = _
"=IF(ISERR(DATE(LEFT(A" & .Row & ",4),MID(A" & .Row & ",5,2)," & _
"RIGHT(A" & .Row & ",2))),"""",DATE(LEFT(A" & .Row & ",4), " & _
"MID(A" & .Row & ",5,2),RIGHT(A" & .Row & ",2)))"
.NumberFormat = "dd/mmm/"
.Value = .Value
End With
End With
End Sub
‘
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: ankur
Sent: Tuesday, June 12, 2012 5:50 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Date format change by running the macro
hi Experts
i use this code to solve the query with slight modification,i want date
format in adjacent cell..
i used Macro for this..
But problem is ,this code can run upto specific no. of row i.e 475
i want to be it a dynamic ...i.e it should work with based on selection of
range onlylike solution of Krishna Sir is doing..what modification is
needed in my codes
File is attached for your reference
thanks in advance
Sub ChgMyFormat()
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERR(DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2))),"""",DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2)))"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A475")
ActiveCell.Range("A1:A475").Select
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Selection.NumberFormat = "dd/mmm/"
ActiveCell.Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Application.CutCopyMode = False
ActiveCell.Offset(0, -1).Range("A1").Select
End Sub
Regards:
CMA Ankur Pandey
On Tue, Jun 12, 2012 at 2:32 PM, Ashish Bhalara
wrote:
Thank you Mr.Krish it is exactly what i want.
On Tue, Jun 12, 2012 at 2:21 PM, ankur wrote:
Hi Ahmed
Sorry for this ...Now i had changed the settings.Thanks for
correcting me..
Regards:
CMA Ankur Pandey
On Tue, Jun 12, 2012 at 2:20 PM, ankur wrote:
Hi Ahmed
Sorry for this ...Now i had changed the settings.Thanks for
correcting me..
Regards:
CMA Ankur Pandey
On Tue, Jun 12, 2012 at 2:11 PM, Ahmed Honest
wrote:
Dear Ankur,
Please if you don't mind will you make your font sytle and size as a
simple Or plain one. It looks too ODD.
Thanks for your understanding.
Regards,
On Tue, Jun 12, 2012 at 11:38 AM, ankur
wrote:
HI ASHISH
I TRIED THIS CODE FOR YOUR SOLUTION ,BUT IT IS NOT SO
GOODbecause i dont know how to stop the code after the blank
cellsExperts will guide me in this issue how to remove this flawfile is
attached for your reference
Sub chgDateFormat()
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2))"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A500")
ActiveCell.Range("A1:A1").Select
End Sub
Regards:
CMA Ankur Pandey
Visit My Site
On Tue, Jun 12, 2012 at 1:04 PM, Ashish Bhalara
wrote:
Dear sir,
I have data in which date written in 20120412 format and i want
to make macro to which transfer the for
Re: $$Excel-Macros$$ Sheet Requirements
sub cs() ‘assumes your master sheet is the FIRST sheet dim I as long for I =2 to sheets.count sheets(i).usedrange.copy sheets(1).cells(rows.count,1).end(xlup)(2) next i end sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Deba Ranjan Sent: Tuesday, June 12, 2012 11:43 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Sheet Requirements Thanks... Thanks & Regards, Deba Ranjan P On Tue, Jun 12, 2012 at 9:43 PM, Mangesh Vimay wrote: Hi Dear, It happens with everybody specially when someone tries to learn new concepts of IT or any other topic. So keep going and this group is good to help you. MangeSH On Tue, Jun 12, 2012 at 6:45 PM, Deba Ranjan wrote: Thanks. i love excel very much, Actually i keep on trying the coding But really i am so sorry that i really don't have this Idea. i purchased books for VBA macros and Excel, and i am trying my best to do so. Actually i am very new to this concepts (Means VBA coding). And even i am trying to learn from this group as well. I adopted to learn one by one. Let me tell you honestly, i have no system at my home. And what ever i learn, i learnt at Office but that too also i have many few times to learnt myself. So its my drawback. i hope i would able very few things a day, Any way thanks for suggesting that. i will keep trying. i have very confusion in coding when i go trough the books. Like String, long etc. Thanks & Regards, Deba Ranjan P On Tue, Jun 12, 2012 at 6:34 PM, dguillett1 wrote: Forgive me but as I recall you have been a part of this group for awhile. If so, you should have learned enough to do this yourself. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Deba Ranjan Sent: Tuesday, June 12, 2012 6:45 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Sheet Requirements Dear Experts, i have attached a file which contains many sheets. in every sheets there contains data. i want a button which on clicking the whole sheet data consolidated. Please find the attached. thanks... Thanks & Regards, Deba Ranjan P -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to mailto:excel-macros%2bunsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to mailto:excel-macros%2bunsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure.
Re: $$Excel-Macros$$ What tools are most suitable to populate existing database with this tools?
Provide a file with complete explanation. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: alvin567 Sent: Wednesday, June 13, 2012 4:02 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ What tools are most suitable to populate existing database with this tools? Due to certain requirements, we need to be able to populate a relational database with our portfolio data. From this we would like to extract certain reports that provide us with insight about the credit performance of the individual positions within the portfolio. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Sheet Requirements
Sorry, I deleted it. You really need to try harder Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Deba Ranjan Sent: Wednesday, June 13, 2012 7:13 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Sheet Requirements Thanks... Don bro, But still i have confusion on the coding. can you please share with the attachment ? i hope i will get more ideas on attached. thanks for that. Thanks & Regards, Deba Ranjan P On Wed, Jun 13, 2012 at 5:30 PM, dguillett1 wrote: sub cs() ‘assumes your master sheet is the FIRST sheet dim I as long for I =2 to sheets.count sheets(i).usedrange.copy sheets(1).cells(rows.count,1).end(xlup)(2) next i end sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Deba Ranjan Sent: Tuesday, June 12, 2012 11:43 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Sheet Requirements Thanks... Thanks & Regards, Deba Ranjan P On Tue, Jun 12, 2012 at 9:43 PM, Mangesh Vimay wrote: Hi Dear, It happens with everybody specially when someone tries to learn new concepts of IT or any other topic. So keep going and this group is good to help you. MangeSH On Tue, Jun 12, 2012 at 6:45 PM, Deba Ranjan wrote: Thanks. i love excel very much, Actually i keep on trying the coding But really i am so sorry that i really don't have this Idea. i purchased books for VBA macros and Excel, and i am trying my best to do so. Actually i am very new to this concepts (Means VBA coding). And even i am trying to learn from this group as well. I adopted to learn one by one. Let me tell you honestly, i have no system at my home. And what ever i learn, i learnt at Office but that too also i have many few times to learnt myself. So its my drawback. i hope i would able very few things a day, Any way thanks for suggesting that. i will keep trying. i have very confusion in coding when i go trough the books. Like String, long etc. Thanks & Regards, Deba Ranjan P On Tue, Jun 12, 2012 at 6:34 PM, dguillett1 wrote: Forgive me but as I recall you have been a part of this group for awhile. If so, you should have learned enough to do this yourself. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Deba Ranjan Sent: Tuesday, June 12, 2012 6:45 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Sheet Requirements Dear Experts, i have attached a file which contains many sheets. in every sheets there contains data. i want a button which on clicking the whole sheet data consolidated. Please find the attached. thanks... Thanks & Regards, Deba Ranjan P -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to mailto:excel-macros%2bunsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum
Re: $$Excel-Macros$$ No. of years and months
Which months in the 200? Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Ahmed Honest Sent: Wednesday, June 13, 2012 7:56 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ No. of years and months Hi Experts, Based on a given number I need the result to be something like below: Given Number : 200 Answer should be 16 Years 8 Months. Is there any way of doing it in Ms Excel by Formula? Thanks, Ahmed Bawazir -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ No. of years and months
A look in the help index for INT may be of use to you Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Ahmed Honest Sent: Wednesday, June 13, 2012 8:49 AM To: excel-macros@googlegroups.com ; talk2mar...@gmail.com Subject: Re: $$Excel-Macros$$ No. of years and months Hi Maries, Can you explain me what is this A1-INT(A1/12)*12 does, I mean the math side explanation. Please explain and Tons of thanks for your solution, it's working as I need. Regards Ahmed Bawazir On Wed, Jun 13, 2012 at 4:02 PM, Maries wrote: HI, Assume data entries in A1, Try below formula, =INT(A1/12)&" Years "&A1-INT(A1/12)*12& " Months" On Wed, Jun 13, 2012 at 5:56 AM, Ahmed Honest wrote: Hi Experts, Based on a given number I need the result to be something like below: Given Number : 200 Answer should be 16 Years 8 Months. Is there any way of doing it in Ms Excel by Formula? Thanks, Ahmed Bawazir -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to mailto:excel-macros%2bunsubscr...@googlegroups.com -- MARIES Excel Inspiration -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to mailto:excel-macros%2bunsubscr...@googlegroups.com -- Ahmed Bawazir احمد باوزير -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ vba - run time error 9 out of range
I would have done it differently but add an on error
And, do you really want to do this with each calculation??. I don’t think so...
'On Error Resume Next
For Each c In rng
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: cardak
Sent: Wednesday, June 13, 2012 2:20 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ vba - run time error 9 out of range
hi all,
im new to VBA and trying run below, when i run the code excel throws run time 9
error message, can you please help on this error
Thanks
Private Sub Worksheet_Calculate()
Dim rng As Range, c As Range
Dim ws As Worksheet
Dim lastRow As Long
Dim x As Variant
Set ws = Worksheets("Component List")
lastRow = ws.Cells(ws.Cells.Rows.Count, 1).End(xlUp).Row
Set rng = ws.Range("A5:A" & lastRow)
For Each c In rng
For i = 1 To Sheets.Count
If c.Offset(0, 1).Value = "Yes" And Trim(c.Value) = Sheets(i).Name Then
Sheets(Trim(c.Value)).Visible = xlSheetVisible
Else
Sheets(Trim(c.Value)).Visible = xlSheetHidden
End If
Next i
Next c
End Sub --
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
Use this without having to use selections
Option Explicit
Sub CombineSheetsSAS()
Dim i As Long
Sheets.Add before:=Sheets(1)
ActiveSheet.Name = "Combined"
Sheets(2).Rows(1).Copy Range("a1")
For i = 2 To Sheets.Count
Sheets(i).UsedRange.Offset(1).Copy Cells(Rows.Count, 1).End(xlUp)(2)
Next i
End Sub
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: R Swarup
Sent: Friday, June 15, 2012 9:23 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
Dear Customer, You have consumed 100% of your total quota allotted on Reliance
Netconnect+. Now speed will be up to 144Kbps till next bill cycle.
On Jun 7, 2012 1:22 AM, "Greg" wrote:
>
>
> Hello,
>
> I am
Hello,
I am trying to process an Excel macro I found online from the following
weblink:
http://excel.tips.net/T003005_Condensing_Multiple_Worksheets_Into_One.html
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"
' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets
' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End SubAccording to the description, I can either use the Excel "Consolidate"
tool or the above macro. Unfortunately, I didn't have much luck with the
"Consolidate" tool because I don't understand how to reference each of the
worksheet ranges.
Once I run the macro, it appears to be doing something because I can see it
trans-versing through each worksheet. When the macro has completed, the first
sheet in the workbook is not named Combined, and it contains no data from the
other worksheets.
Thanks in advance for any assistance you can provide.
Kind regards,
Greg
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
mailto:excel-macros%2bunsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
-
Re: $$Excel-Macros$$ Re: change content of cell across multiple worksheets
Provide a file and a complete explanation.
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: David Grugeon
Sent: Saturday, June 16, 2012 2:32 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Re: change content of cell across multiple
worksheets
Hi Jeanette
Ah! I expect we have some sort of misunderstanding.
Are the sheets concerned actually called "A" to "Z". If not we may have to
change the macro to run on selected sheets.
Also is the information you are looking for actually in column O?
How is the information formatted? Is it as text or as a number?
Do you realise that the macro leaves the found(old) information as it was and
puts the new information into Column S on the same row.
If you feel you want to send your workbook to me (david at grugeon dot com dot
au) I will see what the problem is but it is entirely up to you if you want to
do this as I understand it may have confidential information which you would
not feel like giving to a stranger.
Best regards
David Grugeon
On 16 June 2012 16:55, JLO wrote:
Hi David
Thank you very much for your quick response! I have copied your code into my
module and run it a few times, but nothing happens (apart from the loop bit
doesn't go crazy like mine did!). I selected worksheets A to Z and did a
FindAll afterwards to check the result, but none of the data has changed.
Please revisit the code you very kindly provided, because I haven't got a clue
whether or not there is anything vital missing!!! Thanks for your help and
such quick response.
On Saturday, June 16, 2012 1:18:41 AM UTC+1, JLO wrote:
Could really use some help with this. I have worksheets A to Z. I'm
searching for data the user has entered via an input box, then changing the
data in cells over to the right with the data the user enters via another input
box. This works perfectly on active sheet. What I need is for the exact same
thing to happen across worksheets A to Z (there being further worksheets that
don't get changed), so some sort of loop is needed. I've tried looping, but
with disastrous results!! I'd really appreciate a solution - I cribbed the
example from elsewhere and tailored it, but I'm stumped to get it to work
across the other sheets. Thanks in advance for all your help! Here's my code:
With ActiveSheet.Range("O:O")
Dim mySearch As String
Dim myReplaceValue
mySearch = InputBox("Enter the Box Number requiring relocation: ")
myReplaceValue = InputBox("What is the new location? ")
Set c = .Find(mySearch, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Offset(, 4) = myReplaceValue
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
mailto:excel-macros%2bunsubscr...@googlegroups.com
--
David Grugeon
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Pl
Re: $$Excel-Macros$$ change content of cell across multiple worksheets
I’m not sure what you are trying to do but tested finding 1000 in column O and
putting 1200 in the cell 4 columns to the right
Assuming the code provided does work as desired, try this to use findnext in
each sheet/
Sub tryit()
Dim ws As Worksheet
Dim mySearch As String
Dim myReplaceValue
Dim c As Range
Dim firstaddress As String
mySearch = InputBox("Enter the Box Number requiring relocation: ")
myReplaceValue = InputBox("What is the new location? ")
For Each ws In Worksheets
With ws.Columns("o")
Set c = .Find(mySearch, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Offset(, 3) = myReplaceValue
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
Next ws
End Sub
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: JLO
Sent: Friday, June 15, 2012 7:18 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ change content of cell across multiple worksheets
Could really use some help with this. I have worksheets A to Z. I'm searching
for data the user has entered via an input box, then changing the data in cells
over to the right with the data the user enters via another input box. This
works perfectly on active sheet. What I need is for the exact same thing to
happen across worksheets A to Z (there being further worksheets that don't get
changed), so some sort of loop is needed. I've tried looping, but with
disastrous results!! I'd really appreciate a solution - I cribbed the example
from elsewhere and tailored it, but I'm stumped to get it to work across the
other sheets. Thanks in advance for all your help! Here's my code:
With ActiveSheet.Range("O:O")
Dim mySearch As String
Dim myReplaceValue
mySearch = InputBox("Enter the Box Number requiring relocation: ")
myReplaceValue = InputBox("What is the new location? ")
Set c = .Find(mySearch, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Offset(, 4) = myReplaceValue
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ change content of cell across multiple worksheets
findNEXT should be faster. See my code.
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: JLO
Sent: Saturday, June 16, 2012 8:03 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ change content of cell across multiple worksheets
David, you're a Star! Thank you so much - didn't expect the counter addition -
very thoughtful of you. It works brilliantly!
Orange on its way...! :-)
On Saturday, June 16, 2012 12:05:30 PM UTC+1, David Grugeon wrote:
Final version including force uppercase input and counter so you know what it
has done
Completed with Ucase and Counter.
Sub UpdateCode()
Dim mySearch As String
Dim myReplaceValue As String
Dim ws As Worksheet
Dim c As Range
Dim Counter As Long
mySearch = UCase(InputBox("Enter the Box Number requiring relocation: "))
myReplaceValue = InputBox("What is the new location? ")
For Each ws In Worksheets
If Len(ws.Name) = 1 Then
For Each c In Intersect(ws.Range("O:O"), ws.UsedRange)
If c.Value = mySearch Then
c.Offset(0, 4) = myReplaceValue
Counter = Counter + 1
End If
Next c
End If
Next ws
MsgBox "Made " & Counter & " Replacements", vbOKOnly, "Completed"
End Sub
On 16 June 2012 12:12, David Grugeon wrote:
Sorry - hit the send button too quickly
Option Explicit
Sub UpdateCode()
Dim mySearch As String
Dim myReplaceValue As String
Dim ws As Worksheet
Dim c As Range
mySearch = InputBox("Enter the Box Number requiring relocation: ")
myReplaceValue = InputBox("What is the new location? ")
For Each ws In Worksheets
If Len(ws.Name) = 1 Then
For Each c In Intersect(ws.Range("O:O"), ws.UsedRange)
If c.Value = mySearch Then
c.Offset(0, 4) = myReplaceValue
End If
Next c
End If
Next ws
End Sub
On 16 June 2012 12:10, David Grugeon wrote:
Hi Jeanette
Try the following. It assumes that the sheet names of 1 character length
are the ones you want to work with. If this is not so you will need to change
the line
If Len(ws.Name) = 1 Then
to apply a more appropriate test.
On 16 June 2012 10:18, JLO wrote:
Could really use some help with this. I have worksheets A to Z. I'm
searching for data the user has entered via an input box, then changing the
data in cells over to the right with the data the user enters via another input
box. This works perfectly on active sheet. What I need is for the exact same
thing to happen across worksheets A to Z (there being further worksheets that
don't get changed), so some sort of loop is needed. I've tried looping, but
with disastrous results!! I'd really appreciate a solution - I cribbed the
example from elsewhere and tailored it, but I'm stumped to get it to work
across the other sheets. Thanks in advance for all your help! Here's my code:
With ActiveSheet.Range("O:O")
Dim mySearch As String
Dim myReplaceValue
mySearch = InputBox("Enter the Box Number requiring relocation: ")
myReplaceValue = InputBox("What is the new location? ")
Set c = .Find(mySearch, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Offset(, 4) = myReplaceValue
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook.
Forum owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
excel-macros+unsubscr...@googlegroups.com
--
David Grugeon
--
David Grugeon
--
David Grugeon
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or
Re: $$Excel-Macros$$ Find Narrow Cells & Appying Auto fit to only that cell
or ActiveSheet.UsedRange.Columns.AutoFit Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Sam Mathai Chacko Sent: Saturday, June 16, 2012 6:09 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Find Narrow Cells & Appying Auto fit to only that cell ActiveSheet.usedrange.entirecolumn.autofit Sam Mathai Chacko On Sat, Jun 16, 2012 at 4:28 PM, Rajan_Verma wrote: Select the Column and press ALT + O C A Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Prashant Pawle Sent: 16 June 2012 1:40 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Find Narrow Cells & Appying Auto fit to only that cell Dear Team, Please help of some macro to find Narrow Cells & Appying Auto fit to only that cell , sample sheet attached Regards, Prashant -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to mailto:excel-macros%2bunsubscr...@googlegroups.com -- Sam Mathai Chacko -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Find Narrow Cells & Appying Auto fit to only that cell
Sam, yours and mine both test OK Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Sam Mathai Chacko Sent: Saturday, June 16, 2012 9:11 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Find Narrow Cells & Appying Auto fit to only that cell Should be EntireColumns as I had posted Regards, Sam Mathai Chacko On Sat, Jun 16, 2012 at 7:29 PM, dguillett1 wrote: or ActiveSheet.UsedRange.Columns.AutoFit Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Sam Mathai Chacko Sent: Saturday, June 16, 2012 6:09 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Find Narrow Cells & Appying Auto fit to only that cell ActiveSheet.usedrange.entirecolumn.autofit Sam Mathai Chacko On Sat, Jun 16, 2012 at 4:28 PM, Rajan_Verma wrote: Select the Column and press ALT + O C A Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Prashant Pawle Sent: 16 June 2012 1:40 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Find Narrow Cells & Appying Auto fit to only that cell Dear Team, Please help of some macro to find Narrow Cells & Appying Auto fit to only that cell , sample sheet attached Regards, Prashant -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to mailto:excel-macros%2bunsubscr...@googlegroups.com -- Sam Mathai Chacko -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to mailto:excel-macros%2bunsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or l
Re: $$Excel-Macros$$ Need help!!! MACRO
Try
Sub FooterSAS()
selectcase InputBox("Enter 1=Confidental, 2=For Internal Use Only 3=Other")
Case Is = 1: x = "Confidental"
Case Is = 2: x = "For Internal Use Only"
Case Is = 3: x = "Restricted"
Case Else
End Select
For Each wk In Application.Workbooks
For Each sh In wk.Worksheets
'wk.Sheets(i).Select
sh.PageSetup.CenterFooter = x
Next
Next
MsgBox strResponse & " footer has been successfully added."
End Sub
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-Original Message-
From: jonnie...@gmail.com
Sent: Monday, June 18, 2012 4:10 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Need help!!! MACRO
Hi All,
I need help with macros. Attached (Macro for Footer) is the existing file
with macros for labeling an excel sheet, It asks for Yes or no for label the
sheet as Confidential or Internal Use Only.
I need your help to include 1 more category Restricted. I tried to do some
changes to the vb coding (Macros 2), but couldn’t do it correctly.
Best Regards
Jones Pulikotil
Sent on my BlackBerry® from Vodafone
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Need help!!! MACRO
OOPS Put a space between select and case selectcase select case Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com -Original Message- From: jonnie...@gmail.com Sent: Monday, June 18, 2012 4:10 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Need help!!! MACRO Hi All, I need help with macros. Attached (Macro for Footer) is the existing file with macros for labeling an excel sheet, It asks for Yes or no for label the sheet as Confidential or Internal Use Only. I need your help to include 1 more category Restricted. I tried to do some changes to the vb coding (Macros 2), but couldn’t do it correctly. Best Regards Jones Pulikotil Sent on my BlackBerry® from Vodafone -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Progress Bas for macro status - Help Required
http://oreilly.com/pub/h/2607
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: Avinash
Sent: Monday, June 18, 2012 10:20 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Progress Bas for macro status - Help Required
Hi Rajan,
Thanks for your help but can u please tell me how can i use this progress bar
in to my existing code i mean it is loading directly towards the end but i want
it should run like when my first step gets completed it should say 10 %
complete and till 10 step it should say 100% completed.
it should not complete before any step finished it should load as per the step
are getting completed.
Regards,
Avinash
On Monday, June 18, 2012 6:29:01 PM UTC+5:30, Rajan_Verma wrote:
See the attached file
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of Avinash
Sent: 18 June 2012 2:35
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Progress Bas for macro status - Help Required
Thanks Manoj for you help.
But i want progress bar macro formatting macro i already have i have
mentioned sample macro code not my actual code.
Regards,
Avinash
On Monday, June 18, 2012 11:05:34 AM UTC+5:30, Manoj Kumar wrote:
Dear Avinash.
Sub gg()
For a = 1 To Sheets.Count
Sheets(a).Activate
Range("a1:dd5").Select
Selection.Font.Bold = True
Next
End Sub
Regard
Manoj
On Sun, Jun 17, 2012 at 11:39 PM, Avinash wrote:
Dear Experts,
I have 1 workbook and there are 10 subsheets in that workbook.
i have created 1 macro to format those 10 subsheets.
every sheet does have different formatting e.g. sheet 1 bold text for row 1
and and sheet 2 bold italic text for row 2.
so now i want macro to show the progress bar (progress bar should be VBA
USERFORM ) in % which shows me how much formatting is completed like after
sheet 1 formatting progress bar should show 10% completed after sheet 2 it
should show 20%
and after all formatting done it should say 100% with how much time taken for
formatting all thease sheets.
Please help me in this as i want to incorporate the same type of macro in
other projects.
My macro style
Sub Formatting ()
Step1
format sheet 1
Step2
format sheet 2
Step3
format sheet 3
Step4
format sheet 4
Step5
format sheet 5
Step6
format sheet 6
Step7
format sheet 7
Step7
format sheet 7
Step8
format sheet 8
Step9
format sheet 9
Step10
format sheet 10
End Sub
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3)
Re: $$Excel-Macros$$ Adding Auto Rows
As is often the case you do not fully explain. Do you want a BLANK row below or copy (and insert) the value and what to do with Mah 5-10 L Aurangabad Mah 1-5 L Kolhapur Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Deba Ranjan Sent: Monday, June 18, 2012 9:05 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Adding Auto Rows Dear Experts, Please find the attached file. There is 39 Unique name in MARKET NAME, in every market name is double (compulsary double), if the market name is single then we need to add one row for making double . Eg. if kolkata is single, so we need to add 1 row below the kolkata, is there any macros or Formula to add row. Thanks & Regards, Deba Ranjan P -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Saving Photos from webpage
Application.Workbooks.Open ("http://www.jabong.com";)
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: Kiran Kancharla
Sent: Wednesday, June 20, 2012 1:06 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Saving Photos from webpage
Hi All,
Is any one interested, if I am ready to pay for this task.
Please help to create a Macro to save images folder.
Below are the requirement:
Step 1: Navigating the website : http://www.jabong.com/ - Macro Done
Step 2 : Search for product : WR138MA56HVN - Macro Done
Step 3 : Select Photos and download the photos in the specified location. -
Macro Require
I have created macro to navigate the web page and I need help to save the all
photos in folder.
Please help me as i have a huge data to download.
I have tried in google, unfortunately I am getting all the macro's to download
only 1 photo, in my current need I have to download all the photos.
I can use any type of explorer ie, chrome, firefox or any.
Note : The website (http://www.jabong.com/)is only for example purpose.
Thanks in advance...
If any one interested, please revert back to me..
Thanks & Regards,
Kiran
On Sun, Jun 17, 2012 at 2:55 PM, Kiran Kancharla wrote:
Hi All,
Request you to please help to create a Macro to save images folder.
Below are the requirement:
Step 1: Navigating the website : http://www.jabong.com/ - Macro Done
Step 2 : Search for product : WR138MA56HVN - Macro Done
Step 3 : Select Photos and download the photos in the specified location. -
Macro Require
I have created macro to navigate the web page and I need help to save the all
photos in folder.
Please help me as i have a huge data to download.
I have tried in google, unfortunately I am getting all the macro's to
download only 1 photo, in my current need I have to download all the photos.
I can use any type of explorer ie, chrome, firefox or any.
Note : The website (http://www.jabong.com/)is only for example purpose.
Thanks in advance...
--
Thanks & Regards,
Kiran
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
mailto:excel-macros%2bunsubscr...@googlegroups.com
--
Thanks & Regards,
Kiran
9920456606
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr.
Re: $$Excel-Macros$$ I want to auto sum by macro coding. Please help me......
Sub sumem()’ Does not leave the formula
Range("d4").Value = Application.Sum(Columns(2))
End Sub
Noorain, I humbly suggest using (for versions 2007+)
Set rng = .Range("B2:B" & .cells(rows.count,2).End(xlUp).Row)
or “b” instead of 2
instead of
Set rng = .Range("B2:B" & .Range("B65536").End(xlUp).Row)
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: NOORAIN ANSARI
Sent: Thursday, June 21, 2012 8:04 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ I want to auto sum by macro coding. Please help
me..
Dear Kuldeep,
Please try it and see attached sheet.
Sub Sum_Kuldeep()
Dim rng As Range
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(1)
With sh
Set rng = .Range("B2:B" & .Range("B65536").End(xlUp).Row)
For Each cell In rng
.Cells(3, "D").Value = .Cells(3, "D") + cell
Next cell
End With
End Sub
On Thu, Jun 21, 2012 at 6:27 PM, Kuldeep Singh wrote:
Hi Experts,
I want to auto sum by excel macro coding. Please help me.
I don't use Alt + = Key, and not use =Sum(...:) Formula.
Regards,
Kuldeep Singh
Phone.: +91-9716615535
naukrikuld...@gmail.com || www.naukri.com
Please Consider the environment. Please don't print this e-mail unless you
really need to.
--
-- FORUM RULES (986+ members already BANNED for violation) 1) Use concise,
accurate thread titles. Poor thread titles, like Please Help, Urgent, Need
Help, Formula Problem, Code Problem, and Need Advice will not get quick
attention or may not be answered. 2) Don't post a question in the thread of
another member. 3) Don't post questions regarding breaking or bypassing any
security measure. 4) Acknowledge the responses you receive, good or bad. 5)
Cross-promotion of, or links to, forums competitive to this forum in signatures
are prohibited. NOTE : Don't ever post personal or confidential data in a
workbook. Forum owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com To
unsubscribe, send a blank email to
mailto:excel-macros%2bunsubscr...@googlegroups.com
--
Thanks & regards,
Noorain Ansari
www.noorainansari.com
www.excelmacroworld.blogspot.com
--
-- FORUM RULES (986+ members already BANNED for violation) 1) Use concise,
accurate thread titles. Poor thread titles, like Please Help, Urgent, Need
Help, Formula Problem, Code Problem, and Need Advice will not get quick
attention or may not be answered. 2) Don't post a question in the thread of
another member. 3) Don't post questions regarding breaking or bypassing any
security measure. 4) Acknowledge the responses you receive, good or bad. 5)
Cross-promotion of, or links to, forums competitive to this forum in signatures
are prohibited. NOTE : Don't ever post personal or confidential data in a
workbook. Forum owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com To
unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
--
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Macro to copy a macro from one workbook to another
Create a “template” workbook for the child workbooks or do it from the master. A more detailed explanation of your problem may help. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Richard Sent: Sunday, June 24, 2012 6:46 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Macro to copy a macro from one workbook to another I would like to create a macro to copy a macro from one workbooks module area to another workbooks "ThisWorkbook" area. The first workbook is the master and any other workbook would be subsets of the master. The idea is to have a macro that will further lock cells from input (sub workbook_open) on the child workbooks. Any help would be appreciated Rich -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ tricky some question
Put in a macro enabled workbook
Sub sumifNOTbold()
dim I as long
dim ii as long
For i = 3 To 7
ms = 0
For ii = 5 To 9
If Cells(ii, 2).Font.Bold =False Then ms = ms + Cells(ii, i)
Next ii
MsgBox ms
Cells(16, i) = ms
Next i
end sub
Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com
From: NOORAIN ANSARI
Sent: Saturday, June 30, 2012 12:48 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ tricky some question
Dear Danial,
Please use
=Get.cell(21,indirect("RC[1]",) and create Name range.
then use simple
=SUMPRODUCT(($A$5:$A$9=FALSE)*(C5:C9))
or
=SUMIF($A$5:$A$9,FALSE,C5:C9)
See attached sheet for more reference.
Please revert if you have any query.
--
Thanks & regards,
Noorain Ansari
www.noorainansari.com
www.excelmacroworld.blogspot.com
On Sat, Jun 30, 2012 at 11:06 PM, danial mansoor wrote:
Dear noorani,
i have attached a sample sheet hope you get idea from the given sheet,i will
have to do manual sum as i did in a given sheet and it is hectic job i have
huge data plz help.
--
Date: Sat, 30 Jun 2012 22:50:29 +0530
Subject: Re: $$Excel-Macros$$ tricky some question
From: noorain.ans...@gmail.com
To: excel-macros@googlegroups.com
Dear Mansoor
Please share your workbook with group.
It is possible through Get.Cell Function in Excel.
On Sat, Jun 30, 2012 at 3:38 PM, danial mansoor
wrote:
Dear Experts,
i have 2 heads of utilities one is bold and another is in general form and
i have a bunch of data with same format,wat i want to sum data with one with
general format UTILITIES,now when ever i apply sumif and sumproduct it include
both the heads and sum both the figures number.
Is there a way to pick only head which is not bold can i sum using
unformated head of UTILITIES?
UTILITIES
UTILITIES
Date: Sat, 30 Jun 2012 15:30:45 +0530
Subject: Re: $$Excel-Macros$$ Replace data from another sheets by adding raw
From: ashishbhalar...@gmail.com
To: excel-macros@googlegroups.com
We have manufacturer unit and using fox base software, but now we are going
to develop software of ERP in SQL, so we are transferring data from Fox to
excel and after processing in excel transfer to SQL software of back financial
year. But now this my query is more difficult to do, so I need your help
because as I told there are thousands of record to process and is more
difficult to do manually. If you have a solution for my query it will be more
useful for me.
Actually I tried my level best but in my task I need function formula which
receive data from another sheet only once time (not repeat), inshort I use
vlookup but there are repetition of data, what is the function in which data
not repeat if some type of condition true.
Thanks.
On Sat, Jun 30, 2012 at 10:40 AM, Rajan_Verma
wrote:
homework
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
From: excel-macros@googlegroups.com
[mailto:excel-macros@googlegroups.com] On Behalf Of Ashish Bhalara
Sent: 30 June 2012 9:21
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Replace data from another sheets by adding
raw
Dear experts,
Please suggest what should I do in that case, waiting for reply.
On Fri, Jun 29, 2012 at 7:23 PM, Ashish Bhalara
wrote:
On Fri, Jun 29, 2012 at 7:20 PM, Ashish Bhalara
wrote:
Dear experts,
I need a function or macro to replace data from another sheets by
addition of raw, these addition of raw is depend on the record of another
sheet. you can find attached sheet for better understanding. I can do it
manually but there are thousands of record to process so its not possible to do
this.
Thanks & regards.
Ashish Bhalara
9624111822
PPlease do not print this email unless it is absolutely necessary.
Spread environmental üawareness.
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential dat
Re: $$Excel-Macros$$ MVP Award....Congratulations Ashish Koul and Dilip Pandey !!
And, Don Guillett was not re selected. Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com From: Ayush Jain Sent: Sunday, July 01, 2012 11:24 AM To: excel-macros Cc: Dilip Pandey ; ashish koul Subject: $$Excel-Macros$$ MVP AwardCongratulations Ashish Koul and Dilip Pandey !! Dear members, I am extremely happy to inform you that two members of the group are awarded Microsoft Most Valuable Professional Award. My heartiest congratulations to Ashish koul and Dilip Pandey and thanks for their big support to this forum. Keep posting ! Best regards, Ayush Jain Group Manager Microsoft MVP -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ DASHBOARD FOR STOCK MARKET REVIEW
Nicely done. Suggest refresh of external query on opening . Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com From: hilary lomotey Sent: Thursday, February 09, 2012 8:57 AM To: excel-macros Subject: $$Excel-Macros$$ DASHBOARD FOR STOCK MARKET REVIEW DA I just completed a dashboard for stock market review and wanted to share it to the group,( i got a lot of inputs from this group), i have hidden the all the working sheets except for the dashboard. U can unhide to see how it was prepared. i hope you like it. i would be glad to also get other dashboard from anyone who has similar. thanks -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Re: Is there any way to find out how much memory is being used by a excel sheet in particular excel file
type name of file into a cell and run this macro to determine file size Sub filesize() 'FOR SELECTED MsgBox " FILE SIZE IS " & Format(FileLen(ActiveCell & ".xls") * 0.0009767, "0") & " KB" End Sub Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com From: Prince Dubey Sent: Tuesday, July 03, 2012 11:48 AM To: excel-macros@googlegroups.com Cc: LAKSHMAN PRASAD Subject: $$Excel-Macros$$ Re: Is there any way to find out how much memory is being used by a excel sheet in particular excel file Hi Lakshman, I think you should copy or Move your each sheets into a new work book this help you to know the size of each sheets in your workbook. hope this will help you. regards prince On Tuesday, 3 July 2012 13:02:48 UTC+5:30, lakshm...@yahoo.com wrote: Dear Expert, Is there any way to find out how much memory is being used by a excel sheet in particular excel file . Like I have a file named XYZ (5mb memory used ) and in this file have three sheet (sheet1, sheet2, sheet3) then how much memory used by sheets , sheet1-??? sheet2-??? sheet3-??? Total-5MB Regards Lakshman Prasad Manager (Finance) GAURSONS INDIA LIMITED Ph: +91 12 456777 Mob. No. 9582279261 Website: http://www.gaursonsindia.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ VLOOKUP OR SUMPRODUCT FORMULA HELP
With large data, you may take a bit of time with any method. Perhaps you could break it up. If desired, send your real file to ME. Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com From: Amit Gandhi Sent: Tuesday, July 03, 2012 3:17 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ VLOOKUP OR SUMPRODUCT FORMULA HELP Hi Rajan Get Total function (UDF) is perfectly fine, but it is taking too much time to process large data. Is there any other way to get the same results using Excel in-built functions like VLOOKUP/ SUMPRODUCT/ INDIRECT. I am attaching the output file again. Regards Amit On Mon, Jun 25, 2012 at 11:37 AM, Amit Gandhi wrote: Thanks alot Mr. Rajan This function is Perfectly as per my requirement. Thanks once again Amit Gandhi On Sat, Jun 23, 2012 at 6:45 PM, Rajan_Verma wrote: HI Amit, This function can work independently, you just need to understand the function Arguments : Function GetTotal(rngLookUpRange, rngMapping As Range, rngLookup As Range, lngCOlumnresult As Long, lngMinusValue As Double) As Double rngLookUpRange = it is a value you want to look in table1 rngMapping = Select whole Table1 rngLookup =select the Table2 lngCOlumnresult = Which column you want to Sum from Table2 lngMinusValue = If you want to minus any value from the result formula at every iteration so if you are increasing column in table2 you also need to increase lngCOlumnresult value in the formula use the below function in J15: =GetTotal(G15,$A$8:$B$18,$F$4:$I$9,4,0) Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Amit Gandhi Sent: 23 June 2012 5:19 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ VLOOKUP OR SUMPRODUCT FORMULA HELP Thanks Mr. Rajan For given set of values, its working perfectly. But I have some questions. 1. If values in H5:H9 are changed, then it doesn't give correct result. Pls suggest function, which can work independently and doesn't depend on adjacent column. I am attaching modified task file for your quick reference. It will be a great help if you can suggest formula with inbuilt functions (INDIRECT, MATCH, SUMPRODUCT) as well. Regards Amit On Sat, Jun 23, 2012 at 1:38 PM, Rajan_Verma wrote: Hi Amit, It will fulfil your requirement . J , See the attached. Function GetTotal(rngLookUpRange, rngMapping As Range, rngLookup As Range, lngCOlumnresult As Long, lngMinusValue As Double) As Double Dim VarMapping Dim VarLookup Dim strLookUp Dim lngResult As Double Dim lngCounter As Long Dim StrLookupValue As String VarMapping = rngMapping VarLookup = rngLookup StrLookupValue = rngLookUpRange.Value strLookUp = "" For lngCounter = LBound(VarMapping) To UBound(VarMapping) If VarMapping(lngCounter, 1) = StrLookupValue Then strLookUp = strLookUp & VarMapping(lngCounter, 2) & "|" End If Next lngCounter For lngCounter = LBound(VarLookup) To UBound(VarLookup) If InStr(strLookUp, VarLookup(lngCounter, 1)) Then lngResult = lngResult + VarLookup(lngCounter, lngCOlumnresult) - lngMinusValue End If Next lngCounter GetTotal = lngResult End Function Revert if it is not desired Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Amit Gandhi Sent: 22 June 2012 5:20 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ VLOOKUP OR SUMPRODUCT FORMULA HELP Hi Experts I have two Input/Lookup tables, and 1 result table. I want a formula which can give me desired result instantly. Sample file is attached for your ready reference. How it will be possible? Pls help me here. Regards Amit -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -
Re: $$Excel-Macros$$ Excel Function_Code
http://www.add-in-express.com/docs/net-excel-xll-addins.php Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com From: Deba Ranjan Sent: Wednesday, July 04, 2012 1:43 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Excel Function_Code Dear experts, i am keep on trying to make mention below function code (concatenate) as Excel Add-ins. Can we make function as Add -ins ?. - whenever and wherever we wish to insert the add-in function at any system it should work out. i have tried to save as add- in and after adding the add - in to the excel, it is not working. Any solution for this please. Note- Can we make in one single add- in more then 1 function ? Code:- Function Full_con(rng As Range) Dim cell As Range Dim str As String For Each cell In rng str = str & cell & " " & "," & " " Next cell str = VBA.Right(str, Len(str) - 1) Full_con = str End Function Thanks & Regards, Deba Ranjan P -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ urgent required vba code:: 2 list boxes in userform scroll same time.
I won’t speak for others but your plea for “urgency” makes me NOT respond as all requests are urgent and yours is no more urgent than others. Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com From: Dhartikumar Sahu Sent: Tuesday, July 03, 2012 11:52 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ urgent required vba code:: 2 list boxes in userform scroll same time. Hi Group I have 2 list boxes side by side in userform. I need to use a single scroll bar to scroll all 2 listboxes at the same time, so that all the information in the boxes stay aligned. need urgent reply -- Regards, Dhartikumar Sahu Sr.Database Manager - Institutional Equities IDBI Capital Markets Services Ltd 5th Floor, Mafatlal Centre | Nariman Point | Mumbai – 21 Board: +91 22 4322 1212 | Dir: +91 22 4322 1169 | Cell: +91 77383 63450 -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Rajan Verma - Most helpful Member(June'12)
Congratulations from Austin Texas Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com From: Ayush Jain Sent: Wednesday, July 04, 2012 11:16 AM To: excel-macros Subject: $$Excel-Macros$$ Rajan Verma - Most helpful Member(June'12) Hello Everyone, Rajan Verma has been selected as 'Most Helpful Member' for the month of June'2012. This is third time in row. :) He has posted 149 posts in June 2012 and helped many people through his expertise. I truly appreciate his consistency and commitment to group. He is really doing great job. Thanks to Noorain, Deba Ranjan, Don, David, Lalit, Lokesh, Hilary, Asa and other folks for helping excel enthusiasts voluntarily !! Keep it up !! Keep posting. Regards Ayush Jain Group Manager , Microsoft MVP -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ populate consecutive numbers in discrete rows with other text
Right click sheet tab>view code>insert this. Now when you put 149 in col A col B will populate Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 2 Or Target.Column <> 1 Then Exit Sub Target.Offset(, 1) = "#" & Target & " Story" End Sub -Original Message- From: deej109 Sent: Monday, August 08, 2011 1:37 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ populate consecutive numbers in discrete rows with other text I have two adjacent columns of consecutive numbers, spaced several rows apart each. For example: A B 149 | #149 Story 150 | #150 Story I know how to create consecutive numbers in column A. Is there a way to automatically populate the numbers in column B? Is there a way to combine text and function commands? Thanks very much! deej109 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Help needed. compare two wxcel files
You need to post examples, etc. From: Rajendra prasad yadav Sent: Monday, August 08, 2011 1:31 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Help needed. compare two wxcel files Hi friends, I am not good at excel. I have a requirement now, Can some one please give the macro to compare two worksheets in diferent workbooks and display the differences. Regards, Rajendra 8147524172 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Question
Homework? -Original Message- From: XLS S Sent: Monday, August 08, 2011 3:38 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Question Q-1 )Can I write VBA code to protect or unprotect my VB Project? Q-2) Why does Excel have two macro languages? Q-3) Can I lock cells such that only specific users can modify them? Q-4) When I enter a value, it appears with two decimal places. For example, when I enter 123 it shows up as 1.23. What's wrong? Q-5) How can I increase the number of columns in a worksheet? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Code need to Transfer Excel worksheets labelled 1 to X to Word.
What do you need to do in Word that you can't do in excel? -Original Message- From: skyping1 Sent: Monday, August 08, 2011 12:07 PM To: MS EXCEL AND VBA MACROS Subject: Re: $$Excel-Macros$$ Code need to Transfer Excel worksheets labelled 1 to X to Word. Anyone have any ideas on this please? On Aug 3, 9:50 pm, Kevin gray wrote: Please see attachments Date: Wed, 3 Aug 2011 07:48:44 +0530 Subject: Re: $$Excel-Macros$$ Code need to Transfer Excel worksheets labelled 1 to X to Word. From: venkat1@gmail.com To: excel-macros@googlegroups.com Hi There is No attachment. Best Regards,Venkat Chennai On Tue, Aug 2, 2011 at 10:41 PM, skyping1 wrote: Please find attached files...hope you can help On Aug 2, 10:23 am, "Rajan_Verma" wrote: > Please Attached a sample file > -Original Message- > From: excel-macros@googlegroups.com > [mailto:excel-macros@googlegroups.com] > On Behalf Of skyping1 > Sent: Tuesday, August 02, 2011 12:07 AM > To: MS EXCEL AND VBA MACROS > Subject: $$Excel-Macros$$ Code need to Transfer Excel worksheets > labelled 1 > to X to Word. > I have many Excel files in a folder which all have labelled Worksheets > from 1 to X where X is always an integer. > Most of the files have about 10 worksheets, and these can have both > text and pictures in. > I have searched, for some code (to no avail and through quite a few > forums!!), that would copy the content of the worksheets in numeric > order to a blank Word document. > I am using 2010, and ideally would like to select any number of files > and even perhaps the order(!) that they can be pasted into Word. > Hope you guys can help. > -- > --- > - > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links > :http://twitter.com/exceldailytip > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials athttp://www.excel-macros.blogspot.com > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > To post to this group, send email to excel-macros@googlegroups.com > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below > linkhttp://www.facebook.com/discussexcel -- --- --- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links :http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- --- --- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links :http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Start.xlsm 971KViewDownload finish.docx 154KViewDownload -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><
Re: $$Excel-Macros$$ sumproduct issue
try $F$4581="Lost")+(Data!$F$2:$F$4581=" From: Shiek Peer Mohd Sent: Tuesday, August 09, 2011 1:16 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ sumproduct issue Hi Experts, I have the issues in using the multiple criteria in sumproduct. Sample file attached. Regards, Shiek -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Compute combinations, permutations
Can you clarify with examples From: Sunny Sent: Tuesday, August 09, 2011 5:51 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Compute combinations, permutations Hi , I am very new to combinations, permutations in Excel, Need help on the attached file. I have 3 different component(s) with 2 different supplier(s) with multiple cavity. I need all combinations & permutations with respect part. Eg., A -> Part A, Tool 1, Cavity 1, I -> Part B, Tool 2, Cavity 1 P -> Part C, Tool 1, Cavity 1 Combination: 1. A,I,P 2. B,I,A6 3. F,V,A3 etc., Help me find the solution. Appreciate your support in advance. Regards Sunny -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ UNIQUE values in Array
Will data>advanced filter>unique work for you?
-Original Message-
From: Rajan_Verma
Sent: Tuesday, August 09, 2011 9:04 AM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ UNIQUE values in Array
Hi,
I was trying to Get Unique List by Using Array Function and the Output is
below , it's a array Function To Get Unique values List From Given Range
Function GetUniqueList(rng As Range) As Variant
On Error Resume Next
Dim Arr() As Variant
Dim cell As Range
Dim r, c As Integer
Dim i, j As Integer
i = 0: j = 0
With Application.Caller
r = .Rows.Count
c = .Columns.Count
End With
ReDim Arr(r - 1, c - 1)
For Each cell In rng
If WorksheetFunction.CountIf(rng.Cells(1, 1).Resize(cell.Row, 1),
cell.Value) = 1 Then
Arr(i, j) = cell.Value
If j = c Then j = j + 1
i = i + 1
End If
For k = i To UBound(Arr())
Arr(k, 0) = ""
Next
Next
GetUniqueList = Arr
End Function
-Original Message-
From: Rajan_Verma [mailto:rajanverma1...@gmail.com]
Sent: Saturday, August 06, 2011 9:59 AM
To: 'excel-macros@googlegroups.com'
Subject: RE: $$Excel-Macros$$ UNIQUE values in Array
Hope this will Help You.
Function UniqueList(rng As Range, Pos As Long) As String
Dim List() As String
Dim cell As Range
Dim i As Long
Dim t As Long
i = 0
ReDim List(rng.Cells.Count) As String
For Each cell In rng
flag = 0
For t = LBound(List) To UBound(List)
If cell.Value = List(t) Then
flag = 1
Exit For
End If
Next
If flag = 0 Then
List(i) = cell.Value
i = i + 1
End If
Next
UniqueList = List(Pos)
End Function
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of hanumant shinde
Sent: Saturday, August 06, 2011 2:20 AM
To: Excel Group
Subject: $$Excel-Macros$$ UNIQUE values in Array
Hi friends,
i have some values in column A. i want to take only UNIQUE values in some
array.
how can i do so?
i have developed below function and is working exactly i want it to be but i
think there should be more efficient way of doing so like there may be array
function for storing only UNIQUE values or anything like that.
Sub UniqueArray()
Dim newarr() As String
Dim blnmatchfnd As Boolean
j = 0
For i = 1 To 46
ReDim Preserve newarr(j)
For k = 0 To UBound(newarr)
If newarr(k) = Range("A" & i).Value Then
blnmatchfnd = True
Exit For
Else
blnmatchfnd = False
End If
Next k
If blnmatchfnd = False Then
ReDim Preserve newarr(j)
newarr(j) = Range("A" & i).Value
Range("B" & j + 1).Value = newarr(j)
j = j + 1
End If
Next i
End Sub
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ UNIQUE values in Array
try
Sub GetUnique()
Application.ScreenUpdating = False
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
'get unique
Cells(2, 1).Resize(lr).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("D2"), Unique:=True
'sort
Cells(3, "d").Resize(lr).Sort Key1:=Range("D3"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
Application.ScreenUpdating = True
End Sub
-Original Message-
From: Rajan_Verma
Sent: Tuesday, August 09, 2011 10:16 AM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ UNIQUE values in Array
Hi,
its better to use advance filter Manually again and again if we are
Increasing Our Range,
Suppose we need a List Validation of Unique Number From a Range , we can use
this Function To Get Unique Values instantly , See the attached File For
Example :
Manual Work in Excel is not in our scope if we have VBA.
Thanks
Rajan Verma
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of dguillett1
Sent: Tuesday, August 09, 2011 8:31 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ UNIQUE values in Array
Will data>advanced filter>unique work for you?
-Original Message-
From: Rajan_Verma
Sent: Tuesday, August 09, 2011 9:04 AM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ UNIQUE values in Array
Hi,
I was trying to Get Unique List by Using Array Function and the Output is
below , it's a array Function To Get Unique values List From Given Range
Function GetUniqueList(rng As Range) As Variant
On Error Resume Next
Dim Arr() As Variant
Dim cell As Range
Dim r, c As Integer
Dim i, j As Integer
i = 0: j = 0
With Application.Caller
r = .Rows.Count
c = .Columns.Count
End With
ReDim Arr(r - 1, c - 1)
For Each cell In rng
If WorksheetFunction.CountIf(rng.Cells(1, 1).Resize(cell.Row, 1),
cell.Value) = 1 Then
Arr(i, j) = cell.Value
If j = c Then j = j + 1
i = i + 1
End If
For k = i To UBound(Arr())
Arr(k, 0) = ""
Next
Next
GetUniqueList = Arr
End Function
-Original Message-
From: Rajan_Verma [mailto:rajanverma1...@gmail.com]
Sent: Saturday, August 06, 2011 9:59 AM
To: 'excel-macros@googlegroups.com'
Subject: RE: $$Excel-Macros$$ UNIQUE values in Array
Hope this will Help You.
Function UniqueList(rng As Range, Pos As Long) As String
Dim List() As String
Dim cell As Range
Dim i As Long
Dim t As Long
i = 0
ReDim List(rng.Cells.Count) As String
For Each cell In rng
flag = 0
For t = LBound(List) To UBound(List)
If cell.Value = List(t) Then
flag = 1
Exit For
End If
Next
If flag = 0 Then
List(i) = cell.Value
i = i + 1
End If
Next
UniqueList = List(Pos)
End Function
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of hanumant shinde
Sent: Saturday, August 06, 2011 2:20 AM
To: Excel Group
Subject: $$Excel-Macros$$ UNIQUE values in Array
Hi friends,
i have some values in column A. i want to take only UNIQUE values in some
array.
how can i do so?
i have developed below function and is working exactly i want it to be but i
think there should be more efficient way of doing so like there may be array
function for storing only UNIQUE values or anything like that.
Sub UniqueArray()
Dim newarr() As String
Dim blnmatchfnd As Boolean
j = 0
For i = 1 To 46
ReDim Preserve newarr(j)
For k = 0 To UBound(newarr)
If newarr(k) = Range("A" & i).Value Then
blnmatchfnd = True
Exit For
Else
blnmatchfnd = False
End If
Next k
If blnmatchfnd = False Then
ReDim Preserve newarr(j)
newarr(j) = Range("A" & i).Value
Range("B" & j + 1).Value = newarr(j)
j = j + 1
End If
Next i
End Sub
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
---
Re: $$Excel-Macros$$ Macro copy filter data in a new sheet
What you want is doable with a macro. However, your “dummy” data does not give
data for testing to see what you really want. Redo your data and give a better
explanation of what you want. This to get you started
Application.Goto Sheets(3).Range("a2")
myname = Application.InputBox("Pick a cell Then click OK", Type:=8)
MsgBox myname
From: Simran Singh
Sent: Tuesday, August 09, 2011 10:09 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Macro copy filter data in a new sheet
Hello EXPERTS
I have a workbook wherein there are thee sheets (Database, Mapping & Employee
Name). In the Database sheet, I pull out the data from the tool and put that
data in Database sheet. The mapping sheet consist a list of employee who are
basically associated with one group or more than one groups. And in the last
sheet Employee Name is the data for which i have to take out data from the
Database sheet. So what I want basically is :-
1.. Macro to go in the sheet Employee Name and check for the name for which
it has to filter the data.
2.. After taking the details from the Employee Name sheet it comes to the
Mapping sheet and check for the group or groups a employee is associated with.
3.. After this macro should come to the Database sheet and filter the data
according to group or groups which it has got in the Mapping sheet.
4.. After filter it should copy the Total Hours and Actual Hours data from
there and paste in the Output sheet.
Sorry I cannot give you the actual data, but I am attaching the dummy data for
your reference.
Thanks in advance
Regards,
Simran Singh
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Locking cells based on conditions / todays date
Attach a file with a better explanation and before/after examples -Original Message- From: AJ Sent: Tuesday, August 09, 2011 2:20 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Locking cells based on conditions / todays date There are dates in calendar order stored in row 4, beginning in column F through column BR. How can I write a code so that it locks a certain amount of cells below the date if it occurs in the past. For example: January 14th is in cell O4. If todays date was January 14th, i would want to lock all cells within the range F4:O21 Thanks in advance for the help, AJ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Please help
Attach a file From: Sundarvelan N Sent: Tuesday, August 09, 2011 11:52 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Please help Hi Friends, Please refer the attached image. Please help on this scenatio. Thanks N.Sundarvelan 9600160150 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Want a shortcut to browse tabs, alas, classic code found is not working.
Notice the sheet arrow keys at the bottom left. RIGHT click one>voila
-Original Message-
From: Dsastray
Sent: Tuesday, August 09, 2011 4:29 PM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Want a shortcut to browse tabs, alas, classic code
found is not working.
I would like to be able to press a hotkey and have a sheet pop up that
allows me to navigate to any sheet in my workbook. (A shorter version
to bring up the "Activate" dialog you get when you right click on tab
arrows and select "More Sheets...")
For simplicity sake, we'll say I'm using the following code:
Sub SheetListAbridged()
Application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
End Sub
I get the activate popup one would expect when "More Sheets..." is
selected. Alas, clicking on a workbook name dismisses the dialog, but
does not take me to the selected sheet. Ideas?
Full code:
Sub SheetList_CP()
'Chip Pearson, 2002-10-29, misc., %23ByZYZ3fCHA.1308%40tkmsftngp11
'Dave Peterson, same date/thread, 3DBF0BA8.4DAE9DA0%40msn.com
On Error Resume Next
Application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
If Err.Number > 0 Then
Err.Clear
Application.CommandBars("Workbook Tabs").ShowPopup
End If
On Error GoTo 0
End Sub
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ REMOVE THE ERROR IN THIS FILE
Run this macro on your numbers and just use =e8+g8
Sub fixmynumsSAS()
Application.ScreenUpdating = False
'lr = Cells.SpecialCells(xlCellTypeLastCell).Row
On Error Resume Next
For Each C In Selection 'Range("a1:q" & lr)
If Trim(Len(C)) > 0 And C.HasFormula = False Then
C.NumberFormat = "General"
C.Value = CDbl(C)
End If
Next
Application.ScreenUpdating = True
End Sub
From: Rajan_Verma
Sent: Wednesday, August 10, 2011 8:12 AM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ REMOVE THE ERROR IN THIS FILE
See the attached sheet
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of Jai
Sent: Wednesday, August 10, 2011 11:39 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ REMOVE THE ERROR IN THIS FILE
PLEASE HELP
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ copy selected range to target with params
Post a file with a more complete explanation -Original Message- From: Seba Sent: Wednesday, August 10, 2011 9:36 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ copy selected range to target with params hi, I need help with writing a macro which would copy selected range and prompt me for target workbook, sheet and range. It would need to copy only values and transpose them. any help is greatly appreciated. Regards, seba -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Can you please help regd. lists
Itappears that this is only partially done. You need a worksheet_change event macro tied to the dropdown cell to do the rest. How do you want the list of cities displayed? -Original Message- From: XLS S Sent: Wednesday, August 10, 2011 4:00 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Can you please help regd. lists Hey Naresh, Please find the attachment. On Wed, Aug 10, 2011 at 4:51 PM, naresh v wrote: Hello experts, Let us assume in the particular cell i want a dropdown like "south" or "north". I will select anyone of it.If I select "south" In the next cell it should show the "AP","Tamilnadu","Karnataka" If I select "North" it should show the "Maharastra", "UP" like that can you please help me... Regards, Naresh V -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- . -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Add 1 to a cell every time the file is opened
or
Sheet1.Range("f2").Value=Sheet1.Range("f2").Value + 1
-Original Message-
From: XLS S
Sent: Wednesday, August 10, 2011 3:10 PM
To: excel-macros@googlegroups.com
Cc: John A. Smith
Subject: Re: $$Excel-Macros$$ Add 1 to a cell every time the file is opened
Hey Johnasmith,
Please find the attachment...
Code...
Private Sub Workbook_Open()
dt = Sheet1.Range("f2").Value
Sheet1.Range("f2").Value = dt + 1
End Sub
On Thu, Aug 11, 2011 at 1:24 AM, John A. Smith
wrote:
Excel guru's, can I make a receipt file that adds 1 to a specific cell
everytime the file is opened?
Please see attached.
Thank you.
John
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Please help
Or with col 8 formatted as desired place this in the SHEET module Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 2 Or Target.Column <> 7 Then Exit Sub If UCase(Target) = "DONE" Then Target.Offset(, 1) = Date End Sub -Original Message- From: XLS S Sent: Wednesday, August 10, 2011 1:19 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Please help Hey Sundaryelan, Please find the attachment . On Wed, Aug 10, 2011 at 10:22 AM, Sundarvelan N wrote: Hi Friends, Please refer the attached image. Please help on this scenatio. Thanks N.Sundarvelan 9600160150 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- . -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ PasteSpecial
To remove macros simply save as .xlsX From: ja...@macstop.co.uk Sent: Wednesday, August 10, 2011 2:24 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ PasteSpecial thanks - it's a long story but basically I'm trying to export some sheets from a workbook with loads of code behind it. I wanted to use move/copy originally but it takes the vba behind the sheets with it. (Which I don't want). So I wrote a function that just deleted all vba code in the workbook - but then I ran into Excel 2010 issues having to tick the VBA project security box to let that run. The guy I am working for won't want to tell all his users they have to tick that... and I was worried about backwards compatibility too, so I decided to go for the easier option of copying the cells rather than the sheet. It's working now, not particularly quick but I think it's quick enough!! Thanks for your help -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Macro copy filter data in a new sheet
I now understand what you need. Have you gotten a satisfactory result yet?
From: Rajan_Verma
Sent: Wednesday, August 10, 2011 9:45 AM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Macro copy filter data in a new sheet
Please Ignore Previous Mail..
See if it helps
Option Base 1
Sub GetData()
Application.ScreenUpdating = False
Dim EmpSh As Worksheet
Dim MappingSheet As Worksheet
Dim EmpName As Range
Dim EmpNameRange As Range
Dim EmpDesigNation As String
Dim Fields As Range
Dim Field As Range
Dim Group() As Variant
Dim i_Counter As Integer
Dim Dbsheet As Worksheet
Dim sumTotalHour As Integer
Dim SumActualHour As Integer
Dim cell As Range
Dim Outputsheet As Worksheet
Dim Country As String
Set EmpSh = Sheets("Employee Name")
Set EmpNameRange = EmpSh.Range("A2:A" & EmpSh.UsedRange.Rows.Count)
Set Dbsheet = Sheets("Database")
Set MappingSheet = Sheets("Mapping")
Set Outputsheet = Sheets("Output")
EmpSh.Activate
Set Fields = MappingSheet.Range("A1").Resize(1,
MappingSheet.UsedRange.Columns.Count)
For Each EmpName In EmpNameRange
EmpDesigNation = EmpName.Offset(0, 1).Value
Country = EmpName.Offset(0, 2).Value
MappingSheet.Activate
For Each Field In Fields
If Field.Value = EmpDesigNation Then
MappingSheet.Range("A1").Activate
Selection.AutoFilter
ActiveSheet.Range("$A$1:$E$46").AutoFilter
Field:=Field.Column, Criteria1:=EmpName
ActiveSheet.Range("$A$1:$E$46").AutoFilter
Field:=1, Criteria1:=Country
MappingSheet.Range("B2:B" &
Range("b2").End(xlDown).Row).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
MappingSheet.Range("B" &
MappingSheet.UsedRange.Rows.Count + 10).Select
Selection.PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.RemoveDuplicates 1
ReDim Group(Selection.Cells.Count)
For i_Counter = 1 To UBound(Group)
Group(i_Counter) =
Selection.Cells(i_Counter, 1).Value
Next
Selection.EntireRow.Delete
Dbsheet.Activate
sumTotalHour = 0
SumActualHour = 0
For i_Counter = 1 To UBound(Group)
ActiveSheet.Range("$A$1:$D$46").AutoFilter Field:=2, Criteria1:=Group(i_Counter)
ActiveSheet.Range("$A$1:$D$46").AutoFilter Field:=1, Criteria1:=Country
Set rngtotalhour =
Dbsheet.Range("C2:C" &
Range("C2").End(xlDown).Row).Cells.SpecialCells(xlCellTypeVisible)
Set RngactualHour =
Dbsheet.Range("D2:D" &
Range("D2").End(xlDown).Row).Cells.SpecialCells(xlCellTypeVisible)
sumTotalHour = sumTotalHour
+ WorksheetFunction.Sum(rngtotalhour)
SumActualHour =
SumActualHour + WorksheetFunction.Sum(RngactualHour)
Next
Outputsheet.Activate
Outputsheet.Range("A" &
Range("A" & Rows.Count).End(xlUp).Row + 1).Select
ActiveCell.Value = EmpName
ActiveCell.Offset(0,
1).Value = sumTotalHour
ActiveCell.Offset(0,
2).Value = SumActualHour
End If
Next
Re: $$Excel-Macros$$ Split data order wise New Excel workbook
Put this macro into a regular module in the Bif file to get the number from the
data file
Option Explicit
Sub GetID_SAS()
Dim lr As Long
Dim myid As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
myid = InputBox("ID to get, ie 103")
Workbooks.Open Filename:="Total Order data.xls"
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:M" & lr).AutoFilter Field:=1, Criteria1:=myid
Range("A2:L" & lr).SpecialCells(xlCellTypeVisible).Copy
ActiveWindow.Close SaveChanges:=False
Range("c4").Select
ActiveSheet.Paste
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
From: Brajesh Kumar Porwal
Sent: Thursday, August 11, 2011 1:49 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Split data order wise New Excel workbook
Hi Expert,
I urgent need your help, because i want to create macro for split data. I have
more data in excel workbook so I want to split data order wise New Excel
workbook. Please find enclose example file.
--
--
One Team One Dream One Goal
Warm Regards,
Brajesh Kumar Porwal
E-Mail :- brajeshkumarpor...@gmail.com
7503020750,
"We can't Spell S_ccess without U"
Life is Very beautiful !!!
¨`•.•´¨) Always
`•.¸(¨`•.•´¨) Keep
(¨`•.•´¨)¸.•´ Smiling!
`•.¸.•´.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Like pivot table function
=SUMPRODUCT((A2:A22={"a","c","g"})*B2:B22)
-Original Message-
From: naresh v
Sent: Thursday, August 11, 2011 8:58 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Like pivot table function
Hi friends,
I have attached one excel, can you please help me ..
Regards,
Naresh V
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ UNIQUE values in Array
If I recall properly, your function skipped 1. How do you want it
presented???
-Original Message-
From: hanumant shinde
Sent: Thursday, August 11, 2011 11:58 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ UNIQUE values in Array
Thanks but this will not work for me. i have already developed a function
but i was thinking there must be some array function which will store just
unique values in ARRAY but i guess now there's none. anyways thank for your
help.
--- On Tue, 9/8/11, dguillett1 wrote:
From: dguillett1
Subject: Re: $$Excel-Macros$$ UNIQUE values in Array
To: excel-macros@googlegroups.com
Date: Tuesday, 9 August, 2011, 10:31 PM
try
Sub GetUnique()
Application.ScreenUpdating = False
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
'get unique
Cells(2, 1).Resize(lr).AdvancedFilter Action:=xlFilterCopy,
_
CopyToRange:=Range("D2"), Unique:=True
'sort
Cells(3, "d").Resize(lr).Sort Key1:=Range("D3"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
Application.ScreenUpdating = True
End Sub
-Original Message- From: Rajan_Verma
Sent: Tuesday, August 09, 2011 10:16 AM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ UNIQUE values in Array
Hi,
its better to use advance filter Manually again and
again if we are
Increasing Our Range,
Suppose we need a List Validation of Unique Number From a
Range , we can use
this Function To Get Unique Values instantly , See
the attached File For
Example :
Manual Work in Excel is not in our scope if we have VBA.
Thanks
Rajan Verma
-Original Message-
From: excel-macros@googlegroups.com
[mailto:excel-macros@googlegroups.com]
On Behalf Of dguillett1
Sent: Tuesday, August 09, 2011 8:31 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ UNIQUE values in Array
Will data>advanced filter>unique work for you?
-Original Message- From: Rajan_Verma
Sent: Tuesday, August 09, 2011 9:04 AM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ UNIQUE values in Array
Hi,
I was trying to Get Unique List by Using Array Function and
the Output is
below , it's a array Function To Get Unique values List
From Given Range
Function GetUniqueList(rng As Range) As Variant
On Error Resume Next
Dim Arr() As Variant
Dim cell As Range
Dim r, c As Integer
Dim i, j As Integer
i = 0: j = 0
With Application.Caller
r = .Rows.Count
c = .Columns.Count
End With
ReDim Arr(r - 1, c - 1)
For Each cell In rng
If WorksheetFunction.CountIf(rng.Cells(1,
1).Resize(cell.Row, 1),
cell.Value) = 1 Then
Arr(i, j) = cell.Value
If j = c Then j = j + 1
i = i + 1
End If
For k = i To UBound(Arr())
Arr(k, 0) = ""
Next
Next
GetUniqueList = Arr
End Function
-Original Message-
From: Rajan_Verma [mailto:rajanverma1...@gmail.com]
Sent: Saturday, August 06, 2011 9:59 AM
To: 'excel-macros@googlegroups.com'
Subject: RE: $$Excel-Macros$$ UNIQUE values in Array
Hope this will Help You.
Function UniqueList(rng As Range, Pos As Long) As String
Dim List() As String
Dim cell As Range
Dim i As Long
Dim t As Long
i = 0
ReDim List(rng.Cells.Count) As String
For Each cell In rng
flag = 0
For t = LBound(List) To UBound(List)
If cell.Value = List(t) Then
flag = 1
Exit For
End If
Next
If flag = 0
Then
List(i) =
cell.Value
i = i + 1
End If
Next
UniqueList = List(Pos)
End Function
-Original Message-
From: excel-macros@googlegroups.com
[mailto:excel-macros@googlegroups.com]
On Behalf Of hanumant shinde
Sent: Saturday, August 06, 2011 2:20 AM
To: Excel Group
Subject: $$Excel-Macros$$ UNIQUE values in Array
Hi friends,
i have some values in column A. i want to take only UNIQUE
values in some
array.
how can i do so?
i have developed below function and is working exactly i
want it to be but i
think there should be more efficient way of doing so like
there may be array
function for storing only UNIQUE values or anything like
that.
Sub UniqueArray()
Dim newarr() As String
Dim blnmatchfnd As Boolean
j = 0
For i = 1 To 46
ReDim Preserve newarr(j)
For k = 0 To UBound(newarr)
If newarr(k) = Range("A" & i).Value Then
blnmatchfnd = True
Exit For
Else
blnmatchfnd = False
End If
Next k
If blnmatchfnd = False Then
ReDim Preserve newarr(j)
newarr(j) = Range("A" & i).Value
Range("B" & j + 1).Value = newarr(j)
j = j + 1
End If
Next i
End Sub
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://
Re: $$Excel-Macros$$ ***Macro Code Required to delete Zero value cell and its row***
or just a little bit neater
Sub MtestSAS()
Application.ScreenUpdating = False
For i =cells(rows.count,”P”).end(xlup).row To 1 Step -1
If Cells(i, 16).Value = 0 Then rows(i).Delete
Next i
Application.ScreenUpdating = True
End Sub
From: Mahesh parab
Sent: Thursday, August 11, 2011 1:15 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ ***Macro Code Required to delete Zero value cell
and its row***
Hi Prabhu
Try
Sub Mtest()
Dim LR As Long, i As Long
LR = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count
'LR = Range("P" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = LR To 1 Step -1
If Cells(i, 16).Value = 0 Then Cells(i, 16).EntireRow.Delete
Next i
Application.ScreenUpdating = True
End Sub
OR
Sub Mtest1()
Dim LR As Long, r As Long
LR = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LR To 1 Step -1
If Application.WorksheetFunction.CountIf(Cells(r, 16), "=0") = 1 Then
Rows(r).Delete
Next r
Application.ScreenUpdating = True
End Sub
Thanks
Mahesh
On Thu, Aug 11, 2011 at 10:00 PM, Prabhu wrote:
Hi Mahesh,
It is deleting entire row wherever zero appearing. But i required in a
partiuclar column if Zero value found we need to delete entire row.
Ex. If we use base as "P" column, In 13th row of "P" column contains "0"
value it has to delete entire 13th Row. It should not search other columns.
Regards,
Prabhu
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Forms
Pls ignore previous post. I sent the wrong file Change to vertical>remove all merged cells>use autofilter with SUBTOTAL(109 function From: Dominic Sundar Sent: Thursday, August 11, 2011 7:10 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Forms Hey there attach is a copy of an inventory spread sheet that i designed for my department. my issue that i have with it is that over time my users make mistakes with it as it is very wide. what i need help with is to to design a form for it so as to input the data more accurately. any help i would be grateful. regards -- Dominic A Sundar NET+ "Don't Fear the penguins but embrace them" -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Move row to different sheet based on criteria
Haven't followed and don't see older posts so if desired send your file to me at dguille...@gmail.com with a complete explanation. -Original Message- From: JsinSk Sent: Saturday, August 13, 2011 10:54 AM To: MS EXCEL AND VBA MACROS Subject: Re: $$Excel-Macros$$ Move row to different sheet based on criteria Can anyone else assist? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Move row to different sheet based on criteria
Using that idea, why not just put in the helper column and just filter on that without the need for separate sheets. Use the SUBTOTAL(109 for sums From: ashish koul Sent: Sunday, August 14, 2011 12:21 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Move row to different sheet based on criteria Hi if you have only three areas you can do it without macro see the attachment . just insert a temp column before A and then apply filter and paste data on sheet 2 rename the sheet On Sat, Aug 13, 2011 at 10:32 PM, dguillett1 wrote: Haven't followed and don't see older posts so if desired send your file to me at dguille...@gmail.com with a complete explanation. -Original Message- From: JsinSk Sent: Saturday, August 13, 2011 10:54 AM To: MS EXCEL AND VBA MACROS Subject: Re: $$Excel-Macros$$ Move row to different sheet based on criteria Can anyone else assist? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Ashish Koul akoul.blogspot.com http://akoul.posterous.com/ akoul.wordpress.com My Linkedin Profile P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ AutoHide
Name your shape “LockShape” and use this assigned to a button or another shape
Sub HideUnhideLockShape()
Shapes("LockShape").Visible = Not _
Shapes("LockShape").Visible
End Sub
From: SAJID MEMON
Sent: Tuesday, August 16, 2011 5:33 AM
To: Excel Group
Subject: $$Excel-Macros$$ AutoHide
Hi all Experts,
I have a little doubt but may be challanging.
I have enclosed my file. plz refer this and give me solution.
awaiting your reply
yours
sajid memon
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: Fwd: FW: $$Excel-Macros$$ Workbooks Consolidation Macro
Your source files should all be structured the same. If not, you will have to program. Attach samples From: vikas gupta Sent: Tuesday, August 16, 2011 6:55 AM To: excel-macros@googlegroups.com Cc: pankaj chawla ; tech_pankajcha...@rediffmail.com Subject: Fwd: FW: $$Excel-Macros$$ Workbooks Consolidation Macro Dear Sir, Sir I have a query that by using this macro file I am able to merge different files into single excel but after merging the data is not under single column heading, Pls. suggest how can I merge files having similar column headers in all files, so that I can easily sort data as per my requirement. Thanks for the help. Pankaj Chawla 9911104968 -Original Message- From: vikas gupta [mailto:vikas.63...@gmail.com] Sent: 13 August 2011 18:39 To: pankaj.chawl...@gmail.com Cc: tech_pankajcha...@rediffmail.com Subject: Fwd: $$Excel-Macros$$ Workbooks Consolidation Macro -- Forwarded message -- From: ashish koul Date: Fri, 22 Jul 2011 05:41:02 +0530 Subject: Re: $$Excel-Macros$$ Workbooks Consolidation Macro To: excel-macros@googlegroups.com try this see if it helps On Thu, Jul 21, 2011 at 10:12 PM, Anish Shrivastava wrote: > Harish, > > Did you try it.. ?? > > On Thu, Jul 21, 2011 at 5:19 PM, Anish Shrivastava wrote: > >> Harish, >> >> PLease ignore the earlier attachment as I forgot to add *DUPLICATE CHECK*in that macro. >> >> Use this attachment. >> >> On Thu, Jul 21, 2011 at 5:09 PM, Anish Shrivastava > > wrote: >> >>> Hi Harsih, >>> >>> Since you didnt reply about column headers I assumed that it's the same >>> for every file on share drive. >>> >>> Please see the attached file with macro. >>> >>> You must populate the file path in Sheet2-A1. >>> >>> Do let me know if it works fine for you. >>> Cheers!! >>> Anish >>> On Thu, Jul 21, 2011 at 4:00 PM, Anish Shrivastava < >>> anish@gmail.com> wrote: >>> Harish, I forgot to ask you one thing. The column headers in the file you attached will be constant for all the 30-35 files which are there on your sharedrive, right? Cheers, Anish On Wed, Jul 20, 2011 at 11:06 PM, Harish Sharma < harishsharma...@gmail.com> wrote: > Hi Anish, > > Please find attached the sample file with one row filled. > And Column "C" & Column "H" concatenate should give us unique value so > if this is repeated then we need to have below requested feature. > > Hope this would be more clear now. > > Thanks > Harish > > > > On 20 July 2011 21:32, Anish Shrivastava wrote: > >> One sample sheet would be helpful to us to see the data structure. >> >> However, for this "*Also identifiy if there is any row duplicated and >> highlight them and paste them into another sheet" * There must be any >> particular data field which has to be checked against others in order to >> find duplicates. >> >> Macro for consolidation is not a big deal. >> >> >> On Wed, Jul 20, 2011 at 7:35 PM, Harish Sharma < >> harishsharma...@gmail.com> wrote: >> >>> Hi, >>> >>> I had a macro file which was able to consolidate small files but now >>> I have a requirement to consolidate 30 workbooks saved in a share drive and >>> this data may even cross 66000 row after consolidation. Can someone help me >>> in creating a macro which picks all the file from shared folder and >>> consolidates the one specific worksheet in all the workbook. >>> >>> This macro should also be capable of deleting any blank row in any of >>> these 30 workbooks so that we donot miss any data if someone by error has >>> missed a couple of row and entered the data in couple of row below. >>> >>> 30 workbooks in sharedrive >>> one specific worksheet from all of these named as "Tracker" >>> Should cover all the blanks and delete them so that no data missed >>> Also identifiy if there is any row duplicated and highlight them and >>> paste them into another sheet >>> If data crosses excel 2003 limit of row it should move to next >>> worksheet. >>> >>> Hope to get a solution from excel experts. >>> >>> -- >>> Kind Regards, >>> Harish Sharma >>> >>> -- >>> >>> -- >>> Some important links for excel users: >>> 1. Follow us on TWITTER for tips tricks and links : >>> http://twitter.com/exceldailytip >>> 2. Join our LinkedIN group @ >>> http://www.linkedin.com/groups?gid=1871310 >>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>> To post to this group, send email to excel-macros@googlegroups.com >>> >>> <><><><><><><><><><><><><><><><><><><><><><> >>> Like
Re: $$Excel-Macros$$ Table - extract some data
I would restructure your table to make it simpler but this works. From: x x Sent: Tuesday, August 16, 2011 7:31 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Table - extract some data Folks, please give me a hint about table attached. I'd like to find a way to extract exact and wished value from table but of course regarding pre-existing required conditions. For instance, if someone choose diameter 40, left diameter 10 and delta 60 excel should get in some empty cell the value 40,2. Like crossword game. How to do it? I've tried with filters and some if, vlookup functions but it seems to be to complicated for my excel level of knowledge. Teach me. Many thanks. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel examplei.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ UDF Function for conditoin formetting
I did this VERY recently in another formum. Was it you? Right click sheet tab>view code>insert this Private Sub Worksheet_Change(ByVal Target As Range) Dim tr As Long tr = Target.Row Rows(tr).Borders(xlEdgeBottom).LineStyle = xlNone If Target.Column <> 1 Or Not IsNumeric(Target) Or _ Len(Application.Trim(Target)) < 1 Then Exit Sub Range(Cells(tr, 2), Cells(tr, Target + 1)) _ .Borders(xlEdgeBottom).LineStyle = xlContinuous End Sub From: ICWAI Help Sent: Tuesday, August 16, 2011 11:02 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ UDF Function for conditoin formetting Please provide me UDF functino for below requirement example:) if the cell value is 4 then including active cell next 4 cell in a row should be higleted with color. ex if a1 is 4 then a1 ,b1 ,c 1, d 1, f 1 should be higleted. Thansk, Rakesh. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ UDF Function for conditoin formetting
Use Conditional Formatting formula = =$A2+1>=COLUMN() set patterns to color desired>OK>and copy format Or Simple, just change from drawing a line to coloring the cells. Works when you change the cell. You MUST save the file as an xlsM file for macros and enable macros. Private Sub Worksheet_Change(ByVal Target As Range) Dim tr As Long tr = Target.Row Rows(tr).Interior.ColorIndex = xlNone If Target.Column < 1 Or Not IsNumeric(Target) Or _ Len(Application.Trim(Target)) < 1 Then Exit Sub Range(Cells(tr, 2), Cells(tr, Target + 1)) _ .Interior.ColorIndex = 6 End Sub From: ICWAI Help Sent: Tuesday, August 16, 2011 4:04 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ UDF Function for conditoin formetting Please find the attached file and give me the solution :) if the perticuler cell value is 4 then next 4 coloumn cells in same row shoudl be highleted . On Tue, Aug 16, 2011 at 11:42 PM, dguillett1 wrote: I did this VERY recently in another formum. Was it you? Right click sheet tab>view code>insert this Private Sub Worksheet_Change(ByVal Target As Range) Dim tr As Long tr = Target.Row Rows(tr).Borders(xlEdgeBottom).LineStyle = xlNone If Target.Column <> 1 Or Not IsNumeric(Target) Or _ Len(Application.Trim(Target)) < 1 Then Exit Sub Range(Cells(tr, 2), Cells(tr, Target + 1)) _ .Borders(xlEdgeBottom).LineStyle = xlContinuous End Sub From: ICWAI Help Sent: Tuesday, August 16, 2011 11:02 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ UDF Function for conditoin formetting Please provide me UDF functino for below requirement example:) if the cell value is 4 then including active cell next 4 cell in a row should be higleted with color. ex if a1 is 4 then a1 ,b1 ,c 1, d 1, f 1 should be higleted. Thansk, Rakesh. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Workbooks Consolidation Macro
For a simple solution, simply record a macro while doing it manually. From: Satish Bandaru Sent: Wednesday, August 17, 2011 1:06 AM To: excel-macros@googlegroups.com Subject: Re: FW: $$Excel-Macros$$ Workbooks Consolidation Macro hi experts please help me.plz find the atachment thanks in advance -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Need a Formula or Macro
It would be very helpful if your data could result in an actual case with a real example. You ask to search for data that does NOT exist. -Original Message- From: Baby Patel Sent: Wednesday, August 17, 2011 8:08 AM To: excel-macros Subject: $$Excel-Macros$$ Need a Formula or Macro Hi Guys, I need an urgent help . Attached is the excel file for your reference. Issue : I want the result in the F coulmn of Sheet1. the Sku #from Coulmn B of Sheet 1 will be searched in A column of sheet2. If the SKU was found then it will check the Currency and Margin (B & C column of Sheet 1) with Currency and Price Level ( B & C column of Sheet2). If it matches then , it will take the respective value from column G of sheet 2 and will pop in column F of sheet1. Anyhelp will be highly appreciated. Thanks and regards, Smriti -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Request for Directory Audit Macro
Look in the vba help index for DIR From: crazybond Sent: Wednesday, August 17, 2011 3:30 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Request for Directory Audit Macro Hi Experts, Can a vba code/macro be created/ is available in case one needs to do the audit on the directory/sub-folders/files and get the output of the same in excel sheet with exact name of the file(s) with extension, its location(kind of hyper linked to the files)? And it should ask you what kind of files(.extensions) you are looking for kind of thing? I am looking for file extensions - basically all MS office files, .mp3, .avi, .txt, .pdfs. And also by any chance if you can re-name these file names in the excel sheet and file's name automatically gets updated at its source. Regards J -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ copy cell value from clicked hyperlink
This will do all with the macro
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
With Sheets("Sheet2")
.Range("H6") = Target
.Range("d9") = Target.Offset(, 1)
.Range("d11") = Target.Offset(, 2)
End With
End Sub
-Original Message-
From: Dilip Pandey
Sent: Wednesday, August 17, 2011 11:36 PM
To: excel-macros@googlegroups.com
Cc: vickey
Subject: Re: $$Excel-Macros$$ copy cell value from clicked hyperlink
Hi Vickey,
Hyperlink can never copy values from one place to another, hence given
a two line code which will help you :)
Let me know if this helps you.
Regards,
DILIPandey
On 8/4/11, vickey wrote:
Hi,
Thanks for the same,but my problem has not yet been solved...
Please refer attached sample file
in sheet1 there is Sr. no. name & particular and in sheet2 there is format
like receipt.
now I want that I click sr.no. from sheet1 that sr.no. should copied or
entered in sheet2 H6 cell, because it will minimize the time typing the
number, which will help a lot.
Vickey
On Thursday, 4 August 2011 10:32:51 UTC+5:30, DILIPandey wrote:
Hi Vickey,
Based on your description, attached is the solution. Let us know if this
works.
Thanks,
DILIPandey
On 8/3/11, vickey wrote:
> Is there any way to to get cell value from clicked cell hyperlink.
>
> i.e. if there is sr.no. 1 in cell A1 in sheet1 and it is hyper linked
> with sheet2 the value of sheet1 A1 (1) should reflect in Sheet2 A1 or
> anywhere.
>
> Please help me out to get it solved.
>
> thanks in advance..
>
>
> vickey
>
> --
>
--
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-...@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Thanks & Regards,
DILIP KUMAR PANDEY, mvp
MBA,B.Com(Hons),BCA
Mobile: +91 9810929744
dilip...@gmail.com
dilip...@yahoo.com
New Delhi - 62, India
--
Thanks & Regards,
DILIP KUMAR PANDEY, mvp
MBA,B.Com(Hons),BCA
Mobile: +91 9810929744
dilipan...@gmail.com
dilipan...@yahoo.com
New Delhi - 62, India
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Need a Formula or Macro
Since you do not give a proper example, It is not yet clear what you want. -Original Message- From: Baby Patel Sent: Thursday, August 18, 2011 8:29 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Need a Formula or Macro Hi All, Please find the attchement. All these data are here in the zipped file. You can use price list to find the details. Thanks and regards, Smriti On 8/18/11, dguillett1 wrote: It would be very helpful if your data could result in an actual case with a real example. You ask to search for data that does NOT exist. -Original Message- From: Baby Patel Sent: Wednesday, August 17, 2011 8:08 AM To: excel-macros Subject: $$Excel-Macros$$ Need a Formula or Macro Hi Guys, I need an urgent help . Attached is the excel file for your reference. Issue : I want the result in the F coulmn of Sheet1. the Sku #from Coulmn B of Sheet 1 will be searched in A column of sheet2. If the SKU was found then it will check the Currency and Margin (B & C column of Sheet 1) with Currency and Price Level ( B & C column of Sheet2). If it matches then , it will take the respective value from column G of sheet 2 and will pop in column F of sheet1. Anyhelp will be highly appreciated. Thanks and regards, Smriti -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ filter out the cells with a particular formula
Sub FOREACHTRUE() mc = "h" 'change to column with formula For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row If InStr(Cells(i, mc).Formula, "TRUE") Then _ Rows(i).Hidden = True Next End Sub From: ashish koul Sent: Thursday, August 18, 2011 8:05 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ filter out the cells with a particular formula see if it helps On Fri, Aug 19, 2011 at 6:24 AM, Sara Lee wrote: hi how do i filter out those cells in a column which has a particular vlookup formula in them... for example in one column in a table, i have vlook up fomula set up for the entire column ... some of the cells have vlook up( with true parameter) and some with (false parameter) How do i filter only those cells which have true parameter? any macro ? Thanks -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Ashish Koul akoul.blogspot.com http://akoul.posterous.com/ akoul.wordpress.com My Linkedin Profile P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Help with Formula
A simple vlookup formula such as this to lookup the item in the IS sheet when you know the column should do it. You can then use edit/replace to change the formulas. A one click macro could do it all. =VLOOKUP(J9,'2011 Actual'!B2:AG331,10,0) From: Steve Weaver Sent: Friday, August 19, 2011 11:53 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Help with Formula Hi, Can some one help me to create a formula in excel. Using the attached file, please refer to the "Monthly IS" tab, column D. I need help writing a formula that will: 1.. retrieve the appropriate value from the "2011 Actual" tab based on the month shown in "Monthly IS" tab, cell J3. 2.. retrieve the appropriate value from the "2011 Forecast" tab based on the month shown in "Monthly IS" tab, cell J3. Thank you for your help. Steve -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Hide Rows that are NOT Yellow
Post your file with a complete explanation and tell us your excel version. -Original Message- From: Bob Sent: Saturday, August 20, 2011 7:46 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Hide Rows that are NOT Yellow On Sheet2 I have some rows that are highlighted in yellow and some not. I'd like for when the user clicks on the command button to hide all the rows NOT in Yellow. And another command button to unhide. I'm familiar with installing the command buttons, just don't know how to write the code to hide and unhide. Thanks so much for any help. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Re: Help with Formula
I meant to say you can NOT use that part of the formula for the vlookup for
Actual but you can use the match part for the Forecast sheet.
-Original Message-
From: Don Guillett
Sent: Saturday, August 20, 2011 1:08 PM
To: MS EXCEL AND VBA MACROS
Subject: Re: $$Excel-Macros$$ Re: Help with Formula
You can use this part of the formula for the simple vlookup
COLUMN'=MATCH(TEXT($J$3,"MM/yy"),'2011 Actual'!$A$1:$X$1,0)
On Aug 20, 6:43 am, Steve Weaver wrote:
Sam, thank you so much . . . the formula works GREAT!!!
Steve
On 8/20/2011 1:05 AM, GoldenLance wrote:
> Use =INDIRECT("'2011
> Forecast'!"&ADDRESS(28,MATCH(TEXT(J3,""),'2011 Forecast'!$A$2:$X
> $2,0))) in Monthly IS, range F9
> Steve, sure you can replicate this for all your cells.
> Regards,
> Sam Mathai Chacko (GL)
> On Aug 19, 9:53 pm, Steve Weaver wrote:
>> Hi,
>> Can some one help me to create a formula in excel. Using the attached
>> file, please refer to the "Monthly IS" tab, column D. I need help
>> writing a formula that will:
>> 1. retrieve the appropriate value from the "2011 Actual" tab based on
>> the month shown in "Monthly IS" tab, cell J3.
>> 2. retrieve the appropriate value from the "2011 Forecast" tab based
>> on
>> the month shown in "Monthly IS" tab, cell J3.
>> Thank you for your help.
>> Steve
>> IS Example.XLS
>> 640KViewDownload- Hide quoted text -
- Show quoted text -
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Automatic Birth Day Outlook Mail Message
Sounds a bit like “homework” From: pankaj gmail account Sent: Sunday, August 21, 2011 11:01 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Automatic Birth Day Outlook Mail Message Dear Group, Can you people help me I want to create a Excel based Birthday Mail which will automatically forward to the User whose birthday is and CC to his upervisor with his name and OFT template image or a image. in Excel :- Name --- Date of Birth-- Supervisor mail id --- User mail Id when date of birth matches todays date it automatically send Image and his name saying Happy Birthday through Outlook. Version 2007. Thanks to Group in Advance for Helping. -- Pankaj Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Please help
I sent the original answer. I should have told you to save your file as an .xlsM (macro enabled file) From: Sundarvelan N Sent: Monday, August 22, 2011 6:59 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Please help Hi Team, Still i'm notable to find the solution for this task. Please find the attachments. Thanks N.Sundarvelan 9600160150 On Thu, Aug 11, 2011 at 3:18 AM, dguillett1 wrote: Or with col 8 formatted as desired place this in the SHEET module Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 2 Or Target.Column <> 7 Then Exit Sub If UCase(Target) = "DONE" Then Target.Offset(, 1) = Date End Sub -Original Message- From: XLS S Sent: Wednesday, August 10, 2011 1:19 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Please help Hey Sundaryelan, Please find the attachment . On Wed, Aug 10, 2011 at 10:22 AM, Sundarvelan N wrote: Hi Friends, Please refer the attached image. Please help on this scenatio. Thanks N.Sundarvelan 9600160150 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- . -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ how to delete multiple row & columns
If I understand your request, you may like this. It also saves the file
Sub cleanrowsandcolumnsSAS()
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByColumns, xlPrevious).Column
Application.EnableEvents = False
Rows(lr).Resize(Rows.Count - lr).Delete
Columns(lc).Resize(, Columns.Count - lr).Delete
Application.EnableEvents = True
ActiveWorkbook.Save
End Sub
From: Amit Desai (MERU)
Sent: Tuesday, August 23, 2011 1:32 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ how to delete multiple row & coulmns
Dear All,
Please let me know how to delete multiple rows(say from my last data based row
# 678 to last row) & multiple columns (say from AA to last column).
This is taking a lot of file size…
Best Regards,
Amit Desai
Disclaimer: This message and its attachments contain confidential information
and may also contain legally privileged information. This message is intended
solely for the named addressee. If you are not the addressee indicated in this
message (or authorized to receive for addressee), you may not copy or deliver
any part of this message or its attachments to anyone or use any part of this
message or its attachments. Rather, you should permanently delete this message
and its attachments (and all copies) from your system and kindly notify the
sender by reply e-mail. Any content of this message and its attachments that
does not relate to the official business of Meru Cab Company Pvt. Ltd. must be
taken not to have been sent or endorsed by any of them. Email communications
are not private and no warranty is made that e-mail communications are timely,
secure or free from computer virus or other defect.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Need help in maro
Or simply use data validation with a list of sheet names and select the one to show and tie to a worksheet_Change event From: Rajan_Verma Sent: Tuesday, August 23, 2011 2:00 AM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Need help in maro Hi Prasad, Ashish’s Code working fine..See the attached Sheet. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Suryaprasad Sent: Tuesday, August 23, 2011 10:56 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Need help in maro Hi All, I was trying to write a macro which is not working well, attached is the sheet where is require a small code the below is the what i require: I have a main sheet which is assgined with controls and i have 5 TABS , what i want is when i click on the first control named with Sheet1 in the main TAB then the sheet1 should open automatically and the rest of the 4 TAB's should be hidden and the same logic should be applicable for all the other controls , please help Thanks Prasad. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Very slow performance by Excel file
I will take a look at your file dguillett1 @gmail.com -Original Message- From: Amit Desai (MERU) Sent: Tuesday, August 23, 2011 6:26 AM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Very slow performance by Excel file I have more than 1000 formulaes...spreaded in 4 sheets...pls suggest solutions.. Best Regards, Amit Desai Assistant Manager, Fleet MIS (Corporate) Desk No.: 022 40520129 Cell No.: 9867232534 -Original Message- From: Amit Desai (MERU) [mailto:amit.de...@merucabs.com] Sent: Saturday, August 20, 2011 6:05 PM To: 'excel-macros@googlegroups.com' Subject: RE: $$Excel-Macros$$ Very slow performance by Excel file That's true. My memory usage shows more than 500 MB usage & my system's RAM is 1GB!!! How can I get rid of this issue..I have put the excel formula caln as manual..but still performance is very slow Best Regards, Amit Desai Assistant Manager, Fleet MIS (Corporate) Desk No.: 022 40520129 Cell No.: 9867232534 -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of XLS S Sent: Friday, August 19, 2011 7:16 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Very slow performance by Excel file Try this... Go to "tools" -- "options" -- select "Calculation" tab -- check on "manual" and make sure you check on "recalculate before save". this is going reduce the time taken by excel to calculate the formulae present in your sheet. hence increasing the performance. I'm sure that you have lot of formulae in your sheet :-) On Fri, Aug 19, 2011 at 10:30 AM, Amit Desai (MERU) wrote: Dear All, I have an excel sheet of about 700 rows & column till AZ. Because of the using formula the same is functioning very slowly. Is there any solution to speed up the performance of file? My system's RAM is of 1 GB. Best Regards, Amit Desai Assistant Manager, Fleet MIS (Corporate) Desk No.: 022 40520129 Cell No.: 9867232534 Disclaimer: This message and its attachments contain confidential information and may also contain legally privileged information. This message is intended solely for the named addressee. If you are not the addressee indicated in this message (or authorized to receive for addressee), you may not copy or deliver any part of this message or its attachments to anyone or use any part of this message or its attachments. Rather, you should permanently delete this message and its attachments (and all copies) from your system and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of Meru Cab Company Pvt. Ltd. must be taken not to have been sent or endorsed by any of them. Email communications are not private and no warranty is made that e-mail communications are timely, secure or free from computer virus or other defect. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- . -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Disclaimer: This message and its attachments contain confidential information and may also contain legally privileged information. This message is intended solely for the named addressee. If you are not the addressee indicated in this message (or authorized to receive for addressee), you may not copy or deliver any part of this message or
Re: $$Excel-Macros$$ how to delete multiple row & columns
It should work as desired for the active sheet. Try it. I need to correct lr to
lc as below.
Sub cleanrowsandcolumnsSAS()
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row + 1
lc = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByColumns, xlPrevious).Column + 1
Application.EnableEvents = False
Rows(lr).Resize(Rows.Count - lr).Delete
Columns(lc).Resize(, Columns.Count - lc).Delete
Application.EnableEvents = True
ActiveWorkbook.Save
End Sub
From: Amit Desai (MERU)
Sent: Tuesday, August 23, 2011 10:36 PM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ how to delete multiple row & columns
Thanks a lot for the solution. Can you tell me how shall I use this? I mean How
shall I paste this code so that macro works.. & hope it will delete only blank
cells on the right & bottom !!!-:)
From: excel-macros@googlegroups.com [excel-macros@googlegroups.com] On Behalf
Of STDEV(i) [setiyowati.d...@gmail.com]
Sent: Wednesday, August 24, 2011 6:29 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ how to delete multiple row & columns
A little correction
Sub cleanrowsandcolumnsSAS()
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row + 1
lc = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByColumns, xlPrevious).Column + 1
Application.EnableEvents = False
Rows(lr).Resize(Rows.Count - lr).Delete
Columns(lc).Resize(, Columns.Count - lr).Delete
Application.EnableEvents = True
ActiveWorkbook.Save
End Sub
'--thank you
siti Vi
On Tue, Aug 23, 2011 at 7:37 PM, dguillett1 wrote:
If I understand your request, you may like this. It also saves the file
Sub cleanrowsandcolumnsSAS()
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByColumns, xlPrevious).Column
Application.EnableEvents = False
Rows(lr).Resize(Rows.Count - lr).Delete
Columns(lc).Resize(, Columns.Count - lr).Delete
Application.EnableEvents = True
ActiveWorkbook.Save
End Sub
From: Amit Desai (MERU)
Sent: Tuesday, August 23, 2011 1:32 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ how to delete multiple row & coulmns
Dear All,
Please let me know how to delete multiple rows(say from my last data based
row # 678 to last row) & multiple columns (say from AA to last column).
This is taking a lot of file size…
Best Regards,
Amit Desai
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Disclaimer: This message and its attachments contain confidential information
and may also contain legally privileged information. This message is intended
solely for the named addressee. If you are not the addressee indicated in this
message (or authorized to receive for addressee), you may not copy or deliver
any part of this message or its attachments to anyone or use any part of this
message or its attachments. Rather, you should permanently delete this message
and its attachments (and all copies) from your system and kindly notify the
sender by reply e-mail. Any content of this message and its attachments that
does not relate to the official business of Meru Cab Company Pvt. Ltd. must be
taken not to have been sent or endorsed by any of them. Email communications
are not private and no warranty is made that e-mail communications are timely,
secure or free from computer virus or other defect.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldai
Re: $$Excel-Macros$$ Need a Formula or Macro
If you didn't solve this, I'll take a look? -Original Message- From: Baby Patel Sent: Thursday, August 18, 2011 8:29 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Need a Formula or Macro Hi All, Please find the attchement. All these data are here in the zipped file. You can use price list to find the details. Thanks and regards, Smriti On 8/18/11, dguillett1 wrote: It would be very helpful if your data could result in an actual case with a real example. You ask to search for data that does NOT exist. -Original Message- From: Baby Patel Sent: Wednesday, August 17, 2011 8:08 AM To: excel-macros Subject: $$Excel-Macros$$ Need a Formula or Macro Hi Guys, I need an urgent help . Attached is the excel file for your reference. Issue : I want the result in the F coulmn of Sheet1. the Sku #from Coulmn B of Sheet 1 will be searched in A column of sheet2. If the SKU was found then it will check the Currency and Margin (B & C column of Sheet 1) with Currency and Price Level ( B & C column of Sheet2). If it matches then , it will take the respective value from column G of sheet 2 and will pop in column F of sheet1. Anyhelp will be highly appreciated. Thanks and regards, Smriti -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Copy the Last Row (Values) of Multiple Sheets to a Summary Sheet
I would probably also use a macro but if you have a list of sheet names in
column A and put this formula in col B and copy over and down
=INDEX(INDIRECT($A2&"!a:z"),MATCH("",INDIRECT($A2&"!A:A"),1),COLUMN()-1)
-Original Message-
From: Clint Stevens
Sent: Wednesday, August 24, 2011 12:45 PM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Copy the Last Row (Values) of Multiple Sheets to a
Summary Sheet
Help: I have 40 spreadsheets in one workbook. I update the
spreadsheets daily. I want the last row (Values) on all 40
spreadsheets copied to a summary sheet everyday after I update the
data. How do you do this? After the data is summarized and I review it
I will then delete the data in the summary sheet and repeat the
process the following day.
Thx, Clint
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Copy the Last Row (Values) of Multiple Sheets to a Summary Sheet
Change "" to 99 if numbers in col a of source sheet
-Original Message-----
From: dguillett1
Sent: Thursday, August 25, 2011 8:06 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Copy the Last Row (Values) of Multiple Sheets
to a Summary Sheet
I would probably also use a macro but if you have a list of sheet names in
column A and put this formula in col B and copy over and down
=INDEX(INDIRECT($A2&"!a:z"),MATCH("",INDIRECT($A2&"!A:A"),1),COLUMN()-1)
-Original Message-
From: Clint Stevens
Sent: Wednesday, August 24, 2011 12:45 PM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Copy the Last Row (Values) of Multiple Sheets to a
Summary Sheet
Help: I have 40 spreadsheets in one workbook. I update the
spreadsheets daily. I want the last row (Values) on all 40
spreadsheets copied to a summary sheet everyday after I update the
data. How do you do this? After the data is summarized and I review it
I will then delete the data in the summary sheet and repeat the
process the following day.
Thx, Clint
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Cut Data from Rows and Paste into Columns
Should do it assuming layout as described -Original Message- From: GreenBriar Sent: Thursday, August 25, 2011 10:55 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Cut Data from Rows and Paste into Columns I have a series of spreadsheets that have data in them that needs to be formatted into 3 columns instead of one column. The data in Row 1 needs to go in row 1, column 1 Row 2 needs to go in row 1, column 2 Row 3 = Row 1, Col 3 Row 4 = Row 2, Col 1 Row 5 = Row 2, Col 2 Row 6 = Row 2 Col 3 Row 7 = Row 3, Col 1, etc to the end of the file. What would be the macro that I need to use? I have been tasked with loading this data into a program but am unsure how to get it into the correct format. Any help would be greatly appreciated. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel TransposeRESIZE.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Need a Formula or Macro
Just found this again. I still do NOT understand your need. Please explain in detail with examples. Please use proper definitions a file is called a workbook. NOT a sheet a worksheet within a file is called a worksheet. -Original Message- From: Baby Patel Sent: Wednesday, August 24, 2011 9:50 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Need a Formula or Macro thanks for your assistance. Please take a look. Earlier did not work. Thanks, Smriti On 8/24/11, dguillett1 wrote: If you didn't solve this, I'll take a look? -Original Message- From: Baby Patel Sent: Thursday, August 18, 2011 8:29 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Need a Formula or Macro Hi All, Please find the attchement. All these data are here in the zipped file. You can use price list to find the details. Thanks and regards, Smriti On 8/18/11, dguillett1 wrote: It would be very helpful if your data could result in an actual case with a real example. You ask to search for data that does NOT exist. -Original Message- From: Baby Patel Sent: Wednesday, August 17, 2011 8:08 AM To: excel-macros Subject: $$Excel-Macros$$ Need a Formula or Macro Hi Guys, I need an urgent help . Attached is the excel file for your reference. Issue : I want the result in the F coulmn of Sheet1. the Sku #from Coulmn B of Sheet 1 will be searched in A column of sheet2. If the SKU was found then it will check the Currency and Margin (B & C column of Sheet 1) with Currency and Price Level ( B & C column of Sheet2). If it matches then , it will take the respective value from column G of sheet 2 and will pop in column F of sheet1. Anyhelp will be highly appreciated. Thanks and regards, Smriti -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -
Re: $$Excel-Macros$$ Re: Cut Data from Rows and Paste into Columns
Could have been more efficient without selections or incrementing i as in my
offering I previously sent
Option Explicit
Sub transposerows()
Dim i As Long
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step 3
Cells(i, 1).Resize(3).Copy
Cells(i, 2).PasteSpecial Transpose:=True
Next i
Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns(1).Delete
End Sub
-Original Message-
From: GreenBriar
Sent: Friday, August 26, 2011 12:59 PM
To: MS EXCEL AND VBA MACROS
Subject: Re: $$Excel-Macros$$ Re: Cut Data from Rows and Paste into Columns
This worked like a charm. Thank you.
On Aug 26, 6:53 am, ashish koul wrote:
Sub test()
Dim i, interval, j As Long
interval = 3
j = 2
For i = 1 To Sheets(1).Range("a1").End(xlDown).Row
Sheets(1).Range("A" & i & ":A" & i + interval - 1).Copy
Range("D" & j).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
i = i + interval - 1
j = j + 1
Next
End Sub
On Fri, Aug 26, 2011 at 7:15 PM, GreenBriar
wrote:
> Transpose would not work because every 4th record (or row) would need to
> go
> back to the first column, next row.
> original data could be in either an excel spreadsheet or a text file,
> whichever works best.
> thus
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> would look like
> 1 2 3
> 4 5 6
> 7 8 9
> --
>
--
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
> To post to this group, send email to excel-macros@googlegroups.com
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
>http://www.facebook.com/discussexcel
--
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com
P Before printing, think about the environment.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Some Sorting Technique
I didn't test the other code but you may want to try this simple version.
Put in sheet module & modify to suit your range and top row
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Range("sortarea").Sort Key1:=Cells(2, ActiveCell.Column),
Order1:=xlAscending
End Sub
-Original Message-
From: Lawali
Sent: Sunday, August 28, 2011 12:36 PM
To: MS EXCEL AND VBA MACROS
Subject: Re: $$Excel-Macros$$ Some Sorting Technique
Wow .you guys are great!!!
I've stumbled upon a sorting code at :
www.clearlyandsimply.com/clearly_and_simply/2011/05/sort-excel-tables-by-double-clicking.html
Here is the code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Dim rngTable As Range
Dim rngActiveColumn As Range
Dim rngOneCell As Range
Dim intSortOrder As Integer
Dim blnNumericCol As Boolean
Dim intExistingSortOrder As Integer '0: unsorted, 1: ascending, 2:
descending
Dim intNewSortOrder As Integer
Dim strFormula1 As String
Dim strFormula2 As String
On Error Resume Next
' Exit sub if double click outside of defined table range name
"myDataTable"
If Application.Intersect(ActiveCell, Range("myData").Cells) Is
Nothing Then Exit Sub
Application.ScreenUpdating = False
' Define the ranges of the table and of the active column
Set rngTable = Range("myData")
Set rngActiveColumn = _
Range("myData").Cells(2, ActiveCell.Column -
Range("myData").Cells(1, 1).Column +
1).Resize(Range("myData").Rows.Count - 1, 1)
' Check if the active column contains numbers or alphanumeric data
blnNumericCol = True
For Each rngOneCell In rngActiveColumn
If Not IsNumeric(rngOneCell) Then
blnNumericCol = False
Exit For
End If
Next rngOneCell
' Check if the column is sorted and detect the existing sort order
(create array formula strings to be evaluated)
strFormula1 = "AND(" &
rngActiveColumn.Resize(rngActiveColumn.Rows.Count - 1, 1).Address &
">=" & _
rngActiveColumn.Resize(rngActiveColumn.Rows.Count
- 1, 1).Offset(1, 0).Address & ")"
strFormula2 = "AND(" &
rngActiveColumn.Resize(rngActiveColumn.Rows.Count - 1, 1).Address &
"<=" & _
rngActiveColumn.Resize(rngActiveColumn.Rows.Count
- 1, 1).Offset(1, 0).Address & ")"
If Evaluate(strFormula1) Then
intExistingSortOrder = 2
ElseIf Evaluate(strFormula2) Then
intExistingSortOrder = 1
Else
intExistingSortOrder = 0
End If
' Set the new sort order
Select Case intExistingSortOrder
Case 0:
If blnNumericCol Then
intNewSortOrder = xlDescending
Else
intNewSortOrder = xlAscending
End If
Case 1: intNewSortOrder = xlDescending
Case 2: intNewSortOrder = xlAscending
End Select
' Sort the table
rngTable.Offset(1, 0).Sort Key1:=Cells(rngTable.Row + 1,
ActiveCell.Column), Order1:=intNewSortOrder, Header:=xlNo,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
' Optional: Set the fill color of the sorted column to a light
grey
rngTable.Offset(1, 0).Resize(rngTable.Rows.Count - 1,
rngTable.Columns.Count).Interior.ColorIndex = xlNone
rngActiveColumn.Interior.Color = RGB(234, 234, 234)
' Clean up
Set rngTable = Nothing
Set rngActiveColumn = Nothing
Set rngOneCell = Nothing
Application.ScreenUpdating = True
End Sub
You need only 2 simple steps to transfer this feature to your own
workbook:
Assign the range name “myData” to your table (including the headers of
the columns).
Go to the VBE (ALT-F11) and copy and paste the code from the workbook
provided above into the sheet module of the worksheet containing the
data table.
Regards.
On Aug 28, 2:52 am, rajan verma wrote:
Great Noorain!!
Does anybody have more method for sorting..
On Sat, Aug 27, 2011 at 9:06 PM, NOORAIN ANSARI
wrote:
> Hi Group,
> I have added some extra sorting tips in rajan's Sorting tips.
> Like
> Sorting through Vlookup/Hlookup
> Sorting Through Sumporduct
> Vertical and Horizental both sorting attached herewith.
> On Sat, Aug 27, 2011 at 6:48 PM, Rajan_Verma
> wrote:
>> Hi Group,
>> Refer the attached sheet with some Data Sorting Example. Hope you will
>> like it
>> ** **
>> Rajan.
>> ** **
>> --
>> ---
>> ---
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>>http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>> To post to this group, send email to excel-macros@googlegroups.com
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below li
Re: $$Excel-Macros$$ User form
What userform? Put this in the ThisWorkbook module Private Sub Workbook_Open() nameofyouruserformhere.Show End Sub From: Suryaprasad Sent: Tuesday, August 30, 2011 7:42 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ User form Hi All, Need a small help , i need to get a pop up of the user form when my excel is opened, see the attachment Thanks Prasad. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Named Range to Dynamic with VBA
You can put this in a regular sub or this worksheet double click event
Right click sheet tab>view code>insert this>double click any cell to name range
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
For Each N In ActiveWorkbook.Names
If InStr(1, N.RefersTo, ActiveSheet.Name, vbTextCompare) > 0 Then
Set X = Intersect(ActiveCell, Range(N.RefersTo))
If Not X Is Nothing Then
fc = Range(N).Cells(1, 1).Address
mc = Range(N).EntireColumn.Address
N.RefersTo = "=OFFSET(" & fc & ",,,COUNTA(" & mc & "))"
End If
End If
Next N
End Sub
From: Daniel
Sent: Tuesday, August 30, 2011 4:34 AM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Named Range to Dynamic with VBA
Hi,
Be cautious with this macro and keep a copy of your workbook. Look at the
attached workbook in which there is an example of error.
Sub test()
Dim N As Name, Var As String, Tabl As Variant
For Each N In ActiveWorkbook.Names
If LCase(Left(N.RefersTo, 7)) <> "=offset" Then
Tabl = Split(Right(N.RefersTo, Len(N.RefersTo) - 1), ":")
Var = Range(Tabl(0)).Resize(, 1).EntireColumn.Address
N.RefersTo = "=OFFSET(" & Tabl(0) & ",,,COUNTA(" & Var & "))"
End If
Next N
End Sub
Regards.
Daniel
De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la
part de Cab Boose
Envoyé : lundi 29 août 2011 23:39
À : excel-macros@googlegroups.com
Objet : $$Excel-Macros$$ Named Range to Dynamic with VBA
Hi
I would like to select an already named range and using a command button
convert it to dynamic named range by columns and rows.
Is this possible ? It would be a lot quicker than doing it manually each
time. I failed to make a lot dynamic when I set them up.
Appreciate your help, I am aged !! (67) and do part time work just a few hours
a week for a small business. Love Excel.
Thanks and regards
Charlie Harris
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Option for countifs & Sumifs formula in MS Excel 2003
=sumproduct((a2:a22=1)*b2:b22) From: Amit Desai (MERU) Sent: Tuesday, August 30, 2011 11:24 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Option for countifs & Sumifs formula in MS Excel 2003 Dear All, Do we have any option for countifs & Sumifs formula in MS Excel 2003? Best Regards, Amit Desai Disclaimer: This message and its attachments contain confidential information and may also contain legally privileged information. This message is intended solely for the named addressee. If you are not the addressee indicated in this message (or authorized to receive for addressee), you may not copy or deliver any part of this message or its attachments to anyone or use any part of this message or its attachments. Rather, you should permanently delete this message and its attachments (and all copies) from your system and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of Meru Cab Company Pvt. Ltd. must be taken not to have been sent or endorsed by any of them. Email communications are not private and no warranty is made that e-mail communications are timely, secure or free from computer virus or other defect. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Calculate Product Sold To Date by Product
in d2 copied down =SUMIF($B$2:B2,B2,$C$2:C2) From: John A. Smith Sent: Tuesday, August 30, 2011 11:09 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Calculate Product Sold To Date by Product Your help please. I have a log of product sold and need to calculate the different product sold to date, like a running accumulation field specific to product listed in column B. Please see my attached spreadsheet. Thanking you in advance for all your great help. John -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Option for countifs & Sumifs formula in MS Excel 2003
=sumproduct((a2:a22=1)*(b2:b22)) From: Amit Desai (MERU) Sent: Tuesday, August 30, 2011 12:03 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Option for countifs & Sumifs formula in MS Excel 2003 What about option for countifS? Best Regards, Amit Desai From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1 Sent: Tuesday, August 30, 2011 10:04 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Option for countifs & Sumifs formula in MS Excel 2003 =sumproduct((a2:a22=1)*b2:b22) From: Amit Desai (MERU) Sent: Tuesday, August 30, 2011 11:24 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Option for countifs & Sumifs formula in MS Excel 2003 Dear All, Do we have any option for countifs & Sumifs formula in MS Excel 2003? Best Regards, Amit Desai Disclaimer: This message and its attachments contain confidential information and may also contain legally privileged information. This message is intended solely for the named addressee. If you are not the addressee indicated in this message (or authorized to receive for addressee), you may not copy or deliver any part of this message or its attachments to anyone or use any part of this message or its attachments. Rather, you should permanently delete this message and its attachments (and all copies) from your system and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of Meru Cab Company Pvt. Ltd. must be taken not to have been sent or endorsed by any of them. Email communications are not private and no warranty is made that e-mail communications are timely, secure or free from computer virus or other defect. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Disclaimer: This message and its attachments contain confidential information and may also contain legally privileged information. This message is intended solely for the named addressee. If you are not the addressee indicated in this message (or authorized to receive for addressee), you may not copy or deliver any part of this message or its attachments to anyone or use any part of this message or its attachments. Rather, you should permanently delete this message and its attachments (and all copies) from your system and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of Meru Cab Company Pvt. Ltd. must be taken not to have been sent or endorsed by any of them. Email communications are not private and no warranty is made that e-mail communications are timely, secure or free from computer virus or other defect. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send em
Re: $$Excel-Macros$$ Option for countifs & Sumifs formula in MS Excel 2003
TRY it!!! From: Amit Desai (MERU) Sent: Tuesday, August 30, 2011 12:18 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Option for countifs & Sumifs formula in MS Excel 2003 But what if I need a count (& not sum)of numbers in a cell? Best Regards, Amit Desai From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1 Sent: Tuesday, August 30, 2011 10:41 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Option for countifs & Sumifs formula in MS Excel 2003 =sumproduct((a2:a22=1)*(b2:b22)) From: Amit Desai (MERU) Sent: Tuesday, August 30, 2011 12:03 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Option for countifs & Sumifs formula in MS Excel 2003 What about option for countifS? Best Regards, Amit Desai From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1 Sent: Tuesday, August 30, 2011 10:04 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Option for countifs & Sumifs formula in MS Excel 2003 =sumproduct((a2:a22=1)*b2:b22) From: Amit Desai (MERU) Sent: Tuesday, August 30, 2011 11:24 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Option for countifs & Sumifs formula in MS Excel 2003 Dear All, Do we have any option for countifs & Sumifs formula in MS Excel 2003? Best Regards, Amit Desai Disclaimer: This message and its attachments contain confidential information and may also contain legally privileged information. This message is intended solely for the named addressee. If you are not the addressee indicated in this message (or authorized to receive for addressee), you may not copy or deliver any part of this message or its attachments to anyone or use any part of this message or its attachments. Rather, you should permanently delete this message and its attachments (and all copies) from your system and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of Meru Cab Company Pvt. Ltd. must be taken not to have been sent or endorsed by any of them. Email communications are not private and no warranty is made that e-mail communications are timely, secure or free from computer virus or other defect. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Disclaimer: This message and its attachments contain confidential information and may also contain legally privileged information. This message is intended solely for the named addressee. If you are not the addressee indicated in this message (or authorized to receive for addressee), you may not copy or deliver any part of this message or its attachments to anyone or use any part of this message or its attachments. Rather, you should permanently delete this message and its attachments (and all copies) from your system and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of Meru Cab Company Pvt. Ltd. must be taken not to have been sent or endorsed by any of them. Email communications are not private and no warranty is m
Re: $$Excel-Macros$$ Macro to remove repeats and merge
Not sure of your criteria but try this Option Explicit Sub getonlylastvaluecolB() Dim i As Long For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 If Left(Cells(i + 1, 1), 4) = Left(Cells(i, 1), 4) Then Rows(i).Delete Next i End Sub === -Original Message- From: Gaetan M Sent: Tuesday, August 30, 2011 4:14 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Macro to remove repeats and merge I have the following: Column A B C D E F 12722 12722 51.5 64.89 RNLO X 12723 12723 51.5 64.89 RNLO X Would you have the solution to replace B1 by B2 and delete row 2 once completed since they have been merge? Thanks, G. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Macro to remove repeats and merge
Not sure of your criteria but try this Option Explicit Sub getonlylastvaluecolB() Dim i As Long For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 If Left(Cells(i + 1, 1), 4) = Left(Cells(i, 1), 4) Then Rows(i).Delete Next i End Sub -Original Message- From: Gaetan M Sent: Tuesday, August 30, 2011 4:14 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Macro to remove repeats and merge I have the following: Column A B C D E F 12722 12722 51.5 64.89 RNLO X 12723 12723 51.5 64.89 RNLO X Would you have the solution to replace B1 by B2 and delete row 2 once completed since they have been merge? Thanks, G. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
