date:20110215

Re: $$Excel-Macros$$ Dynamic range

2011-02-15 Thread ashish koul

see if this helps


Sub growth()
Dim i As Long
i = Range("s21").End(xlDown).Row
Columns("S:S").Select
Selection.Insert Shift:=xlToRight
Range("S21").Select
ActiveCell.FormulaR1C1 = "Growth"
Range("S23").Select
ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-1])/RC[-2]"
Range("S23").Select
Selection.NumberFormat = "0.00%"
Selection.AutoFill Destination:=Range("S23:S490")
Range("S23:S" & i).Select
End Sub



On Wed, Feb 16, 2011 at 2:57 AM, Skanda  wrote:

> Sub growth()
> '
> ' growth Macro
> '
> Columns("S:S").Select
> Selection.Insert Shift:=xlToRight
> Range("S21").Select
> ActiveCell.FormulaR1C1 = "Growth"
> Range("S23").Select
> ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-1])/RC[-2]"
> Range("S23").Select
> Selection.NumberFormat = "0.00%"
> Selection.AutoFill Destination:=Range("S23:S490")
> Range("S23:S490").Select
> End Sub
>
>
>
> In the above code, the range is set from S23 to S490.But there could be
> changes in the number of records in the future.
> how to make it more dynamic, keeping the S23 as constant but S490 to change
> depending on the records from RC[-2] and RC[-1].
> i.e if columns RC[-2] and RC[-1] have 5000 records in future how make the
> above code dynamic?
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


P Before printing, think about the environment.

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

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

Re: $$Excel-Macros$$ Saving excel workbook to 2 folders

2011-02-15 Thread ashish koul

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' with password

ThisWorkbook.SaveAs Filename:="d:\test3.xls ", FileFormat:= _
xlNormal, Password:="abc", WriteResPassword:="abc1",
ReadOnlyRecommended:=False _
, CreateBackup:=False

'without password
ThisWorkbook.SaveAs Filename:="d:\test.xls "
End Sub


On Wed, Feb 16, 2011 at 4:05 AM, balders  wrote:

> I ahve a workbook for each of my staff for coaching purposes, I need
> to save this into a Common drive so they can password protect it to
> enable only me and them to access it.  I also need to save it without
> a password into a management drive to allow my manager to access it
> without the need for a password.  Currently i do this in two separate
> operations, idally on save I would like it to just save in the two
> separate folders.  I can write basic macros but do not have the time
> to dig into vba.  Any help greatly appreciated.  thanks Balders
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


P Before printing, think about the environment.

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

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

Re: $$Excel-Macros$$ Segmentation?

2011-02-15 Thread ashish koul

can you please attach the sample workbook

On Tue, Feb 15, 2011 at 11:58 PM, MikeMikeMike
wrote:

> Say for example I have one tab that has a list of contact information
> - say anywhere from 1000 to over 100,000 contact items that include
> name (one field for first and last name), phone, address, etc.
>
> On the other tab I have a list of Categories. 1 column for Category
> Name and the other for a text identifier that is unique to that
> Category.
>
> Something like this:
>
> A column of Worksheet 1:
> Michael Lovelady
> Steve Smith
> Jonathan Lovelady
> Phillip Lovelace
>
> A & B columns of 1st row of Worksheet 2 (hyphen separated):
> Love - lov
>
> What I want to do is add an additional column to Worksheet 1 that does
> a vlookup (or something similar?) to match text specified in worksheet
> 2 (column B) and if it matches to return the Category Name specified
> in column A.
>
> I basically have tons of data that I need to segment on an ongoing
> basis and am looking for a good way to do it.
>
> Many thanks to anyone who can help!
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


P Before printing, think about the environment.

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

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

Re: $$Excel-Macros$$ Partial Matching HELP PLEASE! SOS! SOS!!

2011-02-15 Thread Aindril De

Hi Mike,
Please use:
=VLOOKUP(A2,Sheet1!B:B,1,0) instead.

Regards,
Andy

On Wed, Feb 16, 2011 at 2:21 AM, MikeMikeMike wrote:

> I am struggling with this and wondering if you could help..
>
> Worksheet 1 Column A & B
> Keyword Category
> telecom Telecom
> tele com#N/A
>
> Worksheet 2 Column A & B
> termcategory
> telecom Telecom
> telcom  Telecom
> tele comTelecom
>
> What I want to do is pull the category name from worksheet 2 on to
> worksheet 1.
>
> =VLOOKUP("*"&A2&"*",Sheet1!B:B,1,0)
>
> Is not working...any ideas?
>
> --
>
> --
> 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$$ Saving excel workbook to 2 folders

2011-02-15 Thread Paul Schreiner

The command you're looking for is SaveCopyAs

I would create a BeforeSave event,
there, save the file locally, then define the "backup" location, unprotect the 
file, then save a copy at the backup location.

Let me know if you need specifics.

Paul





From: balders 
To: MS EXCEL AND VBA MACROS 
Sent: Tue, February 15, 2011 5:35:47 PM
Subject: $$Excel-Macros$$ Saving excel workbook to 2 folders

I ahve a workbook for each of my staff for coaching purposes, I need
to save this into a Common drive so they can password protect it to
enable only me and them to access it.  I also need to save it without
a password into a management drive to allow my manager to access it
without the need for a password.  Currently i do this in two separate
operations, idally on save I would like it to just save in the two
separate folders.  I can write basic macros but do not have the time
to dig into vba.  Any help greatly appreciated.  thanks Balders

-- 
--

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$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2

2011-02-15 Thread Nasim

Hi Paul,

Thanks for your help. I think I now understand how it works. After I
sent you the file I tried what is suggested in below link one more
time and it works great. It is basically the same thing that you told
me to do with a little bit of change.

Here is the link for anybody interested:
http://www.tek-tips.com/faqs.cfm?fid=4090

So I changed the sheet's (name) in the properties window to "shtBonus"
and then used it like this: shtBonus.Range("A1").select

Once again, Thanks for your help and directing me to right direction.

Best regards,

Nasim


