date:20111014

Dear Prabhat,

Please try it and see attached sheet  for reference

Copy it and paste in Formula-Define Name-Referes to

Indirect Formula
=INDIRECT(Sheet4!BMATCH('Indirect Example
3'!$A$4,Sheet4!$A:$A,0))

On Fri, Oct 14, 2011 at 9:46 AM, prabhat.shrivasta...@gmail.com 
prabhat.shrivasta...@gmail.com wrote:

 Good morning experts

 Need your valueable help to complete ma project, i have three n more
 pics in name of ram, shyam, n more pasted on diffrent sheet now i want
 if i write ram in a assinged cell ram's pic appear in selected area of
 sheet n so on. I dont wanna use macro for thisi need this to
 complete ma project.

 Thanks n regards,
 Prabhat Shrivastava

 --

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




-- 
Thanks  regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/
*http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.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


Image_Fatching example.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Most Helpful Member Sep'11 - Noorain Ansari

Thanks to all of you dear

On Fri, Oct 14, 2011 at 12:55 PM, Ananta Sapkota
sapkota.ana...@gmail.comwrote:

 Congratulation, Keep it up.


 On Thu, Oct 13, 2011 at 11:18 PM, Ankit Agrawal 
 ankit.agrawal...@gmail.com wrote:

 Congrats Noorain. You deserved it..


 On Fri, Oct 14, 2011 at 11:40 AM, Subhash Yadav scy2...@gmail.comwrote:

 Congratulations Noorain...


 Thanks for your helps provided..

 Regards,


 Subhash Yadav

   On Thu, Oct 13, 2011 at 8:51 PM, Dilip Pandey dilipan...@gmail.comwrote:

 Congratulations NOORAIN.

 sorry for late responss as I was running behind on my email.

 Regards,
 DILIPandey
   On 2 Oct 2011 00:43, Ayush Jain jainayus...@gmail.com wrote:

 Hello Everyone,

 Noorain Ansari has been selected as 'Most Helpful Member' for the month
 of Sept'11
 He has posted 140 posts in sept 2011 and helped many people through his
 expertise. He has been consistent contributor to this excel forum but has
 acheived this recognition for first time.

 *About Noorain Ansari :*
 [image: noorain.jpg]

 Noorain is a great poet and keep sharing his thoughts on different
 topics throgh cool poems. Professionally, he is a Excel aficionado and
 presently working as senior business analyst in One97 Communication Ltd. 
 He
 is a MS excel - vba expert and keep sharing his thoughts with this forum.
 You can follow his blogs for Excel knowledge and cool poems :
 www.excel-macro.blogspot.com  www.noorain-ansari.blogspot.com

 *Noorain, Many thanks for all your great support to group. Keep it up*

 *Thanks to Don Guillett, Bheema, Venkat, GoldenLance, Ashish Koul, XLS
 S , Paul and other folks for helping excel enthusiasts voluntarily !! Keep
 it up !!

 *Keep posting.

 Regards
 Ayush Jain
 Group Manager

 --

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




 --
 Subhash Chand Yadav

 --

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




-- 
Thanks  regards,
Noorain Ansari

$$Excel-Macros$$ Selecting a range based on a cell value

2011-10-14 Thread Seba

Hi all,

I am facing a problem regarding the range selection.

I have a worksheet used for recording daily stuff. SOmething like
this:

ColAColB   ColC
January1.1.2011 9:00 - 10:00
January1.1.2011 10:00 - 11:00
January1.1.2011  etc.
January2.1.2011
January2.1.2011
January2.1.2011
January3.1.2011
January3.1.2011
January3.1.2011

etc.

Other columns contain daily specific data.
At the end of each month I have to create reports on what was done in
that month. Hence I need to copy the range for all stuff in that month
(for example January) and paste it to another worksheet in the same
workbook.

My problem is that I can't use fixed ranges, because of the February
29th.

I tried several things before posting here:

1) I wrote macro to copy and paste the entire range (all months) to
the new sheet and have it delete all entries which didn't contain the
month in question. This turned to be a pain due to 3500 and some rows.

2) I wanted to search for the required month and loop over all rows,
but this would also be time consuming.

3) Then I tried filtering the range to display only entries with
respective month, however I encountered date format issues:

Macro for April recordes like this:

ActiveSheet.Range($A$1:$AS$3653).AutoFilter Field:=3, Operator:=
_
xlFilterValues, Criteria2:=Array(1, 4/1/2011)



I modified this to:

ActiveSheet.Range($A$3:$IN$3653).AutoFilter Field:=3,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MyMonth)


Whereas MyMonth is the variable defined earlier so I can use one macro
for all 12 months.

Here the format is the problem. My locale entry convention is:
dd.mm.

But the macro syntax only works if I put in: mm/dd/, which is not
the format used in our country.

I also tried to modify the format with: MyMonth= Format(MyMonth, mm-
dd-)

but I always get the date displayed with full stops and not with
slashes.


How can I fix this formatting issue or maybe in a more efficient way
select the required range for desired month?

Thank you for your help.

seba


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


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

Re: $$Excel-Macros$$ Selecting a range based on a cell value

First of all...
I think some of your primary assumptions are not accurate!

I have 109,000 rows with 74 columns in one of my reporting documents
(my macros build a summary dashboard based on this data)

I wrote a macro that:
a) prompts for a reporting month,
b) Clears the Report sheet
c) Extracts all records originated in the specified month
d) copies all 74 columns to the Report sheet.

The macro ran and copied 9700 rows in 3 seconds.

Now, your problem is a bit different because the date field isn't a true 
Excel 
date.
It's a string of characters that REPRESENTS a date to YOU.

