date:20110424

Re: $$Excel-Macros$$ Help me in this

2011-04-24 Thread Yogesh Gohil

Its works.
Thanks Dave.

On Sun, Apr 24, 2011 at 5:50 PM, Dave Bonallack
wrote:

>  Hi Yogesh,
> I only have one other idea to stay with your worksheet functions. All your
> Countifs functions contain whole column references.
>
> eg: =COUNTIFS(DB_Sheet!*$D:$D*,$A16,DB_Sheet!*$E:$E*,K$2,DB_Sheet!*$B:$B*
> ,$A$74)
>
> Since none of your DB_Sheet data goes beyond row 288325, you could change
> EVERY formula: eg:
>
> =COUNTIFS(DB_Sheet!*$D1:$D30*,$A16,DB_Sheet!*$E1:$E30*
> ,K$2,DB_Sheet!*$B1:$B30*,$A$74)
>
> This MAY reduce the time significantly.
> Regards - Dave.
>
> --
> Date: Sun, 24 Apr 2011 09:03:05 +0530
> Subject: Re: $$Excel-Macros$$ Help me in this
> From: yashr...@gmail.com
> To: excel-macros@googlegroups.com
>
> Thanks Dave, for your suggestion. but problem is that i want to update all
> sheets on daily basis. If i'll do this, it also be take same time. If you
> have any other idea please share with me. I am also working on it if i will
> get any solution then i will tell you.
>
> Thanks & Regards
> Yogesh Gohil
>
> On Sat, Apr 23, 2011 at 6:44 PM, Dave Bonallack  > wrote:
>
>  Hi Yogesh,
> At first glance, I'd say that you just have too many formulas in the
> workbook, and that most of the calculations should probably be done by VBA
> rather than worksheet functions. There appear to be some UDF's, but the VBA
> is protected, so I can't examine them, or make any decision on how to speed
> up the process.
> I think I would populate each sheet separately with VBA, and only update
> each sheet when it is activated, using a Worksheet_Activate event.
> However, it may be worth trying the following before going wholesale into
> VBA.
> Each sheet has an EnableCalculation property. In VBA, set each sheet to
> False when the workbook opens. The code would go into the ThisWorkbook
> window, as a Workbook_Open event.
> Something like:
>
> For each s in Worksheets
>   s.EnableCalculation = False
> Next s
>
> Then, in the sheet window for each sheet, as a Worksheet_Activate event,
> something like:
>
> Activesheet.EnableCalculation = True
> Activatesheet.Calculate
>
> And as a Worksheet_Deactivate event, something like:
>
> Activesheet.EnableCalculation = False
>
> This way, a worksheet is only updated when needed. It still may take too
> long, but it's simple to do, and worth a try.
> But I still think VBA could do each sheet in seconds, not hours.
>
> Regards - Dave.
> --
> Date: Sat, 23 Apr 2011 09:07:55 +0530
> Subject: $$Excel-Macros$$ Help me in this
> From: yashr...@gmail.com
> To: excel-macros@googlegroups.com
>
> Hi Experts,
>
> Actually, this waterfall made by me but its take 3hour to complete. Please
> help me out to make easy and time reducer format.
> This format have Excel function and VBA coding.
>
> *For the attachment you can click on*
> http://www.megaupload.com/?d=8KTIVGZX
>
> Thanks & Regards
> Yogesh Gohil
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.faceb

Re: Re: $$Excel-Macros$$ How to Separate Text and Nos in a sentence ?

2011-04-24 Thread roberto mensa

with the arrayformula:
text in A2
definited name rng =OFFSET(Foglio1!$A$1,,,50,50)