On Feb 15, 11:48 am, Paul Schreiner  wrote:
> The choice of which approch to use is really a question of Readability and
> Functionality.
>
> You can use:
> Sheet1.Select
> Sheet2.Select
> Sheet3.Select
>
> as long as you know you're not going to be deleting and adding sheets.
> Once you delete a sheet, even if you make a copy and delete the original, this
> will
> fail and you have to update the code.
> Plus, as a programmer, you'll have to remember which sheet is the "Process"
> sheet, etc.
>
> The same problem occurs with the use of Sheets(i)
> If someone moves a sheet within the workbook, the sheets() array changes.
>
> I prefer to use something like this at the beginning of the macro:
> ShtProcess = "Process"
> ShtHours = "Hours"
> ShtBreakdn = "Breakdown"
>
> then within the macro:
> Sheets(ShtProcess).Select
> That way, If I want, I can create a routine that checks for valid sheet names.
> If someone changes the sheet name, I can find it based on keywords and rename
> the sheet variable.
> Even then, if I decide to change the sheet name, I only have to change it once
> in my routine.
>
> also, see my notes below:
>
>  
>
> 
>
> From: Nasim 
> To: MS EXCEL AND VBA MACROS 
> Sent: Tue, February 15, 2011 11:37:11 AM
> Subject: Re: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not
> sheet with index2
>
> --
> this should be:
>  Thisworkbook.sheetS("whatever").select
> --
> 2- Doesn't this change the name that the tab is showing (showing
> "whatever" instead of "hours")? If so, what if somebody renames the
> tabs later. Will this not confuse a user as I am replacing the sheet
> name to what I want?
> -
> Yes... Thisworkbook.sheet2.name = "whatever" does change the sheet name
> that is displayed in the sheet "tabs".
> But you wouldn't change the sheet name unless necessary...
> Did I misunderstand the question?
> ---
>
> 3- Or should I simply say:  Thisworkbook.sheet2.select? Does this
> select sheet2(hours) or sheet2(Breakdown)? I guess it will be
> sheet2(hours).
> -
> First of all sheet2 is not an array (so no ()'s)
> Sheet2 is a sheet OBJECT.
> The Sheets() array requires an array index, or the sheet name.
> sheets(Breakdown) would imply that there is a variable called Breakdown
> that has a value being passed to the Sheets() array.
> Since Breakdown is NOT a variable, it would result in:
> Sheets(), which would fail.
> Using your example, you can use:
> Sheets("Breakdown").select
> Sheets(2).Select (since you said "Breakdown" is the second sheet)
> Sheet3.select
> -
>
> 4- What if I need to use variable and refer to sheet2 not sheet in 2nd
> position? I used to used sheets(i). if i=2 then it will point to
> sheet3(which in 2nd position-as I learnt from you) whereas I need to
> point to sheet2 which is in 3rd position. what syntax should I use?
> --
> I would suggest using the sheet name:
> ShtName = "Breakdown"
> Sheets(ShtName).
> If you MUST use the sheets array index,
> you can find it by using:
> For i = 1 to sheets.count
>     if (sheets(i).name = "Breakdown") then
>     exit for
>     end if
> next i
> sheets(i).Select
> --
>
> 5- Here is the part that I am referencing my sheets:
>
> Dim WSB, WSH As Worksheet
> Dim u, t, p, q, LastRowH, LastRowB As Integer
>
> Set WSB = ThisWorkbook.Sheets(9)  ' :    I changed it to Set WBS =
> Thisworkbook.Sheet9  and it does not work
> Set WSH = ThisWorkbook.Sheets(2)
>  
> --
> Can you tell me what your workbook structure is?
>
> what are the sheet names?
> what does the VB explorer show?
>
> can you send me the file? (or at least part of it?)
>
> I'd be glad to help
>
> Paul
> -
>
> I am still so confused. I appreciate if you could explaine this a bit
> more.
>
> Best regards,
>
> Nasim
>
> On Feb 14, 10:07 pm, Paul

$$Excel-Macros$$ Saving excel workbook to 2 folders

2011-02-15 Thread balders

I ahve a workbook for each of my staff for coaching purposes, I need
to save this into a Common drive so they can password protect it to
enable only me and them to access it.  I also need to save it without
a password into a management drive to allow my manager to access it
without the need for a password.  Currently i do this in two separate
operations, idally on save I would like it to just save in the two
separate folders.  I can write basic macros but do not have the time
to dig into vba.  Any help greatly appreciated.  thanks Balders

-- 
--
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$$ RASHI QUERRY"

2011-02-15 Thread Prakash Gusain

=VLOOKUP(IF(SUM(VLOOKUP(DAY(L1),A20:B50,2,FALSE),VLOOKUP(MONTH(L1),C20:D31,2,FALSE),VLOOKUP(TIME(HOUR(L1),MINUTE(L1),SECOND(L1)),E21:G44,3,TRUE))>K8,SUM(VLOOKUP(DAY(L1),A20:B50,2,FALSE),VLOOKUP(MONTH(L1),C20:D31,2,FALSE),VLOOKUP(TIME(HOUR(L1),MINUTE(L1),SECOND(L1)),E21:G44,3,TRUE))-K8,SUM(VLOOKUP(DAY(L1),A20:B50,2,FALSE),VLOOKUP(MONTH(L1),C20:D31,2,FALSE),VLOOKUP(TIME(HOUR(L1),MINUTE(L1),SECOND(L1)),E21:G44,3,TRUE))),A3:C15,3,TRUE)

will give you the solution!

-- 
Best Regards!
Prakash Singh Gusain

*Please visit my blog (**click here **)*

-- 
--
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$$ Diff excel Sheet based on Name & test

2011-02-15 Thread maulik desai

Hi Gurus,

I Just want to create Diff excel sheet based onn Emp name & sheet name
should be "emp name & date".there are diff test are available for each
process I also want to update that process test scores in respective sheet
(kindly see the sheets for more info),i am having very large excel database
& every week process names are changed so request you to kindly proive the
solution which creates the automatically sheet name & filter the test
columns & also i want to create that sheet as a individual workbook & need
to saved automatically on desktop with same sheet name & date.

-- 
Thanks & Regards,
Maulik Desai
9967363926

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


Diff excel Sheet based on Name & test.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Excel intrinsic constants/enumerations

2011-02-15 Thread Jeff

Sanjoy,

Thanks for your reply.  I suspect you are saying that if I press F2 in 
something like Visual Studio I will get a page come up that will show the 
values for the constants/enumerations. (I'm not at a place that I can check 
VS).My question may be a bit more fundamental.  When I try to compile 
the compiler says such are not defined.  So I think I need to do something 
similar as C and include some header file that defines the constants.  In my 
research I've come across the statement Imports which might be the way but 
unfortunately it did not work for me.

Does it make sense what I'm asking?  I'm sure it's simple for all the 
experienced VB.NET programmers.

Thanks,
Jeff

-- 
--
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$$ Partial Matching HELP PLEASE! SOS! SOS!!

2011-02-15 Thread MikeMikeMike

I am struggling with this and wondering if you could help..

Worksheet 1 Column A & B
Keyword Category
telecom Telecom
tele com#N/A

Worksheet 2 Column A & B
termcategory
telecom Telecom
telcom  Telecom
tele comTelecom

What I want to do is pull the category name from worksheet 2 on to
worksheet 1.

=VLOOKUP("*"&A2&"*",Sheet1!B:B,1,0)

Is not working...any ideas?

-- 
--
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$$ Dynamic range

2011-02-15 Thread Skanda

Sub growth()
'
' growth Macro
'
Columns("S:S").Select
Selection.Insert Shift:=xlToRight
Range("S21").Select
ActiveCell.FormulaR1C1 = "Growth"
Range("S23").Select
ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-1])/RC[-2]"
Range("S23").Select
Selection.NumberFormat = "0.00%"
Selection.AutoFill Destination:=Range("S23:S490")
Range("S23:S490").Select
End Sub