Based on your example, your date(s) must be in d.m. format.
So, we need to split up the string and extract the month character.

By doing that, and comparing STRINGs instead of integers, I was able to 
process 109,000 records in 4 seconds.
Yes, I know that was a 25% increase in CPU time, but if I remove the
StatusBar updates (which is display intensive)
I can recover the wasted second! (just kidding)

The following macro assumes:
- The source data is in a sheet called Data2
- The Destination sheet is called Sheet1
- The column that contains the date is column N
- The maximum column to be copied is column BZ (should be sufficient!)

You can modify it to fit your actual conditions.

Sub CopyRows()
    Dim dRow, sRow, sRows
    Dim sCol
    Dim dMon As Integer
    Dim dArray
    Application.ScreenUpdating = False
    dMon = InputBox(Enter Month, Report Month, Month(Now) - 1)
    tStart = Timer
    Sheets(Sheet1).Select
    Sheets(Sheet1).Range(A2:BZ20).ClearContents
    dRow = 
Application.WorksheetFunction.CountA(Sheets(Sheet1).Range(A1:A10))
    sRows = 
Application.WorksheetFunction.CountA(Sheets(Data2).Range(A1:A20))
    For sRow = 2 To sRows
    If (sRow Mod 1000 = 0) Then Application.StatusBar = sRow   of   
sRows   =   Round(sRow / sRows * 100, 1)  %
    dArray = Split(Sheets(Data2).Cells(sRow, N).Value, .)
    If (dArray(1)  X = dMon  X) Then 'uses X to convert integer 
values to strings from dArray
    dRow = dRow + 1
    Sheets(Sheet1).Range(A  dRow  :BZ  dRow).Value = 
Sheets(Data2).Range(A  sRow  :BZ  sRow).Value
    End If
    Next sRow
    Application.ScreenUpdating = True
    Application.StatusBar = False
    
'xxx

    msg = Reported   dRow - 1   Records in:
    tStop = Timer
    TMin = 0
    TElapsed = tStop - tStart
    TMin = TElapsed \ 60
    TSec = TElapsed Mod 60
    msg = msg  Chr(13)  Chr(13)
    If (TMin  0) Then msg = msg  TMin   mins 
    msg = msg  TSec   sec
    MsgBox msg
    
'xxx

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





From: Seba sebastjan.hri...@gmail.com
To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com
Sent: Fri, October 14, 2011 6:28:25 AM
Subject: $$Excel-Macros$$ Selecting a range based on a cell value

Hi all,

I am facing a problem regarding the range selection.

I have a worksheet used for recording daily stuff. SOmething like
this:

ColA            ColB                  ColC
January        1.1.2011            9:00 - 10:00
January        1.1.2011            10:00 - 11:00
January        1.1.2011              etc.
January        2.1.2011
January        2.1.2011
January        2.1.2011
January        3.1.2011
January        3.1.2011
January        3.1.2011

etc.

Other columns contain daily specific data.
At the end of each month I have to create reports on what was done in
that month. Hence I need to copy the range for all stuff in that month
(for example January) and paste it to another worksheet in the same
workbook.

My problem is that I can't use fixed ranges, because of the February
29th.

I tried several things before posting here:

1) I wrote macro to copy and paste the entire range (all months) to
the new sheet and have it delete all entries which didn't contain the
month in question. This turned to be a pain due to 3500 and some rows.

2) I wanted to search for the required month and loop over all rows,
but this would also be time consuming.

3) Then I tried filtering the range to display only entries with
respective month, however I encountered date format issues:

Macro for April recordes like this:

    ActiveSheet.Range($A$1:$AS$3653).AutoFilter Field:=3, Operator:=
_
        xlFilterValues, Criteria2:=Array(1, 4/1/2011)



I modified this to:

        ActiveSheet.Range($A$3:$IN$3653).AutoFilter Field:=3,
Operator:= _
        xlFilterValues, Criteria2:=Array(1, MyMonth)


Whereas MyMonth is the variable defined

Re: $$Excel-Macros$$ Formula Sorting Data (Alpha + Numeric)

It looks to be an ALPHABETIC sort
rather than a NUMERIC sort.

or.. a Binary sort...

Regardless, that means that
192 comes before 52 because 1 comes before 5

Unfortunately, even if the cells are formatted as Text,
Excel tries to sort numbers as Numeric and text gets sorted separately.

To get the sort sequence you wanted,
I was able to create a column and concatenated the letter x in front of each 
number using:

=x  A2

then copied it down.
By sorting on this column, I was able to achieve the sequence you intended.
 
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-





From: Dilip Pandey dilipan...@gmail.com
To: excel-macros@googlegroups.com excel-macros@googlegroups.com
Cc: sharma@gmail.com
Sent: Fri, October 14, 2011 1:15:27 AM
Subject: Re: $$Excel-Macros$$ Formula Sorting Data (Alpha + Numeric)

Hi,

In the Sorted list, you have shown 92C, 94B, 94C...
This is not sorted as per alphabet order and neither it is in numeric
(Descending) and same goes with 193, 195, 52, 86.

Kindly explain the order.  Thanks

Regards,
DILIPandey

