date:20110708

Re: $$Excel-Macros$$ Vlookup Query

2011-07-08 Thread ashish koul

check the attachment see if it helps


On Sat, Jul 9, 2011 at 9:56 AM, PRADEEP KUMAR KOKNE <
pradeepkumar...@gmail.com> wrote:

> Base file: this file has 3 columns which has
>
>
> CustomerLE  Cost Center
> AUG0001 SDFV445858
> USA0001 JHNE652147
> ZMA0001 NMVE321745
>
> Actual file: I have same 3 columns, I want a macro which has to
> CONCATENATE all the 3 columns and do a vlookup to check the
> combination, if the combination is invalid it has to show an error
> message
>
> CustomerLE  Cost Center Errrs
> AUG0001 SDFV527850  error message; combination is invalid
> USA0001 JHNE652147
> ZMA0001 NMVE321745
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
http://akoul.posterous.com/
*akoul*.wordpress.com 
My Linkedin Profile 


P Before printing, think about the environment.

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

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


lookup value error.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

FW: $$Excel-Macros$$

2011-07-08 Thread Azeem Quraishi (HAB5)

Dear Rajan_Verma,
 

Its working properly , Thank you very much for supporting me.

 

 

Best Regards

 

 

Syed Azeem Quraishi

Engineering Dept. H5-Project,UAE

 

P Before printing, think about the environment.

 

From: excel-macros@googlegroups.com
[mailto:excel-macros@googlegroups.com] On Behalf Of Rajan_Verma
Sent: Wednesday, July 06, 2011 11:12 PM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$

 

Try this

 

Sub PrintPerpage()

Dim rng As Range

Dim cell As Range

Set rng = Sheets("OD_New").Range("N5:N23")

 

For Each cell In rng

Range("G4").Value = cell.Value

Range("G5").Value = cell.Offset(0, 1).Value

ActiveWindow.SelectedSheets.PrintOut Copies:=1

Next



End Sub

 

 

From: excel-macros@googlegroups.com
[mailto:excel-macros@googlegroups.com] On Behalf Of Azeem Quraishi
(HAB5)
Sent: Tuesday, July 05, 2011 7:51 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ 

 

Hi All

 

 

I have excel file which I attached I am not able to create macro because
I need to print each test no each page manually taking more time please 

I need  any macro  and any method easy which is I can print at a time
all test number per printout separate.

 

 

Appreciate your support in advance.

 

 

Best Regards

 

 

Syed Azeem Quraishi

 

-- 

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

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

-- 

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

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

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

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

Re: $$Excel-Macros$$ vlookup query

2011-07-08 Thread Venkatesh Srinivas

please shoot your query

On Sat, Jul 9, 2011 at 9:43 AM, PRADEEP KUMAR KOKNE <
pradeepkumar...@gmail.com> wrote:

> I have a some query regarding the vlookup can somebody help
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 

Regards
Venkatesh KS
Phone: +91 98419 69353

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

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

$$Excel-Macros$$ Vlookup Query

2011-07-08 Thread PRADEEP KUMAR KOKNE

Base file: this file has 3 columns which has


CustomerLE  Cost Center
AUG0001 SDFV445858
USA0001 JHNE652147
ZMA0001 NMVE321745

Actual file: I have same 3 columns, I want a macro which has to
CONCATENATE all the 3 columns and do a vlookup to check the
combination, if the combination is invalid it has to show an error
message

CustomerLE  Cost Center Errrs
AUG0001 SDFV527850  error message; combination is invalid
USA0001 JHNE652147
ZMA0001 NMVE321745

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

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

$$Excel-Macros$$ vlookup query

2011-07-08 Thread PRADEEP KUMAR KOKNE

I have a some query regarding the vlookup can somebody 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$$ Numbering

2011-07-08 Thread Haseeb Avarakkan

A2, copy down as many needed

=IF(ROWS(A$2:A2)<=A$1,ROWS(A$2:A2),"")

Or, If you are inserting rows between A2 & the last rows this will not work 
as expected, so use this one;

=IF(COUNT(A$1:A1)<=A$1,COUNT(A$1:A1),"")

copy down as many needed

HTH
Haseeb

-- 
--
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 Help Help.....Putting value in user from by loop

2011-07-08 Thread shariq khan

Daneil, that is what i want but the same code is not working.in my
sheet.

what is Me.contol ?

On Fri, Jul 8, 2011 at 10:22 PM, Daniel  wrote:

>  Give a try at the attached file.
>
> ** **
>
> Daniel
>
> ** **
>
> *De :* excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> *De la part de* shariq khan
> *Envoyé :* vendredi 8 juillet 2011 18:17
>
> *À :* excel-macros@googlegroups.com
> *Objet :* Re: $$Excel-Macros$$ Need Help Help Help.Putting value in
> user from by loop
>
>   ** **
>
> Daneil,
>
>  
>
> This one "txtbox_val = Me.Controls("TB" & num).Value".
>
>  
>
> This is not on the worksheet, i'm using one userform and on which i have
> many texboxes.. but i'm not able to put the value in by using loop, once
> this is done then i will link it to the sheet values. 
>
>  
>
> The main fight it how to dynamicly refer TB1,TB2,TB3..TB22
>
>  
>
> I really need to do thought loop as many time i have to disable it, enable
> it clear the value of the textbox and putting values into the text box..**
> **
>
>  
>
>
>  
>
> On Fri, Jul 8, 2011 at 6:05 PM, Daniel  wrote:
>
> Didn’t you say the textboxes were in a userform ? if they are on a
> worksheet, the syntax should be :
>
>  
>
> With ActiveSheet
>
> For i = 1 To 22
>
> txtbox_val  = .OLEObjects("TB" & i).Object.Value
>
> Next i
>
> End With
>
>  
>
> Daniel
>
>  
>
> *De :* excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> *De la part de* shariq khan
> *Envoyé :* vendredi 8 juillet 2011 13:11 
>
>
> *À :* excel-macros@googlegroups.com
>
> *Objet :* Re: $$Excel-Macros$$ Need Help Help Help.Putting value in
> user from by loop 
>
>  
>
> Hi Daneil,
>
>  
>
> Thanks but it didnt worked..
>
>  
>
> Run-time error '-2147024809
>
> Could not find the specified object
>
>  
>
> Thanks,
>
> Shariq
>
> On Fri, Jul 8, 2011 at 11:12 AM, Daniel  wrote:
>
> Hi,
>
>  
>
> Try :
>
>  
>
> For num = 1 To 22
>
> txtbox_val = Me.Controls("TB" & num).Value
>
> txtbox_num = num
>
> Next num
>
>  
>
> Regards.
>
> Daniel
>
>  
>
> *De :* excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> *De la part de* shariq khan
> *Envoyé :* vendredi 8 juillet 2011 11:00
> *À :* excel-macros@googlegroups.com
> *Objet :* $$Excel-Macros$$ Need Help Help Help.Putting value in user
> from by loop
>
>  
>
> I’m trying to assign the value in excel user from thought loop.
>
>  
>
> Name of the textboxes are TB1,TB2,TB3…….. TB22  .
>
>  
>
> Below is the code which I’m using ,but its not working. 
>
>  
>
> For num = 1 To 22
>
> txtbox_val = "TB" & num & ".value"
>
> txtbox_val = num
>
> End If
>
>   Next num
>
>  
>
>  
>
> The same thing I wanted to do with the label and the names of the label are
> L1,L2,L3……L22
>
>  
>
> Please help me on this.
>
>  
>
>   
>
>
>
> --
> Thanks and Reagrd’s
> Shariq
>
>
>  
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
>
>
>
> --
> Thanks and Reagrd’s
> Shariq Shazad Khan
> 066979,9891184784
>
> --
>
> --
> 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 tutoria

Re: $$Excel-Macros$$ Numbering

2011-07-08 Thread santosh bahuguna

and if you want opposite
then use

=COUNTIF(A1:A100,A1)

then it will be
A1 : 100 result be will B1 : 100
A2 : 100  B2 : 99
A3 : 100  B3 : 98
A4 : 100  B4 : 97
A5 : 100  B5 : 96
A6 : 100  B6 : 95