in B2:
=MOD(LARGE(ISERROR(--MID("a"&SUBSTITUTE($A$2,".","0"),ROW(rng),1))*ISERROR(--MID(SUBSTITUTE($A$2,".","0"),ROW(rng)+COLUMN(rng),1))*IF(ISNUMBER(--MID(SUBSTITUTE($A$2,"
","#")&"#",ROW(rng),COLUMN(rng))),90-ROW(rng)+MID($A$2,ROW(rng),COLUMN(rng))/10^6),COLUMN(A1))*10^6,10^6)

ctrl+shift+enter and drop right

regards
r


2011/4/20 r 

> as an alternative to solving of siti Vi
>
> Function GetNumbers_r(S As String, Index As Integer)
>Dim mc
>Dim RE As Object
>Set RE = CreateObject("vbscript.regexp")
>RE.Global = True
>RE.Pattern = "(?:0|[+-]?" & _
> "(?:(?!0)\d+(?:\.\d+)?" & _
> "|[0]+(?:\.\d+)?))"
>S = Replace(S, ",", "")
> GetNumbers_r = Val(RE.Execute(S)(Index - 1))
> End Function
>
>
> regards
> r
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ How to Separate Text and Nos in a sentence ?

2011-04-24 Thread karan

Hi

Thanks for the formula. It does help, but while trying it on a new
sheet its not working it gives an error #NAME?

On Apr 19, 9:07 pm, ashish koul  wrote:
> see if it helps
>
>
>
>
>
> On Tue, Apr 19, 2011 at 7:14 PM, karan kanuga  wrote:
> > Hi,
>
> > Can any1 pls let me know how do i separate the text and nos in a
> > given sentence. PFA the sheet where i need to separate the nos and text.
>
> > Thanks.
>
> > --
>
> > ------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> >http://www.facebook.com/discussexcel
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com 
> *akoul*.wordpress.com 
> My Linkedin Profile 
>
> P Before printing, think about the environment.
>
>  extract_nos_from_text(1).xls
> 42KViewDownload- Hide quoted text -
>
> - Show quoted text -

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Remove Password Protection

2011-04-24 Thread ChilExcel

see this link

http://sites.google.com/site/chilexcel/desbloqueos-y-removepass

break the password
books, Sheets VB proyect



2011/4/22 Prakash Gusain 

> Hi,
>
> Can any one provide me any code using which I can break the password
> of any excel add in?
>
> Prakash
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
Visita ; http://sites.google.com/site/chilexcel/Home
Visita ; http://www.youtube.com/user/timextag41

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ help..

2011-04-24 Thread vamsi varma

That spreadsheet is proprietary document and password cant be shared to
others. :(

On Fri, Apr 22, 2011 at 9:44 PM, STDEV(i)  wrote:

> then you have to ask to the author of the spreadsheet
>
>
>
> On Fri, Apr 22, 2011 at 10:19 PM, vamsi varma 
> wrote:
>
>> dear friends,
>>
>> i need some help..
>>
>> i need to cal the macro in another spreadsheet. But unfortunately that
>> spreadsheet is locked. I dont know the name of the macro to cal.
>> only option i hav is to click the macro button using VBA.
>>
>> can anyone help in this regard? it wil helpful to me.
>>
>> Thanks and regards,
>> Vamsi
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ A VBA Challenge.. (Arrays)

2011-04-24 Thread Deepak Pal Singh

Hi Guy's



Need your help in this code.. in the following code...





Sub Time_Test_Array2()



Range("K1") = Now()

Application.ScreenUpdating = False



Range("E:E").Clear

LastRow = 0

LastRow = Range("A100").End(xlUp).Row

iVal = Range("A1:A" & LastRow).Value

ReDim strArray(1 To UBound(iVal))



For i = LBound(iVal) To UBound(iVal)

If iVal(i, 1) Mod 2 = 0 Then

strArray(i) = "Even Number"

Else: strArray(i) = "Odd Number"

End If



Next i



Range(Cells(LBound(strArray), 5), Cells(UBound(strArray), 5)).Value =
strArray

'Range(Cells(LBound(iVal), 5), Cells(UBound(iVal), 5)) = iVal



ActiveSheet.Range("K2") = Now()

End Sub



Suppose there’re some numbers given on column “A”, I want to print whether
the number is Odd or Even in column “E” using Arrays, my code is working but
printing wrong values, instead of printing “Even Number” for an even number
it is printing “Odd Number”, there can be a logical error in the code.



All The Best.. J



Thanks & Regards

Deepak

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Show full screen for a particular workbook only.

2011-04-24 Thread Rajesh K R

Hi Paul,
Thanks for the answer I checked the same idea before I sent the
message, I give the fullscreen code when the work book open, but when
I give the code for display screen normal when work book deactive
it is not working as we think.that's why I posted it.Will u pls send
attached sheet with the macro u mentioned.If so I can understand what
is the fault I made.
Regards

Rajesh kainikkara



On 4/21/11, Paul Schreiner  wrote:
> You really need to create two event macros.
> the workbook_activate event would go to full screen when you "select" the
> workbook,
> if you use a workbook_deactivate macro to change the displayfullscreen to
> "false",
> it will revert to "normal" for when in any other workbook or window.
>
> Paul
>
>
>
>
> 
> From: ChilExcel 
> To: excel-macros@googlegroups.com
> Sent: Wed, April 20, 2011 12:43:36 PM
> Subject: Re: $$Excel-Macros$$ Show full screen for a particular workbook
> only.
>
>
> You need add ins ?
>
>
>
>
> 2011/4/20 rajan verma 
>
> make it add-ins
>>
>>
>>
>>On Fri, Apr 15, 2011 at 5:16 PM, Rajesh K R 
>> wrote:
>>
>>Hi Experts,
>>>
>>>I gave the following code to get the full screen view, if it is only
>>>applicable to that particular workbook how can i change it.
>>>
>>>
>>>Private Sub Workbook_Open()
>>>Application.DisplayFullScreen = True
>>>End Sub
>>>
>>>Regards
>>>Rajesh kainikkara
>>>
>>>--
>>>--
>>>
>>>Some important links for excel users:
>>>1. Follow us on TWITTER for tips tricks and links :
>>>http://twitter.com/exceldailytip
>>>2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>>3. Excel tutorials at http://www.excel-macros.blogspot.com/
>>>4. Learn VBA Macros at http://www.quickvba.blogspot.com/
>>>5. Excel Tips and Tricks at http://exceldailytip.blogspot.com/
>>>
>>>To post to this group, send email to excel-macros@googlegroups.com
>>>
>>><><><><><><><><><><><><><><><><><><><><><><>
>>>Like our page on facebook , Just follow below link
>>>http://www.facebook.com/discussexcel
>>>
>>
>>
>>--
>>Regards
>>Rajan verma
>>+91 9158998701
>>
>>--
>>--
>>
>>Some important links for excel users:
>>1. Follow us on TWITTER for tips tricks and links :
>>http://twitter.com/exceldailytip
>>2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>3. Excel tutorials at http://www.excel-macros.blogspot.com
>>4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>>To post to this group, send email to excel-macros@googlegroups.com
>>
>><><><><><><><><><><><><><><><><><><><><><><>
>>Like our page on facebook , Just follow below link
>>http://www.facebook.com/discussexcel
>>
>
>
> --
>
> Visita ; http://sites.google.com/site/chilexcel/Home
> Visita ; http://www.youtube.com/user/timextag41
> --
> --
>
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
> --
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.c

RE: $$Excel-Macros$$ Help me in this

2011-04-24 Thread Dave Bonallack


Hi Yogesh,
I only have one other idea to stay with your worksheet functions. All your 
Countifs functions contain whole column references.

eg: =COUNTIFS(DB_Sheet!$D:$D,$A16,DB_Sheet!$E:$E,K$2,DB_Sheet!$B:$B,$A$74)

Since none of your DB_Sheet data goes beyond row 288325, you could change EVERY 
formula: eg:

=COUNTIFS(DB_Sheet!$D1:$D30,$A16,DB_Sheet!$E1:$E30,K$2,DB_Sheet!$B1:$B30,$A$74)

This MAY reduce the time significantly.
Regards - Dave.

Date: Sun, 24 Apr 2011 09:03:05 +0530
Subject: Re: $$Excel-Macros$$ Help me in this
From: yashr...@gmail.com
To: excel-macros@googlegroups.com

Thanks Dave, for your suggestion. but problem is that i want to update all 
sheets on daily basis. If i'll do this, it also be take same time. If you have 
any other idea please share with me. I am also working on it if i will get any 
solution then i will tell you.


Thanks & Regards
Yogesh Gohil

On Sat, Apr 23, 2011 at 6:44 PM, Dave Bonallack  
wrote:






Hi Yogesh,
At first glance, I'd say that you just have too many formulas in the workbook, 
and that most of the calculations should probably be done by VBA rather than 
worksheet functions. There appear to be some UDF's, but the VBA is protected, 
so I can't examine them, or make any decision on how to speed up the process.

I think I would populate each sheet separately with VBA, and only update each 
sheet when it is activated, using a Worksheet_Activate event.
However, it may be worth trying the following before going wholesale into VBA.

Each sheet has an EnableCalculation property. In VBA, set each sheet to False 
when the workbook opens. The code would go into the ThisWorkbook window, as a 
Workbook_Open event.
Something like:

For each s in Worksheets

  s.EnableCalculation = False
Next s

Then, in the sheet window for each sheet, as a Worksheet_Activate event, 
something like:


Activesheet.EnableCalculation = True
Activatesheet.Calculate

And as a Worksheet_Deactivate event, something like:


Activesheet.EnableCalculation = False

This way, a worksheet is only updated when needed. It still may take too long, 
but it's simple to do, and worth a try.
But I still think VBA could do each sheet in seconds, not hours.


Regards - Dave.
Date: Sat, 23 Apr 2011 09:07:55 +0530
Subject: $$Excel-Macros$$ Help me in this
From: yashr...@gmail.com
To: excel-macros@googlegroups.com


Hi Experts,

Actually, this waterfall made by me but its take 3hour to complete. Please help 
me out to make easy and time reducer format.
This format have Excel function and VBA coding.

For the attachment you can click on http://www.megaupload.com/?d=8KTIVGZX 



Thanks & Regards

Yogesh Gohil




-- 

--

Some important links for excel users:

1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip

2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310

3. Excel tutorials at http://www.excel-macros.blogspot.com

4. Learn VBA Macros at http://www.quickvba.blogspot.com

5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 

To post to this group, send email to excel-macros@googlegroups.com

 

<><><><><><><><><><><><><><><><><><><><><><>

Like our page on facebook , Just follow below link

http://www.facebook.com/discussexcel
  




-- 

--

Some important links for excel users:

1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip

2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310

3. Excel tutorials at http://www.excel-macros.blogspot.com

4. Learn VBA Macros at http://www.quickvba.blogspot.com

5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 

To post to this group, send email to excel-macros@googlegroups.com

 

<><><><><><><><><><><><><><><><><><><><><><>

Like our page on facebook , Just follow below link

http://www.facebook.com/discussexcel






-- 

--

Some important links for excel users:

1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip

2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310

3. Excel tutorials at http://www.excel-macros.blogspot.com

4. Learn VBA Macros at http://www.quickvba.blogspot.com

5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 

To post to this group, send email to excel-macros@googlegroups.com

 

<><><><><><><><><><><><><><><><><><><><><><>

Like our page on facebook , Just follow below link

http://www.facebook.com/discussexcel
  

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exce

RE: $$Excel-Macros$$ macro to add fill color to a column of selected range

2011-04-24 Thread Dave Bonallack


Hi,
Something like:

Dim c as Range
For each c in Selection
If c.Column = 6 then c.Interior.Colorindex = 3
Next c

Note: Colorindex 6 will give you red. Change the number to suit.

Regards - Dave.



> Date: Sat, 23 Apr 2011 14:17:08 -0700
> Subject: $$Excel-Macros$$ macro to add fill color to a column of selected 
> range
> From: cje...@yahoo.com
> To: excel-macros@googlegroups.com
> 
> 
> I have a ~500 rows of data in columns A-G.  Several contiguous rows of
> this data (columns A-G) will be selected.  I would like my macro to
> fill column F of the selected cells with color without losing the
> original selection because these selected cells are used later in the
> macro.
> 
> I've been able to fill all selected cells with color but I would like
> column F only.
> 
> I might be able to increment through each row of the macro to test if
> that cell has been selected but I'm thinking that this method would
> cause a delay as it tested every row.  I was looking for a more
> straightforward method.
> 
> Thank you for any help.
> 
> -- 
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links : 
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>  
> To post to this group, send email to excel-macros@googlegroups.com
> 
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
  

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ macro to add fill color to a column of selected range

2011-04-24 Thread cjesag


I have a ~500 rows of data in columns A-G.  Several contiguous rows of
this data (columns A-G) will be selected.  I would like my macro to
fill column F of the selected cells with color without losing the
original selection because these selected cells are used later in the
macro.

I've been able to fill all selected cells with color but I would like
column F only.

I might be able to increment through each row of the macro to test if
that cell has been selected but I'm thinking that this method would
cause a delay as it tested every row.  I was looking for a more
straightforward method.

Thank you for any help.

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Help me in this

2011-04-24 Thread Yogesh Gohil

Thanks Dave, for your suggestion. but problem is that i want to update all
sheets on daily basis. If i'll do this, it also be take same time. If you
have any other idea please share with me. I am also working on it if i will
get any solution then i will tell you.

Thanks & Regards
Yogesh Gohil

On Sat, Apr 23, 2011 at 6:44 PM, Dave Bonallack
wrote:

>  Hi Yogesh,
> At first glance, I'd say that you just have too many formulas in the
> workbook, and that most of the calculations should probably be done by VBA
> rather than worksheet functions. There appear to be some UDF's, but the VBA
> is protected, so I can't examine them, or make any decision on how to speed
> up the process.
> I think I would populate each sheet separately with VBA, and only update
> each sheet when it is activated, using a Worksheet_Activate event.
> However, it may be worth trying the following before going wholesale into
> VBA.
> Each sheet has an EnableCalculation property. In VBA, set each sheet to
> False when the workbook opens. The code would go into the ThisWorkbook
> window, as a Workbook_Open event.
> Something like:
>
> For each s in Worksheets
>   s.EnableCalculation = False
> Next s
>
> Then, in the sheet window for each sheet, as a Worksheet_Activate event,
> something like:
>
> Activesheet.EnableCalculation = True
> Activatesheet.Calculate
>
> And as a Worksheet_Deactivate event, something like:
>
> Activesheet.EnableCalculation = False
>
> This way, a worksheet is only updated when needed. It still may take too
> long, but it's simple to do, and worth a try.
> But I still think VBA could do each sheet in seconds, not hours.
>
> Regards - Dave.
> --
> Date: Sat, 23 Apr 2011 09:07:55 +0530
> Subject: $$Excel-Macros$$ Help me in this
> From: yashr...@gmail.com
> To: excel-macros@googlegroups.com
>
> Hi Experts,
>
> Actually, this waterfall made by me but its take 3hour to complete. Please
> help me out to make easy and time reducer format.
> This format have Excel function and VBA coding.
>
> *For the attachment you can click on*
> http://www.megaupload.com/?d=8KTIVGZX
>
> Thanks & Regards
> Yogesh Gohil
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Help me in this

Re: Re: $$Excel-Macros$$ How to Separate Text and Nos in a sentence ?

Re: $$Excel-Macros$$ How to Separate Text and Nos in a sentence ?

Re: $$Excel-Macros$$ Remove Password Protection

Re: $$Excel-Macros$$ help..

$$Excel-Macros$$ A VBA Challenge.. (Arrays)

Re: $$Excel-Macros$$ Show full screen for a particular workbook only.

RE: $$Excel-Macros$$ Help me in this

RE: $$Excel-Macros$$ macro to add fill color to a column of selected range

$$Excel-Macros$$ macro to add fill color to a column of selected range

Re: $$Excel-Macros$$ Help me in this

11 matches

Site Navigation

Mail list logo

Footer information