On 10/14/11, B Sharma sharma@gmail.com wrote:
 Dear Don  other experts

 Coloumn A has raw data and column B have sorted data
 Before        After
 52         193
 86         195
 92C         52
 94B         86
 94B         87
 195         92C
 193         94B
 ABC         94C
 XYZ         94J
 87         94K
 94J         ABC
 94B         XYZ
 94K
 94C


 Hope that you can understand...
 Thanks 7 Regards,
 BS




 On Oct 13, 9:01 pm, dguillett1 dguille...@gmail.com wrote:
 What would the AFTER look like

 Don Guillett
 SalesAid Software
 dguille...@gmail.com







 -Original Message-
 From: B Sharma
 Sent: Thursday, October 13, 2011 8:46 AM
 To: MS EXCEL AND VBA MACROS
 Cc: rajanverma1...@gmail.com
 Subject: $$Excel-Macros$$ Formula Sorting Data (Alpha + Numeric)

 Dear Excel Experts  (and to my friends who have answered to my
 earlier queries)

 From a long time, I am searching for a formula which can sort the list
 and represent the unique datas only

 for Example , In column 1 I have the following data

 52
 86
 92C
 94B
 94B
 195
 193
 ABC
 XYZ
 87
 94J
 94B
 94K
 94C

 like this...
 I want a output in column B where it will ignore the duplicate items
 and sorted figures.

 I think its quiet difficult but please try this ...

 Thanks  Regards,
 B. Sharma

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

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

 
 Like our page on facebook , Just follow below
 linkhttp://www.facebook.com/discussexcel

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



-- 
Thanks  Regards,

DILIP KUMAR PANDEY, mvp
      MBA,B.Com(Hons),BCA
Mobile: +91 9810929744
dilipan...@gmail.com
dilipan...@yahoo.com
New Delhi - 62, India

-- 
--

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

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


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

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

Re: Fwd: $$Excel-Macros$$ Expense Sheet -- Need Help Urgently

In the Date column, is that the date format you have set up in Windows?
dd.mm. ??

In other words, if you change the format of the cells to Long Date format, does
31.05.2011 become Tuesday, May 31, 2011 ??
 
If not, we'll have to get a little creative...

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





From: Anupam Konhar anupam.kon...@gmail.com
To: excel-macros@googlegroups.com
Sent: Fri, October 14, 2011 7:26:36 AM
Subject: Fwd: $$Excel-Macros$$ Expense Sheet -- Need Help Urgently


NO RESPONSE ANYBODY THER???


-- Forwarded message --
From: Anupam Konhar anupam.kon...@gmail.com
Date: Fri, Oct 14, 2011 at 11:58 AM
Subject: $$Excel-Macros$$ Expense Sheet -- Need Help Urgently
To: excel-macros@googlegroups.com



Hi All!

Need your valuable assistance with the attached excel file.

- The Sheet 1 which has Exps has different items, and now  in sheet 2 we have 
categorised it to mainly 3 items.


2. MATERIALS  EQUIPMENTS : will comprise of Equipments, Eqpt Rental, Safety  
Products, Hardware, copper slag, Eqpt Rental.
3. CONSUMABLES: will comprise of Diesel  Consumables.
4. ADMIN CHARGES: Transport, Salary, Levy/CPF, Admin, Food, Entertainment, 
Sub-con and Others.

So need help with this and also want to check if next time when i enter the 
expense for the future months, is it possible to automatically capture the data 
from sheet 1.

Thank you for the help in advance.

Thanks n Regards

Anup



 



-- 
--

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$$ Toggle a Cell value

2011-10-14 Thread dguillett1

Right click sheet tabview codecopy/paste this to make the changes in col G

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column  7 Or Target.Count  1 Then Exit Sub
If Target = 1 Then
Target = 0
Target.Interior.ColorIndex = 4
Else
Target = 1
Target.Interior.ColorIndex = 3
End If
End Sub

Don Guillett
SalesAid Software
dguille...@gmail.com

From: Cab Boose 
Sent: Friday, October 14, 2011 2:33 AM
To: excel-macros@googlegroups.com 
Subject: $$Excel-Macros$$ Toggle a Cell value

Hi eveyrone

See attached sheet.

For a 12 volt motor speed direction, Forward or Reverse  I want to have a value 
of a cell to toggle back and forth between 0 and 1 and cell color also to 
change each time between green and red.

Prefer to use a cell than use a toggle button, unless a toggle button can 
change colors etc and the value of 0 or 1  from the toggle button can be used 
to export etc

Your comments would be appreciated.

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

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

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

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


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

Re: $$Excel-Macros$$ Selecting a range based on a cell value

2011-10-14 Thread dguillett1


Attach your file



Don Guillett
SalesAid Software
dguille...@gmail.com
-Original Message- 
From: Seba

Sent: Friday, October 14, 2011 5:28 AM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Selecting a range based on a cell value

Hi all,

I am facing a problem regarding the range selection.

I have a worksheet used for recording daily stuff. SOmething like
this:

ColAColB   ColC
January1.1.2011 9:00 - 10:00
January1.1.2011 10:00 - 11:00
January1.1.2011  etc.
January2.1.2011
January2.1.2011
January2.1.2011
January3.1.2011
January3.1.2011
January3.1.2011

etc.

Other columns contain daily specific data.
At the end of each month I have to create reports on what was done in
that month. Hence I need to copy the range for all stuff in that month
(for example January) and paste it to another worksheet in the same
workbook.

My problem is that I can't use fixed ranges, because of the February
29th.

I tried several things before posting here:

1) I wrote macro to copy and paste the entire range (all months) to
the new sheet and have it delete all entries which didn't contain the
month in question. This turned to be a pain due to 3500 and some rows.

2) I wanted to search for the required month and loop over all rows,
but this would also be time consuming.

3) Then I tried filtering the range to display only entries with
respective month, however I encountered date format issues:

Macro for April recordes like this:

   ActiveSheet.Range($A$1:$AS$3653).AutoFilter Field:=3, Operator:=
_
   xlFilterValues, Criteria2:=Array(1, 4/1/2011)



I modified this to:

   ActiveSheet.Range($A$3:$IN$3653).AutoFilter Field:=3,
Operator:= _
   xlFilterValues, Criteria2:=Array(1, MyMonth)