Regards
Santosh Bahuguna




On Fri, Jul 8, 2011 at 3:29 PM, santosh bahuguna
wrote:

> Use Simple formula
>
> =COUNTIF(A$1:A1,A1)
>
>
> Please let me know if you looking for this only
>
>
> Regards
> Santosh Bahuguna
>
>
>
> On Fri, Jul 8, 2011 at 8:33 AM, Rajan_Verma wrote:
>
>>  *Hi
>> Try this*
>>
>> * *
>>
>> *Private Sub Worksheet_Change(ByVal Target As Range)*
>>
>> *On Error GoTo Err:*
>>
>> *If Target = Range("A1") Then*
>>
>> *For i = 1 To Range("A1").Value*
>>
>> *Range("A" & i + 1).Value = i*
>>
>> *Next*
>>
>> *End If*
>>
>> *Err:*
>>
>> *End Sub*
>>
>> * *
>>
>> *From:* excel-macros@googlegroups.com [mailto:
>> excel-macros@googlegroups.com] *On Behalf Of *NOORAIN ANSARI
>> *Sent:* Friday, July 08, 2011 5:38 PM
>> *To:* excel-macros@googlegroups.com
>> *Cc:* Kal xcel
>> *Subject:* Re: $$Excel-Macros$$ Numbering
>>
>> ** **
>>
>> kalyan babu.
>>
>> Please try it..also
>>
>>  
>>
>> =IF(A2="","",COUNTA($A$2:A2))
>>
>> On Fri, Jul 8, 2011 at 4:35 PM, Kal xcel  wrote:
>>
>> Dear Experts,
>>
>>  
>>
>> I want the formula for below mention query...
>>
>>  
>>
>> user will put 100 in A1, from A2 autometically numbering will start like
>> this
>>
>>  
>>
>> A21
>>
>> A32
>>
>> A43
>>
>> .
>>
>> .
>>
>> .
>>
>> .
>>
>>  
>>
>> till 100
>>
>>  
>>
>> What will be the formula??
>>
>>  
>>
>> Please help
>>
>>  
>>
>> Thanks in advance
>>
>>  
>>
>> Kalyan Chatterjee
>>
>>  
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>>
>>
>>
>> -- 
>>
>> Thanks & regards,
>>
>> Noorain Ansari
>>
>> *http://noorain-ansari.blogspot.com/*
>> 
>>
>> ** **
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>> --
>>
>> --
>> 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$$ Create a drop down calendar in consecuative cells in a Excel column

2011-07-08 Thread Scholten, George FLNR:EX

Thanks very much, will give it a try

George

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On 
Behalf Of Rajan_Verma
Sent: Friday, July 8, 2011 12:04 PM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Create a drop down calendar in consecuative cells 
in a Excel column

Hi
I have a Solution For You, Hope it will Help you., Follow the instruction :-

1). Insert a MicroSoft Date and Time Picker Control 6.0  On your Worksheet
2). Fit that Object in a Cell "D1"
3) Drag this Cell to Down
4) Run This Code

Sub MakeEventByCode()
Dim sh As Worksheet
Set sh = ActiveSheet
Dim wb As Workbook
Set wb = ActiveWorkbook

