Re: $$Excel-Macros$$ VBA to copy the entire data without the button in the sheet

[╰» ℓαℓιт мσαнη]

Hi

Give this also a try.

sheet2.resize(sheet1.usedrange.rows.count,
sheet1.usedrange.columns.count).value = sheet1.usedrange.value

Regards,
Lalit Mohan


On Sat, Jul 14, 2012 at 1:12 AM, dguillett1  wrote:
> and then
> activesheet.shapes.cut
>
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguille...@gmail.com
>
> From: NOORAIN ANSARI
> Sent: Friday, July 13, 2012 1:47 PM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ VBA to copy the entire data without the button
> in the sheet
>
> Dear Vinay,
>
> Please try
> Sheets(1).copy after:=sheets(sheets.count)
>
> On Fri, Jul 13, 2012 at 9:46 PM, t vinay  wrote:
>>
>> Hi Team,
>>
>> Can any one help me with a vba code to copy the entire sheet into a new
>> one without the button in that sheet.
>>
>> thanks for your help!!
>>
>> --
>> Regards,
>>
>> Vinay. T
>>
>> --
>> 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

-- 
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: Error Cleaning

[╰» ℓαℓιт мσαнη]

Hi,

Please attach file with sample data to make it more clear.

Regards,
Lalit Mohan


On Sat, Jul 14, 2012 at 3:11 AM, bpascal123  wrote:

> Hi,
>
> You say:
>
> I need your help again.We extract the data from database & each user got
>> some errors on different projects.I need to clean the error data to get the
>> count of errors.For this,i do text to column on error column
>
>
>  What settings do you apply for text to columns?
>
> If you first choose "Delimited" then tick "Comma" delimiters box you
> should get your error column automatically split into 3 columns which
> should then be easier to process manually or with vba if someone wants to
> code that.
>
>
> 
>
>
> Regards,
>
> Pascal Baro
> bpascal...@gmail.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$$ Error--Cant Exit Design Mode

[╰» ℓαℓιт мσαнη]

Hi,

Can u provide the file to make it more clear.

Regards,
Lalit Mohan


On Sat, Jul 14, 2012 at 1:36 AM, hanumant shinde  wrote:
> Hi friends.
>
> there is huge macro file which throws an error as "Cant exit design Mode"
> for few users at few occasions.
> we don't know when and why this error comes but it was told us that this
> error comes when we open Office 2003 file which has ActiveX control buttons
> in office 2007 version.
>
> can you provide your thoughts and feedback.
>
> Workaround:
> if we simply open the error file and save it after giving error the issue
> disappears but reappear after few days. we have no idea why is it happening.
>
>
> --
> 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$$ File opens read only on same excel instance

[╰» ℓαℓιт мσαнη]

Hello,

There could be 1 of 3 things affecting this:

1. Is another application using that excel document? If so you will
not be able to edit it until the other application stops using it.

2. Is the file currently in a location that you do not have write
permissions? eg. another users shared documents

3. Check in the properties to see if it is set to read only. (right
click on the document and click properties. in the 'General' Tab see
if 'Read Only' is checked. If yes, uncheck it and click okay)

An easy way to make it editable providing you are the only one using
it (If the above fixes dont work) open it up in excel and then click
File --> Save As... and then save it somewhere else. That will then
make it editable for you.



Regards,
Lalit Mohan


On Fri, Jul 13, 2012 at 8:16 PM, Divaker Pandey  wrote:
> Hi Expert,
>
> I am facing one issue with excel.
>
> I open a excel workbook xyz.xlsm, it opens without any problem.
> But when I closed only xyz.xlsm, and try to reopen it, i am getting message
> that file is in read only mode.
>
> I tried all option. but still no clue
> What problem may be. can you experts give any clue.
>
> Divaker
>
> --
> 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$$ Query- Global Variable

[╰» ℓαℓιт мσαнη]

Dear

If you want a variabler such that it will works for all the procedure
in a module use below.

dim sh as worksheet
dim rng as range

Private sub abc()

set sh = Thisworkbook.Sheets("Sheet1")
set rng = sh.range("a1:c"&sh.range("c"&rows.count).end(xlup).row)

End sub


or else  If you want it access from any module try below one..

Public sh as worksheet
Public rng as range

Private sub abc()

set sh = Thisworkbook.Sheets("Sheet1")
set rng = sh.range("a1:c"&sh.range("c"&rows.count).end(xlup).row)

End sub

Regards,
Lalit Mohan


On Fri, Jul 13, 2012 at 4:51 PM, Excel Vba  wrote:
> Hi Group,
>
>
> How can i declare a global variable for below mentioned.
>
>
>
> Private sub abc()
> dim sh as worksheet
> dim rng as range
>
> set sh = Thisworkbook.Sheets("Sheet1")
> set rng = sh.range("a1:c"&sh.range("c"&rows.count).end(xlup).row)
>
> End sub
>
>
> In my module i have 20 procedure and in future comes many more.
> So, my query how to declare a global variable and how to call in my
> procedure.
>
> Regards,
> excelvba.lear...@gmail.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$$ Error Cleaning

[Asa Rossoff]

Hi SG,

Please provide also a sample of the result you want.  If your spreadsheet
has rows that do NOT have errors, include some of those in your sample as
well. If there are additional columns from the ones you included in the file
you already sent, please include them.

:)

Asa

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of SG
Sent: Friday, July 13, 2012 5:04 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Error Cleaning

 