Whereas MyMonth is the variable defined earlier so I can use one macro
for all 12 months.

Here the format is the problem. My locale entry convention is:
dd.mm.

But the macro syntax only works if I put in: mm/dd/, which is not
the format used in our country.

I also tried to modify the format with: MyMonth= Format(MyMonth, mm-
dd-)

but I always get the date displayed with full stops and not with
slashes.


How can I fix this formatting issue or maybe in a more efficient way
select the required range for desired month?

Thank you for your help.

seba


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

2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

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


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


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

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


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

Re: $$Excel-Macros$$ vlookup return with pcture assinged

2011-10-14 Thread prabhat.shrivasta...@gmail.com

The file need to run on ms excel 2003 version.

Rgrds.Prabhat

On Oct 14, 12:03 pm, ashish koul koul.ash...@gmail.com wrote:
 see if it helps

 On Fri, Oct 14, 2011 at 9:46 AM, prabhat.shrivasta...@gmail.com 





 prabhat.shrivasta...@gmail.com wrote:
  Good morning experts

  Need your valueable help to complete ma project, i have three n more
  pics in name of ram, shyam, n more pasted on diffrent sheet now i want
  if i write ram in a assinged cell ram's pic appear in selected area of
  sheet n so on. I dont wanna use macro for thisi need this to
  complete ma project.

  Thanks n regards,
  Prabhat Shrivastava

  --

  --- 
  ---
  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*
 *http://www.excelvbamacros.com/*

 P Before printing, think about the environment.

  images_index.xlsx
 55KViewDownload

-- 
--
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$$ Need help

2011-10-14 Thread Rajasekhar Praharaju

Good evening experts,

Please assist me in this concern i am  looking for a excel macro which can
give details of  folders and their respective size.

for example  i have Main folder with name Excel with in the folder i have
many other sub folders, so i am looking for a macro which can list the sub
folders names and their respective sizes.

Thanks for your help in advance.

-- 
-- 
Thanks  Regards,
Rajasekhar.P

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


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

Re: $$Excel-Macros$$ Need help

are you wanting all sub-folders within the folder?
or do you want to progress down through all the nested sub-folders?
 
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-





From: Rajasekhar Praharaju rajasekhar.prahar...@gmail.com
To: excel-macros@googlegroups.com
Sent: Fri, October 14, 2011 11:11:22 AM
Subject: $$Excel-Macros$$ Need help

Good evening experts, 

Please assist me in this concern i am  looking for a excel macro which can give 
details of  folders and their respective size.

for example  i have Main folder with name Excel with in the folder i have many 
other sub folders, so i am looking for a macro which can list the sub folders 
names and their respective sizes.

Thanks for your help in advance. 

-- 

-- 
Thanks  Regards,
Rajasekhar.P
-- 
--

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$$ Assign values in an array to a userform listbox, then allow user to select

2011-10-14 Thread DaveO

I have values in an array that I want to appear in a userform listbox.
How do I assign array values to a listbox? I googled and found this
code, but it returns an error:
ListBox1.List() = ArrayName()

Then, once the listbox is populated, I want the user to doubleclick a
listbox entry and store it to a Textbox in the same userform. What is
the syntax for that?

Thank you!

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


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

Re: $$Excel-Macros$$ Need help

This should do it.

Sub List_Folder_Size()
    Dim fso, fldr, sfldr, FolderName
    Dim Sht, R
    FolderName = C:\temp\
    Sht = FolderList
    
    Set fso = CreateObject(Scripting.FileSystemObject)
    
    Sheets(Sht).Range(A2:Z65000).ClearContents
    R = 1
    If (fso.folderexists(FolderName)) Then
    Set fldr = fso.getfolder(FolderName)
    For Each sfldr In fldr.subfolders
    R = R + 1
    Sheets(Sht).Cells(R, A).Value = sfldr.Name
    Sheets(Sht).Cells(R, B).Value = sfldr.Size
    Next
    End If
    MsgBox Done
End Sub

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





From: Rajasekhar Praharaju rajasekhar.prahar...@gmail.com
To: excel-macros@googlegroups.com
Sent: Fri, October 14, 2011 11:18:13 AM
Subject: Re: $$Excel-Macros$$ Need help

Hi Paul, 

Thanks for your quick reply, Only the first sub folder names and their 
respective sizes.

Thanks,
Raj


On Fri, Oct 14, 2011 at 8:45 PM, Paul Schreiner schreiner_p...@att.net wrote:

are you wanting all sub-folders within the folder?
or do you want to progress down through all the nested sub-folders?
 
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-






From: Rajasekhar Praharaju rajasekhar.prahar...@gmail.com
To: excel-macros@googlegroups.com
Sent: Fri, October 14, 2011 11:11:22 AM
Subject: $$Excel-Macros$$ Need help


Good evening experts, 


Please assist me in this concern i am  looking for a excel macro which can 
give 
details of  folders and their respective size.


for example  i have Main folder with name Excel with in the folder i have many 
other sub folders, so i am looking for a macro which can list the sub folders 
names and their respective sizes.


Thanks for your help in advance. 


-- 

-- 
Thanks  Regards,
Rajasekhar.P
-- 
--

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



-- 

-- 
Thanks  Regards,
Rajasekhar.P

Subscribe to our group and receive daily updates.
Excel groups

https://sites.google.com/site/excelencyclopedia/how-to-join/assignments

Send your queries and Suggestions @
excel-gro...@googglegroups.com

Excelforum Blogspot

a 
href=http://feedburner.google.com/fb/a/mailverify?uri=ExcelForumamp;loc=en_US;Subscribe
 to excel forum by Email/a


-- 
--

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