For i = 1 To sh.OLEObjects.Count
Set sh = ActiveSheet
wb.VBProject.VBComponents(sh.Name).CodeModule.AddFromString
("'Private Sub DTPicker" & i & "_Change()")
wb.VBProject.VBComponents(sh.Name).CodeModule.AddFromString
("Range(""A" & i & """)" & ".Value = Me.DTPicker" & i & ".Value")
wb.VBProject.VBComponents(sh.Name).CodeModule.AddFromString
("End Sub")
Next
n = 0
For t = 1 To
ThisWorkbook.VBProject.VBComponents(sh.Name).CodeModule.CountOfLines Step 3
n = n + 1
 
wb.VBProject.VBComponents(sh.Name).CodeModule.ReplaceLine t, "Private Sub
DTPicker" & n & "_Change()"

Next
End Sub



-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of George1960
Sent: Friday, July 08, 2011 10:12 PM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Create a drop down calendar in consecuative cells
in a Excel column

Hi
I am trying to insert a separate drop down calendar into consecutive
cells in a column, so as to reference a different date with the data
on each row in a Excel 2007 spread sheet, (the rows of data reflect
people and their activities for the day)
I found out how to insert a single calendar with the output linked to
a referenced cell e.g. $B$3 but not how to do this for consecutive
cells in the column.

Thanks
George

-- 

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

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

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

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

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

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

Re: $$Excel-Macros$$ Help Required Urgent :)

2011-07-08 Thread ICWAI Help

Thanks a lot Ashish its working perfectly

Regards,
Rakesh.

On Fri, Jul 8, 2011 at 10:54 AM, ashish koul  wrote:

> add this code to new workbook and run the macro and choose the csv file
>
> Option Compare Text
>
>
>
> Sub swa()
>
> Application.ScreenUpdating = False
> Application.DisplayAlerts = False
> Dim s As Workbook
> Dim i, j, k As Long
> Set s = Workbooks.Open(Application.GetOpenFilename)
> j = 1
> For i = 1 To s.Sheets(1).Range("a1048576").End(xlUp).Row
>
> s.Activate
> If s.Sheets(1).Cells(i, 1).Value = "TITLERIGHT" Then
> k = i
> s.Sheets(1).Rows(j & ":" & k).Copy
> ThisWorkbook.Activate
>  ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
> ActiveSheet.Range("a1").Select
>
> ActiveSheet.Paste
> j = i
>
> i = i + 1
>
> End If
>
>
> Next i
> ThisWorkbook.Sheets(4).Activate
>
> Application.ScreenUpdating = True
> Application.DisplayAlerts = True
>
> End Sub
>
>
> On Fri, Jul 8, 2011 at 3:49 AM, ICWAI Help wrote:
>
>> Hi Ashish,
>>
>> i have date in sheet its huge date i want the date between The row
>> highlighted *TITLE RIGHT should copy into new sheet*
>> *like *
>> **
>> *1, first select hte data between Two Title right and paste into new
>> sheet.*
>> *total we need to create 80 sheets as i have huge data.*
>> **
>> **
>> *Thanks,*
>> *Rakesh.*
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com 
> http://akoul.posterous.com/
> *akoul*.wordpress.com 
> My Linkedin Profile 
>
>
> P Before printing, think about the environment.
>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

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

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

$$Excel-Macros$$ Excel Formula needed-Urgent help

2011-07-08 Thread vinod rao

Hi,
I have put a pivot table and i need to get Gross% starting from 0% and above
data listed in pivot. Can i know how to get in pivot by by adding formula?
My data has both -ve and +v % but what i need is any % with greater than 0%
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

Re: $$Excel-Macros$$ Numbering

2011-07-08 Thread santosh bahuguna

Use Simple formula

=COUNTIF(A$1:A1,A1)


Please let me know if you looking for this only


Regards
Santosh Bahuguna



On Fri, Jul 8, 2011 at 8:33 AM, Rajan_Verma wrote:

>  *Hi
> Try this*
>
> * *
>
> *Private Sub Worksheet_Change(ByVal Target As Range)*
>
> *On Error GoTo Err:*
>
> *If Target = Range("A1") Then*
>
> *For i = 1 To Range("A1").Value*
>
> *Range("A" & i + 1).Value = i*
>
> *Next*
>
> *End If*
>
> *Err:*
>
> *End Sub*
>
> * *
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *NOORAIN ANSARI
> *Sent:* Friday, July 08, 2011 5:38 PM
> *To:* excel-macros@googlegroups.com
> *Cc:* Kal xcel
> *Subject:* Re: $$Excel-Macros$$ Numbering
>
> ** **
>
> kalyan babu.
>
> Please try it..also
>
>  
>
> =IF(A2="","",COUNTA($A$2:A2))
>
> On Fri, Jul 8, 2011 at 4:35 PM, Kal xcel  wrote:
>
> Dear Experts,
>
>  
>
> I want the formula for below mention query...
>
>  
>
> user will put 100 in A1, from A2 autometically numbering will start like
> this
>
>  
>
> A21
>
> A32
>
> A43
>
> .
>
> .
>
> .
>
> .
>
>  
>
> till 100
>
>  
>
> What will be the formula??
>
>  
>
> Please help
>
>  
>
> Thanks in advance
>
>  
>
> Kalyan Chatterjee
>
>  
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
>
>
>
> -- 
>
> Thanks & regards,
>
> Noorain Ansari
>
> *http://noorain-ansari.blogspot.com/*
> 
>
> ** **
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
> --
>
> --
> 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$$ solution to put data automtically

2011-07-08 Thread karunanithi ramaswamy

Hi,
 here is a macro u can apply which works well.
Sub test32()
Dim LastRow As Long
Dim j As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).row
k = LastRow
For j = k - 1 To 4 Step -1
    If Worksheets("Sheet1").Cells(k, 1).Value = 
Worksheets("Sheet1").Cells(j, 1).Value Then
   Worksheets("Sheet1").Cells(k, 3).Value = 
Worksheets("Sheet1").Cells(j, 5).Value
   Worksheets("Sheet1").Cells(k, 4).Value = 
Worksheets("Sheet1").Cells(j, 6).Value
   Exit For
   
   
    End If
 Next j
  
End Sub

.r.karunanithi.

--- On Tue, 6/21/11, Subhash Yadav  wrote:

From: Subhash Yadav 
Subject: Re: $$Excel-Macros$$ solution to put data automtically
To: excel-macros@googlegroups.com
Date: Tuesday, June 21, 2011, 8:35 AM

Thanks a lot my friend


 
On Wed, Jun 8, 2011 at 10:01 PM, ashish koul  wrote:

issue = INDEX(E:E,SUMPRODUCT(MAX(($A$4:A10=A11)*ROW($A$4:A10))),0)

reading  =INDEX(F:F,SUMPRODUCT(MAX(($A$4:A10=A11)*ROW($A$4:A10))),0) 





On Wed, Jun 8, 2011 at 7:03 PM, Subhash Yadav  wrote:


Dear friends,
 
I am having one sheet containing data for fuel issue.
 
I want, if i put any asset code previous issue and previous reading should come 
from the latest present issue and present reading of same asset.

 
In the attached sheet yellow cell date should come in yellow cell and magenta 
cell data should come in magenta cell.
 
Please help me out.
 

-- 
Subhash Chand Yadav

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

2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com

4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com


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

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



-- 


Regards
 
Ashish Koul
akoul.blogspot.com
http://akoul.posterous.com/


akoul.wordpress.com
My Linkedin Profile
 

P Before printing, think about the environment.
 




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

2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com

4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com


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

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



-- 

Subhash Chand Yadav




-- 

--

Some important links for excel users:

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

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

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

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

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

 

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

 

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

Like our page on facebook , Just follow below link

http://www.facebook.com/discussexcel

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

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


SAMPLE FILE-rk.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

Re: $$Excel-Macros$$ Ashish Koul : Most Helpful Member - June'11

2011-07-08 Thread ICWAI Help

Hey Ashish heartly Congrats To you .. :)
Thansk for everything Dear.

On Tue, Jul 5, 2011 at 3:06 PM, simplyali  wrote:

> Thanks Ashish for the Invite.. I have accepted the same...
> I would like to thank you million times for making me aware of this
> post
>
> On Jul 5, 10:29 am, Kal xcel  wrote:
> > Congratulation Ashish...You deserve it
> >
> > Kalyan
> >
> >
> >
> > On Mon, Jul 4, 2011 at 11:17 PM, Ahmed galal 
> wrote:
> > >  [image:
> > >
> http://tracking.technodesignip.com/?action=count&projectid=642&conten..]Congratulations
> > > *Ashish*, you deserve it.[image:
> > >
> http://tracking.technodesignip.com/?action=count&projectid=642&conten.
> .]
> >
> > > Best regards,
> >
> > > *Ahmed galal Mohamed*
> >
> > > Procurement Engineer
> >
> > > Head Office : SQUARE Engineering Firm
> > > Tel   :(202) 2402 8846
> > > Fax  :(202) 2405 0476
> > > Mobile :(010) 9 62 60 61
> > > Website :http://www.square.com.eg
> > > 31 Lebanon St. Mohandsen, Giza, Egypt
> >
> > >  --
> > > From: jainayus...@gmail.com
> > > Date: Mon, 4 Jul 2011 22:48:33 +0530
> > > Subject: $$Excel-Macros$$ Ashish Koul : Most Helpful Member - June'11
> > > To: excel-macros@googlegroups.com
> >
> > >   Hello Everyone,
> >
> > > Ashish Koul has been selected as 'Most Helpful Member' for the month of
> > > June'11
> > > He has posted 53 posts in June 2011 and helped many people through his
> > > expertise. He has been consistent contributor to this excel forum and
> > > achieved this recognition from last seven months consecutively.This is
> > > really awesome. He is awarded Microsoft MVP 2011 award for his
> voluntary
> > > contribution.
> >
> > >Thanks to Rajan, Venkat and other folks for helping excel
> enthusiasts !!
> > > keep it up !!
> >
> > >Let see who becomes next MVP..
> >
> > >Keep posting.
> >
> > >  Regards
> > > Ayush Jain
> > > Group Manager
> >
> > > --
> >
> > >
> ------
> > > Some important links for excel users:
> > > 1. Follow us on TWITTER for tips tricks and links :
> > >http://twitter.com/exceldailytip
> > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
> >
> > > To post to this group, send email to excel-macros@googlegroups.com
> >
> > > <><><><><><><><><><><><><><><><><><><><><><>
> > > Like our page on facebook , Just follow below link
> > >http://www.facebook.com/discussexcel
> >
> > > --
> >
> > >
> ------
> > > Some important links for excel users:
> > > 1. Follow us on TWITTER for tips tricks and links :
> > >http://twitter.com/exceldailytip
> > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
> >
> > > To post to this group, send email to excel-macros@googlegroups.com
> >
> > > <><><><><><><><><><><><><><><><><><><><><><>
> > > Like our page on facebook , Just follow below link
> > >http://www.facebook.com/discussexcel- Hide quoted text -
> >
> > - Show quoted text -
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
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$$ Create a drop down calendar in consecuative cells in a Excel column

2011-07-08 Thread Rajan_Verma

Hi
I have a Solution For You, Hope it will Help you., Follow the instruction :-

1). Insert a MicroSoft Date and Time Picker Control 6.0  On your Worksheet
2). Fit that Object in a Cell "D1"
3) Drag this Cell to Down
4) Run This Code

Sub MakeEventByCode()
Dim sh As Worksheet
Set sh = ActiveSheet
Dim wb As Workbook
Set wb = ActiveWorkbook

For i = 1 To sh.OLEObjects.Count
Set sh = ActiveSheet
wb.VBProject.VBComponents(sh.Name).CodeModule.AddFromString
("'Private Sub DTPicker" & i & "_Change()")
wb.VBProject.VBComponents(sh.Name).CodeModule.AddFromString
("Range(""A" & i & """)" & ".Value = Me.DTPicker" & i & ".Value")
wb.VBProject.VBComponents(sh.Name).CodeModule.AddFromString
("End Sub")
Next
n = 0
For t = 1 To
ThisWorkbook.VBProject.VBComponents(sh.Name).CodeModule.CountOfLines Step 3
n = n + 1
 
wb.VBProject.VBComponents(sh.Name).CodeModule.ReplaceLine t, "Private Sub
DTPicker" & n & "_Change()"

Next
End Sub



-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of George1960
Sent: Friday, July 08, 2011 10:12 PM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Create a drop down calendar in consecuative cells
in a Excel column

Hi
I am trying to insert a separate drop down calendar into consecutive
cells in a column, so as to reference a different date with the data
on each row in a Excel 2007 spread sheet, (the rows of data reflect
people and their activities for the day)
I found out how to insert a single calendar with the output linked to
a referenced cell e.g. $B$3 but not how to do this for consecutive
cells in the column.