In the above code, the range is set from S23 to S490.But there could be
changes in the number of records in the future.
how to make it more dynamic, keeping the S23 as constant but S490 to change
depending on the records from RC[-2] and RC[-1].
i.e if columns RC[-2] and RC[-1] have 5000 records in future how make the
above code dynamic?

-- 
--
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$$ RE: Excel graphs and formula for adding time spent

2011-02-15 Thread Prakash Gusain

Dear Vinod,

The sum formula is giving correct solution if you mean by 0:20 mins.  Kindly
explain where are you facing the issue or why do you think the sum for 4:30
is not correct.

-- 
Best Regards!
Prakash Singh Gusain

*Please visit my blog (**click here **)*

-- 
--
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$$ Re: error checking VBA Excel 2003

2011-02-15 Thread BJthebear

I have solved the cancel button exit from the routine problem part 1
as follows:-

'Check to see if cancel button is pressed go back to Menu

If Newuser = false Then
Sheets("Menu").Select
   Exit Sub

End If

Still cannot get anything on the other two problems

Brian
Scotland

On Feb 15, 11:40 am, BJthebear  wrote:
> I have written a sub routine to add a name to a dynamic list but I
> have three problems with it and would be grateful for some advice.
>
> First the routine:-
>
> Sub InputNewUser()
> '
> ' InputNewUser Macro
> ' Macro recorded 01/02/2011 by Brian
> '
> Dim Newuser As String
> Dim nextRow As Long
>
> Dim flag As Boolean
>
>     flag = False
>
>     'find out if main database or staff copy - true if main database
> false if email copy
>
>     For Each wksheet In Application.Worksheets
>     If wksheet.Name = "HoursWorkedexpenses" Then
>         flag = True
>     Exit For
>     End If
>     Next wksheet
>
>     If flag = False Then
>         MsgBox "You do not have sufficient authority to create a new
> user"
>         End
>
>     Else
>     End If
>
> 'Ask for name of new user
>
> Newuser = Application.InputBox("Please enter name of new user (Surname
> first)")
>
> 'turns it into proper letters
>
> Newuser = Application.Proper(Newuser)
>
> 'Check to see if cancel button is pressed go back to Menu
>
>     If Newuser = "" Then
>         Sheets("Menu").Select
>        Exit Sub
>
>     End If
>
> 'Routine to check if Newuser already exists in list of users
>
>      '
>
> 'Find last row
>
> Set SrcSht = Sheets("UserList")
>
> nextRow = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
>
> Application.ScreenUpdating = False
>
>     Range("A" & nextRow).Select
>     ActiveCell.FormulaR1C1 = Newuser
>         Columns("A:A").Select
>     Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
> Header:=xlGuess, _
>         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
> _
>         DataOption1:=xlSortNormal
>
>     Application.ScreenUpdating = True
>
>     'display message to show that it has been added to userlist
>     MsgBox (Newuser & " has been added to List of Users")
>
>  'return to menu page
>     Sheets("Menu").Select
> End Sub
>
> The problems are as follows:-
>
> 1.  I have just added the code to check to see if the cancel button is
> pressed but it does not exit the subroutine - it adds a false to the
> list of users.
>
> 2.  How do I check to see if the Newuser entry already exists in the
> list of users
>
> 3.  The routine seems to work when running it from the VBA window but
> when the macro is attached to a button on the "Menu" Wrksheet it fails
> to add the name to the list of users.  This routine worked up until a
> few days ago and it really has me stumped
>
> Any help would be appreciated
>
> Brian
> Scotland

-- 
--
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$$ Segmentation?

2011-02-15 Thread MikeMikeMike

Say for example I have one tab that has a list of contact information
- say anywhere from 1000 to over 100,000 contact items that include
name (one field for first and last name), phone, address, etc.

On the other tab I have a list of Categories. 1 column for Category
Name and the other for a text identifier that is unique to that
Category.

Something like this:

A column of Worksheet 1:
Michael Lovelady
Steve Smith
Jonathan Lovelady
Phillip Lovelace

A & B columns of 1st row of Worksheet 2 (hyphen separated):
Love - lov

What I want to do is add an additional column to Worksheet 1 that does
a vlookup (or something similar?) to match text specified in worksheet
2 (column B) and if it matches to return the Category Name specified
in column A.

I basically have tons of data that I need to segment on an ongoing
basis and am looking for a good way to do it.

Many thanks to anyone who can help!

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials 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$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2

2011-02-15 Thread Paul Schreiner

The choice of which approch to use is really a question of Readability and 
Functionality.

You can use:
Sheet1.Select
Sheet2.Select
Sheet3.Select

as long as you know you're not going to be deleting and adding sheets.
Once you delete a sheet, even if you make a copy and delete the original, this 
will
fail and you have to update the code.
Plus, as a programmer, you'll have to remember which sheet is the "Process" 
sheet, etc.