Re: $$Excel-Macros$$ Need help

2011-10-14 Thread Rajasekhar Praharaju

hi Paul,

i am getting bug here,

ub List_Folder_Size()
Dim fso, fldr, sfldr, FolderName
Dim Sht, R
FolderName = D:\C-backup
Sht = FolderList

Set fso = CreateObject(Scripting.FileSystemObject)

Sheets(Sht).Range(A2:Z65000).ClearContents

this yellow highlighted one i am getting bug.

Please can you help me in this concern.

Thanks,
Raj
On Fri, Oct 14, 2011 at 8:51 PM, Paul Schreiner schreiner_p...@att.netwrote:

This should do it.

Sub List_Folder_Size()
Dim fso, fldr, sfldr, FolderName
Dim Sht, R
FolderName = C:\temp\
Sht = FolderList

Set fso = CreateObject(Scripting.FileSystemObject)

Sheets(Sht).Range(A2:Z65000).ClearContents
R = 1
If (fso.folderexists(FolderName)) Then
Set fldr = fso.getfolder(FolderName)
For Each sfldr In fldr.subfolders
R = R + 1
Sheets(Sht).Cells(R, A).Value = sfldr.Name
Sheets(Sht).Cells(R, B).Value = sfldr.Size
Next
End If
MsgBox Done
End Sub

*Paul*

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

--
*From:* Rajasekhar Praharaju rajasekhar.prahar...@gmail.com
*To:* excel-macros@googlegroups.com
*Sent:* Fri, October 14, 2011 11:18:13 AM
*Subject:* Re: $$Excel-Macros$$ Need help

Hi Paul,

Thanks for your quick reply, Only the first sub folder names and their
respective sizes.

Thanks,
Raj

On Fri, Oct 14, 2011 at 8:45 PM, Paul Schreiner schreiner_p...@att.netwrote:

are you wanting all sub-folders within the folder?
or do you want to progress down through all the nested sub-folders?

*Paul*

--
*From:* Rajasekhar Praharaju rajasekhar.prahar...@gmail.com
*To:* excel-macros@googlegroups.com
*Sent:* Fri, October 14, 2011 11:11:22 AM
*Subject:* $$Excel-Macros$$ Need help

Good evening experts,

Please assist me in this concern i am looking for a excel macro which can
give details of folders and their respective size.

for example i have Main folder with name Excel with in the folder i have
many other sub folders, so i am looking for a macro which can list the sub
folders names and their respective sizes.

Thanks for your help in advance.

--
--
Thanks Regards,
Rajasekhar.P

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

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 Regards,
Rajasekhar.P

Subscribe to our group and receive daily updates.
Excel groups

https://sites.google.com/site/excelencyclopedia/how-to-join/assignments

Send your queries and Suggestions @
excel-gro...@googglegroups.com

Excelforum Blogspot

a href=
http://feedburner.google.com/fb/a/mailverify?uri=ExcelForumamp;loc=en_UShttp://feedburner.google.com/fb/a/mailverify?uri=ExcelForumloc=en_USSubscribe
to excel forum by Email/a

Re: $$Excel-Macros$$

Dear Sunil,


It is possible to Excel and VBA both.
You can see attached sheet with both example.

-- 
Thanks  regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/
*http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/
On Fri, Oct 14, 2011 at 5:40 PM, sunil bn bnsuni...@gmail.com wrote:

 Hi Expert,

 Kindly help me... what is the formula in excel to convert numerical to text
 ..eg if Number 1 its has to convert in text as One help me very urgent.

 --
 *Regards*
 **Sunil B.N
 ***9035858585


 --

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


Convert Text to Number(Excel and VBA Both).xlsm
Description: Binary data

Re: $$Excel-Macros$$ Need help

do you HAVE a sheet called FolderList ??

If not, replace FolderList with the name of the sheet that you want to use to 
display the sizes!
 
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-





From: Rajasekhar Praharaju rajasekhar.prahar...@gmail.com
To: excel-macros@googlegroups.com
Sent: Fri, October 14, 2011 11:31:34 AM
Subject: Re: $$Excel-Macros$$ Need help

hi Paul, 

i am getting bug here,

ub List_Folder_Size()
    Dim fso, fldr, sfldr, FolderName
    Dim Sht, R
    FolderName = D:\C-backup
    Sht = FolderList
    
    Set fso = CreateObject(Scripting.FileSystemObject)
    
    Sheets(Sht).Range(A2:Z65000).ClearContents

this yellow highlighted one i am getting bug.

Please can you help me in this concern.

Thanks,
Raj

On Fri, Oct 14, 2011 at 8:51 PM, Paul Schreiner schreiner_p...@att.net wrote:

This should do it.

Sub List_Folder_Size()
    Dim fso, fldr, sfldr, FolderName
    Dim Sht, R
    FolderName = C:\temp\
    Sht = FolderList
    
    Set fso = CreateObject(Scripting.FileSystemObject)
    
    Sheets(Sht).Range(A2:Z65000).ClearContents
    R = 1
    If (fso.folderexists(FolderName)) Then
    Set fldr = fso.getfolder(FolderName)
    For Each sfldr In fldr.subfolders
    R = R + 1
    Sheets(Sht).Cells(R, A).Value = sfldr.Name
    Sheets(Sht).Cells(R, B).Value = sfldr.Size
    Next
    End If
    MsgBox Done
End Sub

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






From: Rajasekhar Praharaju rajasekhar.prahar...@gmail.com
To: excel-macros@googlegroups.com
Sent: Fri, October 14, 2011 11:18:13 AM
Subject: Re: $$Excel-Macros$$ Need help


Hi Paul, 