Hi Experts,

 

I need your help again.We extract the data from database & each user got
some errors on different projects.I need to clean the error data to get the
count of errors.For this,i do text to column on error column.Then, in each
column after error column, i take the value one by one & copy paste the data
below the original data.It's too time consuming as data may expand up to
many columns & each column has irregular no. of errors description.I have
tried to explain best & need a macro to make unique row of erros of all
users.I have attached the excel file & a document in which i have tried to
explain.Looking forward for your help.

 

Thanks in advance.

-- 
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

$$Excel-Macros$$ Re: Error Cleaning

[bpascal123]

Hi,

You say:

I need your help again.We extract the data from database & each user got 
> some errors on different projects.I need to clean the error data to get the 
> count of errors.For this,i do text to column on error column 


 What settings do you apply for text to columns?

If you first choose "Delimited" then tick "Comma" delimiters box you should 
get your error column automatically split into 3 columns which should then 
be easier to process manually or with vba if someone wants to code that.




Regards,

Pascal Baro
bpascal...@gmail.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

$$Excel-Macros$$ Sherry's HotList

[sherry ..]

*Hi,*

*Hope you are doing great today..*

*Please find the updated hotlist of my consultants..*

*Please send the requirements to she...@nityainc.com or feel free to call
me at 801-833-0416*

* *

*Consultant*

*Technology*

*Location*

*Relocation*

*Karthik*

*Oracle apps Finance functional consultant*

*Moore, OK*

*Anywhere*

*Konda*

*Oracle Apps SCM Consultant*

*Chicago,IL
   *

*Chicago, IL*

*Arun*

*Oracle Apps Techno- functional*

*Texas*

*CA and UT*

*Chanikya*

*Oracle Apps technical Consultant*

*Indianapolis, IN*

*Anywhere *

*Padma*

*.Net Developer*

*Salt Lake City, UT*

*Salt Lake City, UT*

*Shilpa*

*Informatica Developer*

*Hopkins, MN*

*Salt Lake City, UT*

*Sneha*

*Java Developer*

*Kansas City, MO*

*Only TX*



*Thanks & Regards**...**?*

* *

*Sherry*

*NITYA** **Software Solutions, Inc***

*Direct:801-833-0416;  Office : 801-983-2771;  Fax:801-606-7919***

*Email: **she...@nityainc.com* *; Web: **
www.nityainc.com* **