The same problem occurs with the use of Sheets(i)
If someone moves a sheet within the workbook, the sheets() array changes.

I prefer to use something like this at the beginning of the macro:
ShtProcess = "Process"
ShtHours = "Hours"
ShtBreakdn = "Breakdown"

then within the macro:
Sheets(ShtProcess).Select
That way, If I want, I can create a routine that checks for valid sheet names.
If someone changes the sheet name, I can find it based on keywords and rename
the sheet variable.
Even then, if I decide to change the sheet name, I only have to change it once 
in my routine.

also, see my notes below:

From: Nasim 
To: MS EXCEL AND VBA MACROS 
Sent: Tue, February 15, 2011 11:37:11 AM
Subject: Re: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not 
sheet with index2

--
this should be:
 Thisworkbook.sheetS("whatever").select
--
2- Doesn't this change the name that the tab is showing (showing
"whatever" instead of "hours")? If so, what if somebody renames the
tabs later. Will this not confuse a user as I am replacing the sheet
name to what I want?
-
Yes... Thisworkbook.sheet2.name = "whatever" does change the sheet name
that is displayed in the sheet "tabs".
But you wouldn't change the sheet name unless necessary...
Did I misunderstand the question?
---

3- Or should I simply say:  Thisworkbook.sheet2.select? Does this
select sheet2(hours) or sheet2(Breakdown)? I guess it will be
sheet2(hours).
-
First of all sheet2 is not an array (so no ()'s)
Sheet2 is a sheet OBJECT.
The Sheets() array requires an array index, or the sheet name.
sheets(Breakdown) would imply that there is a variable called Breakdown
that has a value being passed to the Sheets() array.
Since Breakdown is NOT a variable, it would result in:
Sheets(), which would fail.
Using your example, you can use: 
Sheets("Breakdown").select
Sheets(2).Select (since you said "Breakdown" is the second sheet)
Sheet3.select
-

4- What if I need to use variable and refer to sheet2 not sheet in 2nd
position? I used to used sheets(i). if i=2 then it will point to
sheet3(which in 2nd position-as I learnt from you) whereas I need to
point to sheet2 which is in 3rd position. what syntax should I use?
--
I would suggest using the sheet name:
ShtName = "Breakdown"
Sheets(ShtName).
If you MUST use the sheets array index,
you can find it by using:
For i = 1 to sheets.count
    if (sheets(i).name = "Breakdown") then
    exit for
    end if
next i
sheets(i).Select
--

5- Here is the part that I am referencing my sheets:

Dim WSB, WSH As Worksheet
Dim u, t, p, q, LastRowH, LastRowB As Integer

Set WSB = ThisWorkbook.Sheets(9)  ' :    I changed it to Set WBS =
Thisworkbook.Sheet9  and it does not work
Set WSH = ThisWorkbook.Sheets(2)

--
Can you tell me what your workbook structure is?

what are the sheet names?
what does the VB explorer show?

can you send me the file? (or at least part of it?)

I'd be glad to help

Paul
-

I am still so confused. I appreciate if you could explaine this a bit
more.

Best regards,

Nasim

On Feb 14, 10:07 pm, Paul Schreiner  wrote:
> In your VBA Object Explorer, you'll see
> Sheet1(Name1)
> Sheet2(Name2)
> Sheet3(Name3)
>
> If you delete the sheet named "Name2", create a new sheet, and call the NEW
> sheet "Name2"
> It won't be:
> Sheet2(Name2), it'll be:
> Sheet4(Name2).
>
> So the LIST will be:
> Sheet1(Name1)
> Sheet3(Name3)
> Sheet4(Name4)
>
> in VBA the Sheets array is:
> Sheets(1).Name = "Name1"
> Sheets(2).Name = "Name3"
> Sheets(3).Name = "Name2"
>
> Because the Sheets() array is a list of sheet names.
> An odd occurence though is:
> If you save and exit Excel, open the file,
> and create a new Sheet (Name4)
> it will create it as:
> Sheet2(Name4)
> and the array becomes:
> Sheets(1).Name = "Name1"
> Sheets(2).Name = "Name4"
> Sheets(3).Name = "Name3"
> Sheets(4).Name = "Name2"
>
> So, basically, when you see

Re: $$Excel-Macros$$ Fwd: Need to Check

2011-02-15 Thread santosh bahuguna

what exactly you are looking for ..  do you want us to transfer data into
the site from excel sheet

On Tue, Feb 15, 2011 at 6:46 AM, Rajasekhar Praharaju <
rajasekhar.prahar...@gmail.com> wrote:

>
> hi ,
>
> Please help in this concern can any one help in this concern i require this
> computation using excel macro
> below is the link which is coded in HTML. Which is working fine but i cant
> use this link in my organisation so kindly can anyone help to build the
> excel macro for this concern.
>
> Thanks,
> Raj
>
>
>  http://billingcombins.appspot.com/
>
> --
>
> --
> 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$$ Load image from the url

2011-02-15 Thread San Pat

I am using a web url.

Is there anyway I can covert/save web url in picture format and then call it
in the below function?

Regards,
San

On Tue, Feb 15, 2011 at 5:46 AM, ashish koul  wrote:

> Sub test()
>
> UserForm1.Image1.Picture = LoadPicture("D:\koul\krishna.jpg")
> UserForm1.Show
>
>
> End Sub
>
>
> On Tue, Feb 15, 2011 at 11:55 AM, San Pat  wrote:
>
>> How I can load a image from a url using the LoadPicture function in
>> userform.
>>
>> Regards,
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com 
> *akoul*.wordpress.com 
> My Linkedin Profile 
>
>
> P Before printing, think about the environment.
>
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

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

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

Re: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2

2011-02-15 Thread Nasim

Thanks for your thorough explanation Paul. I now understand that
sheets() points to the position of the sheet in the file. I still do
not understand how to use the sheet.name though.
This is what I have in objext explorer

sheet1 (Process)  'is the first sheet
sheet2 (Hours) ' is 3rd sheet
sheet3 (Breakdown) ' is 2nd sheet


1- Should I say : Thisworkbook.sheet2.name= "whatever"
 Thisworkbook.sheet("whatever").select
2- Doesn't this change the name that the tab is showing (showing
"whatever" instead of "hours")? If so, what if somebody renames the
tabs later. Will this not confuse a user as I am replacing the sheet
name to what I want?