Thanks for your quick reply, Only the first sub folder names and their 
respective sizes.


Thanks,
Raj


On Fri, Oct 14, 2011 at 8:45 PM, Paul Schreiner schreiner_p...@att.net wrote:

are you wanting all sub-folders within the folder?
or do you want to progress down through all the nested sub-folders?
 
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-






From: Rajasekhar Praharaju rajasekhar.prahar...@gmail.com
To: excel-macros@googlegroups.com
Sent: Fri, October 14, 2011 11:11:22 AM
Subject: $$Excel-Macros$$ Need help


Good evening experts, 


Please assist me in this concern i am  looking for a excel macro which can 
give 
details of  folders and their respective size.


for example  i have Main folder with name Excel with in the folder i have 
many 
other sub folders, so i am looking for a macro which can list the sub folders 
names and their respective sizes.


Thanks for your help in advance. 


-- 

-- 
Thanks  Regards,
Rajasekhar.P
-- 
--

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




-- 

-- 
Thanks  Regards,
Rajasekhar.P

Subscribe to our group and receive daily updates.
Excel groups

https://sites.google.com/site/excelencyclopedia/how-to-join/assignments

Send your queries and Suggestions @
excel-gro...@googglegroups.com

Excelforum Blogspot

a

Re: $$Excel-Macros$$ Need help

2011-10-14 Thread Rajasekhar Praharaju

thanks alot Paul its working.

On Fri, Oct 14, 2011 at 9:09 PM, Paul Schreiner schreiner_p...@att.netwrote:

 do you HAVE a sheet called FolderList ??

 If not, replace FolderList with the name of the sheet that you want to
 use to display the sizes!


 *Paul*

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


  --
 *From:* Rajasekhar Praharaju rajasekhar.prahar...@gmail.com
 *To:* excel-macros@googlegroups.com
 *Sent:* Fri, October 14, 2011 11:31:34 AM

 *Subject:* Re: $$Excel-Macros$$ Need help

 hi Paul,

 i am getting bug here,

  ub List_Folder_Size()
 Dim fso, fldr, sfldr, FolderName
 Dim Sht, R
 FolderName = D:\C-backup
 Sht = FolderList

 Set fso = CreateObject(Scripting.FileSystemObject)

 Sheets(Sht).Range(A2:Z65000).ClearContents


 this yellow highlighted one i am getting bug.

 Please can you help me in this concern.

 Thanks,
 Raj
 On Fri, Oct 14, 2011 at 8:51 PM, Paul Schreiner schreiner_p...@att.netwrote:

   This should do it.

 Sub List_Folder_Size()
 Dim fso, fldr, sfldr, FolderName
 Dim Sht, R
 FolderName = C:\temp\
 Sht = FolderList

 Set fso = CreateObject(Scripting.FileSystemObject)

 Sheets(Sht).Range(A2:Z65000).ClearContents
 R = 1
 If (fso.folderexists(FolderName)) Then
 Set fldr = fso.getfolder(FolderName)
 For Each sfldr In fldr.subfolders
 R = R + 1
 Sheets(Sht).Cells(R, A).Value = sfldr.Name
 Sheets(Sht).Cells(R, B).Value = sfldr.Size
 Next
 End If
 MsgBox Done
 End Sub



 *Paul*

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


  --
 *From:* Rajasekhar Praharaju rajasekhar.prahar...@gmail.com
 *To:* excel-macros@googlegroups.com
 *Sent:* Fri, October 14, 2011 11:18:13 AM
 *Subject:* Re: $$Excel-Macros$$ Need help

 Hi Paul,

 Thanks for your quick reply, Only the first sub folder names and their
 respective sizes.

 Thanks,
 Raj

 On Fri, Oct 14, 2011 at 8:45 PM, Paul Schreiner 
 schreiner_p...@att.netwrote:

   are you wanting all sub-folders within the folder?
 or do you want to progress down through all the nested sub-folders?


 *Paul*

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


  --
 *From:* Rajasekhar Praharaju rajasekhar.prahar...@gmail.com
 *To:* excel-macros@googlegroups.com
 *Sent:* Fri, October 14, 2011 11:11:22 AM
 *Subject:* $$Excel-Macros$$ Need help

 Good evening experts,

 Please assist me in this concern i am  looking for a excel macro which
 can give details of  folders and their respective size.

 for example  i have Main folder with name Excel with in the folder i have
 many other sub folders, so i am looking for a macro which can list the sub
 folders names and their respective sizes.

 Thanks for your help in advance.

 --
 --
 Thanks  Regards,
 Rajasekhar.P


 --

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




 --
 --
 Thanks  Regards,
 Rajasekhar.P

 Subscribe to our group and receive daily updates.
 Excel groups

Re: $$Excel-Macros$$ Urgent Help required

Here's a VBA formula that does the trick

http://www.ozgrid.com/forum/showthread.php?t=144212

Regards,

Sam Mathai Chacko (GL)

On Fri, Oct 14, 2011 at 11:03 AM, Dilip Pandey dilipan...@gmail.com wrote:

 Hi Prathap,

 If this is one time exercise then, write 100 somewhere in and copy
 it. Now select the data and apply paste special -  Values - Divide.
 If this is kinda template where figures will come and go, then you can
 use custom format.

 Regards,
 DILIPandey


 On 10/13/11, Prathap prathap@gmail.com wrote:
  Hi,
 
  Is there a way to convert all the cells with numbers in to million format
  with shortcut / formula.
 
  Example: 1232345487 i want as 1232.
 
   Attached is the sample file
 
  Best Regards,
  PRATHAP D
  M +91 9899-370-328
 
  --
 
 --
  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
 


 --
 Thanks  Regards,

 DILIP KUMAR PANDEY, mvp
   MBA,B.Com(Hons),BCA
 Mobile: +91 9810929744
 dilipan...@gmail.com
 dilipan...@yahoo.com
 New Delhi - 62, India

 --

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

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

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