Thanks
George

-- 

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

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

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

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

$$Excel-Macros$$ Create a drop down calendar in consecuative cells in a Excel column

2011-07-08 Thread George1960

Hi
I am trying to insert a separate drop down calendar into consecutive
cells in a column, so as to reference a different date with the data
on each row in a Excel 2007 spread sheet, (the rows of data reflect
people and their activities for the day)
I found out how to insert a single calendar with the output linked to
a referenced cell e.g. $B$3 but not how to do this for consecutive
cells in the column.

Thanks
George

-- 
--
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 Help Help.....Putting value in user from by loop

2011-07-08 Thread shariq khan

Daneil,

This one "txtbox_val = Me.Controls("TB" & num).Value".

This is not on the worksheet, i'm using one userform and on which i have
many texboxes.. but i'm not able to put the value in by using loop, once
this is done then i will link it to the sheet values.

The main fight it how to dynamicly refer TB1,TB2,TB3..TB22

I really need to do thought loop as many time i have to disable it, enable
it clear the value of the textbox and putting values into the text box..



On Fri, Jul 8, 2011 at 6:05 PM, Daniel  wrote:

>  Didn’t you say the textboxes were in a userform ? if they are on a
> worksheet, the syntax should be :
>
> ** **
>
> With ActiveSheet
>
> For i = 1 To 22
>
> txtbox_val  = .OLEObjects("TB" & i).Object.Value
>
> Next i
>
> End With
>
> ** **
>
> Daniel
>
> ** **
>
> *De :* excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> *De la part de* shariq khan
> *Envoyé :* vendredi 8 juillet 2011 13:11
>
> *À :* excel-macros@googlegroups.com
> *Objet :* Re: $$Excel-Macros$$ Need Help Help Help.Putting value in
> user from by loop
>
>   ** **
>
> Hi Daneil,
>
>  
>
> Thanks but it didnt worked..
>
>  
>
> Run-time error '-2147024809
>
> Could not find the specified object
>
>  
>
> Thanks,
>
> Shariq
>
> On Fri, Jul 8, 2011 at 11:12 AM, Daniel  wrote:
>
> Hi,
>
>  
>
> Try :
>
>  
>
> For num = 1 To 22
>
> txtbox_val = Me.Controls("TB" & num).Value
>
> txtbox_num = num
>
> Next num
>
>  
>
> Regards.
>
> Daniel
>
>  
>
> *De :* excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> *De la part de* shariq khan
> *Envoyé :* vendredi 8 juillet 2011 11:00
> *À :* excel-macros@googlegroups.com
> *Objet :* $$Excel-Macros$$ Need Help Help Help.Putting value in user
> from by loop
>
>  
>
> I’m trying to assign the value in excel user from thought loop.
>
>  
>
> Name of the textboxes are TB1,TB2,TB3…….. TB22  .
>
>  
>
> Below is the code which I’m using ,but its not working. 
>
>  
>
> For num = 1 To 22
>
> txtbox_val = "TB" & num & ".value"
>
> txtbox_val = num
>
> End If
>
>   Next num
>
>  
>
>  
>
> The same thing I wanted to do with the label and the names of the label are
> L1,L2,L3……L22
>
>  
>
> Please help me on this.
>
>  
>
>   
>
>
>
> --
> Thanks and Reagrd’s
> Shariq
>
>
>  
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
>
>
>
> --
> Thanks and Reagrd’s
> Shariq Shazad Khan
> 066979,9891184784
>
> --
>
> --
> 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.c

RE: $$Excel-Macros$$ Macros require to create Pivot Table

2011-07-08 Thread Rajan_Verma

See if it helps

http://excelpoweruser.blogspot.com/2011/07/creating-pivot-table.html

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Prabhu
Sent: Friday, July 08, 2011 6:08 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ ***Macros require to create Pivot Table***

 

Hi Friends,

 

I required macros for the attached data (Base sheet) which I can download
from my system.

The Excel sheet contains Base sheet in which all the transactions are
include (Both positive and negative value).

We have to remove the entire negative transactions and create Pivot table as
per the pivot sheet attached 

Pivot table:

. All the values are linked from Balance column.(Not booked value)

. Display -Classic Pivot Table Layout.

. Subtotal not required

. Pivot table  to be placed in new sheet(right to the Base sheet)

Thanks,

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

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

2011-07-08 Thread Rajan_Verma

Hi
Try this

 

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Err:

If Target = Range("A1") Then

For i = 1 To Range("A1").Value

Range("A" & i + 1).Value = i

Next

End If

Err:

End Sub

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of NOORAIN ANSARI
Sent: Friday, July 08, 2011 5:38 PM
To: excel-macros@googlegroups.com
Cc: Kal xcel
Subject: Re: $$Excel-Macros$$ Numbering

 

kalyan babu.

Please try it..also

 

=IF(A2="","",COUNTA($A$2:A2))

On Fri, Jul 8, 2011 at 4:35 PM, Kal xcel  wrote:

Dear Experts,

 

I want the formula for below mention query...

 

user will put 100 in A1, from A2 autometically numbering will start like
this

 

A21

A32

A43

.

.

.

.

 

till 100

 

What will be the formula??

 

Please help

 

Thanks in advance

 

Kalyan Chatterjee

 

-- 

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

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




-- 

Thanks & regards,

Noorain Ansari

  http://noorain-ansari.blogspot.com/

 

-- 

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

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

-- 
--
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$$ marco to find common value

2011-07-08 Thread karunanithi ramaswamy

Hi,

Please try this macro. This macro will give reference of numbers that matches 
in D column
i have inserted two columns in bwtween) with col A and also "true" and also a 
ref of A column
next to D column. Confirm actualy what u want.(i have added 4or 6 numbers for 
examble)
--R.Karunanithi

Sub Compare()
  Dim i As Integer
  Dim j As Integer
  For i = 2 To 36
 For j = 2 To 40
    If cells(i, 1).Value = cells(j, 4).Value Then
   cells(i, 2).Value = "True"
   cells(i, 3).Value = "Row" & j
   cells(j, 5).Value = "Row" & i
    End If
 Next j
  Next i
  
End Sub


--- On Wed, 7/6/11, HARI NAIR  wrote:

From: HARI NAIR 
Subject: $$Excel-Macros$$ marco to find common value
To: excel-macros@googlegroups.com
Date: Wednesday, July 6, 2011, 10:49 AM

I want a marco to find common values of column A and B in column C in the 
attached file. Please 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


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

Re: $$Excel-Macros$$ Pivot Issue

2011-07-08 Thread Chandra Shekar

Thank you I got the solution. I need to know why Adddatafield is used?

On Fri, Jul 8, 2011 at 9:32 AM, Chandra Shekar  wrote:

> Hi,
>
> Sorry I could not able to provide data bcoz its highly confidential I ran
> same using run macro I think lines which I have bolded has problem and I
> don't know how to use Adddatafield in code my code.
>
> Points what I am following is.
>
> 1) I am putting 3 fields i.e. Location, Asset and Report date in Row
> fields.
> 2) I am putting 2 fields i.e. BTN and Report date in Column field
> 3) while getting count I am facing problem I am getting all -4142.
> 4) But I am not getting Column Labels values when I ran the macro which I
> have done. i thin problem lies in Adddatafield
>
> Please let me know how to do this.
>
>
>
> Sub Macro1()
> '
> ' Macro1 Macro
> '
> '
> Sheets.Add
> ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=
> _
> "FormatData!R1C1:R238C13",
> Version:=xlPivotTableVersion10).CreatePivotTable _
> TableDestination:="Sheet3!R3C1", TableName:="PivotTable2",
> DefaultVersion _
> :=xlPivotTableVersion10
> Sheets("Sheet3").Select
> Cells(3, 1).Select
> With ActiveSheet.PivotTables("PivotTable2").PivotFields("Person
> Location")
> .Orientation = xlRowField
> .Position = 1
> End With
> With ActiveSheet.PivotTables("PivotTable2").PivotFields("Asset")
> .Orientation = xlRowField
> .Position = 2
> End With
> With ActiveSheet.PivotTables("PivotTable2").PivotFields("Reported
> DateTime")
> .Orientation = xlRowField
> .Position = 3
> End With
>
> *ActiveSheet.PivotTables("PivotTable2").AddDataField
> ActiveSheet.PivotTables( _
> "PivotTable2").PivotFields("Bridge Ticket Number"), _
>
> "Count of Bridge Ticket Number", xlCount
>
> ActiveSheet.PivotTables("PivotTable2").AddDataField
> ActiveSheet.PivotTables( _
> "PivotTable2").PivotFields("Reported DateTime"), "Count of Reported
> DateTime", _
> xlCount
> With ActiveSheet.PivotTables("PivotTable2").DataPivotField
>
> .Orientation = xlColumnField
> .Position = 1
> End With
> *End Sub
>
>
>   On Thu, Jul 7, 2011 at 8:46 PM, Rajan_Verma wrote:
>
>>  *Please attached corresponding data*
>>
>> * *
>>
>> *From:* excel-macros@googlegroups.com [mailto:
>> excel-macros@googlegroups.com] *On Behalf Of *Chandra Shekar
>> *Sent:* Thursday, July 07, 2011 8:15 PM
>> *To:* excel-macros@googlegroups.com
>> *Subject:* $$Excel-Macros$$ Pivot Issue
>>
>> ** **
>>
>> Hi,
>>
>>  
>>
>> The data is not displaying in the below code which has written for pivot.
>> Please let me know where is the error is.
>>
>>  
>>
>> Thanks in advance
>>
>>  
>>
>> Sub pivot_table()
>>
>> Dim pvt_ch As PivotCache
>> Dim pvt_tbl As PivotTable
>> Dim rng As Range
>>
>> Set rng = ThisWorkbook.Worksheets("Formatdata").UsedRange
>> Set pvt_ch = ThisWorkbook.PivotCaches.Add(xlDatabase, rng)
>>
>> ThisWorkbook.Worksheets("Pivot").Select
>>
>> Set pvt_tbl = pvt_ch.CreatePivotTable(Worksheets("Pivot").Range("B3"))
>> With pvt_tbl
>> .AddFields Array("Person Location", "Asset", "Reported DateTime")
>> End With
>>
>>
>> With pvt_tbl
>> .CalculatedFields.Add "Count of Bridge Ticket Number", xlCount
>> .CalculatedFields.Add "Count of Reported DateTime", xlCount
>> End With
>>
>>
>> With pvt_tbl.PivotFields("Count of Bridge Ticket Number")
>> .Orientation = xlDataField
>> .Function = xlCount
>> End With
>>
>> With pvt_tbl.PivotFields("Count of Reported DateTime")
>> .Orientation = xlDataField
>>  '   .Function = xlCount
>> End With
>>
>>
>> With pvt_tbl.DataPivotField
>> .Orientation = xlColumnField
>> .Position = 1
>> End With
>>
>> With pvt_tbl
>> pitm_cnt = pvt_tbl.PivotFields("Person Location").PivotItems.Count
>> For j = 1 To pitm_cnt
>> pvt_tbl.PivotFields("Person Location").PivotItems(j).ShowDetail =
>> False
>> Next
>> End With
>> Application.CutCopyMode = False
>>
>> 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
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and li

Re: $$Excel-Macros$$ Numbering

2011-07-08 Thread NOORAIN ANSARI

Dear Kalyan,

Please try it

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i, j, k As Long
i = Sheet1.Range("A1").Value
Sheet1.Range("A2:A1000").Clear
For j = 2 To i + 1
Sheet1.Cells(j, 1).Value = k + 1
k = k + 1
Next
End Sub

-- 
Thanks & regards,
Noorain Ansari
*http://noorain-ansari.blogspot.com/* 

On Fri, Jul 8, 2011 at 6:07 PM, ashish koul  wrote:

> try this
>
>
> On Fri, Jul 8, 2011 at 5:40 PM, Kal xcel  wrote:
>
>> Dear Ashish,
>>
>> I want the numbering will start frm 1 and it will end at the number given
>> by user, like if user put 100 so numbering 1,2,3,4,5,6,7100, if
>> user put 200 numbering will start from 1,2,3,4,5,6,7,8,9,10200.
>>
>> I think you got what I am saying.
>>
>> Thanks in advance
>>
>> Kalyan
>>
>> On Fri, Jul 8, 2011 at 5:29 PM, ashish koul wrote:
>>
>>> a2= a1+1
>>>
>>>
>>> On Fri, Jul 8, 2011 at 4:35 PM, Kal xcel  wrote:
>>>
 Dear Experts,

 I want the formula for below mention query...

 user will put 100 in A1, from A2 autometically numbering will start like
 this

 A21
 A32
 A43
 .
 .
 .
 .

 till 100

 What will be the formula??

 Please help

 Thanks in advance

 Kalyan Chatterjee


 --

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

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

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

>>>
>>>
>>>
>>> --
>>> *Regards*
>>> * *
>>> *Ashish Koul*
>>> *akoul*.*blogspot*.com 
>>> http://akoul.posterous.com/
>>> *akoul*.wordpress.com 
>>> My Linkedin Profile 
>>>
>>>
>>> P Before printing, think about the environment.
>>>
>>>
>>>   --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com 
> http://akoul.posterous.com/
> *akoul*.wordpress.com 
> My Linkedin Profile 
>
>
> P Before printing, think about the environment.
>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links

Re: $$Excel-Macros$$ Numbering

2011-07-08 Thread ashish koul

try this

On Fri, Jul 8, 2011 at 5:40 PM, Kal xcel  wrote:

> Dear Ashish,
>
> I want the numbering will start frm 1 and it will end at the number given
> by user, like if user put 100 so numbering 1,2,3,4,5,6,7100, if
> user put 200 numbering will start from 1,2,3,4,5,6,7,8,9,10200.
>
> I think you got what I am saying.
>
> Thanks in advance
>
> Kalyan
>
> On Fri, Jul 8, 2011 at 5:29 PM, ashish koul  wrote:
>
>> a2= a1+1
>>
>>
>> On Fri, Jul 8, 2011 at 4:35 PM, Kal xcel  wrote:
>>
>>> Dear Experts,
>>>
>>> I want the formula for below mention query...
>>>
>>> user will put 100 in A1, from A2 autometically numbering will start like
>>> this
>>>
>>> A21
>>> A32
>>> A43
>>> .
>>> .
>>> .
>>> .
>>>
>>> till 100
>>>
>>> What will be the formula??
>>>
>>> Please help
>>>
>>> Thanks in advance
>>>
>>> Kalyan Chatterjee
>>>
>>>
>>> --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>>
>>
>> --
>> *Regards*
>> * *
>> *Ashish Koul*
>> *akoul*.*blogspot*.com 
>> http://akoul.posterous.com/
>>  *akoul*.wordpress.com 
>> My Linkedin Profile 
>>
>>
>> P Before printing, think about the environment.
>>
>>
>>   --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
http://akoul.posterous.com/
*akoul*.wordpress.com 
My Linkedin Profile 


P Before printing, think about the environment.

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

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


kal.xlsm
Description: Binary data

RE: $$Excel-Macros$$ Need Help Help Help.....Putting value in user from by loop

2011-07-08 Thread Daniel

Didn’t you say the textboxes were in a userform ? if they are on a
worksheet, the syntax should be :

 

With ActiveSheet

For i = 1 To 22

txtbox_val  = .OLEObjects("TB" & i).Object.Value

Next i

End With

 

Daniel

 

De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De
la part de shariq khan
Envoyé : vendredi 8 juillet 2011 13:11
À : excel-macros@googlegroups.com
Objet : Re: $$Excel-Macros$$ Need Help Help Help.Putting value in user
from by loop

 

Hi Daneil,

 

Thanks but it didnt worked..

 

Run-time error '-2147024809

Could not find the specified object

 

Thanks,

Shariq

On Fri, Jul 8, 2011 at 11:12 AM, Daniel  wrote:

Hi,

 

Try :

 

For num = 1 To 22

txtbox_val = Me.Controls("TB" & num).Value

txtbox_num = num

Next num

 

Regards.

Daniel

 

De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De
la part de shariq khan
Envoyé : vendredi 8 juillet 2011 11:00
À : excel-macros@googlegroups.com
Objet : $$Excel-Macros$$ Need Help Help Help.Putting value in user from
by loop

 

I’m trying to assign the value in excel user from thought loop.

 

Name of the textboxes are TB1,TB2,TB3…….. TB22  .

 

Below is the code which I’m using ,but its not working. 

 

For num = 1 To 22

txtbox_val = "TB" & num & ".value"

txtbox_val = num

End If

  Next num

 

 

The same thing I wanted to do with the label and the names of the label are
L1,L2,L3……L22

 

Please help me on this.

 

  



-- 
Thanks and Reagrd’s
Shariq


 

-- 

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

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

-- 

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

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




-- 
Thanks and Reagrd’s
Shariq Shazad Khan 
066979,9891184784

-- 

--
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$$ Need Help Help Help.....Putting value in user from by loop

2011-07-08 Thread Daniel

Which line gets the error ?

 

Daniel

 

De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De
la part de shariq khan
Envoyé : vendredi 8 juillet 2011 13:11
À : excel-macros@googlegroups.com
Objet : Re: $$Excel-Macros$$ Need Help Help Help.Putting value in user
from by loop

 

Hi Daneil,

 

Thanks but it didnt worked..

 

Run-time error '-2147024809

Could not find the specified object

 

Thanks,

Shariq

On Fri, Jul 8, 2011 at 11:12 AM, Daniel  wrote:

Hi,

 

Try :

 

For num = 1 To 22

txtbox_val = Me.Controls("TB" & num).Value

txtbox_num = num

Next num

 

Regards.

Daniel

 

De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De
la part de shariq khan
Envoyé : vendredi 8 juillet 2011 11:00
À : excel-macros@googlegroups.com
Objet : $$Excel-Macros$$ Need Help Help Help.Putting value in user from
by loop

 

I’m trying to assign the value in excel user from thought loop.

 

Name of the textboxes are TB1,TB2,TB3…….. TB22  .

 

Below is the code which I’m using ,but its not working. 

 

For num = 1 To 22

txtbox_val = "TB" & num & ".value"

txtbox_val = num

End If

  Next num

 

 

The same thing I wanted to do with the label and the names of the label are
L1,L2,L3……L22

 

Please help me on this.

 

  



-- 
Thanks and Reagrd’s
Shariq


 

-- 

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

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

-- 

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

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




-- 
Thanks and Reagrd’s
Shariq Shazad Khan 
066979,9891184784

-- 

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

2011-07-08 Thread Kal xcel

Dear Ashish,

I want the numbering will start frm 1 and it will end at the number given by
user, like if user put 100 so numbering 1,2,3,4,5,6,7100, if
user put 200 numbering will start from 1,2,3,4,5,6,7,8,9,10200.

I think you got what I am saying.

Thanks in advance

Kalyan

On Fri, Jul 8, 2011 at 5:29 PM, ashish koul  wrote:

> a2= a1+1
>
>
> On Fri, Jul 8, 2011 at 4:35 PM, Kal xcel  wrote:
>
>> Dear Experts,
>>
>> I want the formula for below mention query...
>>
>> user will put 100 in A1, from A2 autometically numbering will start like
>> this
>>
>> A21
>> A32
>> A43
>> .
>> .
>> .
>> .
>>
>> till 100
>>
>> What will be the formula??
>>
>> Please help
>>
>> Thanks in advance
>>
>> Kalyan Chatterjee
>>
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com 
> http://akoul.posterous.com/
> *akoul*.wordpress.com 
> My Linkedin Profile 
>
>
> P Before printing, think about the environment.
>
>
>   --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

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

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

Re: $$Excel-Macros$$ Numbering

2011-07-08 Thread NOORAIN ANSARI

kalyan babu.
Please try it..also

=IF(A2="","",COUNTA($A$2:A2))

On Fri, Jul 8, 2011 at 4:35 PM, Kal xcel  wrote:

> Dear Experts,
>
> I want the formula for below mention query...
>
> user will put 100 in A1, from A2 autometically numbering will start like
> this
>
> A21
> A32
> A43
> .
> .
> .
> .
>
> till 100
>
> What will be the formula??
>
> Please help
>
> Thanks in advance
>
> Kalyan Chatterjee
>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
Thanks & regards,
Noorain Ansari
*http://noorain-ansari.blogspot.com/* 

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

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

$$Excel-Macros$$ Numbering

2011-07-08 Thread Kal xcel

Dear Experts,

I want the formula for below mention query...

user will put 100 in A1, from A2 autometically numbering will start like
this

A21
A32
A43
.
.
.
.

till 100

What will be the formula??

Please help

Thanks in advance

Kalyan Chatterjee

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

2011-07-08 Thread ashish koul

a2= a1+1


On Fri, Jul 8, 2011 at 4:35 PM, Kal xcel  wrote:

> Dear Experts,
>
> I want the formula for below mention query...
>
> user will put 100 in A1, from A2 autometically numbering will start like
> this
>
> A21
> A32
> A43
> .
> .
> .
> .
>
> till 100
>
> What will be the formula??
>
> Please help
>
> Thanks in advance
>
> Kalyan Chatterjee
>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
http://akoul.posterous.com/
*akoul*.wordpress.com 
My Linkedin Profile 


P Before printing, think about the environment.

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

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

Re: $$Excel-Macros$$ Need Help Help Help.....Putting value in user from by loop

2011-07-08 Thread shariq khan

Hi Daneil,

Thanks but it didnt worked..

Run-time error '-2147024809
Could not find the specified object

Thanks,
Shariq
On Fri, Jul 8, 2011 at 11:12 AM, Daniel  wrote:

>  Hi,
>
> ** **
>
> Try :
>
> ** **
>
> For num = 1 To 22
>
> txtbox_val = Me.Controls("TB" & num).Value
>
> txtbox_num = num
>
> Next num
>
> ** **
>
> Regards.
>
> Daniel
>
> ** **
>
> *De :* excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> *De la part de* shariq khan
> *Envoyé :* vendredi 8 juillet 2011 11:00
> *À :* excel-macros@googlegroups.com
> *Objet :* $$Excel-Macros$$ Need Help Help Help.Putting value in user
> from by loop
>
> ** **
>
> I’m trying to assign the value in excel user from thought loop.
>
>  
>
> Name of the textboxes are TB1,TB2,TB3…….. TB22  .
>
>  
>
> Below is the code which I’m using ,but its not working. 
>
>  
>
> For num = 1 To 22
>
> txtbox_val = "TB" & num & ".value"
>
> txtbox_val = num
>
> End If
>
>   Next num
>
>  
>
>  
>
> The same thing I wanted to do with the label and the names of the label are
> L1,L2,L3……L22
>
>  
>
> Please help me on this.
>
>  
>
>   
>
>
>
> --
> Thanks and Reagrd’s
> Shariq
>
>
>  
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
Thanks and Reagrd’s
Shariq Shazad Khan
066979,9891184784

-- 
--
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$$ Filename for unsaved workbook

2011-07-08 Thread Rajan_Verma

Try this

Workbook.SaveAs FileName

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Stilgar
Sent: Friday, July 08, 2011 2:40 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Filename for unsaved workbook

 

Hello,

 

I wrote a script to make a export with specific data.

Our workplanner can generate this report and save it of mail it.

But when a workbook is created is is called book1.xlsx (map1 for the Dutch)
or like that.

Is there a way to give the new made, unsaved workbook a filename?

 

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

-- 
--
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 Help Help.....Putting value in user from by loop

2011-07-08 Thread Rajan_Verma

You have to make a loop through each OleObject Embaded on Worksheet, 

See if it helps.

Sub LoopWsCntrl()

Dim OLECont As OLEObject

Dim lRow As Long, lCol As Long

 

For Each OLECont In Sheet1.OLEObjects

If OLECont.progID = "Forms.TextBox.1" Then

  Variable = OLECont.Object.Value

End If

 Next OLECont

End Sub

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of shariq khan
Sent: Friday, July 08, 2011 2:30 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Need Help Help Help.Putting value in user from
by loop

 

I'm trying to assign the value in excel user from thought loop.

 

Name of the textboxes are TB1,TB2,TB3 TB22  .

 

Below is the code which I'm using ,but its not working. 

 

For num = 1 To 22

txtbox_val = "TB" & num & ".value"

txtbox_val = num

End If

  Next num

 

 

The same thing I wanted to do with the label and the names of the label are
L1,L2,L3..L22

 

Please help me on this.

 

  



-- 
Thanks and Reagrd's
Shariq


 

-- 

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

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

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

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

RE: $$Excel-Macros$$ Help regarding bank statment

2011-07-08 Thread Rajan_Verma

See if it help,

 

Use this in L2

 

=OFFSET(INDEX(F:F,MATCH(K2,F:F,0),1),COUNTIF(F:F,K2)-1,-4,1,1)

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of ashish koul
Sent: Friday, July 08, 2011 11:06 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Help regarding bank statment

 

hi

 

check the attachment  see if it helps

 

On Fri, Jul 8, 2011 at 10:09 AM, L.K. Modi  wrote:

Dear Members,

 

Thanks for your valuable support. I have an querry in which i have to check
interest calculation for banks.

 

Me attaching a sheet in which column A have date that is in different
format. and in column B the balance as on the respective dates.

 

As the bank charges interest on the closing balance for particular dates. So
there are many balances as on the same date.

 

What i need first that the date should be in format of dd-mmm-yy and
secondly like there are many balances of 1st june so i need only the closing
balance of 1st june and so on. it means last line that is in 1st june this i
have to do for all dates.

 

can anyone help me as this is more time taking without proper direction.

 

Thanks in advance

 

Regards

LKModi

 

 

-- 

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

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




-- 

Regards

 

Ashish Koul

  akoul.blogspot.com
http://akoul.posterous.com/

  akoul.wordpress.com

My   Linkedin Profile

 

P Before printing, think about the environment.

 

 

-- 

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

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

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

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


Copy of test sheet.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

$$Excel-Macros$$ need help help........Texbox of user form

2011-07-08 Thread shariq khan

I’m trying to assign the value in excel user form thought loop.

Name of the textboxes are TB1,TB2,TB3…….. TB22  .

Below is the code which I’m using ,but its not working.

For num = 1 To 22
txtbox_val = "TB" & num & ".value"
txtbox_val = num
End If
  Next num


The same thing I wanted to do with the label and the names of the label are
L1,L2,L3……L22

Please help me on 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

RE: $$Excel-Macros$$ Filename for unsaved workbook

2011-07-08 Thread Daniel

Hello,

 

Maybe, use « SaveAs » instead of « Save » ?

Anyway, can you post your code ?

 

Regards.

Daniel

 

De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De
la part de Stilgar
Envoyé : vendredi 8 juillet 2011 11:10
À : excel-macros@googlegroups.com
Objet : $$Excel-Macros$$ Filename for unsaved workbook

 

Hello,

 

I wrote a script to make a export with specific data.

Our workplanner can generate this report and save it of mail it.

But when a workbook is created is is called book1.xlsx (map1 for the Dutch)
or like that.

Is there a way to give the new made, unsaved workbook a filename?

 

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

-- 
--
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 Help Help.....Putting value in user from by loop

2011-07-08 Thread Daniel

Hi,

 

Try :

 

For num = 1 To 22

txtbox_val = Me.Controls("TB" & num).Value

txtbox_num = num

Next num

 

Regards.

Daniel

 

De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De
la part de shariq khan
Envoyé : vendredi 8 juillet 2011 11:00
À : excel-macros@googlegroups.com
Objet : $$Excel-Macros$$ Need Help Help Help.Putting value in user from
by loop

 

I’m trying to assign the value in excel user from thought loop.

 

Name of the textboxes are TB1,TB2,TB3…….. TB22  .

 

Below is the code which I’m using ,but its not working. 

 

For num = 1 To 22

txtbox_val = "TB" & num & ".value"

txtbox_val = num

End If

  Next num

 

 

The same thing I wanted to do with the label and the names of the label are
L1,L2,L3……L22

 

Please help me on this.

 

  



-- 
Thanks and Reagrd’s
Shariq


 

-- 

--
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$$ Convert HTML file to Excel

2011-07-08 Thread Chidurala, Shrinivas

Rajan,

I am using Excel 2003 and the macro is not from coding " Dim Fso As New 
Scripting.FileSystemObject" .


Regards,
Shrinivas




On Thu, Jul 7, 2011 at 10:45 PM, Chidurala, Shrinivas 
mailto:shrinivas.chidur...@citi.com>> wrote:
Thanks a lot Rajan. But the below macro is not running in Excel 2003 or any 
reference need to select from Tools. Please confirm.

Regards,
Shrinivas
Citi(r) Global Transaction Services - India
UB City, Canberra Block, # 24, Vittal Mallya Road,
Bangalore - 56 00 01.
Ph- +91-80-4144 6339 / 6340
Email - shrinivas.chidur...@citi.com


-Original Message-
From: excel-macros@googlegroups.com 
[mailto:excel-macros@googlegroups.com] On 
Behalf Of Rajan_Verma
Sent: Thursday, July 07, 2011 1:23 AM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Convert HTML file to Excel

See if it Help.


Sub Compile()
On Error GoTo Err_Clear:
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Fso As New Scripting.FileSystemObject Dim Path As String

Application.FileDialog(msoFileDialogFolderPicker).Title = "Select Folder to 
Pick Downloaded Bills"
Application.FileDialog(msoFileDialogFolderPicker).Show
Path = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
If Path = "" Then Exit Sub

Application.FileDialog(msoFileDialogFolderPicker).Title = "Select Folder to 
Save Compiled File"
CompilePath = Application.FileDialog(msoFileDialogFolderPicker).Show
compiledPath =
Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
If compiledPath = "" Then Exit Sub


Dim Counter
Dim File As File
Dim FOlder As FOlder
Dim wb As Workbook
Dim ws As Worksheet
Dim AcWb As Workbook
Set AcWb = ActiveWorkbook
ActiveWorkbook.Sheets.Add
ActiveSheet.Name = "Index"

Set FOlder = Fso.GetFolder(Path)

   For Each File In FOlder.Files

 Counter = Counter + 1
Set wb = Workbooks.Open(Path & File.Name)

   If Application.Ready = True Then

wb.Sheets("Index").Activate
 ActiveSheet.UsedRange.Copy
 AcWb.Sheets("Index").Activate
 Range("A100").End(xlUp).Select
 ActiveSheet.Paste
Application.CutCopyMode = False
wb.Close
End If

   Next
If Counter > 0 Then
AcWb.SaveAs compiledPath & "Compiled", xlExcel12 AcWb.Close End If
Err_Clear:
Err.Clear
Resume Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True
   If Counter < 1 Then
   MsgBox "No File Found For Compile", vbInformation
   Else
   MsgBox Counter & " File Has been Compiled, Please Find your File at"
& vbCrLf & compiledPath, vbInformation
   End If

End Sub




-Original Message-
From: excel-macros@googlegroups.com 
[mailto:excel-macros@googlegroups.com]
On Behalf Of Chidurala, Shrinivas
Sent: Wednesday, July 06, 2011 5:00 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Convert HTML file to Excel

Dear Excel Gurus,

I have some HTML files which are saved in same folder and I want to convert 
them into Excel and merger the all files into 1 workbook. Please help me to 
create the macro for the same. Find attached 2 HTML files and required report.


Regards,
Shrinivas

--

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

Re: $$Excel-Macros$$ Convert HTML file to Excel

2011-07-08 Thread SHREE

Thanks Vasant Its working fine, Please can you also advise coding for
only converting HTML file into Excel...

On Jul 6, 8:57 pm, Vasant  wrote:
> Hi
>
> Pls find the code and the file attached.
> The html files stored in the folder 'Folder' will be used for the report.
>
> Sub Test()
> Dim Fs As New FileSystemObject, Fl As File
> Dim Fld As Folder, FolderPath As String
> Dim WkBk As Workbook, DtWkBk As Workbook
> Set WkBk = Workbooks.Add
> NoShts = WkBk.Worksheets.Count
> WkBk.SaveAs Filename:="Report"
>
> FolderPath = ThisWorkbook.Worksheets("Sheet1").Range("B1")
> Application.DisplayAlerts = False
> Set Fld = Fs.GetFolder(FolderPath)
> Cn = 1
> For Each fls In Fld.Files
>     If fls.Type = "HTML Document" Then
>         Debug.Print fls.Type
>         Workbooks.Open Filename:=fls.Name
>         ActiveSheet.Cells.Copy
>         If Cn <= NoShts Then
>             WkBk.Worksheets(Cn).Activate
>             ActiveSheet.Paste
>             ActiveSheet.Name = Left(fls.Name, Len(fls.Name) - 4)
>             Cn = Cn + 1
>         Else
>             WkBk.Worksheets.Add
>             Cn = Cn + 1
>             WkBk.Worksheets(Cn).Activate
>             ActiveSheet.Paste
>             ActiveSheet.Name = Left(fls.Name, Len(fls.Name) - 4)
>
>         End If
>     End If
>
> Next
>
> Application.DisplayAlerts = True
>
> End Sub
>
> On Wed, Jul 6, 2011 at 4:59 PM, Chidurala, Shrinivas <
>
>
>
>
>
> shrinivas.chidur...@citi.com> wrote:
> > Dear Excel Gurus,
>
> > I have some HTML files which are saved in same folder and I want to convert
> > them into Excel and merger the all files into 1 workbook. Please help me to
> > create the macro for the same. Find attached 2 HTML files and required
> > report.
>
> > Regards,
> > Shrinivas
>
> > --
>
> > ------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> >http://www.facebook.com/discussexcel
>
> --
> Regards
>
> Vasant
>
> skype Id: vasantjobhttp://facebook.com/vasantjob
>
>  Code.xlsm
> 23KViewDownload- Hide quoted text -
>
> - Show quoted text -

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

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

$$Excel-Macros$$ Filename for unsaved workbook

2011-07-08 Thread Stilgar

Hello,
 
I wrote a script to make a export with specific data.
Our workplanner can generate this report and save it of mail it.
But when a workbook is created is is called book1.xlsx (map1 for the Dutch) 
or like that.
Is there a way to give the new made, unsaved workbook a filename?
 
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

$$Excel-Macros$$ Need Help Help Help.....Putting value in user from by loop

2011-07-08 Thread shariq khan

I’m trying to assign the value in excel user from thought loop.



Name of the textboxes are TB1,TB2,TB3…….. TB22  .



Below is the code which I’m using ,but its not working.



For num = 1 To 22

txtbox_val = "TB" & num & ".value"

txtbox_val = num

End If

  Next num





The same thing I wanted to do with the label and the names of the label are
L1,L2,L3……L22



Please help me on this.






-- 
Thanks and Reagrd’s
Shariq

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

2011-07-08 Thread Kishan Reddy, K

Be simple.

You might be well aware of command buttons, drop downs, Text box,
check box etc.
Well these are called CONTROLS or OBJECTS.

Either You are a programmer or not you might be well aware of these
controls,
You interact with these controls using keyboard or mouse or other
input divices.

Example with mouse You can click, double click, right click, drag &
drop objects etc.
As well with keyboard you press the keys on the keyboard, known as
typing, means you press the keys.

These are nothing but called EVENTS (click, double click, right click,
keypress).

You expect something by CLICKING on a COMMAND BUTTON.

These expectations means, what should happen when an event is written
an EVENT-PROCEDURE

Simple Example.

You have a FORM.
It Contains a Command Button.
You want to display a message "Command Button was clicked" when the
command button is clicked.

So You write the code in the event procedure

Sub CommandButton_Press
Msgbox "Command Button was clicked"
End Sub

Let us see the BIG PICTURE.

Revisit CONTROLS/OBJECTS: command buttons, drop downs, Text box, check
box are simple controls.
Worksheet, Workbook are composite objects, which are made with other
objects ie, rows, columns, cells etc.

Workbook contains worksheets (collection of worksheet).
Worksheet contains rows, columns, cells etc.

Just like a Human body contains different organs (objects) like heart,
brain, stomack etc each will have its own functions (event procedure).
and each orgon inturn is made up of different types of cells (basic
controls).

This is called OBJECT ORIENTED PROGRAMMING or EVENT DRIVEN
PROGRAMMING.

Regards,
Kishan Reddy, K

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

2011-07-08 Thread Chandra Shekar

Hi,

Sorry I could not able to provide data bcoz its highly confidential I ran
same using run macro I think lines which I have bolded has problem and I
don't know how to use Adddatafield in code my code.

Points what I am following is.

1) I am putting 3 fields i.e. Location, Asset and Report date in Row fields.
2) I am putting 2 fields i.e. BTN and Report date in Column field
3) while getting count I am facing problem I am getting all -4142.
4) But I am not getting Column Labels values when I ran the macro which I
have done. i thin problem lies in Adddatafield