3- Or should I simply say:  Thisworkbook.sheet2.select? Does this
select sheet2(hours) or sheet2(Breakdown)? I guess it will be
sheet2(hours).

4- What if I need to use variable and refer to sheet2 not sheet in 2nd
position? I used to used sheets(i). if i=2 then it will point to
sheet3(which in 2nd position-as I learnt from you) whereas I need to
point to sheet2 which is in 3rd position. what syntax should I use?


5- Here is the part that I am referencing my sheets:

Dim WSB, WSH As Worksheet
Dim u, t, p, q, LastRowH, LastRowB As Integer

Set WSB = ThisWorkbook.Sheets(9)  ' :I changed it to Set WBS =
Thisworkbook.Sheet9   and it does not work
Set WSH = ThisWorkbook.Sheets(2)


I am still so confused. I appreciate if you could explaine this a bit
more.

Best regards,

Nasim

On Feb 14, 10:07 pm, Paul Schreiner  wrote:
> In your VBA Object Explorer, you'll see
> Sheet1(Name1)
> Sheet2(Name2)
> Sheet3(Name3)
>
> If you delete the sheet named "Name2", create a new sheet, and call the NEW
> sheet "Name2"
> It won't be:
> Sheet2(Name2), it'll be:
> Sheet4(Name2).
>
> So the LIST will be:
> Sheet1(Name1)
> Sheet3(Name3)
> Sheet4(Name4)
>
> in VBA the Sheets array is:
> Sheets(1).Name = "Name1"
> Sheets(2).Name = "Name3"
> Sheets(3).Name = "Name2"
>
> Because the Sheets() array is a list of sheet names.
> An odd occurence though is:
> If you save and exit Excel, open the file,
> and create a new Sheet (Name4)
> it will create it as:
> Sheet2(Name4)
> and the array becomes:
> Sheets(1).Name = "Name1"
> Sheets(2).Name = "Name4"
> Sheets(3).Name = "Name3"
> Sheets(4).Name = "Name2"
>
> So, basically, when you see "sheet1", "sheet2", etc in the VBA Project 
> Explorer,
> it is NOT a sheet name and canNOT be used as the array.
>
> Because of this, using sheets(1) or Sheets(2) is inconsistent, and I NEVER use
> it.
> (some of my macros delete and create several sheets during processing.  It's 
> not
> uncommon to be up to sheet300 in a session).
>
> What I would suggest is to use:
> Sheets("Name1") or set a variable for the sheet name
> ShtName = "Name3"
> and use:
>
> Sheets(ShtName).Select
>
> Paul
>
> 
> From: Nasim 
> To: MS EXCEL AND VBA MACROS 
> Sent: Mon, February 14, 2011 6:02:46 PM
> Subject: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not 
> sheet
> with index2
>
> Hi,
>
> we are using excel 2010. I use sheets(2) in my code and it was working
> fine (since this was the 2nd tab in my file till I manually added
> another sheet and I moved this 3rd sheet betwenn sheet 1 and 2 so now
> 3rd sheet is in 2nd order in my file(2nd tab). I have renamed the tabs
> ofcourse and the indexes should still work but they dont. here is the
> broblem
>
> set wsh = thisworkbook.sheets(2)
> wsh.activate  : this line activates sheets(3) which is the 2nd tab in
> my file now
>
> I used sheets("sheet2") and it produces errors.
> It seems like all sheet indexes after have shifted 1 number up.
> I need to reference many different sheets in this file and I want to
> be able to add/delete/move/rename sheets without any problems. Only if
> index was working 
> I have never had this problem before. this is the first time this is
> happening. Is it seemthing I do wrong or is it the setup or is it
> excel 2010?
>
> I appreciate your help.
>
> Best regards,
>
> Nasim
>
> --
> ------
>
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links 
> :http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below 
> linkhttp://www.facebook.com/discussexcel

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel t

Fwd: $$Excel-Macros$$ Solution required

2011-02-15 Thread Jitender kumar

Dear Excel Gurus,

I know mailmerge but how to do the same thing in excel, I also know that It
is very easily done using Ms-access. how to do mailmerge in excel.
because we want the desired data in excel only

Regards

Jitender

-- Forwarded message --
From: Sanjoy Nath 
Date: 14 February 2011 12:46
Subject: Re: $$Excel-Macros$$ Solution required
To: excel-macros@googlegroups.com


Dear Jitender,
Use the mail merge option in the word



On Sat, Feb 12, 2011 at 9:38 PM, Jitender kumar wrote:

> Dear Excel Gurus,
>
> Urgent help required from your part, Plz help me on the file attached.
> Problem in file
>
> Thanks in advance
>
> Regards
>
> Jitender
>
> --
>
> --
> 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
>



-- 
Your Sincerely
Sanjoy Nath
Engineer
EOL Gurgaon
EOL/M/NZ/Engg.Dept.(Haryana Division)

 --
--
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$$ data in horizontal row

2011-02-15 Thread Rajasekhar Praharaju

hi please use this simple procedure

Excel Text to Column Command
The Text to Column command allows you to break text in one column into
several columns. For example, if you have LastName, FirstName in a column,
you can break the text into two columns—one containing LastName and the
other containing FirstName.

1. Insert as many columns to the left of the column to be split as needed.
2. Select the cells to be separated.
3. Pull down the Data menu and choose Text to Columns
4. Choose Delimited and click on Next
5. Choose the Delimiter that is used in the column, such as comma and click
on Next
6. To set the column data format, click on the column and then click on the
format desired under column data format (this is optional).  Repeat this for
each column and click on Finish.

Excel Transpose Command
Use the transpose feature to switch data from vertical to horizontal.

1. Select the cells to switch
2. Click on the Copy tool
3. Click where you want the data to be transposed to
4. Pull down the Edit menu and choose Paste Special
5. Click on Transpose and click on OK

Thanks,
Raj

On Tue, Feb 15, 2011 at 1:37 AM, Hems  wrote:

> Hi Guys,
>
> Pls check attached excel sheet.
>
> I have data in across 2 columns which i want move in one row through macro,
> is it possible?
>
> actually the data is extracted from access database so if its poss through
> macro then i can process in access rather trolling it through excel.
>
> appreciate your efforts and thanks in advance.
>
> --
> Kind Regards,
> Hems
>
> --
>
> --
> 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$$ RASHI QUERRY

2011-02-15 Thread ashish koul

VLOOKUP(IF(VLOOKUP(J4,$A$19:$B$50,2,0)+VLOOKUP(J5,$C$19:$D$31,2,0)+VLOOKUP(J6,$E$20:$G$44,3,1)>=
1440,VLOOKUP(J4,$A$19:$B$50,2,0)+VLOOKUP(J5,$C$19:$D$31,2,0)+VLOOKUP(J6,$E$20:$G$44,3,1)-1440,VLOOKUP(J4,$A$19:$B$50,2,0)+VLOOKUP(J5,$C$19:$D$31,2,0)+VLOOKUP(J6,$E$20:$G$44,3,1)),$A$2:$C$15,3,1)

On Tue, Feb 15, 2011 at 4:43 PM, Nemi Gandhi  wrote:

> Thank you Mr. sundervalen, but i need only one formula instead of
> calculating value for date, month and time separately,. Is it
> possible?
> and thank you to Mr. Ashish kaul also for help.
>
> On Feb 14, 8:42 pm, Sundarvelan N  wrote:
> > Check if mine is ok for you
> >
> > Thanks,
> > Sundarvelan,
> > 9600160150
> >
> >
> >
> > On Mon, Feb 14, 2011 at 8:08 PM, ashish koul 
> wrote:
> > > see if it helps
> >
> > > On Mon, Feb 14, 2011 at 5:15 PM, Nemi Gandhi 
> wrote:
> >
> > >> attached herewith rashi querry sheet. Please provide me formula and
> help.
> >
> > >> --
> > >> Nemi Gandhi
> > >> 98204 92963
> >
> > >> --
> >
> > >>
> --
> > >> 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.
> >
> > > --
> >
> > >
> --
> > > 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
> >
> > --
> > Thanks
> > N.Sundarvelan
> > 9600160150
> >
> >  Xl002.xls
> > 41KViewDownload
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


P Before printing, think about the environment.

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

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

Re: $$Excel-Macros$$ Query

2011-02-15 Thread ashish koul

will it be these three classifications only

On Tue, Feb 15, 2011 at 8:35 PM, Aamir Shahzad wrote:

> Thanks *Ashish *for reply but can you please provide formula without
> pivot. Condition you have understand that if sum of any classification is
> grater then others,it shows that value.
>
>
>
> On Tue, Feb 15, 2011 at 8:14 AM, ashish koul wrote:
>
>>
>>
>> On Tue, Feb 15, 2011 at 12:16 AM, Aamir Shahzad > > wrote:
>>
>>>
>>> Hi all experts,
>>>
>>> Please resolve my query by formula. sheet attached.
>>>
>>> Regards,
>>>
>>> Aamir Shahzad
>>> --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>>
>>
>> --
>> *Regards*
>> * *
>> *Ashish Koul*
>> *akoul*.*blogspot*.com 
>> *akoul*.wordpress.com 
>> My Linkedin Profile 
>>
>>
>> P Before printing, think about the environment.
>>
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


P Before printing, think about the environment.

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

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

Re: $$Excel-Macros$$ Query

2011-02-15 Thread Aamir Shahzad

Thanks *Ashish *for reply but can you please provide formula without pivot.
Condition you have understand that if sum of any classification is grater
then others,it shows that value.



On Tue, Feb 15, 2011 at 8:14 AM, ashish koul  wrote:

>
>
> On Tue, Feb 15, 2011 at 12:16 AM, Aamir Shahzad 
> wrote:
>
>>
>> Hi all experts,
>>
>> Please resolve my query by formula. sheet attached.
>>
>> Regards,
>>
>> Aamir Shahzad
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com 
> *akoul*.wordpress.com 
> My Linkedin Profile 
>
>
> P Before printing, think about the environment.
>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

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

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

$$Excel-Macros$$ Fwd: Need to Check

2011-02-15 Thread Rajasekhar Praharaju

hi ,

Please help in this concern can any one help in this concern i require this
computation using excel macro
below is the link which is coded in HTML. Which is working fine but i cant
use this link in my organisation so kindly can anyone help to build the
excel macro for this concern.

Thanks,
Raj


 http://billingcombins.appspot.com/

-- 
--
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$$ Problem with Sorting in row wise

2011-02-15 Thread ashish koul

try this

Sub Macro1()
Dim i As Long

For i = 1 To Sheets(1).Range("a1").End(xlDown).Row


ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("b" & i & ":i" & i),
_
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Worksheets("Sheet1").Range("b" & i & ":i" & i)
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With


   Next i

End Sub

On Tue, Feb 15, 2011 at 7:09 PM, Excel Expert  wrote:

> Respected Sir,
>
> I have many 500 rows in my excel sheet
> i want sort all row ascending order.
>
> for exp
> 1 row  102 103 105  110 120
> 2 row  103  102 110 105 120
>
> how i convert it into
>
> 1 row  102 103 105  110 120
> 2 row  102  103 105 110 120
>
>
>
>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


P Before printing, think about the environment.

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

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

$$Excel-Macros$$ Problem with Sorting in row wise

2011-02-15 Thread Excel Expert

Respected Sir,

I have many 500 rows in my excel sheet
i want sort all row ascending order.

for exp
1 row  102 103 105  110 120
2 row  103  102 110 105 120

how i convert it into

1 row  102 103 105  110 120
2 row  102  103 105 110 120

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


RAVI.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Re: Array or Variant ???

2011-02-15 Thread rajan verma

Its a variant type Variable..

On Mon, Feb 14, 2011 at 8:12 AM, hanumant shinde
wrote:

> also pls tell me if dim abc is array then
> does dim abc is equal to dim abc() ?
>
>
> --
> *From:* hanumant shinde 
> *To:* excel macros 
> *Sent:* Tue, 15 February, 2011 1:38:21 AM
> *Subject:* Array or Variant ???
>
> if i seclare variable like this
>
> dim abc
>
> is this array or variant or something else. if array the what data type
> i have code in my office (somebody else's code) where variables are
> declared like this and i thought these are variants but after going through
> paul's mail chain i guess those are arrays which seems more accurate. bcoz
> the kind of it has i always used to wonder how variant is used like an
> array.
>
> so can somebody explain it.
>
>
>
>  --
>
> --
> 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

$$Excel-Macros$$ error checking VBA Excel 2003

2011-02-15 Thread BJthebear

I have written a sub routine to add a name to a dynamic list but I
have three problems with it and would be grateful for some advice.

First the routine:-

Sub InputNewUser()
'
' InputNewUser Macro
' Macro recorded 01/02/2011 by Brian
'
Dim Newuser As String
Dim nextRow As Long

Dim flag As Boolean

flag = False

'find out if main database or staff copy - true if main database
false if email copy

For Each wksheet In Application.Worksheets
If wksheet.Name = "HoursWorkedexpenses" Then
flag = True
Exit For
End If
Next wksheet

If flag = False Then
MsgBox "You do not have sufficient authority to create a new
user"
End


Else
End If

'Ask for name of new user

Newuser = Application.InputBox("Please enter name of new user (Surname
first)")

'turns it into proper letters

Newuser = Application.Proper(Newuser)


'Check to see if cancel button is pressed go back to Menu

If Newuser = "" Then
Sheets("Menu").Select
   Exit Sub

End If

'Routine to check if Newuser already exists in list of users

 '

'Find last row

Set SrcSht = Sheets("UserList")

nextRow = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1

Application.ScreenUpdating = False


Range("A" & nextRow).Select
ActiveCell.FormulaR1C1 = Newuser
Columns("A:A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

Application.ScreenUpdating = True

'display message to show that it has been added to userlist
MsgBox (Newuser & " has been added to List of Users")

 'return to menu page
Sheets("Menu").Select
End Sub


The problems are as follows:-

1.  I have just added the code to check to see if the cancel button is
pressed but it does not exit the subroutine - it adds a false to the
list of users.

2.  How do I check to see if the Newuser entry already exists in the
list of users

3.  The routine seems to work when running it from the VBA window but
when the macro is attached to a button on the "Menu" Wrksheet it fails
to add the name to the list of users.  This routine worked up until a
few days ago and it really has me stumped


Any help would be appreciated

Brian
Scotland

-- 
--
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$$ RASHI QUERRY

2011-02-15 Thread Nemi Gandhi

Thank you Mr. sundervalen, but i need only one formula instead of
calculating value for date, month and time separately,. Is it
possible?
and thank you to Mr. Ashish kaul also for help.

On Feb 14, 8:42 pm, Sundarvelan N  wrote:
> Check if mine is ok for you
>
> Thanks,
> Sundarvelan,
> 9600160150
>
>
>
> On Mon, Feb 14, 2011 at 8:08 PM, ashish koul  wrote:
> > see if it helps
>
> > On Mon, Feb 14, 2011 at 5:15 PM, Nemi Gandhi  wrote:
>
> >> attached herewith rashi querry sheet. Please provide me formula and help.
>
> >> --
> >> Nemi Gandhi
> >> 98204 92963
>
> >> --
>
> >> --
> >> 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.
>
> > --
>
> > --
> > 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
>
> --
> Thanks
> N.Sundarvelan
> 9600160150
>
>  Xl002.xls
> 41KViewDownload

-- 
--
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$$ Load image from the url

2011-02-15 Thread ashish koul

Sub test()

UserForm1.Image1.Picture = LoadPicture("D:\koul\krishna.jpg")
UserForm1.Show


End Sub


On Tue, Feb 15, 2011 at 11:55 AM, San Pat  wrote:

> How I can load a image from a url using the LoadPicture function in
> userform.
>
> Regards,
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


P Before printing, think about the environment.

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

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

Re: $$Excel-Macros$$ A little OT

2011-02-15 Thread Paul Schreiner

The biggest confusion here is the difference between
the Worksheet OBJECT: Sheet1 
the Worksheet NAME:   Sheet1
and Sheets ARRAY: Sheets(1)

An Excel workbook must contain at least one sheet.
Excel will INTERNALLY "Identify" this sheet object as "Sheet1"
and will initially NAME the sheet "Sheet1"

But these are two different things.

In the VB Editor Object Explorer, you'll see:
Sheet1(Sheet1)

Meaning that the Sheet1 object is NAMED "Sheet1"
The key here is that Sheet1 can be NAMED anything, 
but will always be Sheet1 as long as it exists.
  
You can name your dog "kitty" but that doesn't change the fact that it's still 
a 
dog.

That's why you can use:
Sheet1.Name = "DataSheet"
The Name Property of the Sheet1 Object is set to "DataSheet"

So, even though the VB Object Explorer shows:
Sheet1(DataSheet)
You cannot use:
DataSheet.Select

Because "DataSheet" is not an Object...

Now, the Sheets() Array...
It's exactly that.
It's an array of sheet objects.
The interesting thing here is that the position in the array is
dependent upon where the sheet appears in the workbook.

If you have three sheets, Named: "Name1", "Name2" and "Name3"
and they were originally created in numerical order and positioned 
left-to-right.
The Sheets() Array will have them listed as:
Sheets(1).Name = "Name1"  
Sheets(2).Name = "Name2"
Sheets(3).Name = "Name3"
and the VBA Object Explorer will show them as:
Sheet1(Name1)
Sheet2(Name2)
Sheet3(Name3)

But if you move Name3 to the far left, so they're shown as:
"Name3", "Name1", "Name2"

The Object Explorer will still show them as:  
Sheet1(Name1)
Sheet2(Name2)
Sheet3(Name3)

But the Sheets ARRAY will be:
Sheets(1).Name = "Name3"  
Sheets(2).Name = "Name1"
Sheets(3).Name = "Name2"

BTW: The Sheets Array also has the "internal" name as the "CodeName" object.

 So.. summarize...
There are three distinct ways to identify a sheet (maybe more?)
The Sheet  OBJECT, 
the Sheet  NAME, and 
the Sheets ARRAY

You cannot change the "codeName" of an existing sheet object.
You CAN (of course) change the Sheet Name,
and the Sheets ARRAY is changed by the position of the sheet in the workbook.

In answer to your question.. Your question contains syntax errors.
Because when you say there are 7 sheets, but Sheet(7) doesn't exist.
You mean that Sheet7 doesn't exist... Sheets(7) is the 7th element of the Sheets
ARRAY, which DOES exist.

Does that help? or was it so "wordy" that you fell asleep midway and woke up
with a keyboard imprint on your right cheek?

Paul


 

 




From: Dave Bonallack 
To: "excel-macros@googlegroups.com" 
Sent: Mon, February 14, 2011 11:33:24 PM
Subject: $$Excel-Macros$$ A little OT

Hi Paul,
I have also noticed that after deleting and creating sheets, the Sheet numbers 
are out of order, and can be missing altogether. ie in a workbook of 7 sheets, 
one of them may be Sheet(11), while sheets 8 and 9 are not present at all.
So, my question is, why does this line of code (Sheets(Sheets.Count).Select) 
always select the last sheet in a workbook, even when the last sheet isn't 
necessarily the one with the highest sheet number, and also, if the sheet.count 
is say, 7, the thing still works if Sheet(7) doesn't exist!
Regards - Dave

 

Date: Mon, 14 Feb 2011 19:07:28 -0800
From: schreiner_p...@att.net
Subject: Re: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not 
sheet with index2
To: excel-macros@googlegroups.com


In your VBA Object Explorer, you'll see 
Sheet1(Name1)
Sheet2(Name2)
Sheet3(Name3)

If you delete the sheet named "Name2", create a new sheet, and call the NEW 
sheet "Name2"
It won't be:
Sheet2(Name2), it'll be:
Sheet4(Name2).

So the LIST will be:
Sheet1(Name1)
Sheet3(Name3)
Sheet4(Name4)

in VBA the Sheets array is:
Sheets(1).Name = "Name1"
Sheets(2).Name = "Name3"
Sheets(3).Name = "Name2"

Because the Sheets() array is a list of sheet names.
An odd occurence though is: 
If you save and exit Excel, open the file,
and create a new Sheet (Name4)
it will create it as:
Sheet2(Name4)
and the array becomes:
Sheets(1).Name = "Name1"
Sheets(2).Name = "Name4"
Sheets(3).Name = "Name3"
Sheets(4).Name = "Name2"

So, basically, when you see "sheet1", "sheet2", etc in the VBA Project 
Explorer, 
it is NOT a sheet name and canNOT be used as the array.

Because of this, using sheets(1) or Sheets(2) is inconsistent, and I NEVER use 
it.
(some of my macros delete and create several sheets during processing.  It's 
not 
uncommon to be up to sheet300 in a session).

What I would suggest is to use:
Sheets("Name1") or set a variable for the sheet name
ShtName = "Name3"
and use:

Sheets(ShtName).Select

Paul



From: Nasim 
To: MS EXCEL AND VBA MACROS 
Sent: Mon, February 14, 2011 6:02:46 PM
Subject: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not 
sheet 
with index2

Hi,

we are using excel 2010. I use sheets(2) in my code and it was working
fine (since this w

Re: $$Excel-Macros$$ Nos. of Sundays between two days

2011-02-15 Thread N Pradhan

Thank you very much for the prompt reply Sir.



- Original Message - 
  From: ashish koul 
  To: excel-macros@googlegroups.com 
  Sent: Monday, February 14, 2011 8:17 PM
  Subject: Re: $$Excel-Macros$$ Nos. of Sundays between two days


  start date in a1

  and end date in a2

  no of sundays  =INT(((A2-A1)-NETWORKDAYS(A1,A2))/2)+1


  On Mon, Feb 14, 2011 at 6:04 PM, N Pradhan  wrote:

Dear Sir,

Formula required for nos. of sundays between two days.
e.g. if an employee has joined on 18/7/2010, how many sundays between 
18/7/2010 and 31/01/2011.

Thanks & Regards.

-- 

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

To post to this group, send email to excel-macros@googlegroups.com
 
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel




  -- 

  Regards

  Ashish Koul
  akoul.blogspot.com
  akoul.wordpress.com
  My Linkedin Profile

  P Before printing, think about the environment.

   



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

  To post to this group, send email to excel-macros@googlegroups.com
   
  <><><><><><><><><><><><><><><><><><><><><><>
  Like our page on facebook , Just follow below link
  http://www.facebook.com/discussexcel

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

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

Re: $$Excel-Macros$$ Dynamic range

Re: $$Excel-Macros$$ Saving excel workbook to 2 folders

Re: $$Excel-Macros$$ Segmentation?

Re: $$Excel-Macros$$ Partial Matching HELP PLEASE! SOS! SOS!!

Re: $$Excel-Macros$$ Saving excel workbook to 2 folders

Re: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2

$$Excel-Macros$$ Saving excel workbook to 2 folders

RE: "$$Excel-Macros$$ RASHI QUERRY"

$$Excel-Macros$$ Diff excel Sheet based on Name & test

Re: $$Excel-Macros$$ Excel intrinsic constants/enumerations

$$Excel-Macros$$ Partial Matching HELP PLEASE! SOS! SOS!!

$$Excel-Macros$$ Dynamic range

$$Excel-Macros$$ RE: Excel graphs and formula for adding time spent

$$Excel-Macros$$ Re: error checking VBA Excel 2003

$$Excel-Macros$$ Segmentation?

Re: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2

Re: $$Excel-Macros$$ Fwd: Need to Check

Re: $$Excel-Macros$$ Load image from the url

Re: $$Excel-Macros$$ Sheets(2) refers to 2nd sheet in excell file not sheet with index2

Fwd: $$Excel-Macros$$ Solution required

Re: $$Excel-Macros$$ data in horizontal row

Re: $$Excel-Macros$$ RASHI QUERRY

Re: $$Excel-Macros$$ Query

Re: $$Excel-Macros$$ Query

$$Excel-Macros$$ Fwd: Need to Check

Re: $$Excel-Macros$$ Problem with Sorting in row wise

$$Excel-Macros$$ Problem with Sorting in row wise

Re: $$Excel-Macros$$ Re: Array or Variant ???

$$Excel-Macros$$ error checking VBA Excel 2003

Re: $$Excel-Macros$$ RASHI QUERRY

Re: $$Excel-Macros$$ Load image from the url

Re: $$Excel-Macros$$ A little OT

Re: $$Excel-Macros$$ Nos. of Sundays between two days

33 matches

Site Navigation

Mail list logo

Footer information