-- 
Sam Mathai Chacko

-- 
--
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$$ Fuzzy name match- output from userform

My take would be to have a list-box instead of a dropdown. Have a
multi-select listbox, and give an option to the user to select the desired
list of names.

Regards,

Sam

On Fri, Oct 14, 2011 at 7:09 AM, DaveO dmo...@gmail.com wrote:

 I have a scenario in which a user needs to perform a fuzzy match
 between a name and a variation of that name in a list about 4500 names
 long. For example, the user may be looking for Smith, John but needs
 to match to Smith,John Jr in the longer list.

 My plan is to write the long list to an array and then perform a first
 name and last name match within the array. If multiple names match I'd
 like to open a user form and allow the user to choose from the smaller
 list of names, then have that name written to the spreadsheet.

 My question: What construct is available in the user form that would
 best suit this purpose? Can I arrange a dropdown, for instance?

 Any ideas appreciated- thanks.

 --

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

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

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




-- 
Sam Mathai Chacko

-- 
--
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$$ Worksheetfunction with Excel for Mac

Cannot comment conclusively without having Mac first hand exposure, however,
just a suggestion. Try Application.VarP

Regards,
Sam

On Fri, Oct 14, 2011 at 12:56 PM, james D jpadlimi...@gmail.com wrote:

 Hi,

 I have coded something that works fine in Excel 2010 (on Windows) -
 but when a Mac user uses it they get compile errors.

 The issue is me using WorksheetFunction.Var_p (or average... or
 anything I think) - this does not compile for the Mac. I think this
 might be to do with the analysis Pak? I can't find anything that
 allows a Mac to install the pack... I can find different packs for it
 to do stats analysis - but I cannot find one thing on how to fix this
 exact problem.

 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




-- 
Sam Mathai Chacko

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

2011-10-14 Thread Alexander Cargill

hi there  have cells on another sheet of my workbook and was wondering
how i use these to populate a combo box.

alex cargill

ps i use excel 2010

-- 
--
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$$ Toggle a Cell value

This should do it

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address(0, 0) = G15 Then
Target.Value = Abs(CLng(Not -Target.Value))
End If

End Sub

Use a conditional format for coloring the cell.

If you do not want to add a conditional format (which is faster than VBA by
the way), and do the coloring also through VBA, then just add the following
line within the If End IF statement in the VBA above.

Target.Interior.ColorIndex = 4 - Target.Value

Regards,

Sam Mathai Chacko

On Fri, Oct 14, 2011 at 6:09 PM, dguillett1 dguille...@gmail.com wrote:

   Right click sheet tabview codecopy/paste this to make the changes in
 col G

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Target.Column  7 Or Target.Count  1 Then Exit Sub
 If Target = 1 Then
 Target = 0
 Target.Interior.ColorIndex = 4
 Else
 Target = 1
 Target.Interior.ColorIndex = 3
 End If
 End Sub

 Don Guillett
 SalesAid Software
 dguille...@gmail.com

  *From:* Cab Boose swch...@gmail.com
 *Sent:* Friday, October 14, 2011 2:33 AM
 *To:* excel-macros@googlegroups.com
 *Subject:* $$Excel-Macros$$ Toggle a Cell value

  Hi eveyrone

 See attached sheet.

 For a 12 volt motor speed direction, Forward or Reverse  I want to have a
 value of a cell to toggle back and forth between 0 and 1 and cell color also
 to change each time between green and red.

 Prefer to use a cell than use a toggle button, unless a toggle button can
 change colors etc and the value of 0 or 1  from the toggle button can be
 used to export etc

 Your comments would be appreciated.

 Charlie Harris
 --

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

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

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

 --

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

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

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




-- 
Sam Mathai Chacko

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


CellToggle.xls
Description: MS-Excel spreadsheet

$$Excel-Macros$$ Pivot Help

2011-10-14 Thread hanumant shinde

Hi frends,

need to learn Pivot programming do you have any good stuff or site which you 
can refer to me?

-- 
--
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$$ Assign values in an array to a userform listbox, then allow user to select

1) The array has to be a one dimensional array

Private Sub UserForm_Activate()

Dim arrayname As Variant
arrayname = Sheet2.Range(A1:A10).Value
ListBox1.List() = arrayname

End Sub

If it is a two dimensional array, you could loop through the values and add
item to list

like

Dim lngLoop as long

For lngLoop = lbound(arrayname,1) to ubound(arrayname,1)
listbox1.additem arrayname(lngLoop,1)
next lngLoop

2) It can be done on a single select listbox. not on a multiselect

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

TextBox1.Text = ListBox1.Value

End Sub

Regards

Sam Mathai Chacko (GL)


On Fri, Oct 14, 2011 at 8:49 PM, DaveO dmo...@gmail.com wrote:

 I have values in an array that I want to appear in a userform listbox.
 How do I assign array values to a listbox? I googled and found this
 code, but it returns an error:
 ListBox1.List() = ArrayName()

 Then, once the listbox is populated, I want the user to doubleclick a
 listbox entry and store it to a Textbox in the same userform. What is
 the syntax for that?

 Thank you!

 --

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




-- 
Sam Mathai Chacko

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

Here's a solution that will return the text in Thousands, Lakh, Crore, Arab
instead of million, billion, trillion etc.

http://www.excelfox.com/forum/showthread.php?45-Number-into-words-(Rupees)

Regards,

Sam Mathai Chacko (GL)

