Re: $$Excel-Macros$$ Expert Advice Needed - Formula to count Worksheets

[STDEV(i)]

Create a Name in your workbook (ribbon > Tab Formula > Name Manager

Name (for Excample) : SheetsCount
Refer to:   =GET.WORKBOOK(4)

[image: Create a NAME.PNG]

in the sheet you can use
*=SheetsCount*
resultling number of sheet in the workbook

they called it : Macro Excel4
http://support.microsoft.com/kb/109976




On Tue, Nov 29, 2011 at 4:36 AM, Secret Shot  wrote:

> Dear Experts,
> It there any Formula to count worksheets in a Excel File. I know VBA code
> for this but i want any Formula or any combination of formula's only to
> solve this. kindly Help. This is very critical for me.
>
> Thanks in Advance..
> Pankaj Pandey
> Bhopal
>
>

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

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

Re: $$Excel-Macros$$ Setting a default cell format in excel 2007

[STDEV(i)]

It seems you need a new template standards.

Open a new workbook
Set the desired number of sheet in the workbook
Select all sheets at once as a group
Select all cells (Ctrl + A)
Format the cells of this workbook  in your
own font-style, font-size, and so on,
including the format number  (= GENERAL)
save the workbook with NAME:  BOOK.XLTX
(assumption: you you are the users of Excel 2007 / 2010)
storage paths:
If you are a Windows XP user
the Templates folder is usually C:\Documents and Settings\\Application
Data\Microsoft\Templates.
In Windows Vista the Templates folder is usually
C:\Users\\AppData\Roaming\Microsoft\Templates.

Make the same workbook but also contains only one sheet and save as
SHEET.XLTX

book.xltx will be used by Excel when you open a new workbook.
sheet.xltx will be used by Excel every time you insert a new sheet.

~siti



On Thu, Nov 17, 2011 at 12:48 PM, Amit Desai (MERU)  wrote:

>  Dear Masters,
>
> ** **
>
> Can you please let me know, how to set a default format for all cells in
> excel 2007. As of now whenever I type any data it comes in date format, I
> want to change this to general.
>
> ** **
>
> Regards,
>
> *Amit Desai*
>
>
> **
>

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

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

Re: $$Excel-Macros$$ Simple Excel Calendar

[STDEV(i)]

Another *Array Formula* for Simple Calendar
(MultipleResult Array Formula on  7 columns X 6 Rows) based on Date in a
cell F22)

grid formula
=$F$22-WEEKDAY($F$22)+COLUMN(1:7)+ROW(1:7)*7-7

weekday name formula
=TEXT({1,2,3,4,5,6,7},"ddd")



On Sun, Oct 30, 2011 at 8:40 AM, NOORAIN ANSARI wrote:

> Dear Group,
>
> Please find attached Simple Excel Calender.
>
> =TRANSPOSE({1;2;3;4;5;6;7})
>
> =IF(MONTH(F10)<>MONTH(F10-WEEKDAY(F10)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}),"",F10-WEEKDAY(F10)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7})
>
> Press ctrl+shift+enter with both formula
> --
> Thanks & regards,
> Noorain Ansari
>  *
> *
>

-- 
FORUM RULES (925+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

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


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

Re: $$Excel-Macros$$ How to learn vba

[STDEV(i)]

Dear Mr excel,

You are welcome to visit your brother here
http://www.mrexcel.com/articles.shtml#VBA

best regards
~siti
your fans from indonesia



On Thu, Oct 20, 2011 at 8:21 AM, Mr excel  wrote:

> Thanks all,for the replies.i had been working with the VBA macro
> recorder.Its really very funny & intersting to learn like this.But there are
> some situation where i need to learn the programming techniques like Do
> Loops,While Loops etc which cannot be learnt from macro recording.Please
> suggest me some ideas or techniques for learning...Thanks in advance to all
> those who had helped 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$$ Re: Cut Data from Rows and Paste into Columns

[STDEV(i)]

without macro solution

Assuming 1,2,3,4,5  etc is stored in A1:A300

Put this formula in  D1
*=OFFSET($A$1,ROW(A1)*3-(4-COLUMN(A1)),0)*
copy right & down   3 columns x 100 rows

best regards,
~ siti





On Fri, Aug 26, 2011 at 8:45 PM, GreenBriar wrote:

> Transpose would not work because every 4th record (or row) would need

to go back to the first column, next row.
>
> original data could be in either an excel spreadsheet or a text file,

whichever works best.
>
> thus
>
> *1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> *
> would look like
>
> *1   2   3
> 4   5   6
> 7   8   9*

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

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


ctv_wrapped every 3 columns.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Hi

[STDEV(i)]

http://chrisrae.com/vba/whatisvba.html
http://www.excel-vba.com/
http://www.excel-vba-easy.com/
http://www.youtube.com/watch?v=t1vdt0FddsE


On Fri, Aug 26, 2011 at 10:24 PM, Mohamed Youssouf <
youssouf.mohame...@gmail.com> wrote:

> I'd like to learn how ti use MACRO

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

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

Re: $$Excel-Macros$$ how to delete multiple row & columns

[STDEV(i)]

A little correction

Sub cleanrowsandcolumnsSAS()
   lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row *+ 1*
   lc = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByColumns, xlPrevious).Column *+ 1*
   Application.EnableEvents = False
   Rows(lr).Resize(Rows.Count - lr).Delete
   Columns(lc).Resize(, Columns.Count - lr).Delete
   Application.EnableEvents = True
   ActiveWorkbook.Save
End Sub

'--thank you
siti Vi


On Tue, Aug 23, 2011 at 7:37 PM, dguillett1  wrote:

>   If I understand your request, you may like this. It also saves the file
>
> Sub cleanrowsandcolumnsSAS()
> lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
> , , , xlByRows, xlPrevious).Row
> lc = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
> , , , xlByColumns, xlPrevious).Column
> Application.EnableEvents = False
> Rows(lr).Resize(Rows.Count - lr).Delete
> Columns(lc).Resize(, Columns.Count - lr).Delete
> Application.EnableEvents = True
> ActiveWorkbook.Save
> End Sub
>
>  *From:* Amit Desai (MERU) 
> *Sent:* Tuesday, August 23, 2011 1:32 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ how to delete multiple row & coulmns
>
>
> Dear All,
>
> 
>
> Please let me know how to delete multiple rows(say from my last data based
> row # 678 to last row) & multiple columns (say from AA to last column).***
> *
>
> 
>
> This is taking a lot of file size…
>
> 
>
> Best Regards,
>
> Amit Desai
>
> 
>

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

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

Re: $$Excel-Macros$$ Hi I need your help , Please explain it with example.

[STDEV(i)]

*1.
**Worksheet.Change Event*
*
*Occurs when cells on the worksheet are changed by the user or by an
external link.
This event doesn't occur when cells change during a recalculation. Use the
Calculate event to trap a sheet recalculation.

This example changes the color of changed cells to blue.
Private Sub Worksheet_Change(ByVal Target as Range)
   Target.Font.ColorIndex = 5
End Sub


*2
Worksheet.SelectionChange Event *

Occurs when* the selection changes* on a worksheet.

e.g You move cell pointer from A1 to A2
The parameter Target  is  Range("A2")

This example scrolls through the workbook window until* the selection is in
the upper-left corner of the window*.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   With ActiveWindow
   .ScrollRow = Target.Row
   .ScrollColumn = Target.Column
   End With
End Sub


CMIIW




On Fri, Jul 15, 2011 at 6:45 PM, neil johnson 
wrote:
>
> Hi Sir,
> Can you please tell me what is difference between these two codes, Please
guide me.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> End Sub
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> End Sub

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

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

Re: $$Excel-Macros$$ Regression Analysis in Excel

[STDEV(i)]

visit ..
http://phoenix.phys.clemson.edu/tutorials/excel/regression.html
http://mallit.fr.umn.edu/fr4218/assigns/excel_reg.html
http://www.chem.orst.edu/courses/ch361-464/ch464/RegrssnFnl.pdf
http://faculty.fuqua.duke.edu/~pecklund/ExcelReview/Use%20Excel%202007%20Regression.pdf
http://cameron.econ.ucdavis.edu/excel/ex61multipleregression.html
http://www.youtube.com/watch?v=lTNyalLBZo0
http://www.law.uchicago.edu/files/files/20.Sykes_.Regression.pdf
http://en.wikipedia.org/wiki/Regression_analysis
http://office.microsoft.com/en-us/excel-help/perform-a-regression-analysis-HA00963.aspx



On Thu, Jul 14, 2011 at 2:49 PM, Venkatesan c  wrote:

> Dear All,
> Can any one help me on Regression Analysis in Excel with sample data..
> *Best Regards,*
>  *Venkat*
> *
> *
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ months and fraction of a month between two days

[STDEV(i)]

how about:
fraction to the LASTMONT ( days in EndDate Month)



On Wed, Jul 13, 2011 at 9:28 AM, Dave Bonallack
wrote:

> Hi,
> Fractions of a month are a bit subjective. Fractions of WHICH month, needs
> to be decided. Fractions of a 28-day month? 29- 30- or 31- day month? Or
> maybe a standardized 30.4375-day month?
> Regards - Dave
>
>
> On 12/07/2011, at 8:55 PM, Richard  wrote:
>
> Hi All,
> I would appreciate a vba function that will calculation the number of
> months and fraction of a month between 2 dates.
>
> i.e.
> January 1, 2011 to February 14, 2011 would be 1.5 months
> January 1, 2011 to February 15, 2011 would be 1.5335714
> January 1, 2011 to March 15, 2011 would be 2.483871
>
> Thank you in advance
>
> Rich
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ months and fraction of a month between two days

[STDEV(i)]

Function MonthFrac(StartDate As Date, EndDate As Date) As Single
*'= siti Vi / Jakarta 12 July 2011*
Dim YY1 As Integer, MM1 As Integer, DD1 As Integer
   Dim YY2 As Integer, MM2 As Integer, DD2 As Integer
Dim MM  As Integer, DD  As Integer, EndMonthDays As Integer

   YY1 = Year(StartDate):YY2 = Year(EndDate)
MM1 = Month(StartDate):   MM2 = Month(EndDate)
   DD1 = Day(StartDate): DD2 = Day(EndDate)

If DD2 < DD1 Then
  DD2 = DD2 + Day(DateSerial(YY2, MM2, 0))
  MM2 = MM2 - 1
End If

   If MM2 < MM1 Then
  MM2 = MM2 + 12
   YY2 = YY2 - 1
   End If

   EndMonthDays = Day(DateSerial(YY2, MM2 + 1, 0))
MM = (YY2 - YY1) * 12 + (MM2 - MM1)
   DD = (DD2 - DD1)
   MonthFrac = MM + (DD / EndMonthDays)

End Function


On Tue, Jul 12, 2011 at 7:55 PM, Richard  wrote:
> Hi All,
> I would appreciate a vba function that will calculation the number of
months
> and fraction of a month between 2 dates.
>
> i.e.
> January 1, 2011 to February 14, 2011 would be 1.5 months
> January 1, 2011 to February 15, 2011 would be 1.5335714
> January 1, 2011 to March 15, 2011 would be 2.483871
>
> Thank you in advance
>
> Rich

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

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


sitiVi_UDF_MonthFrac.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Query

[STDEV(i)]

=*LOOKUP*
(D16,{3001,5001,6001,7001,8001,9001,15001,25001,40001},{30,40,45,50,60,110,130,150,200})

or..

=*VLOOKUP*(D16,$A$1:$B$9,2,TRUE)

the above VLookUp fungtion  refers to a table
*tabel A1:B9*
* 3001   30 *
* 5001   40 *
* 6001   45 *
* 7001   50*
* 8001   60*
* 9001  110*
*15001  130*
*25001  150*
*40001  200 *



On Wed, Jul 6, 2011 at 6:26 PM, Kal xcel  wrote:

> Dear Experts,
>
> For this chart
>
>  3001-5000  = 30
>  5001-6000  = 40
>  6001-7000  = 45
>  7001-8000  = 50
>  8001-9000  = 50
>  9001-15000 = 110
> 15001-25000 = 130
> 25001-4 = 150
> 40001>= = 200
>
> I use this formula:
>
>
>
> =IF(AND(D16>=3001,D16<=5000),30,IF(AND(D16>=5001,D16<=6000),40,IF(AND(D16>=6001,D16<=7000),45,IF(AND(D16>=7001,D16<=8000),50,IF(AND(D16>=8001,D16<=9000),90,IF(AND(D16>=9001,D16<=15000),110,IF(AND(D16>=15001,D16<=25000),130,IF(AND(D16>=25001,D16<=4),150,IF(AND(D16>=40001),200,0)
>
> Is their any other short/intelligent way to get desired result?
>
> Please help
>
> Thanks in advance
>
> Kalyan
>

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

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


LOOKUP and VLOOUP (1_2_3_TRUE).xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Converting Text Case By VBA

[STDEV(i)]

Converting text case to ProperCase in VBA

alt:
Instead of using Application.Function / WorksheetFunction) we can also use
VBA function:
*StrConv *function

StrConv("  ", vbProperCase)

Example in sub procedur

Sub ConvertProperCase()
   Application.ScreenUpdating = False
   Dim Cell As Range
   For Each Cell In Selection.Cells
  If Not Cell.HasFormula Then
  Cell.Value = *StrConv(Rng, vbProperCase)*
  End If
   Next Cell
   Application.ScreenUpdating = True
End Sub


Thank you



On Thu, Jul 7, 2011 at 3:18 AM, Rajan_Verma wrote:

>  http://excelpoweruser.blogspot.com/2011/07/hiding-data-on-worksheet.html*
> ***
>
> ** **
>
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ workbook location on status bar

[STDEV(i)]

'--Thisworkbook Module:--

Dim OldStaBar As String

Private Sub Workbook_Open()
   OldStaBar = Application.DisplayStatusBar
   Application.DisplayStatusBar = True
   Application.StatusBar = ThisWorkbook.FullName
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Application.DisplayStatusBar = False
   Application.DisplayStatusBar = OldStaBar
End Sub



On Mon, Jul 4, 2011 at 2:41 AM, netuser501  wrote:

> Hi,
> Is there a code or a build-in option to have the location path of a
> workbook permanently showing on the Excel status bar?
>
> Thanks
>  Pascal
>
>

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

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

Re: $$Excel-Macros$$ Macro for find any possible combination of numbers from given numbers to a given total

[STDEV(i)]

you are talking about *Permutation*

given number : 1 2 3 4 5
total arrangement of permutation  *=FACT(5)* =  *120*

==
12345 21345 31245 41235 51234
12354 21354 31254 41253 51243
12435 21435 31425 41325 51324
12453 21453 31452 41352 51342
12534 21534 31524 41523 51423
12543 21543 31542 41532 51432
13245 23145 32145 42135 52134
13254 23154 32154 42153 52143
13425 23415 32415 42315 52314
13452 23451 32451 42351 52341
13524 23514 32514 42513 52413
13542 23541 32541 42531 52431
14235 24135 34125 43125 53124
14253 24153 34152 43152 53142
14325 24315 34215 43215 53214
14352 24351 34251 43251 53241
14523 24513 34512 43512 53412
14532 24531 34521 43521 53421
15234 25134 35124 45123 54123
15243 25143 35142 45132 54132
15324 25314 35214 45213 54213
15342 25341 35241 45231 54231
15423 25413 35412 45312 54312
15432 25431 35421 45321 54321
==


' VBA CODE:
'-
Dim D() As String  ' =Array Data Characters
Dim N   As Byte' =number of char
Dim Tulis   As Range   ' =range where output data to be written
Dim oRowAs Long' =Row index for Output
Dim oColAs Byte' =COlumn index for Output
Dim MaxRow  As Long' =oRow when reaching Fact(N-1)


Sub PermutArranger_Jilid3()
   '--
   ' this procedure is only preparing variables & inisials, and
   ' calling "ArrangeAndWrite" (main procedur) for the first time
   '--
   Dim k As Byte

   oRow = 0: oCol = 1
   N = Len(Trim(Range("B2")))
   Set Tulis = Range("L5")
   ReDim D(1 To N) As String
   MaxRow = WorksheetFunction.Fact(N - 1)
   ActiveSheet.Unprotect xpas
   ClearDataArea ActiveSheet.Range("L2")

   If N > 9 Then
  MsgBox "max 9 digit, due to limitation on number of cells in a
sheet..", _
 16, ThisWorkbook.Name
  Exit Sub
   End If

   For k = 1 To N:  D(k) = Mid(Trim(Range("B2")), k, 1):  Next k

   Application.Calculation = xlCalculationManual

   Call ArrangeAndWrite(D, 1)
   Application.Calculation = xlCalculationAutomatic
   ActiveSheet.Protect xpas

End Sub



Private Sub ArrangeAndWrite(ByVal D, i As Byte)
   '  this is a recursive procedure
   '-
   Dim txt As String, tmp As String * 1, j As Byte
   *' limitation (if i = N)*
   If i = N Then
  For j = 1 To N: txt = txt & D(j): Next j
  If oRow = MaxRow Then
 oRow = 0: oCol = oCol + 1
  End If
  oRow = oRow + 1:  Tulis(oRow, oCol) = txt
   Else
  For j = i To N
 tmp = D(j): D(j) = D(i): D(i) = tmp
  ArrangeAndWrite D, (i + 1)
  Next j
   End If
End Sub
'---


you can download my example workbook, if you like, at:

http://www.box.net/shared/22pginzj8p

kind regards,
siti Vi



On Sat, Jul 2, 2011 at 10:47 PM, Markkim  wrote:

> Hi
> I can think of a way to write this macro.
> What I want is to find any possible combination of numbers from given
> numbers to a given total. For example,
>
> Let's say
> Given numbers: 1, 2, 3, 4, 5
> total = 30
>
> Will it be possible to get any combination of Given numbers adding up to
> 30?
> Macro should say number of possible combinations and details of
> combindation
> I have been thinking about this macro for long time...  but I can't think
> of any way..
>
> Any ideas???
> cheers!
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ User form + Macro to activate certain region of active work sheet.

[STDEV(i)]

Dim ws As Worksheet
Const pass As String = "rash"
Dim ArChkBox(1 To 6) As Object

Private Sub UserForm_Activate()
   Set ArChkBox(1) = CheckBox1
   Set ArChkBox(2) = CheckBox2
   Set ArChkBox(3) = CheckBox3
   Set ArChkBox(4) = CheckBox4
   Set ArChkBox(5) = CheckBox5
   Set ArChkBox(6) = CheckBox6
End Sub

Private Sub Locked_Unlocked(Rowval As Long)
   Dim r As Long
   r = Rowval * 10 - 9
   Sheet1.Unprotect pass
   Sheet1.Cells.Locked = True
   If ArChkBox(Rowval) Then
  Cells(r, 1).Resize(10, 1).EntireRow.Locked = False
   Else
  Cells(r, 1).Resize(10, 1).EntireRow.Locked = True
   End If
   Sheet1.Protect pass
   If Rowval > 1 Then ActiveWindow.ScrollRow = r - 2 Else:
ActiveWindow.ScrollRow = r
   Cells(r, 1).Resize(10, 1).EntireRow.Select
End Sub

Private Sub CheckBox1_Click()
   Call Locked_Unlocked(1)
End Sub
Private Sub CheckBox2_Click()
   Call Locked_Unlocked(2)
End Sub
Private Sub CheckBox3_Click()
   Call Locked_Unlocked(3)
End Sub
Private Sub CheckBox4_Click()
   Call Locked_Unlocked(4)
End Sub
Private Sub CheckBox5_Click()
   Call Locked_Unlocked(5)
End Sub
Private Sub CheckBox6_Click()
   Call Locked_Unlocked(6)
End Sub




On Wed, Jun 29, 2011 at 1:53 PM, Rash  wrote:

> Hi all,
>
> Need Help...
>
> I have a user form in which i have 6 check boxes. ok and cancel button.
>
> when ever I click 1st check box, rows 1 to 10 should to be editable, rest
> all rows should be non editable and hidden.
>
> For check box 2, rows 11 to 20 editable, rest up and down area should be
> non editable and hidden. And vice versa for other check boxes.
>
> I need a macro for this. Attached is the excel sheet with user form created
> in background.
>
> Thanks in advance,
> Rsh
>
>

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

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


ctv_Test_Cells_Locking_Unlocking.xlsm
Description: Binary data

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

[STDEV(i)]

Correction:
the macro shd be :


Sub Insert2Rows()
   ' siti Vi / jakarta, 29 Jun 2011
   '---
   Dim RNG As Range, r As Long
   Set RNG = ActiveSheet.Cells(1).CurrentRegion.Offset(1, 0)

   For r = RNG.Rows.Count To 2 Step -1
  *RNG(r, 1).Resize(2, 1).EntireRow.Insert*
   Next

End Sub




On Wed, Jun 29, 2011 at 8:55 PM, STDEV(i)  wrote:

>
> Sub Insert2Rows()
>' siti Vi / jakarta, 29 Jun 2011
>'---
>Dim RNG As Range, r As Long
>Set RNG = ActiveSheet.Cells(1).CurrentRegion.Offset(1, 0)
>
>For r = RNG.Rows.Count To 2 Step -1
>   If r Mod 2 > 0 Then RNG(r, 1).Resize(2, 1).EntireRow.Insert
>Next
>
> End Sub
>
>
>
> On Wed, Jun 29, 2011 at 6:29 PM, Rakesh Sharma wrote:
>
>> *Dear all,
>> *
>> *
>> *
>> *please help*
>>
>> thanks in adv.
>> --
>> Regards
>> Rakesh Sharma
>>
>

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

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

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

[STDEV(i)]

Sub Insert2Rows()
   ' siti Vi / jakarta, 29 Jun 2011
   '---
   Dim RNG As Range, r As Long
   Set RNG = ActiveSheet.Cells(1).CurrentRegion.Offset(1, 0)

   For r = RNG.Rows.Count To 2 Step -1
  If r Mod 2 > 0 Then RNG(r, 1).Resize(2, 1).EntireRow.Insert
   Next

End Sub



On Wed, Jun 29, 2011 at 6:29 PM, Rakesh Sharma wrote:

> *Dear all,
> *
> *
> *
> *please help*
>
> thanks in adv.
> --
> Regards
> Rakesh Sharma
>
>

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

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

Re: $$Excel-Macros$$ VBA object?

[STDEV(i)]

You need  some Variables  (memory variable)
http://www.excel-vba.com/excel-vba-contents.htm


Sub Test()

'--declaring variable

Dim MyCell As Range
Dim MyValue As Variant
Dim CompareResult as Boolean

'--assignments

Set MyCell = Range("A1")
MyValue  = MyCell.Value

'--copying the value to another cell

Range("T4").Value = MyValue

'--comparaing  two thing (in same Type)
'--you can compare two values by one of the Comparison Operators :
'  = / > / < / >= / <= / <> / Like
' http://www.excelfunctions.net/VBA-Operators-And-Functions.html
' http://sourcedaddy.com/ms-excel/working-with-vba-operators.html

CompareResult = (Range("T4").Value* = *MyCell.Value)
MsgBox CompareResult

or in another expression

If Range("T4").Value *=* MyCell.Value Then
   MsgBox "Equal"
Else
   MsgBox "Not Equal"
End If

End Sub


hope it helps
CMIIW



On Mon, Jun 27, 2011 at 10:10 PM, Andrew  wrote:

> I am new to VBA and would like to ask everyone a question about how to
> capture an object?
>
> I want to copy a value and position in excel.  For example:
>
> 1 - Find cell A1 and get the contents, "code1234" and remember that
> position
> 2 - I want to copy the contents to cell T4
> 3 - Go back to cell position A1 and compare it to the value of cell A2
>
> So i think my problem right now being a newbie at this is that i can
> record the actions using the recorder and can copy and paste a cell by
> referring to it as say, Range("F48").Select
> But if i want to know the value of the F48 cell i am not sure how to
> do that after i have copied that cell?
>
> Also how do you compare the value of cells? For example i want to
> compare cells, A1 and A2?
>
> Andrew
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ vba code instr - help to understand it

[STDEV(i)]

its look like  to extrac a* list of unique values *form Range Rng1

CMIIW




On Mon, Jun 27, 2011 at 6:24 AM, netuser1110  wrote:

> Hi,
>
> Does anyone can explain me what this loop does and more specificly the
> "InStr" function the way it is used below :
>
>  For Each Cel In Rng1
>  If Len(Cel) > 0 Then
> If InStr(1, TmpX, LCase(Cel) & Chr(171)) = 0 Then
>TmpX = TmpX & LCase(Cel) & Chr(171)
>n = n + 1: Rng2(n, nCol + 2) = Cel.Value
> End If
>  End If
>   Next Cel
>
> 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

Re: $$Excel-Macros$$ Excel Pop-ups

[STDEV(i)]

please check this vba code, hope it helps..

-Standard Module

Sub ExpiryWarning()
  ' siti Vi // Jakarta, 19 Jan 2011
  '
  Dim dTable As Range, t As String
  Dim R As Long, N As Long, I As Integer

  Set dTable = Cells(1).CurrentRegion
  N = dTable.Rows.Count

  For R = 2 To N
     If IsDate(dTable(R, 5)) Then
        I = dTable(R, 5) - Date
        If I <= 15 Then
        If I > 0 Then
           dTable(R, 1).Resize(1, dTable.Columns.Count).Select
           t = "Name  : " & dTable(R, 2) & vbCr & vbCr
           t = t & "Expiry Date : " & Format(dTable(R, 5),
"dd-mmm-") & vbCr & vbCr
           t = t & "Number of days to come : " & Format(I, "0") & vbCr
           MsgBox t, 48, "Warning"
        End If
        End If
     End If
  Next
  Cells(1).Activate
End Sub

-Module Sheet1

Private Sub Worksheet_Activate()
   ExpiryWarning
End Sub

-Tiisworkbook Module

Private Sub Workbook_Open()
   ExpiryWarning
End Sub



On Sun, Jun 19, 2011 at 12:25 AM, Mahreen Ellahi
 wrote:
> Hii
>
> I have a some data with limits expiry on different dates. I want to have
> popup window to appear whenever the limit is about to expire like 15 to 30
> days earlier. Can anyone help me for VBA coding.
>
> Regards
> Mahreen

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

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


ctv_Excel MsgBox.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Recover delete file.

[STDEV(i)]

use  Recuva
Recuva recovers files deleted from your Windows computer, Recycle Bin,
digital camera card, or MP3 player. And it's free!

http://www.piriform.com/recuva
 

On Fri, Jun 17, 2011 at 12:26 PM, Bhushan Sabbani wrote:

> Dear Excel Expert,
>
> I was using the below code to delete the temp data file by mistake my
> personal which contain all the macro and the senstive data got delete i have
> serach in the recycle bin and whereever pls help me to find the file the
> file name was "personal.xls"
>
> Sub Killed()
> Application.DisplayAlerts=False
> ThisWorkbook.ChangeFileAccess xlReadOnly
> Kill ThisWorkbook.FullName
> ThisWorkbook.Close False
> End Sub
>
> Thanks in advance.
>
> Bhushan Sabbani.
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ Transpose Data by Formula

[STDEV(i)]

Dear  Mr. Smith,

Please try and check this VBA Code, if it helps...


Sub AbnormalizeYourTabel()
   ' STDEV(i) / milis belajar-excel / 08 apr 2011
   ' retouched for another table-structure
   ' jakarta, Jun 12, 2011
   '---
   Dim Tbl As Range, NewTbl As Range
   Dim n As Long, r As Long, i As Long, tR As Long
   Dim c As Integer, u As Integer, TotQty As Double, ArtQty()
   Dim StrItm As String, Itm As String

   Set Tbl = Sheets("Data Transpose").Cells(1).CurrentRegion
   tR = Tbl.Rows.Count
   Set NewTbl = Tbl(tR + 6, 1)

   ' searching UniqItems
   StrItm = "|"
   Application.Calculation = -4135 '=manual
   Application.ScreenUpdating = 0
   For i = 2 To tR
  Itm = Tbl(i, 1) & "|"
  If InStr(1, StrItm, Tbl(i, 1), 1) = 0 Then
 r = r + 1
 StrItm = StrItm & Itm
 Tbl(i, 1).Resize(1, 2).Copy
 NewTbl(r, 1).PasteSpecial 12
  End If
   Next i

   ' Repost all datas in New Table-Structure
   Application.CutCopyMode = False
   Set NewTbl = NewTbl.CurrentRegion
   ReDim ArtQty(1 To NewTbl.Rows.Count)
   For n = 1 To NewTbl.Rows.Count
  c = 0: TotQty = 0
  For i = 2 To tR
 If NewTbl(n, 1) = Tbl(i, 1) Then
c = c + 3
*TotQty = TotQty + Tbl(i, 3*)
Tbl(i, 3).Resize(1, 3).Copy
NewTbl(n, c).PasteSpecial 12
 End If
  Next i
  *ArtQty(n) = TotQty*
   Next n
   Tbl.Resize(1, Tbl.Columns.Count - 2).Copy NewTbl(0, 1)
   u = (NewTbl.CurrentRegion.Columns.Count - 1)

   ' Headings..
   Tbl(1, 3).Resize(1, 3).Copy
   For c = 3 To u Step 3
  NewTbl(0, c).PasteSpecial xlAll
   Next c

*   ' new column (Total Qty)*
   With NewTbl(0, c)
  .Value = "TOTAL QTY"
  .Font.Bold = True
  .BorderAround Weight:=xlThin
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = True
   End With
   For n = 1 To NewTbl.Rows.Count
  NewTbl(n, c) = ArtQty(n)
   Next n

   Application.CutCopyMode = False
   Application.Calculation = -4105
   Application.ScreenUpdating = 1

End Sub




On Mon, Jun 13, 2011 at 2:07 AM, John A. Smith wrote:

> Please, attached is an example of data which I need to transpose and a
> pivot table doesn't do it...
>
> Thank you for your help.
>
> John
>
>
>

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

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


ctv_Abnormalize Your Normal Tabel.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

Re: $$Excel-Macros$$ Reverse Vlookup - -- very very urgent

[STDEV(i)]

=OFFSET($A$1,MATCH(E2,$B$2:$B$8,0),0)
or
=INDEX($A$2:$B$8,MATCH(E2,$B$2:$B$8,0),1)





On Fri, Jun 10, 2011 at 3:14 AM, hanumant shinde wrote:

> Hi friends,
>
> this is really really urgent and very very very IMP for me so quick help
> would
> be really really appreciated.
>
> Name No
> a 1
> s 2
> d 3
> f 4
> g 5
> h 6
> j 7
> data is like above i.e. first Name and then No. i want to Vlookup for No.
> values
> and get Name but without moving any col or modifying anything. i.e
> something
> like =VLOOKUP(E2,$B$2:$D$8,3,0) but this will not work as in a range
> parameter
> No. column is not at 1st place.
>
> please find the attached workbook for more details.
> let me know if you need anything else.
>
>

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

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


ctv_Vlookup_to_LEFT.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Print area that has data

[STDEV(i)]

May be, by hiding the rows that contains formula, before printing
A cell contaning formula can be detected by  *HasFormula property*

Please check this code if it helps.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
   Dim MyRng As Range, r As Long, c As Integer
   Set MyRng = ActiveSheet.UsedRange
   Application.ScreenUpdating = False
   For r = 1 To MyRng.Rows.Count
  For c = 1 To MyRng.Columns.Count
 If MyRng(r, c).HasFormula Then
MyRng(r, c).EntireRow.Hidden = True
 End If
  Next c
   Next r
   Application.ScreenUpdating = True
End Sub

best regards,
siti Vi



On Mon, Jun 6, 2011 at 5:10 AM, Bob  wrote:

> Is there a way to print the rows with data in them and not print the
> rows with just the formula. My spreadsheet has several rows with
> formulas in them and I only want to print the rows which I have added
> data. I know you can select print area and all that. But mainly it's
> for the inexperianced user that might have trouble setting the print
> area tab. Is there a setting that I can set or a macro for this?
> Thanks in Advance!!!
>

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

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


ctv_hiding formulasRow.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Find value in Active Cell in another sheet(s) & delete

[STDEV(i)]

Dear GoldenLance,

Thank you very much for your great sugestion..

user just *selects/activates a cell* where the criteria is written, and run
the macro...

kindest regards,
STDEV(i)


On Sat, Jun 4, 2011 at 5:57 PM, GoldenLance  wrote:

> Just a minor tweak to STDEV(i)'s code. Based on the original subject,
> I might change *sCriteria* = "MyCriteriaText" to sCriteria = *
> ActiveCell.Value*
>
> On Jun 4, 2:24 pm, "STDEV(i)"  wrote:
> > please check and try this VBA code if it helps
> >
> > Sub DoSomething()
> > *   ' siti Vi*
> >Dim w As Worksheet, xCel As Range
> >Dim sCriteria As String
> >
> >sCriteria = "MyCriteriaText"  '<< please edit
> >For Each w In Worksheets
> >   If Not w.Name = ActiveSheet.Name Then
> >  If Not LCase(w.Name) = "home" Then
> > For Each xCel In w.UsedRange
> >If xCel.Value = sCriteria Then xCel.Clear
> > Next xCel
> >  End If
> >   End If
> >Next w
> > End Sub
> >
> >
> > On Sat, Jun 4, 2011 at 11:47 AM, sswcharlie  wrote:
> > > Hi
> > > Part of a another code -
> > > I want to find the value(text) in active cell (value changes often) in
> > > workbook (except current WS and one other worksheet 'home") and
> > > delete. Could be more than one instance.
> > > Thanks
> > > Charlie
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ Find value in Active Cell in another sheet(s) & delete

[STDEV(i)]

please check and try this VBA code if it helps

Sub DoSomething()
*   ' siti Vi*
   Dim w As Worksheet, xCel As Range
   Dim sCriteria As String

   sCriteria = "MyCriteriaText"  '<< please edit
   For Each w In Worksheets
  If Not w.Name = ActiveSheet.Name Then
 If Not LCase(w.Name) = "home" Then
For Each xCel In w.UsedRange
   If xCel.Value = sCriteria Then xCel.Clear
Next xCel
 End If
  End If
   Next w
End Sub


On Sat, Jun 4, 2011 at 11:47 AM, sswcharlie  wrote:

> Hi
> Part of a another code -
> I want to find the value(text) in active cell (value changes often) in
> workbook (except current WS and one other worksheet 'home") and
> delete. Could be more than one instance.
>
> Thanks
> Charlie
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ Moving files from folder to folder

[STDEV(i)]

Sub Copy_One_File_Only()
FileCopy "D:\Bla_Bla_Bla\SOS\SourceFolder\FileName.pdf",
"E:\WOW\AngelinaJoly\DestinFolde\FileName.pdf"
End Sub



On Sun, May 29, 2011 at 11:06 PM, PAB  wrote:
> Hi
>
> Using VBA in Excel 2003 is it possible to select a pdf file in a
> folder and move it to another folder.
> If not, how can I automate this procedure.
> Thanks
> Paul

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ Formula to extract parent folder, parent of parent folder etc

[STDEV(i)]

try this formula
=*SUBSTITUTE(B3*
,MID(B3,1+FIND("^",SUBSTITUTE(B3,"\","^",LEN(B3)-LEN(SUBSTITUTE(B3,"\",""-1,999),"")

and see the attachment if it helps



On Fri, May 27, 2011 at 12:56 PM, Jhoomla  wrote:

> Does any one have a function or formula to extract for a path for
> eg.,
>
> E:\02 My Documents\20 Ebooks and Articles\10.04 Tintin\01 Tintin in
> the Land of the Soviets.pdf
>
> Parent Folder: = 10.04 Tintin
> Parent.Parent.folder = 20 Ebooks and Articles
> Parent.Parent.Parent.Folder= 02 My Documents
>
> any help in this regard would be greatly appreciated.

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

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


ctv_File Path and his parents.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Dates in Excel

[STDEV(i)]

Assuming that you r date is in Cell A1
Convert them  with this formula:

Formula in B1=Date(2010,Month(A1),Day(A1))



On Fri, May 27, 2011 at 12:49 AM, Rich Prince  wrote:

> I have a column of various dates that have the incorrect year, for
> example:
>
> 9/1/2011
> 12/17/2011
> 10/31/2011
> etc, etc...
>
> The year should be 2010 not 2011. Is there a quick remedy to correct
> this problem for several hundred dates?  The Month and day are correct
> - just not the year.
>
> 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

Re: $$Excel-Macros$$ Keep formula in cell

[STDEV(i)]

when you type a data ( event a dot ) in a cell,
the old data (incl. FORMULA) in the cell will be removed
and replaced with the new data.

with macro you can "automatically" keep the old data
(and stored in another place)
and then enter the new value in the same cell

cmiiw


On Mon, May 23, 2011 at 5:21 AM, Bob  wrote:

> Is there a way to keep the formula in the cell when you enter your own
> value? Thanks in advance
>
>

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

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

Re: $$Excel-Macros$$ Simple VBA code for filter data in ascending

[STDEV(i)]

do you mean SORTING ?

Ascending / descending order are parameters of SORTING action
not Filtering..

CMIIW




On Fri, May 20, 2011 at 2:57 PM, Deepak Rawat wrote:

> Hi All
> I need the simplest VBA code to filter any data in ascending.
> Regards,
> Deepak
>
>

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

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

Re: $$Excel-Macros$$ Help on Transpose Function based upon criteria

[STDEV(i)]

*Array Formula, written in 9 cells (oneRow Range) at once !*

=IFERROR(TRANSPOSE(INDEX($B$3:$B$11,SMALL(IF($A$3:$A$11=$C16,ROW($1:$9)),ROW($1:$9,"")


On Wed, May 18, 2011 at 5:36 PM, KAUSHIK SAVLA wrote:

> Hi All,
>
> I want to transpose certain data from row to column if meets specific
> criteria: eg
> Whole data is as below:
> Claim No.   Amount
> 1   10
> 2   20
> 1   30
> 1   10
> 2   20
>
>
> I want as
>
> Claim No.
> 1  10  30  10
>
> I want transpose to happen if meets criteria that it match the claim
> number, i have 5 records as such.
> I have attached an spreadsheet as example
> Please help its urgent.
> Regards,
> Kaushik
>
>

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

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


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

Re: $$Excel-Macros$$ How to remove space in excel cell

[STDEV(i)]

check whether the character is space  / CHAR(32)

=CODE(A1)
if this formula returns 32 then use TRIM
if this formual returns other numbers that 32 (space) use  SUBSTITUTE



On Wed, May 18, 2011 at 2:43 PM, anandydr  wrote:

> At times the space is leading space which is not completely removed
> using TRIM ( ) could anyone suggest what to do in such case?
>
> On May 18, 3:38 am, "STDEV(i)"  wrote:
> > sometimes what we thought as the space (as prefix or suffix) turns out to
> be
> >  *Char (160)*so we need formula like this
> > =SUBSTITUTE(A1,CHAR(160),"")
> >
> > On Sun, May 15, 2011 at 12:48 PM, Prabhu 
> wrote:
> > > Hi friends,
> > > How to remove spaces(bugs) in a particular cell either will be as
> > > prefix or suffix before or after  number or text.
> > > Regards,
> > > Prabhu
>
>

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

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

Re: $$Excel-Macros$$ How to remove space in excel cell

[STDEV(i)]

sometimes what we thought as the space (as prefix or suffix) turns out to be
 *Char (160)*so we need formula like this
=SUBSTITUTE(A1,CHAR(160),"")

On Sun, May 15, 2011 at 12:48 PM, Prabhu  wrote:

> Hi friends,
> How to remove spaces(bugs) in a particular cell either will be as
> prefix or suffix before or after  number or text.
> Regards,
> Prabhu

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

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

Re: $$Excel-Macros$$ database concept - Reg

[STDEV(i)]

it looks like you need a textbook /  excel standard manual
Go to a bookstore and buy one

you can also visit to this sites:

http://blog.contextures.com/archives/2010/03/10/sort-it-your-way-with-excel-custom-lists/
http://www.ehow.com/how_5905455_create-custom-list-excel-2007.html
http://spreadsheets.about.com/od/excel101/ss/enter_data.htm
 
http://oit.utk.edu/scc/HowToUseExcelForDataEntry.pdf
 
http://www.timeatlas.com/5_minute_tips/general/excel_data_form_makes_input_easier

http://www.internet4classrooms.com/excel_enter_edit.htm
 
http://www.homeandlearn.co.uk/me/mes10p1.html



On Tue, May 17, 2011 at 9:36 PM, jmothilal  wrote:

> how to create custom list , pls help 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$$ Special Cursor Movement After the Enter Key is Pressed

[STDEV(i)]

Put this code into  *Sheet1 Module*

Private Sub Worksheet_Change(ByVal Target As Range)
   With Target
   If .Count = 1 Then
  If .Column = 8 Then Target(1, 2).Activate
  If .Column = 9 Then Target(2, 0).Activate
   End If
   End With
End Sub

' this macro run in column H and column I of Sheet1 only




On Tue, May 17, 2011 at 5:58 PM, John A. Smith wrote:

> I need a macro that will move the curser in a specific pattern of; Right
> one cell the first time, and down one and left one the second time and then
> repeat.
>
> It would save a lot of time entering the data in two side by side columns
> so I need it to not be column specific (so I could use it in any two side by
> side columns).
>
> Thank you for your continueing dedication to helping others with Excel.
>
> John
>
>

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

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


ctv_Special Cursor Movement Upon Hitting Enter on H or I columns ONLY.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

Re: $$Excel-Macros$$ insert new row if column is missing date....

[STDEV(i)]

Sub InsertDatetUntilToday()
  ' coded by: siti Vi / Jakarta, May 16, 2011
  '---
   Dim SeleDate As Date, DiffDate As Long, n As Long
   Do While ActiveCell > 0
  If IsDate(ActiveCell) And ActiveCell < Date Then
 If Not IsEmpty(ActiveCell(2, 1)) Then
SeleDate = ActiveCell.Value
DiffDate = Date - SeleDate
ActiveCell(2, 1).Resize(DiffDate, 1).EntireRow.Insert
For n = 1 To DiffDate
   ActiveCell(n + 1, 1) = SeleDate + n
Next n
 End If
  End If
  ActiveCell(n + 1, 1).Activate
   Loop
   Me.Cells(1).Activate
End Sub




On Sun, May 15, 2011 at 4:32 PM, Stuart  wrote:

> Hi all
>
> I hope someone can help with this.
>
> My data is in columns A to F with the short date being in column B.
>
> The data is sorted in date order and the first date that appears in
> column B is 23/12/2002 and the next row contains the date 14/01/2002.
> What I need is for a macro to look at the first date in the cell that
> I have selected and then look at the next date and fill in the missing
> dates until it reaches today.  The data can go down in an unlimited
> amount of rows.
>
> So basically the macro would insert a new row with the date is column
> B as 24/12/2002, 25/12/2002, 26/12/2002 and so on and so forth.
>
> I hope this makes sense to someone
>
> Best regards
>
> Stuart
>
>

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

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


ctv_Insert Row and Date.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Re: no of days in the month ?

[STDEV(i)]

i like this cool formula

=32-DAY(A1-DAY(A1)+32)

last day in a month / number of days in a month
based on a date.



On Sun, May 15, 2011 at 8:53 AM, Armando  wrote:

> Try these:
> =DAY(EOMONTH(A1,0))
> =32-DAY(A1-DAY(A1)+32)
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ Excel filtered data selection

[STDEV(i)]

you should visit
http://www.rondebruin.nl/merge.htm


On Mon, May 16, 2011 at 12:44 AM, Prathima R  wrote:

> Hi All,
>
> i have excel data in five differrent workbooks which i will consolidate
> into one workbook and apply filter and select data by category Xin
> particular column and if yes need to be moved to another sheet and rename as
> In active..
>
> Please advise themacro or  code to be used to select data by filtering the
> excel sheet and select only X category in particular specified column.
>
> Thanks,
> Prathima
>
>

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

[STDEV(i)]

use your keyboard



On Wed, May 11, 2011 at 6:15 PM, jmothilal  wrote:

>
> I am  typing   Name  and place name  day 2 day different  excel files,
> what is easy method   i type these names and place names.
>
> --
> J.Mohilal
> Universal Computer Systems
> # 16, Brindavan Complex
> Otteri, Vellore-2
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ no of days in the month ?

[STDEV(i)]

thanks to mr verma, with his formula
=DAY(DATE(YEAR(TODAY()),MONTH(*(B2&1)*)+1,1)-1)

how about
=DAY(DATE(YEAR(TODAY()),MONTH((B2&1))+1,*0*))

The *last day of this month*  is = the  *0*th day of the Next Month !!



On Sat, May 14, 2011 at 7:03 PM, rajan verma 
wrote:
> find attached Sheet
>

> On Sat, May 14, 2011 at 3:36 PM, jmothilal  wrote:
>> Dear Friends
>> i want find total no days in the month
>> i.e   January   = 31 days
>>   Feb   = 29 days
>>   march   = 30 days like
>> --
>> J.Mohilal
>> Universal Computer Systems
>> # 16, Brindavan Complex
>> Otteri, Vellore-2

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

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

Re: $$Excel-Macros$$ How to change Date format

[STDEV(i)]

thank you mr sixthsense

may i modif the formula to be:

=DATE(YEAR(
DATEVALUE("1Jan"&RIGHT(B2,2))),MID(TEXT(B2,"00"),3,2),LEFT(TEXT(B2,"00"),2))



On Fri, May 13, 2011 at 6:03 PM, Sixthsense  wrote:

> Hi Stdev,
>
>
> Your Solution will fail when the person intended to mention the year 1998
> in this manner 10498.
>
>
> ---
> *Sixthsense
> **:) Man of Extreme & Innovative Thoughts :)*
>
>
> On Fri, May 13, 2011 at 4:13 PM, STDEV(i) wrote:
>
>> *
>> =DATE(2000+RIGHT(B2,2),MID(TEXT(B2,"00"),3,2),LEFT(TEXT(B2,"00"),2))
>> *
>>
>> see the attachment
>>
>>
>>
>> On Thu, May 12, 2011 at 7:14 PM, Prabhu  wrote:
>>
>>> Hi friends,
>>>
>>> Plz help to change date format when downloaded report from 1st of every
>>> month to 9th of the month will be like DMMYY .
>>>
>>> I have no issue in changing the format using text to column  from 10th
>>> date because it will be in DDMMYY.
>>>
>>> I have attached the sample sheet for your reference.Plz help to know the
>>> date format as DDMMYY for entire month.(i am requesting macros to do the
>>> same)
>>>
>>> Regards,
>>>
>>>  Pr
>>>
>>

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

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


ctv__bsninvreport(V2).xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Re: How to change Date format

[STDEV(i)]

another variant
=DATE("20"&RIGHT(B2,2),MID(TEXT(B2,"00"),3,2),LEFT(TEXT(B2,"00"),2))



On Thu, May 12, 2011 at 8:58 PM, GoldenLance  wrote:

> Use this in E2 and drag down
>
> =DATE(2000+RIGHT(B2,2),LEFT(RIGHT(B2,4),2),LEFT(B2,LEN(B2)-4))
>
> On May 12, 5:14 pm, Prabhu  wrote:
> > Hi friends,
> >
> > Plz help to change date format when downloaded report from 1st of every
> > month to 9th of the month will be like DMMYY .
> >
> > I have no issue in changing the format using text to column  from 10th
> date
> > because it will be in DDMMYY.
> >
> > I have attached the sample sheet for your reference.Plz help to know the
> > date format as DDMMYY for entire month.(i am requesting macros to do the
> > same)
> >
> > Regards,
> >
> > Prabhu
>

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

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

Re: $$Excel-Macros$$ Please help to make my project good.

[STDEV(i)]

please check the attachment if it helps..

best regards,
STDEV(i)


note:
we need dynamic* Max Value* of ScrollBar
so we use Scroolbar from ActiveX Control, instead of from FORM.
A little macro wil work each time you change the "career"



On Wed, May 11, 2011 at 5:21 PM, karan 1237  wrote:

> Respected Sirs/Mam,
>
> I need your help making my project good.  Please find attachment. I will be
> very obliged if someone can help me as earlier as possible.
>
> --
> Basically I have to do that when I click on drop down list & select another
> career i.e. Building and Construction so In sub career list data should
> comes from the Building and construction's sheet and If I select another
> career i.e. Catering and Hospitality so data should comes from its list
> (Catering and Hospitality) and scroll bar should work with the same. I have
> 16 careers so please tell me how can I do this.
>
>
> Thnx in Advance
>
> ııııllıı ~ ♣ кαяαηη ♣ ~ ııııllıı
>

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

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


ctv_Indirect And Address FUNCTION.xlsb
Description: application/vnd.ms-excel.sheet.binary.macroenabled.12

Re: $$Excel-Macros$$ Auto Refresh Data

[STDEV(i)]

try using
Application.OnTime  method
http://www.ozgrid.com/Excel/run-macro-on-time.htm
http://www.cpearson.com/excel/OnTime.aspx


On Mon, May 9, 2011 at 1:17 PM, Anil Bhange <
anil.bha...@tatacommunications.com> wrote:

> Hi All,
>
>
>
> I am having one database which is link to the external data (Internet)… I
> need the VBA code where the data will automatically refresh after 30 seconds
> / 15 Seconds, but it should not be a loop it just a macro automatically
> running after 15 sec.
>
>
>
> *Example : My data is refreshed at 11:44:15 time and I wanted macro to be
> automatically run at 11:45:30 and again after 11:45:45 then 11:50:00 and so
> on…*
>
>
>
> Please suggest something.
>
>
>
> I am having one problem using loop here because while macro is running in
> background I can’t able to work on other files and I wanted that macro to be
> run at background and I should able to run on the files.
>
>
>
> Is there any way to do it..
>
>
>
> Thanks & Regards,
>
>
>
> Anil Bhange
>
> IP - 512320
>
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ get name of months from date

[STDEV(i)]

Unfortunately  *Month Function* returns *Month Index ( 1 to 12 )* not *Month
NAME*
Assuming A1 containts date data :  12/31/2011
Formula in B1  =MONTH(A1)  returns :  *12*  not  *"December"*

In vba you can used MonthName Function

Sub AboutMonth()
   Range("B1") = Month(Range("A1"))
   Range("C1") = *MonthName(Range("B1"))*
End Sub

In worksheet there is no MonthName  function

CMIIW



On Mon, May 9, 2011 at 2:44 AM, Nishant Sharma  wrote:
*> Use Month formula
> open excel and copy this in A1
> 04-04-2011 =month(A1)
> Thanks
> Nishant
*>
> On Sun, May 8, 2011 at 4:48 PM, Rajesh K R 
> wrote:
>>
>> Hi Experts
>> If I want to get the name of month from  dates
>>  1/4/11- April
>> 5/5/11   - May
>>
>> how I can I do it with a formula or code
>>
>> Regards
>>
>> Rajesh Kainikkara

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

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

Re: $$Excel-Macros$$ Need Help

[STDEV(i)]

dear Mr. Verma,

et us try both macros with the NEW source table  (as New Input)  like this

please find attached:
** a workbook containing  this data
** a workbook containing both macros

X1  1.7 11/11/2008  01/11/2010
X2  2.4 11/12/2009  01/11/2010
X3  3.8 11/01/2010  01/11/2010
X4  2.5 12/30/2009  01/11/2010
X5  4.2 11/22/2009  01/11/2010
X6  4.9 02/01/2010  01/11/2010
X7  5   01/06/2010  01/11/2010
X8  5.5 01/16/2010  01/11/2010
X9  5.2 01/03/2010  01/11/2010

if you don't mind,  you are requested to attached the result sheets to
this groups.
I 'll do the same on May 10, 2011.

thank you and best regards
STDEV(i)




On Sun, May 8, 2011 at 3:13 PM, rajan verma  wrote:
>
> hi STDV(i)
> Your macro only working with 3 Rows.. if he want to add more data in that 
> table it will not expend all table..
>
> On Sun, May 8, 2011 at 1:40 PM, rajan verma  wrote:
>>
>> hi
>> find attached File
>>
>>
>> On Sat, May 7, 2011 at 11:33 AM, anvesh.gw...@gmail.com 
>>  wrote:
>>>
>>> Hi,
>>>
>>> I need help in VBA code.
>>> I have excel sheet on which data is write as below mention
>>>
>>>
>>> Input
>>>
>>>      A       B        C                         D
>>> 1   X1      1.5    11/11/2008       01/11/2010
>>> 2   X2      2.5    11/12/2009       01/11/2010
>>> 3   X3      3.5    11/01/2010       01/11/2010
>>>
>>> I want Output in another sheet as given below
>>>
>>> Output
>>>
>>>    A       B        C                         D
>>> 1   X1      1.0    11/11/2008       01/11/2010
>>> 1   X1      0.5    11/11/2008       01/11/2010
>>> 2   X2      2.0    11/12/2009       01/11/2010
>>> 2   X2      0.5    11/12/2009       01/11/2010
>>> 3   X3      3.0    11/01/2010       01/01/2010
>>> 3   X3      0.5    11/01/2010       01/01/2010
>>>
>>>
>>> Procedure
>>> 1. Check column B if it contain decimal than separate integer and
>>> decimal i.e if value is 1.5 than
>>> insert row and split  as mention above in output
>>> 2. Check C and D for date if "C" column Date is less than "D" Column
>>> than pick Date of "D" column other wise pick date of  "C" Column.
>>>
>>> Can any body help me in this.
>>>
>>> Thanks in advance
>>> Anvesh

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

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


NewInput for Anvesh_Case.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet


2X_Solution.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

Re: $$Excel-Macros$$ go back to the last visited page

[STDEV(i)]

Sub backward()
   If ActiveSheet.Index = 1 Then
  Exit Sub
   Else
  Sheets(ActiveSheet.Index - 1).Activate
   End If
End Sub


Sub forward()
   If ActiveSheet.Index = Sheets.Count Then
  Exit Sub
   Else
  Sheets(ActiveSheet.Index + 1).Activate
   End If
End Sub


On Sun, May 8, 2011 at 5:31 PM, Rajesh K R wrote:

> Hi Experts
> Is it possible to create two buttons one for forward & other for
> backward and move to sheets in the order what we used after login just
> like we do in a browser.Pls check the attachment
>
> Regards
> Rajesh Kainikkara
>

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

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


c__logfile.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ =Now() Keep from changing

[STDEV(i)]

instead of writing  =NOW()  or =TODAY()  in a cell

select a cell then
press Ctrl + ;(for FIXED today's date )
press Ctrl + Shift + :(for Fixed Now time )



On Sun, May 8, 2011 at 2:12 AM, Bob  wrote:

> If I enter the Customer in B27 I'd like for todays date to be entered
> in F27 automatically. How would you keep the date from changing the
> next day, each time the workbook is opened? Thanks in advance!!!
>
>

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

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

Re: $$Excel-Macros$$ Please Help

[STDEV(i)]

try to use this UDF (user defined function)
Function UniqueList(Dat As Range)
   ' List of UnSorted Unique Values  '
   '-'
   Dim Cel As Range, vArr(), n As Long, Tx As String
   Tx = ", "
   For Each Cel In Dat
  If InStr(1, Tx, ", " & Cel & ", ") = 0 Then
 Tx = Tx & Cel & ", "
  End If
   Next Cel
   If Len(Tx) > 0 Then Tx = Left(Tx, Len(Tx) - 2)
   If Len(Tx) > 0 Then Tx = Right(Tx, Len(Tx) - 2)
   UniqueList = Tx
End Function



On Sat, May 7, 2011 at 3:50 PM, karan 1237  wrote:

>   *
> LIST1* *LIST1* *LIST1* *RESULT*  BACK SIDE FRONT BACK, SIDE, FRONT  FRONT
> SIDE FRONT FRONT, SIDE  BACK FRONT BACK BACK, FRONT  FRONT FRONT FRONT
> FRONT
> *Hi Experts,
>
> I want like this. Please help.
>
> Thnx in Advance.
>
>
> *
> ııııllıı ~ ♣ кαяαηη ♣ ~ ııııllıı
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post 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


UDF_UniqValues.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

Re: $$Excel-Macros$$ How to use Vlookup in macro

[STDEV(i)]

It would be easy for anyone in the group to respond, if you can send a set
of sample data.


On Sat, May 7, 2011 at 1:47 PM, maulik desai  wrote:

> Hi All,
>
> I have excel database sheet
> I have created some excel output sheets (around 35 excel sheets) which
> gives me output data with the help of Vlookup fuction.
> But the problem is in every 3 months I have to change the database month
> name & because of that I need to change the vlookup range also in output
> sheets manually (need to open each sheet &change the new file name in that)
>
> Is there any way that whenever I changed the file name of database ,the
> same new range also updated in output sheets automatically.
>
> Thanks In advance.
>
>

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

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

Re: $$Excel-Macros$$ A small query, need your help in vba coding.

[STDEV(i)]

Your code looks like to be run in excel 2003  only

(1)
In both excel 2003 / excel 2007
Range("A65000")   ' last row in A column
can be coded as
Cells(*Rows.Count*,1)
or
Range("A" & *Rows.Count*)

(2)
*Value* property of a Range Object is  a *DEFAULT Property*
so you can ignore it
For example
Range("A1").*Value* = Range("C1").*Value*
can be coded as:
Range("A1") = Range("C1")
*
*



On Sat, May 7, 2011 at 1:14 PM, rajan verma wrote:

> Private Sub CommandButton1_Click()
> *Range("A65000")*.End(xlUp).Cells(2, 1) = Range("C1").Value: *
> Range("A65000")*.End(xlUp).Cells(1, 2)   = Range("D1").Value:
> End Sub
>
>
> On Sat, May 7, 2011 at 1:56 AM, Indrajit $nai wrote:
>
>> Hi All,
>> I have a little query in vba coding, please see the attached file. In the
>> excel file there is a "Save" command button, whenever I press the button it
>> will copy the data from cell no. c1:d1 and paste in the cell a1:b1, now my
>> query is, if the data is already in the cell no. a1:b1 it will be saved in
>> the next cell no. means a2:b2 then a3:b3 just going on like this, can anyone
>> help me in that coding how do I create that loop?
>> Thanks in advance.
>> Indrajit
>>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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 small query, need your help in vba coding.

[STDEV(i)]

just a little modif:

Private Sub CommandButton1_Click()
   Dim R As Long
*   R = Cells(Rows.Count, 1).End(xlUp).Row + 1*
   Cells(R, 1) = Cells(1, 3)
   Cells(R, 2) = Cells(1, 4)
End Sub




On Sat, May 7, 2011 at 11:38 AM, rajan verma wrote:

> It macro will not work properly if these is any blank row between the
> Data.. It will all time replace existing Data..
>
> On Sat, May 7, 2011 at 8:56 AM, STDEV(i) wrote:
>
>> Dear Indrajit
>> please try this code
>>
>> Private Sub CommandButton1_Click()
>>' siti Vi / jakarta, May 07, 2011
>>Dim R As Long
>>R = WorksheetFunction.CountA(Range("A:A")) + 1
>>Cells(R, 1) = Cells(1, 3)
>>Cells(R, 2) = Cells(1, 4)
>> End Sub
>>
>>
>> On Sat, May 7, 2011 at 3:26 AM, Indrajit $nai wrote:
>>
>>> Hi All,
>>>
>>> I have a little query in vba coding, please see the attached file. In the
>>> excel file there is a "Save" command button, whenever I press the button it
>>> will copy the data from cell no. c1:d1 and paste in the cell a1:b1, now my
>>> query is, if the data is already in the cell no. a1:b1 it will be saved in
>>> the next cell no. means a2:b2 then a3:b3 just going on like this, can anyone
>>> help me in that coding how do I create that loop?
>>>
>>> Thanks in advance.
>>> --
>>> Indrajit
>>>
>>>  --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post 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
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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 small query, need your help in vba coding.

[STDEV(i)]

Dear Indrajit
please try this code

Private Sub CommandButton1_Click()
   ' siti Vi / jakarta, May 07, 2011
   Dim R As Long
   R = WorksheetFunction.CountA(Range("A:A")) + 1
   Cells(R, 1) = Cells(1, 3)
   Cells(R, 2) = Cells(1, 4)
End Sub


On Sat, May 7, 2011 at 3:26 AM, Indrajit $nai wrote:

> Hi All,
>
> I have a little query in vba coding, please see the attached file. In the
> excel file there is a "Save" command button, whenever I press the button it
> will copy the data from cell no. c1:d1 and paste in the cell a1:b1, now my
> query is, if the data is already in the cell no. a1:b1 it will be saved in
> the next cell no. means a2:b2 then a3:b3 just going on like this, can anyone
> help me in that coding how do I create that loop?
>
> Thanks in advance.
> --
> Indrajit
>
>

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

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

Re: $$Excel-Macros$$ How to reduce the file size

[STDEV(i)]

how can we forget this:

http://www.excelitems.com/2010/11/shrink-reduce-excel-file-size.html

 

On Wed, Apr 27, 2011 at 6:05 PM, Sundarvelan N  wrote:

> How to reduce the file size
>
> --
> Thanks
> N.Sundarvelan
> 9600160150
>

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

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

Re: $$Excel-Macros$$ Convert Excel Ranges to PDF

[STDEV(i)]

Save As *.PDF  (excel 2007)

[image: save as PDF.GIF]


On Thu, Apr 28, 2011 at 7:39 PM, Anil Bhange <
anil.bha...@tatacommunications.com> wrote:

> Hi Expert,
>
>
>
> I am having one excel file which contains many report, I wanted to convert
> the same in PDF. Below example will give you further details,
>
>
>
> Sheet 1 : from A2 : K30
>
> Sheet 2 : from A2 : K 45
>
> Sheet 4 : from B5 : H14
>
>
>
> Worksheet also has other details but I wanted to convert those specific
> range only.
>
>
>
> Thanks & Regards,
>
>
>
> Anil Bhange
>
> IP - 512320
>
>
>

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

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

Re: $$Excel-Macros$$ Please Help.................

[STDEV(i)]

by hiding application objec at time of userform initialized or activated
Application.visible = False

and don' forget to unhide the aplication (excel) at time of userform
deactivated or unloaded
Application.visible = False

se attachaed workbook



On Wed, Apr 27, 2011 at 1:38 PM, NOORAIN ANSARI wrote:

> Dear Experts,
>
> How can Hide only workbook but userform should be visilble and work as it,
> when we click on "*Eng In out Report*"  button.
> Please see attached sheet..
>
> Thanks in Advance
>
> --
> Thanks & regards,
> Noorain Ansari
>

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

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


Query (mr_ansari).xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

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

[STDEV(i)]

when you say it is a challenge; then you have to  have your own good answer.

Sub Time_Test_Array2()

Dim DatRng As Range, LastRow As Long
Dim r As Long, i As Long

Application.ScreenUpdating = False
Range("E:E, K1:K3").Clear
Range("K1") = Timer

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set DatRng = Range("A1:A" & LastRow)

For i = 1 To DatRng.Rows.Count
If DatRng(i, 1) Mod 2 = 0 Then
   Cells(i, 5) = "Event Number"
Else
   Cells(i, 5) = "Odd Number"
End If
Next i

Range("K2") = Timer
Range("K3") = Timer - Range("K1")

End Sub



On Sun, Apr 24, 2011 at 9:13 PM, Deepak Pal Singh  wrote:

> Hi Guy's
>
> Need your help in this code.. in the following code...
>
>
>
> Sub Time_Test_Array2()
>
> Range("K1") = Now()
>
> Application.ScreenUpdating = False
>
> Range("E:E").Clear
>
> LastRow = 0
>
> LastRow = Range("A100").End(xlUp).Row
>
> iVal = Range("A1:A" & LastRow).Value
>
> ReDim strArray(1 To UBound(iVal))
>
> For i = LBound(iVal) To UBound(iVal)
>
> If iVal(i, 1) Mod 2 = 0 Then
>
> strArray(i) = "Even Number"
>
> Else: strArray(i) = "Odd Number"
>
> End If
>
> Next i
>
> Range(Cells(LBound(strArray), 5), Cells(UBound(strArray), 5)).Value =
> strArray
>
> 'Range(Cells(LBound(iVal), 5), Cells(UBound(iVal), 5)) = iVal
>
> ActiveSheet.Range("K2") = Now()
>
> End Sub
>
>
>
> Suppose there’re some numbers given on column “A”, I want to print whether
> the number is Odd or Even in column “E” using Arrays, my code is working but
> printing wrong values, instead of printing “Even Number” for an even number
> it is printing “Odd Number”, there can be a logical error in the code.
>
>
>
> All The Best.. J
>
>
>
> Thanks & Regards
>
> Deepak
>
>

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

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


ctv_Even and Odd.xls
Description: MS-Excel spreadsheet

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

[STDEV(i)]

then you have to ask to the author of the spreadsheet



On Fri, Apr 22, 2011 at 10:19 PM, vamsi varma wrote:

> dear friends,
>
> i need some help..
>
> i need to cal the macro in another spreadsheet. But unfortunately that
> spreadsheet is locked. I dont know the name of the macro to cal.
> only option i hav is to click the macro button using VBA.
>
> can anyone help in this regard? it wil helpful to me.
>
> Thanks and regards,
> Vamsi

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

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

Re: $$Excel-Macros$$ Dear Group Please Help

[STDEV(i)]

1. *array formula*
=OFFSET(Sheet1!$B$6:$C$6,MATCH($C$4,Sheet1!$B$6:$B$55,0),0,5,2)
witten on  I6:J10  at one !

2. formula wittten in one cell I5
=$C$4



On Fri, Apr 22, 2011 at 1:47 PM, Jai  wrote:

> Attachemtn attached
>
> --
>

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

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


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

Re: $$Excel-Macros$$ STDEV

[STDEV(i)]

http://www.techonthenet.com/excel/formulas/stdev.php

http://www.excelfunctions.net/Excel-Stdev-Function.html
 
http://support.microsoft.com/kb/826349
and
F1 function button on you keyboard when you open your excel



On Wed, Apr 20, 2011 at 6:07 PM, sudhir kumar  wrote:

> hi
> what is STDEV?
> please explain
>
>

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

[STDEV(i)]

unlock all cells in column A
instanciate a checkbox in cell A1  (CheckBox1)
create a conditional formatting for B1:C1
put the thos vba code to Sheet1 Module

Private Sub CheckBox1_Click()
   Me.Unprotect
   If CheckBox1 = False Then
  Range("B1:C1").Locked = True
   Else
  Range("B1:C1").Locked = False
   End If
   Me.Protect
End Sub

(please dfind attached  sample on workbook - sheet1)



On Wed, Apr 20, 2011 at 4:07 AM, Tony O  wrote:

> I would like to add a checkbox in column A for an Excel spreadsheet.
>
> When the checkbox is checked, you can type in columns B, C and D.  If
> it is unchecked, the cells are locked.
> Ideally, I would like columns B, C and D to be grey if they are
> unchecked.
>
> How would I go about doing this?
>
>

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

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


checkbox, cell locking n conditional formatting.xls
Description: MS-Excel spreadsheet

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

[STDEV(i)]

try to use this UDF and see if it helps..

Function GetNumbers(S As String, Index As Integer)
   '
   ' siti Vi / jakarta, 30-08-2009
   '
   Dim ArrN(), i%, tmp$, n%, t$, t2$

   S = Trim(S) & "|"
   For i% = 1 To Len(S) - 1
  t$ = Mid(S, i%, 1)
  t2$ = Mid(S, i% + 1, 1)
  If InStr(1, "0123456789.", t$) > 0 Then
 If InStr(1, "0123456789.", t2$) > 0 Then
tmp$ = tmp$ & t$
 Else
tmp$ = tmp$ & t$
If Len(tmp$) > 0 Then
   n = n + 1
   ReDim Preserve ArrN(1 To n)
   ArrN(n) = Val(tmp$)
   tmp$ = ""
End If
 End If
  End If
   Next i%
   GetNumbers = ArrN(Index)
End Function
'-

formula in worksheet
=GetNumbers($B3,1)
=GetNumbers($B3,2)
=GetNumbers($B3,3)
and so on


On Tue, Apr 19, 2011 at 8:44 PM, karan kanuga  wrote:

> Hi,
>
> Can any1 pls let me know how do i separate the text and nos in a
> given sentence. PFA the sheet where i need to separate the nos and text.
>
> Thanks.
>

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

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


ctv_UDF_GetNumber (extract nos from text).xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Copy results from one workbook to another

[STDEV(i)]

correction on the last command.

Sub CanSomeOneHelp()
   Dim RgDest As Range

   With Workbooks("Anodize.xls").Sheets(1)
  .Unprotect
  Set RgDest = .Cells(1).CurrentRegion
   End With
   Set RgDest = RgDest.Cells(RgDest.Rows.Count + 1, 1)

   Workbooks("Quote.xls").Sheets(1).Cells(1).CurrentRegion.Copy RgDest
   RgDest.Parent.Protect
   Application.CutCopyMode = False
   *Workbooks("Quote.xls").Sheets(1).Cells(1).CurrentRegion.ClearContents*
End Sub



On Wed, Apr 20, 2011 at 7:51 AM, STDEV(i)  wrote:

> assuming that the both workbooks are openned
>
> Sub CanSomeOneHelp()
>Dim RgDest As Range
>
>With Workbooks("Anodize.xls").Sheets(1)
>   .Unprotect
>   Set RgDest = .Cells(1).CurrentRegion
>End With
>Set RgDest = RgDest.Cells(RgDest.Rows.Count + 1, 1)
>
>Workbooks("Quote.xls").Sheets(1).Cells(1).CurrentRegion.Copy RgDest
>RgDest.Parent.Protect
>Application.CutCopyMode = False
>RgFrom.ClearContents
>  End Sub
>
>
> On Tue, Apr 19, 2011 at 7:02 AM, Dick  wrote:
>
>> Can someone help with the following: In one workbook named (Quote) I
>> would like to copy the contents of Columns A and B to another workbook
>> named (Anodize). Where the copied contents from (Quote) would be
>> placed on the next available blank row in Sheet1 Columns A and B. I
>> know how to place a Command button on the (Quote) workbook. I just
>> don't know how to code it. Once command button is clicked:
>> 1.)Copy contents from Column A and B from (Quote) Workbook Sheet1
>> 2.)Clear contents from Column A and B from (Quote) Workbook Sheet1
>> 3.)Unprotect Workbook from (Anodize) Sheet1
>> 4.)Paste contents from Column A and B from (Quote) Workbook Sheet1 TO
>> (Anodize) Sheet1 Column A and B NEXT AVAILABLE(EMPTY) ROW.
>> 5.) Protect Workbook (Anodize) Sheet1
>> Thanks so much for any help!!!
>>
>>

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

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

Re: $$Excel-Macros$$ Copy results from one workbook to another

[STDEV(i)]

assuming that the both workbooks are openned

Sub CanSomeOneHelp()
   Dim RgDest As Range

   With Workbooks("Anodize.xls").Sheets(1)
  .Unprotect
  Set RgDest = .Cells(1).CurrentRegion
   End With
   Set RgDest = RgDest.Cells(RgDest.Rows.Count + 1, 1)

   Workbooks("Quote.xls").Sheets(1).Cells(1).CurrentRegion.Copy RgDest
   RgDest.Parent.Protect
   Application.CutCopyMode = False
   RgFrom.ClearContents
End Sub


On Tue, Apr 19, 2011 at 7:02 AM, Dick  wrote:

> Can someone help with the following: In one workbook named (Quote) I
> would like to copy the contents of Columns A and B to another workbook
> named (Anodize). Where the copied contents from (Quote) would be
> placed on the next available blank row in Sheet1 Columns A and B. I
> know how to place a Command button on the (Quote) workbook. I just
> don't know how to code it. Once command button is clicked:
> 1.)Copy contents from Column A and B from (Quote) Workbook Sheet1
> 2.)Clear contents from Column A and B from (Quote) Workbook Sheet1
> 3.)Unprotect Workbook from (Anodize) Sheet1
> 4.)Paste contents from Column A and B from (Quote) Workbook Sheet1 TO
> (Anodize) Sheet1 Column A and B NEXT AVAILABLE(EMPTY) ROW.
> 5.) Protect Workbook (Anodize) Sheet1
> Thanks so much for any help!!!
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

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

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

Re: $$Excel-Macros$$ Fwd: SEARCH FUNCTION

[STDEV(i)]

plese check the attached workbook, and see if it helps.


*ARRAY FORMULA*
=small(if(left(C2:C29,len(F4))=F4,row(1:28)),row(1:28))

FORMULA
=IF(ISERR($D6),"",OFFSET(A$1,$D6,0))




On Mon, Apr 18, 2011 at 3:16 PM, C.G.Kumar wrote:

> PFA sample File.
>
> -- Forwarded message --
> From: C.G.Kumar 
> Date: Mon, Apr 18, 2011 at 1:45 PM
> Subject: SEARCH FUNCTION
> To: excel-macros@googlegroups.com
>
>
> Dear All,
>
> I intend to generate list based on search of Text String.Sample file
> attached.
> Regards,
> C.G.Kumar
>

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

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


siti_About Filtering Formula (Medical Rate).xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Password protect & unprotect sheets using code

[STDEV(i)]

Sub ABCD()
Sheets("MySheet").Unprotect "yourpassword"
--- your program accessing to sheets("MySheet")
Sheets("MySheet").Protect "yourpassword"
End sub


On Fri, Apr 15, 2011 at 6:51 PM, Rajesh K R wrote:

> Hi Experts,
>  I want to password protect my work sheets, but the same time it is
> necessary to unprotect it for the running of macro so i have to give a
> code to unprotect them. how can I do it.
>  Regards
>  Rajesh kainikkara
>
>

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

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

Re: $$Excel-Macros$$ optionbuttons

[STDEV(i)]

You can check the GROUPNAME property of your OptionButton
(active-X-Controls)
A group / a set of OptionButtons  shd have SAME GroupName

[image: OptionButton GroupName.GIF]



On Thu, Apr 14, 2011 at 8:04 AM, Dick  wrote:

> Why is some option buttons linked to each other and some not? I have 2
> that will cancel each other out when the oposite one is selected. Then
> I have 4 others that do the same but are not affected by the first 2.
> Thanks for any help.
>
>

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

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

Re: $$Excel-Macros$$ Excel formula help

[STDEV(i)]

try this formula and see if it helps

=SUM(OFFSET($D8:$I8,0,0,1,MONTH(TODAY(



On Wed, Apr 13, 2011 at 12:23 AM, vinod rao  wrote:

> Hi,
> I need a formula adding each month one column.
> Attached file has a details.
>
> Regards,
> Vin
>

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

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


ctv_Dynamic_Sum_By_Month.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ querry

[STDEV(i)]

_xlfn  IS
http://office.microsoft.com/en-us/excel-help/issue-an-xlfn-prefix-is-displayed-in-front-of-a-formula-HA010204569.aspx


On Sat, Apr 9, 2011 at 11:02 PM, Dhananjay Pinjan wrote:

> Pl. explain what is *"=_xlfn*."???
> Is it User Defined function or what else?
>
> Regards,
> Dhanajay
>
> On Sat, Apr 9, 2011 at 9:22 PM, ashish koul  wrote:
>
>> sumifs function is avaliable in excel 2007 onwards check out the link
>> below
>>
>>
>> http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
>>
>>   On Sat, Apr 9, 2011 at 7:19 PM, Dhananjay Pinjan wrote:
>>
>>> Dear Ashish,
>>>
>>> Pl. explain what is *"=_xlfn."*
>>> SUMIFS($G:$G,$M:$M,$P6,$F:$F,$O6,$D:$D,Q$5)
>>>
>>> Regards,
>>> Dhananjay
>>>
>>>   On Sat, Apr 9, 2011 at 4:07 PM, ashish koul wrote:
>>>
 see if it helps

 On Fri, Apr 8, 2011 at 7:04 PM, Nemi Gandhi wrote:

> Please reply.
>
> --
> 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 at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ Compare strings in cells, return differences

[STDEV(i)]

*=TextDif(A2,B2)*

TextDif is an UDF, like this:

Function TextDif(S1 As String, S2 As String) As String
   Dim Arr1, Arr2
   Dim n As Integer, i As Integer, t As String

   Arr1 = Split(S1, ","): Arr2 = Split(S2, ",")

   For i = LBound(Arr2) To UBound(Arr2)
  For n = LBound(Arr1) To UBound(Arr1)
 If Arr1(n) = Arr2(i) Then Arr2(i) = ""
  Next n
   Next i

   For i = LBound(Arr2) To UBound(Arr2)
  If Len(Arr2(i)) > 0 Then t = t & Arr2(i) & ","
   Next i

   TextDif = t
   If Len(t) > 0 Then TextDif = Left(t, Len(t) - 1)
End Function




On Fri, Apr 8, 2011 at 4:46 AM, SHC wrote:

> I'd like a function script that compares the strings from two cells,
> and returns, in a third cell, the parts of the 2nd string that do not
> match parts of the 1st string (I don't need to return the parts of 1st
> string that do not match parts of the 2nd string).
>
> Examples:
> Cell1: Chris
> Cell2: Carl,Chris,Peter
> Result in Cell3: Carl,Peter
>
> Cell1: Chris,Carl
> Cell2: Carl,Peter,Chris
> Result in Cell3: Peter
>
> Cell1: Chris,Dave
> Cell2: Carl,Chris,Peter
> Result: Carl,Peter
>
> Cell1: Chris,Dave,Peter,Carl
> Cell2: Dave,Chris,Carl
> Cell3 is empty
>
> Note, parts of the strings (separated by commas) will not necessarily
> be in the same order.
>
> Any help would be most 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


ctv_TextDif about Chris and Peter.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Excel help

[STDEV(i)]

i think  mr. bhanu needs

"000999"  tobe "999"  not "00999"


On Mon, Apr 4, 2011 at 11:45 AM, §»VIPER«§  wrote:

> if your data in a1 type the below formula on anywhere in the same worksheet
> =IF(AND(ISTEXT(A1),LEFT(A1,1)="0"),RIGHT(A1,LEN(A1)-1),A1)
> --
> *Thanks & Regards
> Thamu
> *
>
>

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

[STDEV(i)]

then the macro should be modified like this

Sub RemoveLeadingZeros()
   Dim xCell As Range, i As Integer, s As String
   For Each xCell In ActiveSheet.UsedRange
  s = CStr(xCell)
  For i = 1 To Len(xCell)
 If Mid(xCell, i, 1) = "0" Then
s = Right(s, Len(s) - 1)
 Else
Exit For
 End If
  Next i
  xCell = s
   Next xCell
End Sub


On Mon, Apr 4, 2011 at 7:34 AM, bhanu prakash  wrote:

> Guys thanks a ton for  responses ,i need to remove only first  letter in
> cell if it is zero in alpha numeric data - regards - bhanu
>

>
> On 04-Apr-2011 3:45 AM, "STDEV(i)"  wrote:
>
> run this macro
> Sub RemoveAllZeros()
>Dim xCell As Range
>For Each xCell In ActiveSheet.UsedRange
>   xCell = Replace(xCell, "0", "")
>Next
> End Sub
> since you ask for  "REMOVING ZERO IN EVERY CELL"
> 1500   will be 15
> 2100500  will be 215
> and so on

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

[STDEV(i)]

run this macro

Sub RemoveAllZeros()
   Dim xCell As Range
   For Each xCell In ActiveSheet.UsedRange
  xCell = Replace(xCell, "0", "")
   Next
End Sub

since you ask for  "REMOVING ZERO IN EVERY CELL"
1500   will be 15
2100500  will be 215
and so on




On Sun, Apr 3, 2011 at 9:55 PM, bhanu prakash  wrote:

> Hi - need it help excel. Removing zeros in alpha numeric data, ex ,,07855a
> need help in removing zeros in every cell. Regards ,- bhanu
>

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

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

Re: $$Excel-Macros$$ remove empty cells

[STDEV(i)]

"remove" blank ROWS
1. type  202 in cell A145
2. select range A3:K145
3. in the Standar Toolbar > click Icon Sort Ascending


Change figures to positif numbers
use function ABS function
for example   in cell  N3  put this formula
=ABS(F3)
you can copy N3 and PasteSpecial > Value to F3 again



On Sat, Apr 2, 2011 at 2:31 PM, Nandkumar kakvipure
 wrote:
> hello exerts,
> plzz help me remove empty cell and convert minus figer to plus and tell me
> step
>
> thanks in advance
> nandkumar

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

[STDEV(i)]

CompareWorksheetsAdd-in.xla  !!!
It is a usefull tool / add-in ..

Thank you Mr Ashish

best regards,
STDEV(i)



On Fri, Apr 1, 2011 at 12:21 AM, ashish koul  wrote:

> try this
>
> On Thu, Mar 31, 2011 at 8:37 PM, hanumant shinde <
> hanumant_5...@yahoo.co.in> wrote:
>
>>  Hi Guys,
>> is it a way where i can compare 2 sheets. lets say sheet1 and sheet2.
>>
>

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

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

Re: $$Excel-Macros$$ Help : Formula Required

[STDEV(i)]

so... make it simplest

=IF((B2>=7/24)*(B2<=*21*/24),"NO","YES")


On Thu, Mar 31, 2011 at 12:23 AM, JsinSk  wrote:

> On my response I screwed up the 24 hour time format! Should be 21:00
> instead of 17:00.
>
> On Mar 30, 1:17 pm, "STDEV(i)"  wrote:
> > Try this formula
> >
> > *=IF((B2>=7/24)*(B2<=9/24),"NO","YES")*
> >
> > On Wed, Mar 30, 2011 at 6:53 PM,  wrote:
> > > Hi All,
> >
> > > I need formula to update column C,  value if the time is between 7:00
> AM to
> > > 9:00 PM "YES" else "NO"
> > > Find attached for the same.
> >
> > > Column AColumn BColumn C
> > > DateTime (MST)  7:00 AM 9:00 PM
> > > 3/28/2011   6:26 AM YES IF LESS THAN 7:00:00 AM THEN
> YES
> > > 3/28/2011   6:47 AM YES IF GREATER THAN 9:00:00 PM THEN
> YES
> > > 3/28/2011   6:51 AM YES Between 7:00 Am to 9:00 Pm,
> Cell
> > > value "NO"
> > > 3/28/2011   6:59 AM YES
> > > 3/28/2011   7:13 AM NO
> > > 3/28/2011   7:32 AM NO
> > > 3/28/2011   7:45 AM NO
> > > 3/28/2011   7:46 AM NO
> >
> > > Thanks,
> > > Mayur
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ Two different work book in one Sheet

[STDEV(i)]

in excel 2003
use  menu:  Window  > New Window
and then:  menu Window  > Arrange


On Wed, Mar 30, 2011 at 12:54 PM, manhar prajapati <
prajapati.man...@gmail.com> wrote:

> Hi expert
> I have a software which show two diff. work sheet in one excel under
> different tab.
> If any one know that software please mail me the name.
> Regards
> Manhar Prajapati
>
>

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

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

Re: $$Excel-Macros$$ Help : Formula Required

[STDEV(i)]

Try this formula

=IF((B2>=7/24)*(B2<=9/24),"NO","YES")



On Wed, Mar 30, 2011 at 6:53 PM,  wrote:

> Hi All,
>
> I need formula to update column C,  value if the time is between 7:00 AM to
> 9:00 PM "YES" else "NO"
> Find attached for the same.
>
> Column AColumn BColumn C
> DateTime (MST)  7:00 AM 9:00 PM
> 3/28/2011   6:26 AM YES IF LESS THAN 7:00:00 AM THEN YES
> 3/28/2011   6:47 AM YES IF GREATER THAN 9:00:00 PM THEN YES
> 3/28/2011   6:51 AM YES Between 7:00 Am to 9:00 Pm, Cell
> value "NO"
> 3/28/2011   6:59 AM YES
> 3/28/2011   7:13 AM NO
> 3/28/2011   7:32 AM NO
> 3/28/2011   7:45 AM NO
> 3/28/2011   7:46 AM NO
>
> Thanks,
> Mayur
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ Calculating An Average Score from Multiple Tabs

[STDEV(i)]

assuming your data (one column range)  are in  A1:A100
and there are some 0 (blank / zero) value in these column..

try this *Array Formula*

=MIN(IF(A1:A100<>0,A1:A100))




On Wed, Mar 30, 2011 at 3:39 AM, John A. Smith wrote:

> Thank you very much.  It works great.  One other question please; I have a
> column with all formula's and I am looking for the smallest number in it by
> using =MIN(range).  I get 0 (zero) because the formula is
> =IF(DY9="GONE","",D9).  DY9 is an automatic status column that shows "GONE"
> if a ship date is filled out in a previous column.  D9 is the value of the
> shipment yet to ship.  =MAX(range) works but it senses the zero if it is
> gone so the =MIN(range) doesn't.
>
> Thank you for your kind teaching help.
>
> John
>
> On Tue, Mar 29, 2011 at 4:17 PM, STDEV(i) wrote:
>
>> Please try, and check if it helps
>>
>>  =IF(COUNT('Dept A:Dept C'!B3)=0,"",AVERAGE('Dept A:Dept C'!B3))
>>
>>

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

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

Re: $$Excel-Macros$$ Help in formula to find the top 10 values

[STDEV(i)]

your data in B columns are TEXT data, not time data /  numbers data
so you can pass into LARGE function

please try this array formula

=TEXT(LARGE(TIMEVALUE(RIGHT(B2:B886,8)),ROW(1:10)),"[hhh]:mm:ss")
array formula written on one_column (10 cells) at once !!


On Mon, Mar 28, 2011 at 5:59 PM, Bhushan Sabbani  wrote:
> Dear All,
> I am facing the problem in the formula of the large to find the top 10.
> Here i am attaching the sample file for the reference.
> Thanks in advance.
> Warm Regards
> Bhushan Sabbani

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

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

Re: $$Excel-Macros$$ How to convert a (blood pressure number 120/80) to a format that will allow averaging of total amount of blood pressure numbers

[STDEV(i)]

please try this Array formula
=ROUND(AVERAGE(--LEFT(A1:A3,3)),0)&"/"&ROUND(AVERAGE(--RIGHT(A1:A3,2)),0)

Array Formula needs to be entered with 3 keys :  Ctrl + Shift,  Enter !


On Mon, Mar 28, 2011 at 12:28 AM, Susan  wrote:

> Hi experts!
>
>
>
> I have a project to do given to me by a doctor that has a series of blood
> pressure results (numbers) from various patients.  And would like me to give
> him the average blood pressure result.
>
>
>
> How can I get Excel 2007 to convert the 120/80 into a format that will
> allow me to find averages, without breaking 120/80 into two different cells.
>
>
>
> My example file would look like this:
>
>
>
> In column A
>
> A1120/80
>
> A2119/79
>
> A3125/60
>
>
>
> Average of A1:A2:A3 = 121/73
>
>
>
> Thanking you much!
>
>
>

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

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

Re: $$Excel-Macros$$ Re: Identify the room status by color code

[STDEV(i)]

please find attached
and see if it helps

best regards
siti Vi


On Sat, Mar 26, 2011 at 9:15 PM, Rajesh K R wrote:

> On 3/26/11, Rajesh K R  wrote:
> > Hi Experts
> >
> >  How to understand the room status, by color code.pls check the
> attachment.
> >
> > Regards
> >
> > Rajesh Kainikkara
> >
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ Identify the room status by color code

[STDEV(i)]

no attachments in your mail/posting

just for info
Color Index can be detected by
*(1) making and UDF (user Definded Function)*

 Public Function ColorIndex(Rng as range) as long
ColorIndex = Rng.Interior.ColorIndex
 End Function

 in worksheet you just use this NEW FUNCTION
  =ColorIndex(B5)

another way
*(2) By Define Name using Macro Excel-4 function*

Please check in the attached workbook, hope it helps






On Sat, Mar 26, 2011 at 10:39 PM, Rajesh K R wrote:

> attachment is added now
>
> On 3/26/11, vijayajith VA  wrote:
> > Hai
> > attachment is missing
> > thanks
> >
> > On Sat, Mar 26, 2011 at 7:43 PM, Rajesh K R
> > wrote:
> >
> >> Hi Experts
> >>  How to understand the room status, by color code.pls check the
> >> attachment.
> >> Regards
> >> Rajesh Kainikkara
>

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

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


ctv_ColorIndex detector.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Select numbers date wise using a formula

[STDEV(i)]

please visit :

http://www.cpearson.com/excel/ArrayFormulas.aspx

<http://www.cpearson.com/excel/ArrayFormulas.aspx>
http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx

<http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx>

On Sat, Mar 26, 2011 at 9:18 PM, Rajesh K R wrote:

> Hi Dev
> Thank u very much for your quick reply, unfortunately I don't have any
> idea about array formulas.
> how can I learn it properly.
>
> Regards
>
> Rajesh Kainikkara
>
> On 3/25/11, STDEV(i)  wrote:
> > 1.
> > formula HELPER
> > ( creating list of RowIndex )
> > array formula in multi cells
> > =IF(ISNUMBER($A5),OFFSET(BILL!$D$1,$A5,0),"")
> >
> > 2
> > lookup the table based on RowIndex
> > individual formula in a cell
> > =IF(ISNUMBER($A5),OFFSET(BILL!$D$1,$A5,0),"")
> >
> > On Fri, Mar 25, 2011 at 7:40 PM, Rajesh K R
> > wrote:
> >
> >> Hi,
> >> How can I select bill numbers of a particular date and show it in
> >> ascending order using a formula.Kindly check the attachment .
> >> Regards
> >> Rajesh Kainikkara
>

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

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

Re: RE: $$Excel-Macros$$ discussexcel : Macro: Column Number to Alphabetical reference

[STDEV(i)]

Thank you Mr. Ayush..
Thank you Mr. Bonallack

Best Regards
siti



On Sat, Mar 26, 2011 at 9:43 PM, Ayush  wrote:

> Really Awesome formula.
>
> Thanks Siti Vi. I have published this formula on the same page along with
> your name.
>
>
> Regards
> Ayush Jain
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ discussexcel : Macro: Column Number to Alphabetical reference

[STDEV(i)]

*=SUBSTITUTE(ADDRESS(1,A1,4),1,"")*
*
*
*=SUBSTITUTE(ADDRESS(1,16384,4),1,"")*



On Fri, Mar 25, 2011 at 10:40 PM, Ayush  wrote:

> Hello everyone,
>
> Here is the UDF to convert a number into Alphabetical reference. If you
> pass 1 to the function, it will Return A. If you pass 26, It will return Z.
> If you pass 27 It will return AAand so on.
> http://www.discussexcel.com/learn-macros/vba-macros
>
> Let me know if you have better solution.
>
> Thanks and best regards
> Ayush Jain
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ discussexcel : Macro: Column Number to Alphabetical reference

[STDEV(i)]

Please try my UDF for the same

Public Function ColumnLetter(N As Long) As String
ColumnLetter = Replace(Replace(Cells(1, N).Address, "$", ""), 1, "")
End Function


On Fri, Mar 25, 2011 at 10:40 PM, Ayush  wrote:

> Hello everyone,
>
> Here is the UDF to convert a number into Alphabetical reference. If you
> pass 1 to the function, it will Return A. If you pass 26, It will return Z.
> If you pass 27 It will return AAand so on.
> http://www.discussexcel.com/learn-macros/vba-macros
>
> Let me know if you have better solution.
>
> Thanks and best regards
> Ayush Jain
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ Find all NamedRanges in Worksheet/workbook

[STDEV(i)]

Dim nm as name, r as long
For each nm in thisworkbook.names
r = r + 1
Cells(r, 1) = nm.Name
Cells(r, 2) = nm.address(false, false)
Next



On 3/18/11,  wrote:
> Hi friends,
>
> i want to find out all the namedranges from worksheet / workbook. can
> somebody
> please help me.
>
> eg. lets say A1 to A4 name of the range is range1
> lets say B1 to B4 name of the range is range2
> lets say C1 to C4 name of the range is range3
> lets say D1 to D4 name of the range is range4
>
> NamedRange may have any name and it wil refer to any range. there is no fix 
> format.
> looking for reply
>
> --

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

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

Re: $$Excel-Macros$$ Help on TL Recital Snapshot

[STDEV(i)]

an *array formula*  =IFERROR(INDIRECT(C3)," ") is written in a range  C5:H20
at once !!

about array formula:
http://www.cpearson.com/excel/ArrayFormulas.aspx
http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx

but before you do it, you have to  define some NAME in sheet GROUP, *
manually*
e.g.:
range  B3:G17as  SHARIFA
range  B18:G16  as NEELAM
and so on



On Mon, Mar 21, 2011 at 6:39 AM, Intelligent But Crazy  wrote:

> Hi Noorain,
>
> This is a Very good Solution. But i am unable to edit this array.
> can you pls tell me if i want to use this same concept of array in any
> other file so how can do this.
>
> please give me some more detail about using array formula in excel.
>
> On Fri, Mar 18, 2011 at 10:51 AM, NOORAIN ANSARI  > wrote:
>
>> Dear Lucky,
>> Please see attached sheet..
>> Rgds,
>> Noorain Ansari
>>
>>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ Round the decimal part of the numbers .5 or 1

[STDEV(i)]

=INT(I3)+CHOOSE(MATCH(I3-INT(I3),{0,0.32999,0.82999},1),0,0.5,1)

or
=IF(ROUND(MOD(I3,1),2)>=0.83,ROUNDUP(I3,0),IF(ROUND(MOD(I3,1),2)>=0.33,TRUNC(I3)+0.5,TRUNC(I3)))

and more..



On Fri, Mar 18, 2011 at 10:42 PM, Rajesh K R wrote:
>
> I want to round the value 16.33 as 16.5 and 16.83 as 17 how can I do
> it with a formula.
> Regards
> Rajesh kainikkara
>
>

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

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


ctv_Special_Rounding.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Remove text from a string

[STDEV(i)]

=LEFT(A1,FIND("@",A1,FIND("@",A1)+1)-1)
or
=MID(A2,1,FIND("@",A2,FIND("@",A2)+1)-1)
of
array formula
=LEFT(A3,MAX((MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)="@")*ROW(INDIRECT("1:"&LEN(A3-1)


On Fri, Mar 18, 2011 at 3:16 PM, Deepak Rawat wrote:

> Hi Masters,
>
> My query is to remove all the characters after the last "@" from the text.
>
> Example:
> The Raw test is
> deepak@gmail.comkaml...@yahoo.com
>
> The final text i want is deepak@gmail.comkamlesh
>
> Regards,
> Deepak Rawat
>
>

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

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

Re: $$Excel-Macros$$ code explanation please

[STDEV(i)]

excel is opening a text File
the array are data for  column index and length of string string to be
converted to a cells

Array(Array(1, 2), Array(2, 4))

text file contents:  "ABCDEFG"

converted to 2 columns
column 1  = "AB"
column 2 = "CDEF"

If you use [Text To Column] ( of DATA menu) you will understand this code.
CMIIW



On Wed, Mar 16, 2011 at 12:04 AM, Skanda  wrote:

> What does the following code do?especially the array part!
>
> Application.StatusBar = "Please Wait.Loading " & Trim(Prod) & " Data
> File"
> Workbooks.OpenText FileName:=FileLoc, Origin _
> :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
> xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
> Semicolon:=False, _
> Comma:=False, Space:=False, Other:=True, OtherChar:="~",
> FieldInfo:= _
> Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5,
> 2), Array(6, 2), _
>   Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2),
> Array(11, 2), Array(12, 2), _
>   Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1),
> Array(17, 1), Array(18, 1), _
>   Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1),
> Array(23, 1), Array(24, 1), _
>   Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1),
> Array(29, 1), Array(30, 1), _
>   Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1),
> Array(35, 1), Array(36, 1), _
>   Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1),
> Array(41, 1), _
>   Array(52, 2))
>
>

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

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

Re: $$Excel-Macros$$ How to short repeated name

[STDEV(i)]

you are talking about List of Unique Values

in Excel 2007 you can use "REMOVE DUPLICATE"
in Excel 2003 use : Advanced Filter  (Copy to another range / UniqRecords
Only / Criteria = blank)

or you can use a mega-formula  see my example workbook at
http://www.box.net/shared/o614r2gnrj



On Tue, Mar 15, 2011 at 7:20 PM, sudhir kumar  wrote:

> Dear Experts
> i have list of reapted name. and i want a list which have name only one
> time
> if any new name add in list then it will add name.
>
> example
>
> List of Repeated name:
>
> A
> B
> H
> E
> A
> H
> A
> H
> I
> A
> B
> I
>
> List should be this type:
>  A
> B
> H
> E
> I
>
> thanking 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$$ how to increse rows in excel 2007 from 65 k to more

[STDEV(i)]

Yes you are right
For some reason, my XL default format = "*.xls"

Thank you Mr Schreiner.

[image: XL default format.GIF]



On Tue, Mar 15, 2011 at 7:26 PM, Paul Schreiner wrote:

> "Technically" if your Excel 2007 Default Save format is "Excel97-2003"
> format,
> then what you said is true: a new workbook opens in "compatibility mode".
>
> If your default format is .xlsx, .xlsm, or .xlsb
> then a new workbook will have 1,048,576 rows and  16,384 columns
> (even before saving!)
>
> Paul
>
> *From:* STDEV(i) 
> *To:* excel-macros@googlegroups.com
> *Sent:* Tue, March 15, 2011 3:31:24 AM
> *Subject:* Re: $$Excel-Macros$$ how to increse rows in excel 2007 from 65
> k to more
> if you open a blank workbook in excell 2007
> the workbook is in compatibility mode
> save your  workbook in *.XLSX format
> close your workbook,   and open again
>
>

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

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

Re: $$Excel-Macros$$ how to increse rows in excel 2007 from 65 k to more

[STDEV(i)]

if you open a blank workbook in excell 2007
the workbook is in compatibility mode

save your  workbook in *.XLSX format
close your workbook,   and open again
ta da


On Mon, Mar 7, 2011 at 5:36 PM, dpk  wrote:

> how to increse rows in excel 2007 from 65 k to more
>
>

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

[STDEV(i)]

in the google search box, type "sumproduct"


On Mon, Mar 14, 2011 at 2:50 PM, sudhir kumar  wrote:

> Hi
> Dear Members
> can any one sent me example of Sumproducts.
> thanking u
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ formula to add date in next column

[STDEV(i)]

let us be very patient, & waiting for some one who
give a formula that can write a Constat_Date into a cell


On Mon, Mar 14, 2011 at 1:06 AM, Hems  wrote:

> Hey STDEV(i),
>
> Thanks a lot but i dont want macro, i want formula for it.
>
> Let me know if is there any formula for this.
>
> Regards,
> Hemant
>
> On Sun, Mar 13, 2011 at 1:04 PM, STDEV(i) wrote:
>
>> put this code into sheets' module
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>>' siti Vi >
>>' jakarta, 11 mar 2011
>>'
>>If Target.Count = 1 Then
>>   If Target.Column = 1 Then
>>  If Target.Row > 1 Then
>> If Len(Target) > 0 Then
>>Target(1, 2) = Date
>> End If
>>  End If
>>   End If
>>End If
>> End Sub
>>
>>
>>
>> On Sun, Mar 13, 2011 at 1:53 AM, Hems  wrote:
>>
>>> Dear All,
>>>
>>>  I want today's date in next column (column B) on the day when first
>>> column ( column A) was updated.
>>> for Ex. if A3 was updated on 14th Mar'11 then B3 should display that
>>> date. If i open this file on 16th Mar'11 then also B3 should display 14th
>>> Mar'11 and not 16th Mar'11.
>>>
>>> Appreciate i get formula for this issue rather then macro.
>>>
>>>
>>> Thanks in advance
>>>
>>> Kind Regards,
>>> Hemant
>>
>>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post 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$$ formula to add date in next column

[STDEV(i)]

put this code into sheets' module

Private Sub Worksheet_Change(ByVal Target As Range)
   ' siti Vi 
   ' jakarta, 11 mar 2011
   '
   If Target.Count = 1 Then
  If Target.Column = 1 Then
 If Target.Row > 1 Then
If Len(Target) > 0 Then
   Target(1, 2) = Date
End If
 End If
  End If
   End If
End Sub



On Sun, Mar 13, 2011 at 1:53 AM, Hems  wrote:

> Dear All,
>
>  I want today's date in next column (column B) on the day when first column
> ( column A) was updated.
> for Ex. if A3 was updated on 14th Mar'11 then B3 should display that date.
> If i open this file on 16th Mar'11 then also B3 should display 14th Mar'11
> and not 16th Mar'11.
>
> Appreciate i get formula for this issue rather then macro.
>
>
> Thanks in advance
>
> Kind Regards,
> Hemant

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

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


ctv_auto date in column B.xls
Description: MS-Excel spreadsheet