Please let me know how to do this.



Sub Macro1()
'
' Macro1 Macro
'
'
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"FormatData!R1C1:R238C13",
Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet3!R3C1", TableName:="PivotTable2",
DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet3").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Person
Location")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Asset")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Reported
DateTime")
.Orientation = xlRowField
.Position = 3
End With

*ActiveSheet.PivotTables("PivotTable2").AddDataField
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Bridge Ticket Number"), _
"Count of Bridge Ticket Number", xlCount

ActiveSheet.PivotTables("PivotTable2").AddDataField
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Reported DateTime"), "Count of Reported
DateTime", _
xlCount
With ActiveSheet.PivotTables("PivotTable2").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
*End Sub


On Thu, Jul 7, 2011 at 8:46 PM, Rajan_Verma wrote:

>  *Please attached corresponding data*
>
> * *
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Chandra Shekar
> *Sent:* Thursday, July 07, 2011 8:15 PM
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Pivot Issue
>
> ** **
>
> Hi,
>
>  
>
> The data is not displaying in the below code which has written for pivot.
> Please let me know where is the error is.
>
>  
>
> Thanks in advance
>
>  
>
> Sub pivot_table()
>
> Dim pvt_ch As PivotCache
> Dim pvt_tbl As PivotTable
> Dim rng As Range
>
> Set rng = ThisWorkbook.Worksheets("Formatdata").UsedRange
> Set pvt_ch = ThisWorkbook.PivotCaches.Add(xlDatabase, rng)
>
> ThisWorkbook.Worksheets("Pivot").Select
>
> Set pvt_tbl = pvt_ch.CreatePivotTable(Worksheets("Pivot").Range("B3"))
> With pvt_tbl
> .AddFields Array("Person Location", "Asset", "Reported DateTime")
> End With
>
>
> With pvt_tbl
> .CalculatedFields.Add "Count of Bridge Ticket Number", xlCount
> .CalculatedFields.Add "Count of Reported DateTime", xlCount
> End With
>
>
> With pvt_tbl.PivotFields("Count of Bridge Ticket Number")
> .Orientation = xlDataField
> .Function = xlCount
> End With
>
> With pvt_tbl.PivotFields("Count of Reported DateTime")
> .Orientation = xlDataField
>  '   .Function = xlCount
> End With
>
>
> With pvt_tbl.DataPivotField
> .Orientation = xlColumnField
> .Position = 1
> End With
>
> With pvt_tbl
> pitm_cnt = pvt_tbl.PivotFields("Person Location").PivotItems.Count
> For j = 1 To pitm_cnt
> pvt_tbl.PivotFields("Person Location").PivotItems(j).ShowDetail =
> False
> Next
> End With
> Application.CutCopyMode = False
>
> 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
>
> --
>
> --
> 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@goo

42 matches

Mail list logo