On Fri, Oct 14, 2011 at 9:06 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote:

 Dear Sunil,


 It is possible to Excel and VBA both.
 You can see attached sheet with both example.

 --
 Thanks  regards,
 Noorain Ansari
  *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/
 *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/
 On Fri, Oct 14, 2011 at 5:40 PM, sunil bn bnsuni...@gmail.com wrote:

 Hi Expert,

 Kindly help me... what is the formula in excel to convert numerical to
 text ..eg if Number 1 its has to convert in text as One help me very urgent.

 --
 *Regards*
 **Sunil B.N
 ***9035858585


 --

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




-- 
Sam Mathai Chacko

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

If it is a form control, right-click on the control and click format
control, and in the input range, add the entire range reference including
the name of the sheet, like Sheet2!$A$1:$A$10

If it is an activex control, right-click on the control and click
properties, and in the ListFillRange, add the entire range reference
including the name of the sheet, same as above.

Attaching a sample for reference.

Regards,

Sam Mathai Chacko (GL)

On Fri, Oct 14, 2011 at 11:11 PM, Alexander Cargill 
zandercatz1...@gmail.com wrote:

 hi there  have cells on another sheet of my workbook and was wondering
 how i use these to populate a combo box.

 alex cargill

 ps i use excel 2010

 --

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




-- 
Sam Mathai Chacko

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


CellToggle.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Pivot Help

You would find countless results if you just do a search on 'VBA Pivot
Table'

Anyway, a few sites that could be recommended are

http://www.ozgrid.com/News/pivot-tables.htm
http://msdn.microsoft.com/en-us/library/aa164537%28v=office.10%29.aspx
http://www.zerrtech.com/content/pivot-table-creation-using-excel-vba

Regards,

Sam Mathai Chack (GL)

On Sat, Oct 15, 2011 at 12:56 AM, hanumant shinde arsfan2...@yahoo.co.inwrote:

 Hi frends,

 need to learn Pivot programming do you have any good stuff or site which
 you can refer to me?

  --

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




-- 
Sam Mathai Chacko

-- 
--
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$$ Toggle a Cell value

2011-10-14 Thread Cab Boose

Hi Don and Sam

Thanks for your input.

I use Win7  Excel  and on a laptop.


Don, your code work ok but  small hiccup.  The code/color changes ok when I
select a different cell and then come back to actual cell again.  Color and
number change as required.  However I would like to be able to leave the
cursor in the active cell, somethimes,  and each time I tap the cell on the
laptop pad, it should change. That is without having to go to another
cell first.  In other words how do you get Excel to know when you select
cell and then select again without moving.  Maybe if we can after the first
selection and change of color/code Excel places the active cell into a
default cell nearby, and if the direction needs to be changed again I need
to select that cell again.  Or is there a way of refreshing the g15 cell
without moving.

Sam, your code brings up a   run time error '13'   type mismatch.  The color
is ok by format, but does the code allow for changing the value in the cell
back and forth from 1 to 0 and 0 to 1 .


Thankyou all

Charlie Harris





On Sat, Oct 15, 2011 at 6:36 AM, Sam Mathai Chacko samde...@gmail.comwrote:

 This should do it


 Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If Target.Address(0, 0) = G15 Then
 Target.Value = Abs(CLng(Not -Target.Value))
 End If

 End Sub

 Use a conditional format for coloring the cell.

 If you do not want to add a conditional format (which is faster than VBA by
 the way), and do the coloring also through VBA, then just add the following
 line within the If End IF statement in the VBA above.

 Target.Interior.ColorIndex = 4 - Target.Value

 Regards,

 Sam Mathai Chacko


 On Fri, Oct 14, 2011 at 6:09 PM, dguillett1 dguille...@gmail.com wrote:

   Right click sheet tabview codecopy/paste this to make the changes in
 col G

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Target.Column  7 Or Target.Count  1 Then Exit Sub
 If Target = 1 Then
 Target = 0
 Target.Interior.ColorIndex = 4
 Else
 Target = 1
 Target.Interior.ColorIndex = 3
 End If
 End Sub

 Don Guillett
 SalesAid Software
 dguille...@gmail.com

  *From:* Cab Boose swch...@gmail.com
 *Sent:* Friday, October 14, 2011 2:33 AM
 *To:* excel-macros@googlegroups.com
 *Subject:* $$Excel-Macros$$ Toggle a Cell value

  Hi eveyrone

 See attached sheet.

 For a 12 volt motor speed direction, Forward or Reverse  I want to have a
 value of a cell to toggle back and forth between 0 and 1 and cell color also
 to change each time between green and red.

 Prefer to use a cell than use a toggle button, unless a toggle button can
 change colors etc and the value of 0 or 1  from the toggle button can be
 used to export etc

 Your comments would be appreciated.

 Charlie Harris
 --

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

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

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

 --

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

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

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




 --
 Sam Mathai Chacko

  --

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

Re: $$Excel-Macros$$ Dynamic chart range

Dear Atul,

Please see attached sheet with Dynamic Chart example.

-- 
Thanks  regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/
*http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/
On Thu, Oct 13, 2011 at 5:32 AM, Atul atul_pate...@yahoo.com wrote:

 Hi All,
 I want to make chart  range dynamic  For example, range A1:g1
 represents series for the first week and I want to extend the range
 for the second week means I want to change the range to A1:N1. Please
 help me to make it dynamic
 Thanks
 Atul

 --

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


DynamicChart_Example.xls
Description: MS-Excel spreadsheet

$$Excel-Macros$$ Solution required

2011-10-14 Thread N Pradhan

Dear Experts,


I have been given the following task.

Please help me solving the problem attached in the excel sheet.

Thanks.

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


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


Book1.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Toggle a Cell value