*[image: Description: Description: Description:
http://images.wisestamp.com/yahoo.png]**&** **[image: Description:
Description: Description: http://images.wisestamp.com/gtalk.png]**: **
sherry.nityainc***

*9690 South 300 West, #319 Salt Lake City, UT 84070***

-- 
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

$$Excel-Macros$$ Error--Cant Exit Design Mode

[hanumant shinde]

Hi friends.

there is huge macro file which throws an error as "Cant exit design Mode" for 
few users at few occasions.
we don't know when and why this error comes but it was told us that this error 
comes when we open Office 2003 file which has ActiveX control buttons in office 
2007 version.

can you provide your thoughts and feedback.

Workaround:
if we simply open the error file and save it after giving error the issue 
disappears but reappear after few days. we have no idea why is it happening.

-- 
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 to copy the entire data without the button in the sheet

[dguillett1]

and then
activesheet.shapes.cut

Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com

From: NOORAIN ANSARI 
Sent: Friday, July 13, 2012 1:47 PM
To: excel-macros@googlegroups.com 
Subject: Re: $$Excel-Macros$$ VBA to copy the entire data without the button in 
the sheet

Dear Vinay,

Please try
Sheets(1).copy after:=sheets(sheets.count)


On Fri, Jul 13, 2012 at 9:46 PM, t vinay  wrote:

  Hi Team, 

  Can any one help me with a vba code to copy the entire sheet into a new one 
without the button in that sheet.

  thanks for your help!!


  -- 

  Regards,

  Vinay. T

  -- 
  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$$ VBA to copy the entire data without the button in the sheet

[NOORAIN ANSARI]

Dear Vinay,

Please try
Sheets(1).copy after:=sheets(sheets.count)

On Fri, Jul 13, 2012 at 9:46 PM, t vinay  wrote:

> Hi Team,
>
> Can any one help me with a vba code to copy the entire sheet into a new
> one without the button in that sheet.
>
> thanks for your help!!
>
> --
> Regards,
>
> Vinay. T
>
>  --
> 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




-- 
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

Re: $$Excel-Macros$$ long opening file on the external server

[pawel lupinski]

Hi Noorain,
 
unfortunatelly it won't help, but you give me some thoughts,
 
Thanks you it helps
 
Pawel
 


 From: NOORAIN ANSARI 
To: excel-macros@googlegroups.com 
Sent: Friday, July 13, 2012 10:21 AM
Subject: Re: $$Excel-Macros$$ long opening file on the external server
  

Hi lupinski,

Please try it once again..

Option Explicit
Sub SHRINK_EXCEL_FILE_SIZE()

    Dim WSheet As Worksheet
    Dim CSheet As String 'New Worksheet
    Dim OSheet As String 'Old WorkSheet
    Dim Col As Long
    Dim ECol As Long 'Last Column
    Dim lRow As Long
    Dim BRow As Long 'Last Row
    Dim Pic As Object
   
    For Each WSheet In Worksheets
        WSheet.Activate
         'Put the sheets in a variable to make it easy to go back and forth
        CSheet = WSheet.Name
         'Rename the sheet to its name with _Delete at the end
        OSheet = CSheet & "_Delete"
        WSheet.Name = OSheet
         'Add a new sheet and call it the original sheets name
        Sheets.Add
        ActiveSheet.Name = CSheet
        Sheets(OSheet).Activate
         'Find the bottom cell of data on each column and find the further row
        For Col = 1 To Columns.Count 'Find the actual last bottom row
            If Cells(Rows.Count, Col).End(xlUp).Row > BRow Then
                BRow = Cells(Rows.Count, Col).End(xlUp).Row
            End If
        Next
       
        'Find the end cell of data on each row that has data and find the 
furthest one
        For lRow = 1 To BRow 'Find the actual last right column
            If Cells(lRow, Columns.Count).End(xlToLeft).
Column > ECol Then
                ECol = Cells(lRow, Columns.Count).End(xlToLeft).Column
            End If
        Next
       
         'Copy the REAL set of data
        Range(Cells(1, 1), Cells(BRow, ECol)).Copy
        Sheets(CSheet).Activate
         'Paste Every Thing
        Range("A1").PasteSpecial xlPasteAll
         'Paste Column Widths
        Range("A1").PasteSpecial xlPasteColumnWidths

        Sheets(OSheet).Activate
        For Each Pic In ActiveSheet.Pictures
            Pic.Copy
            Sheets(CSheet).Paste
            Sheets(CSheet).Pictures(Pic.Index).Top = Pic.Top
            Sheets(CSheet).Pictures(Pic.Index).Left = Pic.Left
        Next Pic
        Sheets(CSheet).Activate
       
        'Reset the variable for the next sheet
        BRow = 0
        ECol = 0
    Next WSheet
   
    ' Since, Excel will automatically replace the sheet references for you on 
your formulas,
     ' the below part puts them back.
     ' This is done with a simple replace, replacing _Delete with nothing
    For Each WSheet In Worksheets
        WSheet.Activate
        Cells.Replace "_Delete", ""
    Next WSheet
   
    'Roll through the sheets and delete the original fat sheets
    For Each WSheet In Worksheets
        If Not Len(Replace(WSheet.Name, "_Delete", "")) = Len(WSheet.Name) Then
            Application.DisplayAlerts = False
            WSheet.Delete
            Application.DisplayAlerts = True
        End If
    Next
End Sub

Hope it will help to you.



On Fri, Jul 13, 2012 at 12:14 PM, pawel lupinski  wrote:

Hi All,
>
>I need your help on this issue. I have quite big file (21MB) that is doing a 
>lot of calculation between tabs, in order to run this file more efficient I've 
>switch off calculation, and I'm switch it on when needed. When I've done this 
>file on my computer its opening slowly but not so bad 10 sec. The problem is: 
>when I've put this file on company server for other users its a nightmare. 
>Yesterday I've been waiting to open about 6-10 min!!! I've checked this on 
>different workstation and the same opening time unbelievable. 
>Do you have any thoughts? How Can I change this? What can improve it?
>
>Need your help
>
>Pawel
-- 
>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
http://www.noorainansari.com/
www.excelmacroworld.blogspot.com 
 



-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor threa

$$Excel-Macros$$ VBA to copy the entire data without the button in the sheet

[t vinay]

Hi Team,

Can any one help me with a vba code to copy the entire sheet into a new one
without the button in that sheet.

thanks for your help!!

-- 
Regards,

Vinay. T

-- 
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$$ Search for keyword, and move all rows above found keyword to specific sheet

[Paul Schreiner]

I think I'd skip the part about reading the "raw" data into the "Paste" Sheet.
Instead, I'd read from the text file and place it in the appropriate sheet.
A simple method would be like this:

Option Explicit
Sub ReadData()
    Dim TDataFile, TextLine, TxtArray
    Dim Sht, oRow, inx
    
    TDataFile = "C:\temp\barber.txt"
    Sht = 1
    oRow = 1
    ' Open file -
    Open TDataFile For Input As #1
    ' Loop until end of file --
    Do While Not EOF(1)
    ' Read line into variable
    Line Input #1, TextLine
    '---split line using "," delimeter--
    If (UCase(Left(TextLine, 6)) = "TOTAL:") Then
    Sht = Sht + 1
    oRow = 1
    Else
    TxtArray = Split(TextLine, Chr(9))
    oRow = oRow + 1
    For inx = 0 To UBound(TxtArray)
    Sheets(Sht).Cells(oRow, inx + 1).Value = TxtArray(inx)
    Next inx
    End If
    Loop
    Close #1    ' Close file.
End Sub

this assumes a couple of things.
The destination sheets are in the first three sheets 
(if the first destination sheet is sheet2,then change the starting value of 
oRow)
Next, it assumes the data is tab delimited (chr(9)) if you are using a 
different 
delimiter, thenchange the "Split" line.

 
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-





From: JsinSk 
To: excel-macros@googlegroups.com
Sent: Fri, July 13, 2012 10:13:32 AM
Subject: $$Excel-Macros$$ Search for keyword, and move all rows above found 
keyword to specific sheet

Hello Group, 

I'm attempting to automatically format a report that has several parts and 
break 
it down to what I need.

The report comes in plain text and has three parts that all in with the phrase 
"total :" in the last part of that section. I'd like to see about having it 
break down each section into different sheets. I currently have it broken down 
to four sheets. 

Sheet 1 "Paste Sheet" - raw data goes here
Sheet 2 "Listing" - First section of the raw data is put here
Sheet 3 "Bond" - Second section of raw data
Sheet 4 "Clear" - Third section

The way I currently do the is is search for "total :" scroll up to the top of 
the paste sheet until I get to the top row and cut all data and move to the 
first sheet. Delete blank rows still the next section and repeat searching for 
total :. Then I do some text to columns to format the data to my requirements. 
All the formatting is done already just need help getting the data to 
automatically move to different sheets.

Uploading sample data could be done if needed, will just require quite a bit of 
time to cleanse confidential information.

Regards,
Jason-- 
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

$$Excel-Macros$$ File opens read only on same excel instance

[Divaker Pandey]

Hi Expert,

I am facing one issue with excel.

I open a excel workbook xyz.xlsm, it opens without any problem.
But when I closed only xyz.xlsm, and try to reopen it, i am getting message
that file is in read only mode.

I tried all option. but still no clue
What problem may be. can you experts give any clue.

Divaker

-- 
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

$$Excel-Macros$$ Search for keyword, and move all rows above found keyword to specific sheet

[JsinSk]

Hello Group,

I'm attempting to automatically format a report that has several parts and 
break it down to what I need.

The report comes in plain text and has three parts that all in with the 
phrase "total :" in the last part of that section. I'd like to see about 
having it break down each section into different sheets. I currently have 
it broken down to four sheets. 

Sheet 1 "Paste Sheet" - raw data goes here
Sheet 2 "Listing" - First section of the raw data is put here
Sheet 3 "Bond" - Second section of raw data
Sheet 4 "Clear" - Third section

The way I currently do the is is search for "total :" scroll up to the top 
of the paste sheet until I get to the top row and cut all data and move to 
the first sheet. Delete blank rows still the next section and repeat 
searching for total :. Then I do some text to columns to format the data to 
my requirements. All the formatting is done already just need help getting 
the data to automatically move to different sheets.

Uploading sample data could be done if needed, will just require quite a 
bit of time to cleanse confidential information.

Regards,
Jason

-- 
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: Query- Global Variable

[noorain . ansari]

Dear Hemant,

Pls declear variable as Public and it will declear out of pocedure

Like
Public abc as string
Sub A()
Abc="ram"
Msg abc
End sub

Sub B()
Abc="rahim"
Msg abc
End sub
Sent from BlackBerry® on Airtel

-Original Message-
From: Excel Vba 
Sender: excel-macros@googlegroups.com
Date: Fri, 13 Jul 2012 18:24:57 
To: 
Reply-To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Re: Query- Global Variable

Hi Group,

Is it good practise to declare a global variable.
But, when i use global variable in loop it performs better.

Regards,
excelvba.lear...@gmail.com

-
On Fri, Jul 13, 2012 at 4:51 PM, Excel Vba wrote:

> Hi Group,
>
>
> How can i declare a global variable for below mentioned.
>
>
>
> Private sub abc()
> dim sh as worksheet
> dim rng as range
>
> set sh = Thisworkbook.Sheets("Sheet1")
> set rng = sh.range("a1:c"&sh.range("c"&rows.count).end(xlup).row)
>
> End sub
>
>
> In my module i have 20 procedure and in future comes many more.
> So, my query how to declare a global variable and how to call in my
> procedure.
>
> Regards,
> excelvba.lear...@gmail.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

$$Excel-Macros$$ Re: Query- Global Variable

[Excel Vba]

Hi Group,

Is it good practise to declare a global variable.
But, when i use global variable in loop it performs better.

Regards,
excelvba.lear...@gmail.com

-
On Fri, Jul 13, 2012 at 4:51 PM, Excel Vba wrote:

> Hi Group,
>
>
> How can i declare a global variable for below mentioned.
>
>
>
> Private sub abc()
> dim sh as worksheet
> dim rng as range
>
> set sh = Thisworkbook.Sheets("Sheet1")
> set rng = sh.range("a1:c"&sh.range("c"&rows.count).end(xlup).row)
>
> End sub
>
>
> In my module i have 20 procedure and in future comes many more.
> So, my query how to declare a global variable and how to call in my
> procedure.
>
> Regards,
> excelvba.lear...@gmail.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$$ R1C1

[Excel Vba]

Hi Sunnie,

Goto File Menu Bar, Click to options

On Thu, Jul 12, 2012 at 7:13 PM, Sunnie  wrote:

> I received a spreadsheet that is using the R1C1 reference formula.   If I
> turn turn off the R1C1 to apply the A1 reference will that then change how
> the formula works?
>
> 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$$ R1C1

[Anil Gawli]

Dear Sunnie
Uncheck R1c1 check box from file-Options-Formula.

Hope it should work

Regards,
Gawli Anil

On Fri, Jul 13, 2012 at 8:20 AM, Susan 1 <> wrote:

> Attached are the two spreadsheets.  The one titled Aug2012 has the R1C1
> formula, the file ETS2011, is the data template for this year's entry.
>
> Basically what I was asked to do is move a few columns around and add more
> columns to Aug2012 workbook.  Most of the columns can be joined into two or
> three categories, and I will create sheets for those.
>
> I'd like to know if I covert to A1 reference on Aug2012 workbook, if it
> will still calculate correctly, taking the new sheets in consideration.
>
> if you have issues opening the files in Excel 2010 , I found that saving
> the files to the desktop then opening Excel and select open from Excel
> works.
>
>
> Thanks!
>
>
> On Thu, Jul 12, 2012 at 8:11 AM, NOORAIN ANSARI 
> wrote:
>
>> Sunnie, Can you share spreadsheet with group as a sample.
>>
>> --
>> Thanks & regards,
>> Noorain Ansari
>> www.noorainansari.com
>> www.excelmacroworld.blogspot.com
>>
>>
>> On Thu, Jul 12, 2012 at 7:13 PM, Sunnie  wrote:
>>
>>> I received a spreadsheet that is using the R1C1 reference formula.   If
>>> I turn turn off the R1C1 to apply the A1 reference will that then change
>>> how the formula works?
>>>
>>> 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
>>
>
> --
> 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
>



-- 
Thanks & Regards,
Gawli Anil Narayan
Software Developer,
Abacus Software Services Pvt Ltd

-- 
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 
sign

$$Excel-Macros$$ Query- Global Variable

[Excel Vba]

Hi Group,


How can i declare a global variable for below mentioned.



Private sub abc()
dim sh as worksheet
dim rng as range

set sh = Thisworkbook.Sheets("Sheet1")
set rng = sh.range("a1:c"&sh.range("c"&rows.count).end(xlup).row)

End sub


In my module i have 20 procedure and in future comes many more.
So, my query how to declare a global variable and how to call in my
procedure.

Regards,
excelvba.lear...@gmail.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$$ Unique list

[NOORAIN ANSARI]

Dear Joseph,

>
>> Please try through Excel..
>>
>>
>>
>> =IFERROR(INDEX(Lists1,MATCH(0,COUNTIF($F$1:F1,Lists1),0)),IFERROR(INDEX(List2,MATCH(0,COUNTIF($F$1:F1,List2),0)),IFERROR(INDEX(Lists2,MATCH(0,COUNTIF($F$1:F1,Lists2),0)),IFERROR(INDEX(List3,MATCH(0,COUNTIF($F$1:F1,List3),0)),""
>>
>> Press ctrl+shift+enter
>
>
>> See attached sheet.
>>
>>
>> On Thu, Jul 12, 2012 at 10:05 PM,  wrote:
>>
>>> **
>>> Thanks Noorain,
>>>
>>> Can you give me a formula to retrieve this.
>>>
>>>
>>> Thanks,
>>> Joseph
>>> Sent on my BlackBerry® from Vodafone
>>> --
>>> *From: * NOORAIN ANSARI 
>>> *Sender: * excel-macros@googlegroups.com
>>> *Date: *Thu, 12 Jul 2012 11:47:55 +0530
>>> *To: *
>>> *ReplyTo: * excel-macros@googlegroups.com
>>> *Subject: *Re: $$Excel-Macros$$ Unique list
>>>
>>> Dear Joseph,
>>>
>>> Please try it..
>>>
>>> Sub Unique_List_Creater()
>>> Dim i As Long
>>> Dim col As Long
>>> Dim rw As Long
>>> Dim sh As Worksheet
>>> Application.ScreenUpdating = False
>>> Application.Calculation = xlCalculationManual
>>> Set sh = ThisWorkbook.Sheets(3)
>>> sh.Range("F2:F" & sh.Cells(Rows.Count, "F").End(xlUp).Row).
>>> ClearContents
>>> sh.Range("F1").Value = "Unique List"
>>> i = sh.Cells(Rows.Count, "F").End(xlUp).Row + 1
>>> j = 2
>>> For col = 1 To sh.Cells(1, 1).End(xlToRight).Column
>>> For rw = 2 To sh.Cells(Rows.Count, 1).End(xlUp).Row
>>> sh.Cells(rw, col).Copy sh.Cells(j, "F")
>>> j = j + 1
>>> Next rw
>>> Next col
>>> sh.Range("F2:F" & sh.Cells(Rows.Count,
>>> "F").End(xlUp).Row).RemoveDuplicates 1
>>> Application.ScreenUpdating = True
>>> Application.Calculation = xlCalculationAutomatic
>>> End Sub
>>>
>>> see attached sheet for reference
>>>
>>>
>>> On Thu, Jul 12, 2012 at 9:11 AM,  wrote:
>>>
 Hi experts,

 The formulae provided by the members is working fine when I have data
 in one column. But if I have to do the same from multiple columns, then how
 do I do?

 Thanks,
 Joseph
 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
>>>
>>>
>>>
>>>
>>> --
>>> 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 

Re: $$Excel-Macros$$ Need formula to take out total number of boy and girl

[सुनिता पौडेल]

Hi Noorain,

Its working perfectly. I have tried this formulae for other sheets too.

Thanx alot...I need it for my report...

Have a wonderful weekend.

Sunita

On Fri, Jul 13, 2012 at 3:02 PM, NOORAIN ANSARI wrote:

> Hi Sunita,
>
> Please confirm..
>
> Is it not working..
>
> paste in Cell *E2*
> *=SUMPRODUCT(--($E$10:$E$46=$E$1)*($D$10:$D$46=C2))*
>
>
> On Fri, Jul 13, 2012 at 1:22 PM, सुनिता पौडेल  wrote:
>
>> Hi,
>>
>> Still not working :(
>> its 2010
>>
>> On Fri, Jul 13, 2012 at 12:04 PM, NOORAIN ANSARI <
>> noorain.ans...@gmail.com> wrote:
>>
>>> Incase of 2003
>>>
>>> You can also use..
>>>
>>> *=SUMPRODUCT(--($E$10:$E$46=$E$1)*($D$10:$D$46=C2))*
>>>
>>>
>>> On Fri, Jul 13, 2012 at 11:48 AM, NOORAIN ANSARI <
>>> noorain.ans...@gmail.com> wrote:
>>>
 Hi Sunita,

 Please Try it..
 =COUNTIFS($E$10:$E$46,$E$1,$D$10:$D$46,C2)




 On Fri, Jul 13, 2012 at 11:31 AM, सुनिता पौडेल wrote:

> Dear all,
>
> however this is not working out , can you please solve this where i
> went wrong/
> thnx
>
> On Thu, Jul 12, 2012 at 3:36 PM, सुनिता पौडेल wrote:
>
>> wohoo so may options thnx guys ..it really worked out
>>
>> thank you so much
>>
>> Sunita
>>
>> On Thu, Jul 12, 2012 at 3:25 PM, NOORAIN ANSARI <
>> noorain.ans...@gmail.com> wrote:
>>
>>> You Can also use..
>>>
>>> *=DCOUNTA($E$9:$E$49,E9,G1:G2)*
>>> See attached sheet.
>>>
>>>
>>> On Thu, Jul 12, 2012 at 3:04 PM, NOORAIN ANSARI <
>>> noorain.ans...@gmail.com> wrote:
>>>
 Hi Sunita,

 You can also *try..

 =SUMPRODUCT(--($E$10:E49="GIRL"))*

 On Thu, Jul 12, 2012 at 2:54 PM, सुनिता पौडेल 
 wrote:

> Dear all,
>
> Can you please help me to find out the total number of boy and
> girl ? I used countif for their grade and it worked. However, for 
> girl and
> boy seggregation it isnot working.
>
> Thank you very much for the support you all have been providing.
>
> sunita
>
> --
> 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




 --
 Thanks & regards,
 Noorain Ansari
 www.noorainansari.com
 www.excelmacroworld.blogspot.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 con

Re: $$Excel-Macros$$ long opening file on the external server

[Divaker Pandey]

if you can do then.

1. Clear all formatting you have done, and reformat all same(as was
initial) cell in just one step.
2. try to avoid array formula
3. Before open file set calculation mode manual

Divaker




On Fri, Jul 13, 2012 at 2:26 PM, pawel lupinski  wrote:

> I'd like to do this but my knowladge is still not there, sad but true.
> Maybe some other solution, please.
>
> Regards,
>
> Pawel
>
>*From:* Divaker Pandey 
> *To:* excel-macros@googlegroups.com
> *Sent:* Friday, July 13, 2012 9:40 AM
> *Subject:* Re: $$Excel-Macros$$ long opening file on the external server
>
> try to calculate required calculation with the help of macros.
>
> Divaker
>
> On Fri, Jul 13, 2012 at 12:14 PM, pawel lupinski wrote:
>
> Hi All,
>
> I need your help on this issue. I have quite big file (21MB) that is doing
> a lot of calculation between tabs, in order to run this file more efficient
> I've switch off calculation, and I'm switch it on when needed. When I've
> done this file on my computer its opening slowly but not so bad 10 sec. The
> problem is: when I've put this file on company server for other users its a
> nightmare. Yesterday I've been waiting to open about 6-10 min!!! I've
> checked this on different workstation and the same opening time
> unbelievable.
> Do you have any thoughts? How Can I change this? What can improve it?
>
> Need your help
>
> Pawel
> --
> 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.
>
>
> --
> 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 responsibl

Re: $$Excel-Macros$$ long opening file on the external server

[NOORAIN ANSARI]

Hi lupinski,

Please try it once again..

Option Explicit
Sub SHRINK_EXCEL_FILE_SIZE()

Dim WSheet As Worksheet
Dim CSheet As String 'New Worksheet
Dim OSheet As String 'Old WorkSheet
Dim Col As Long
Dim ECol As Long 'Last Column
Dim lRow As Long
Dim BRow As Long 'Last Row
Dim Pic As Object

For Each WSheet In Worksheets
WSheet.Activate
 'Put the sheets in a variable to make it easy to go back and forth
CSheet = WSheet.Name
 'Rename the sheet to its name with _Delete at the end
OSheet = CSheet & "_Delete"
WSheet.Name = OSheet
 'Add a new sheet and call it the original sheets name
Sheets.Add
ActiveSheet.Name = CSheet
Sheets(OSheet).Activate
 'Find the bottom cell of data on each column and find the further
row
For Col = 1 To Columns.Count 'Find the actual last bottom row
If Cells(Rows.Count, Col).End(xlUp).Row > BRow Then
BRow = Cells(Rows.Count, Col).End(xlUp).Row
End If
Next

 'Find the end cell of data on each row that has data and find the
furthest one
For lRow = 1 To BRow 'Find the actual last right column
If Cells(lRow, Columns.Count).End(xlToLeft).
Column > ECol Then
ECol = Cells(lRow, Columns.Count).End(xlToLeft).Column
End If
Next

 'Copy the REAL set of data
Range(Cells(1, 1), Cells(BRow, ECol)).Copy
Sheets(CSheet).Activate
 'Paste Every Thing
Range("A1").PasteSpecial xlPasteAll
 'Paste Column Widths
Range("A1").PasteSpecial xlPasteColumnWidths

Sheets(OSheet).Activate
For Each Pic In ActiveSheet.Pictures
Pic.Copy
Sheets(CSheet).Paste
Sheets(CSheet).Pictures(Pic.Index).Top = Pic.Top
Sheets(CSheet).Pictures(Pic.Index).Left = Pic.Left
Next Pic
Sheets(CSheet).Activate

 'Reset the variable for the next sheet
BRow = 0
ECol = 0
Next WSheet

 ' Since, Excel will automatically replace the sheet references for you
on your formulas,
 ' the below part puts them back.
 ' This is done with a simple replace, replacing _Delete with nothing
For Each WSheet In Worksheets
WSheet.Activate
Cells.Replace "_Delete", ""
Next WSheet

'Roll through the sheets and delete the original fat sheets
For Each WSheet In Worksheets
If Not Len(Replace(WSheet.Name, "_Delete", "")) = Len(WSheet.Name)
Then
Application.DisplayAlerts = False
WSheet.Delete
Application.DisplayAlerts = True
End If
Next
End Sub

Hope it will help to you.


On Fri, Jul 13, 2012 at 12:14 PM, pawel lupinski wrote:

> Hi All,
>
> I need your help on this issue. I have quite big file (21MB) that is doing
> a lot of calculation between tabs, in order to run this file more efficient
> I've switch off calculation, and I'm switch it on when needed. When I've
> done this file on my computer its opening slowly but not so bad 10 sec. The
> problem is: when I've put this file on company server for other users its a
> nightmare. Yesterday I've been waiting to open about 6-10 min!!! I've
> checked this on different workstation and the same opening time
> unbelievable.
> Do you have any thoughts? How Can I change this? What can improve it?
>
> Need your help
>
> Pawel
>
> --
> 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




-- 
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)  Cr

Re: $$Excel-Macros$$ pls help in conditional formating

[Iqbal Merchant]

Please share the code.

Regards

Iqbal M

On Fri, Jul 13, 2012 at 5:15 AM, ChilExcel  wrote:

> Hi Mohammed
>
> Please see attached file, I think is what you need
>
>
> Chilexcel
>
>
>  2012/7/12 Mohammed Hamed Ajaz 
>
>> Pls help in conditional formatting
>>
>> Example
>>
>> Column A Column B name Column C due date   columne  D last
>> due date
>>
>> 5/7/12  colony
>>
>> pls provide me a formula so that column c and column d should turn yellow
>> and red respectively after 20 and 30 days considering Column A Date as
>> basis.
>>
>> awaiting for the forumla
>>
>> as i have already told i am basic learner of excel
>>
>> which u experts can understand with query that i have asked and help in
>> providing this formula as well as other formula to have good command on
>> excel
>>
>> thanks
>>
>> best regards.
>>
>> --
>> 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
>
>
>
>
> --
> Visita ; http://sites.google.com/site/chilexcel/Home
> Visita ; http://www.youtube.com/user/timextag41
>
> --
> 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
>



-- 
Regards

Iqbal Merchant
+919833567656

-- 
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 formula to take out total number of boy and girl

[NOORAIN ANSARI]

Hi Sunita,

Please confirm..

Is it not working..

paste in Cell *E2*
*=SUMPRODUCT(--($E$10:$E$46=$E$1)*($D$10:$D$46=C2))*

On Fri, Jul 13, 2012 at 1:22 PM, सुनिता पौडेल  wrote:

> Hi,
>
> Still not working :(
> its 2010
>
> On Fri, Jul 13, 2012 at 12:04 PM, NOORAIN ANSARI  > wrote:
>
>> Incase of 2003
>>
>> You can also use..
>>
>> *=SUMPRODUCT(--($E$10:$E$46=$E$1)*($D$10:$D$46=C2))*
>>
>>
>> On Fri, Jul 13, 2012 at 11:48 AM, NOORAIN ANSARI <
>> noorain.ans...@gmail.com> wrote:
>>
>>> Hi Sunita,
>>>
>>> Please Try it..
>>> =COUNTIFS($E$10:$E$46,$E$1,$D$10:$D$46,C2)
>>>
>>>
>>>
>>>
>>> On Fri, Jul 13, 2012 at 11:31 AM, सुनिता पौडेल wrote:
>>>
 Dear all,

 however this is not working out , can you please solve this where i
 went wrong/
 thnx

 On Thu, Jul 12, 2012 at 3:36 PM, सुनिता पौडेल wrote:

> wohoo so may options thnx guys ..it really worked out
>
> thank you so much
>
> Sunita
>
> On Thu, Jul 12, 2012 at 3:25 PM, NOORAIN ANSARI <
> noorain.ans...@gmail.com> wrote:
>
>> You Can also use..
>>
>> *=DCOUNTA($E$9:$E$49,E9,G1:G2)*
>> See attached sheet.
>>
>>
>> On Thu, Jul 12, 2012 at 3:04 PM, NOORAIN ANSARI <
>> noorain.ans...@gmail.com> wrote:
>>
>>> Hi Sunita,
>>>
>>> You can also *try..
>>>
>>> =SUMPRODUCT(--($E$10:E49="GIRL"))*
>>>
>>> On Thu, Jul 12, 2012 at 2:54 PM, सुनिता पौडेल 
>>> wrote:
>>>
 Dear all,

 Can you please help me to find out the total number of boy and girl
 ? I used countif for their grade and it worked. However, for girl and 
 boy
 seggregation it isnot working.

 Thank you very much for the support you all have been providing.

 sunita

 --
 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
>>>
>>>
>>>
>>>
>>> --
>>> Thanks & regards,
>>> Noorain Ansari
>>> www.noorainansari.com
>>> www.excelmacroworld.blogspot.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.

 

Re: $$Excel-Macros$$ long opening file on the external server

[pawel lupinski]

I'd like to do this but my knowladge is still not there, sad but true. Maybe 
some other solution, please.
 
Regards,
 
Pawel
 


 From: Divaker Pandey 
To: excel-macros@googlegroups.com 
Sent: Friday, July 13, 2012 9:40 AM
Subject: Re: $$Excel-Macros$$ long opening file on the external server
  

try to calculate required calculation with the help of macros.

Divaker


On Fri, Jul 13, 2012 at 12:14 PM, pawel lupinski  wrote:

Hi All,
>
>I need your help on this issue. I have quite big file (21MB) that is doing a 
>lot of calculation between tabs, in order to run this file more efficient I've 
>switch off calculation, and I'm switch it on when needed. When I've done this 
>file on my computer its opening slowly but not so bad 10 sec. The problem is: 
>when I've put this file on company server for other users its a nightmare. 
>Yesterday I've been waiting to open about 6-10 min!!! I've checked this on 
>different workstation and the same opening time unbelievable. 
>Do you have any thoughts? How Can I change this? What can improve it?
>
>Need your help
>
>Pawel
-- 
>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.

--
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$$ long opening file on the external server

[Divaker Pandey]

try to calculate required calculation with the help of macros.

Divaker

On Fri, Jul 13, 2012 at 12:14 PM, pawel lupinski wrote:

> Hi All,
>
> I need your help on this issue. I have quite big file (21MB) that is doing
> a lot of calculation between tabs, in order to run this file more efficient
> I've switch off calculation, and I'm switch it on when needed. When I've
> done this file on my computer its opening slowly but not so bad 10 sec. The
> problem is: when I've put this file on company server for other users its a
> nightmare. Yesterday I've been waiting to open about 6-10 min!!! I've
> checked this on different workstation and the same opening time
> unbelievable.
> Do you have any thoughts? How Can I change this? What can improve it?
>
> Need your help
>
> Pawel
>
> --
> 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 formula to take out total number of boy and girl

[सुनिता पौडेल]

Thank you so muck Krishnaraddi ji. Still wondering where it went wrong.
Anyway thanks heaps.
Sunita



On Fri, Jul 13, 2012 at 1:53 PM, Krishnaraddi V. Madolli <
krishnaraddi.mado...@asia.xchanging.com> wrote:

> "CONFIDENTIALITY NOTICE: This message and any attachment are confidential
> and may also be privileged. If you are not the intended recipient of this
> e-mail you may not copy, forward, disclose or otherwise use it or any part
> of it in any form whatsoever. If you are not the intended recipient please
> telephone or e-mail the sender and delete this message and any attachment
> from your system."
>

-- 
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 formula to take out total number of boy and girl

[सुनिता पौडेल]

Hi,

Still not working :(
its 2010

On Fri, Jul 13, 2012 at 12:04 PM, NOORAIN ANSARI
wrote:

> Incase of 2003
>
> You can also use..
>
> *=SUMPRODUCT(--($E$10:$E$46=$E$1)*($D$10:$D$46=C2))*
>
>
> On Fri, Jul 13, 2012 at 11:48 AM, NOORAIN ANSARI  > wrote:
>
>> Hi Sunita,
>>
>> Please Try it..
>> =COUNTIFS($E$10:$E$46,$E$1,$D$10:$D$46,C2)
>>
>>
>>
>>
>> On Fri, Jul 13, 2012 at 11:31 AM, सुनिता पौडेल wrote:
>>
>>> Dear all,
>>>
>>> however this is not working out , can you please solve this where i went
>>> wrong/
>>> thnx
>>>
>>> On Thu, Jul 12, 2012 at 3:36 PM, सुनिता पौडेल wrote:
>>>
 wohoo so may options thnx guys ..it really worked out

 thank you so much

 Sunita

 On Thu, Jul 12, 2012 at 3:25 PM, NOORAIN ANSARI <
 noorain.ans...@gmail.com> wrote:

> You Can also use..
>
> *=DCOUNTA($E$9:$E$49,E9,G1:G2)*
> See attached sheet.
>
>
> On Thu, Jul 12, 2012 at 3:04 PM, NOORAIN ANSARI <
> noorain.ans...@gmail.com> wrote:
>
>> Hi Sunita,
>>
>> You can also *try..
>>
>> =SUMPRODUCT(--($E$10:E49="GIRL"))*
>>
>> On Thu, Jul 12, 2012 at 2:54 PM, सुनिता पौडेल wrote:
>>
>>> Dear all,
>>>
>>> Can you please help me to find out the total number of boy and girl
>>> ? I used countif for their grade and it worked. However, for girl and 
>>> boy
>>> seggregation it isnot working.
>>>
>>> Thank you very much for the support you all have been providing.
>>>
>>> sunita
>>>
>>> --
>>> 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
>>
>>
>>
>>
>> --
>> Thanks & regards,
>> Noorain Ansari
>> www.noorainansari.com
>> www.excelmacroworld.blogspot.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