Re: $$Excel-Macros$$ Re: VBA macro - WhatsApp group.

[Sandeep Chhajer]

Re: $$Excel-Macros$$ Merge multiple Workbook

[Sandeep Chhajer]

  Thanks Devendra, I will send you some sample files for better understanding my requirements.Thanks again for your help.  Sent from my BlackBerry 10 smartphone.From: Devendra SahaySent: Wednesday 27 January 2016 15:32To: excel-macros@googlegroups.comReply To: excel-macros@googlegroups.comSubject: Re: $$Excel-Macros$$ Merge multiple WorkbookHi Sandeep,I am sharing the code, If any group member can modify the code according to you, would be helpful. ** If we can assign few header names, of which data we want to extract from the workbooks. It would be relevant. Code Sub Merge_Files()Dim objFSO As ObjectDim objFolder As ObjectDim objFile As ObjectDim i As LongDim col_no As Long, row_no As LongDim arr_ws As VariantDim ws1 As Worksheet, wb1 As WorkbookDim col_ws1 As Long, row_ws1 As LongSet wb1 = ThisWorkbookSet ws1 = ActiveSheetSet d = Application.FileDialog(msoFileDialogFolderPicker)d.Show fldpath = d.SelectedItems(1) & "\"'Create an instance of the FileSystemObjectSet objFSO = CreateObject("Scripting.FileSystemObject")'Get the folder objectSet objFolder = objFSO.GetFolder(fldpath)i = 0'loops through each file in the directory and prints their names and pathFor Each objFile In objFolder.FilesDebug.Print objFile    i = i + 1    Workbooks.Open FileName:=objFile    row_no = ActiveSheet.Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row    col_no = ActiveSheet.Range(Cells(1, Columns.Count), Cells(1, Columns.Count)).End(xlToLeft).Column    arr_ws = ActiveSheet.Range(Cells(1, 1), Cells(row_no, col_no))    ActiveWorkbook.Close savechanges = no    ws1.Activate    If ws1.Range("A1").Value <> "" Then    row_ws1 = ActiveSheet.Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row + 1    col_ws1 = ActiveSheet.Range(Cells(1, Columns.Count), Cells(1, Columns.Count)).End(xlToLeft).Column    Else    row_ws1 = 1    col_ws1 = 1    End If    ws1.Range(Cells(row_ws1, 1), Cells(row_ws1 + row_no - 1, col_no)) = arr_ws    Next objFileEnd SubOn Wed, Jan 27, 2016 at 2:19 PM, Sandeep Chhajer <chhajersand...@gmail.com> wrote:  Hi Devendra, Thanks for your reply. The problem is the headers are not same in all the files. But some column headers are same in all the workbooks.So,  my requiremnt is a Macro which can search the column with that particular header name (common accross All the workbooks and paste it.Regards,



-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel
 
FORUM RULES
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
 
NOTE  : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Merge multiple Workbook

[Sandeep Chhajer]

  Hi Devendra, Thanks for your reply. The problem is the headers are not same in all the files. But some column headers are same in all the workbooks.So,  my requiremnt is a Macro which can search the column with that particular header name (common accross All the workbooks and paste it.Regards,Sandeep K Chhajer.     Sent from my BlackBerry 10 smartphone.From: Devendra SahaySent: Wednesday 27 January 2016 10:59To: excel-macros@googlegroups.comReply To: excel-macros@googlegroups.comSubject: Re: Fw: $$Excel-Macros$$ Merge multiple WorkbookHi Sandeep,I have a code, which simply goes to specific folder & open the workbooks one by one & copy the data and paste them in same sheet (Where the marcro is written).** Make sure all the files have same header, because it copies all the data including the header. So if this is relevant, I can share the code.Thanks.Devendra On Tue, Jan 26, 2016 at 8:52 PM, Sandeep Chhajer <chhajersand...@gmail.com> wrote:  Hi,Any way to solve this problem.Sent from my BlackBerry 10 smartphone.    From: Sandeep Chhajer <chhajersand...@gmail.com>Sent: Sunday 24 January 2016 18:07To: Excel MacrosReply To: excel-macros@googlegroups.comSubject: $$Excel-Macros$$ Merge multiple WorkbookHi Excel guru,I have found macros for merging multiple workbook (kept in a single folder) into a single worksheet.But my requirement is a step ahead...can that macro to be changed to copy only specified column from every wrokbook (Sheet1). Like it will search in all the workbooks (sheet1 row no 1 to 6 ) kept in a folder; and wherever it will find "Name" , "Emp code"  or " Net payment" then copy the range into new sheet one after one.the code I am using (got from website) for merging the workbooks are pasted below. (Any thing new to solve the above problem is also welcome)Sub simpleXlsMerger()Dim bookList As WorkbookDim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As ObjectApplication.ScreenUpdating = FalseSet mergeObj = CreateObject("Scripting.FileSystemObject") 'change folder path of excel files hereSet dirObj = mergeObj.Getfolder("C:WorkingMTMT 2015 16MT Q3Actuas All  ")Set filesObj = dirObj.FilesFor Each everyObj In filesObjSet bookList = Workbooks.Open(everyObj) 'change "A2" with cell reference of start point for every files here'for example "B3:IV" to merge all files start from columns B and rows 3'If you're files using more than IV column, change it to the latest column'Also change "A" column on "A65536" to the same column as start pointRange("A2:IV" & Range("A65536").End(xlUp).Row).CopyThisWorkbook.Worksheets(1).Activate 'Do not change the following column. It's not the same column as aboveRange("A65536").End(xlUp).Offset(1, 0).PasteSpecialApplication.CutCopyMode = FalsebookList.CloseNextEnd Sub      Thanking you in advance.-- Regards,CA Sandeep Kumar Chhajer




-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel
 
FORUM RULES
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
 
NOTE  : Don't ever post confidential data i

Fw: $$Excel-Macros$$ Merge multiple Workbook

[Sandeep Chhajer]

  Hi,Any way to solve this problem.Sent from my BlackBerry 10 smartphone.From: Sandeep Chhajer Sent: Sunday 24 January 2016 18:07To: Excel MacrosReply To: excel-macros@googlegroups.comSubject: $$Excel-Macros$$ Merge multiple WorkbookHi Excel guru,I have found macros for merging multiple workbook (kept in a single folder) into a single worksheet.But my requirement is a step ahead...can that macro to be changed to copy only specified column from every wrokbook (Sheet1). Like it will search in all the workbooks (sheet1 row no 1 to 6 ) kept in a folder; and wherever it will find "Name" , "Emp code"  or " Net payment" then copy the range into new sheet one after one.the code I am using (got from website) for merging the workbooks are pasted below. (Any thing new to solve the above problem is also welcome)Sub simpleXlsMerger()Dim bookList As WorkbookDim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As ObjectApplication.ScreenUpdating = FalseSet mergeObj = CreateObject("Scripting.FileSystemObject") 'change folder path of excel files hereSet dirObj = mergeObj.Getfolder("C:\Working\MT\MT 2015 16\MT Q3\Actuas All  ")Set filesObj = dirObj.FilesFor Each everyObj In filesObjSet bookList = Workbooks.Open(everyObj) 'change "A2" with cell reference of start point for every files here'for example "B3:IV" to merge all files start from columns B and rows 3'If you're files using more than IV column, change it to the latest column'Also change "A" column on "A65536" to the same column as start pointRange("A2:IV" & Range("A65536").End(xlUp).Row).CopyThisWorkbook.Worksheets(1).Activate 'Do not change the following column. It's not the same column as aboveRange("A65536").End(xlUp).Offset(1, 0).PasteSpecialApplication.CutCopyMode = FalsebookList.CloseNextEnd Sub      Thanking you in advance.-- Regards,CA Sandeep Kumar Chhajer




-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel
 
FORUM RULES
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
 
NOTE  : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.




-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel
 
FORUM RULES
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
 
NOTE  : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

$$Excel-Macros$$ Merge multiple Workbook

[Sandeep Chhajer]

Hi Excel guru,

I have found macros for merging multiple workbook (kept in a single folder)
into a single worksheet.

But my requirement is a step ahead...can that macro to be changed to copy
only specified column from every wrokbook (Sheet1). Like it will search in
all the workbooks (sheet1 row no 1 to 6 ) kept in a folder; and wherever it
will find "Name" , "Emp code"  or " Net payment" then copy the range into
new sheet one after one.

the code I am using (got from website) for merging the workbooks are pasted
below. (Any thing new to solve the above problem is also welcome)


Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As
Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("C:\Working\MT\MT 2015 16\MT Q3\Actuas All
 ")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

Thanking you in advance.
-- 
Regards,
CA Sandeep Kumar Chhajer

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Re: How can I match two different data, where only a reference number and amount are common.

[Sandeep Chhajer]

  Hi,You can use VLOOKUP. For more how to use VLOOKUP check..Microsoft office help.Sandeep.Sent from my BlackBerry 10 smartphone.From: Saddam AliSent: Monday 30 March 2015 18:19To: excel-macros@googlegroups.comReply To: excel-macros@googlegroups.comSubject: $$Excel-Macros$$ Re: How can I match two different data, where only a reference number and amount are common.‎Kindly find the attached excel work book and possibly assist. i need to match all the data of the first sheet to the outstanding in the second sheet‎On Monday, March 30, 2015 at 12:37:28 PM UTC+3, Saddam Ali wrote:My name is Baricha, from Kenya. I work in a bank and so i cannot upload the data because Its sensitive and also a  breach of contract. I normally receive data from two different sources, and i am required to match the two data, the only common features between the 2 data, are the amount and a reference number. this is what i normally use to match up the two data. i cannot do VLOOKUP because the reference numbers which are client specific re-occur and so instead of matching a reference number with the same matching amount, it picks the reference number yes, but with the amount of the very first similar reference number. My question is, is it possible for me to match the two datas with both the EXACT reference number and amount even if the reference number re-occurs. I match manually and its very tiresome.Please assist.



-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel
 
FORUM RULES
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
 
NOTE  : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.




-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel
 
FORUM RULES
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
 
NOTE  : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Re: What is this Cache stands for.

[Sandeep Chhajer.]

Hi Mandeep,

Thanks for your quick response. I have many add-ins being installed. I was 
fearing it to some thing like malicious object. So was bit worried.

By the way will check by disabling one by one all the ad-ins and remove the one 
which is causing this. 

Thanks once again. 
Thanks & Regards,

Sandeep Kr. Chhajer.
9920751444

-Original Message-
From: Mandeep Baluja 
Sender: excel-macros@googlegroups.com
Date: Fri, 26 Dec 2014 23:56:42 
To: 
Reply-To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Re: What is this Cache stands for.

You have installed a addin in your sheet. remove the addin first it will 
automatically remove. 

   1. 
   
   Click the Microsoft Office Button [image: Office button image], click Excel 
   Options, and then click Add-Ins. then select addin and press go uncheck 
   the addin. 
   


Regards,
Mandeep Baluja 
LearningZmyPassion
https://www.linkedin.com/profile/view?id=312532939
https://www.facebook.com/VBAEXCELSQL?ref=hl 


On Saturday, December 27, 2014 11:48:23 AM UTC+5:30, sandeep chhajer wrote:
>
> Dear Excel Gurus,
>
> Please check the print screen attached herewith. I am getting a sheet 
> called "Acerno_cache_x" with a status very hidden, in every workbook I 
> am opening.
>
> Can any one help me out.  
>
> -- 
> Regards,
> CA Sandeep Kumar Chhajer.
> Membership no: 160212
>  

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Automatic email send from excel list

[Sandeep Chhajer]

Thanks Vaibhav. Will check and revert back to you.


On 2 June 2014 13:50, Vaibhav Joshi  wrote:

> Hey
>
> Chek this..
>
> Modified mine earlier posted template to suits you.
>
> Mail will be send using outlook..
>
> Cheers!!
>
>
>
>
> On Mon, Jun 2, 2014 at 12:54 PM, Sandeep Chhajer  > wrote:
>
>> Dear Excel Gurus,
>> I want to make emailing procedure to my client automated.
>>
>> I have a excel sheet as below
>>
>> Name of the CompanyConcerned person PAN email
>> ID
>>
>> ABC LTDMurli JoshiAKJCC0305D
>>mu...@abc.com
>>
>> XYZ P LTD  Sahil Maheswari  KJACC0503D
>> mahesw...@gmail.com
>>
>>
>> Now every month I create a folder where I save certificate to be sent to
>> concerned person. The name of the certificate comprise of PAN NO_Month_year
>> like AKJCC0305D_FEB_2014. All the certificate for a particular month say
>> Feb are kept in the same folder.
>>
>> Now I want a macro which can search according to the PAN no of the
>> customer in the folder and accordingly attach respective PDF file and send
>> it to all the clients listed in that excel sheet.
>>
>> Thanks in ADVANCE.
>>
>>
>>
>>
>>
>> --
>> Warm Regards,
>> Sandeep Kumar Chhajer.
>>
>>  --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> 2) Don't post a question in the thread of another member.
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> 4) Acknowledge the responses you receive, good or bad.
>> 5) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "MS EXCEL AND VBA MACROS" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to excel-macros+unsubscr...@googlegroups.com.
>> To post to this group, send email to excel-macros@googlegroups.com.
>> Visit this group at http://groups.google.com/group/excel-macros.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>  --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>



-- 
Regards,
Sandeep Kumar Chhajer.

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security 

$$Excel-Macros$$ Automatic email send from excel list

[Sandeep Chhajer]

Dear Excel Gurus,
I want to make emailing procedure to my client automated.

I have a excel sheet as below

Name of the CompanyConcerned person PAN email ID

ABC LTDMurli JoshiAKJCC0305D
mu...@abc.com

XYZ P LTD  Sahil Maheswari  KJACC0503D
mahesw...@gmail.com


Now every month I create a folder where I save certificate to be sent to
concerned person. The name of the certificate comprise of PAN NO_Month_year
like AKJCC0305D_FEB_2014. All the certificate for a particular month say
Feb are kept in the same folder.

Now I want a macro which can search according to the PAN no of the customer
in the folder and accordingly attach respective PDF file and send it to all
the clients listed in that excel sheet.

Thanks in ADVANCE.





-- 
Warm Regards,
Sandeep Kumar Chhajer.

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Re:

[Sandeep Chhajer]

Thank you Basole for your reply. But after the suggested changes when i am
running the code it is showing me error code 400. any specific reason.



On 15 October 2013 17:49, Basole  wrote:

> Hi Sandeep, try this:
>
> > Replaces the highlighted code for this:
>
>
> Option Explicit
>
> #If VBA7 Then
> Private Type BROWSEINFO
> hOwner As LongPtr
> pidlRoot As LongPtr
> pszDisplayName As String
> lpszTitle As String
> ulFlags As Long
> lpfn As LongPtr
> lParam As LongPtr
> iImage As Long
> End Type
>
> Private Declare PtrSafe Function SHBrowseForFolder Lib "shell32.dll"
> Alias "SHBrowseForFolderA" _
> (lpBrowseInfo As BROWSEINFO) As LongPtr
> Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll"
> Alias "SHGetPathFromIDListA" _
> (ByVal pidl As LongPtr, ByVal pszPath As String) As Boolean
> #Else
> Private Type BROWSEINFO
> hOwner As Long
> pidlRoot As Long
> pszDisplayName As String
> lpszTitle As String
> ulFlags As Long
> lpfn As Long
> lParam As Long
> iImage As Long
> End Type
>
> Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias
> "SHBrowseForFolderA" _
> (lpBrowseInfo As BROWSEINFO) As Long
> Declare Function SHBrowseForFolder Lib "shell32.dll" _
> Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
> #End If
>
> Private Const BIF_RETURNONLYFSDIRS = &H1
>
>
>
>
>
> Option Explicit
>
>  '32-bit API declarations
> Declare Function SHGetPathFromIDList Lib "shell32.dll" _
> Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal _
> pszpath As String) As Long
>
> Declare Function SHBrowseForFolder Lib "shell32.dll" _
> Alias "SHBrowseForFolderA" (lpBrowseInfo As BrowseInfo) _
> As Long
>
> Public Type BrowseInfo
> hOwner As Long
> pIDLRoot As Long
> pszDisplayName As String
> lpszTitle As String
> ulFlags As Long
> lpfn As Long
> lParam As Long
> iImage As Long
> End Type
>
>
>
>
> Em terça-feira, 15 de outubro de 2013 06h12min23s UTC-3, sandeep chhajer
> escreveu:
>>
>> Dear Excel Gurus,
>>
>> I have got this Macro from this forum for* adding worksheets from
>> different work book kept in a folder,* but now when i am trying this
>> code I am getting compiling error message" can not define a public user
>> defined type with an object module.
>>
>> Please help.
>>
>> Thanks in advance.
>>
>> Option Explicit
>>
>>  '32-bit API declarations
>> Declare Function SHGetPathFromIDList Lib "shell32.dll" _
>> Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal _
>> pszpath As String) As Long
>>
>> Declare Function SHBrowseForFolder Lib "shell32.dll" _
>> Alias "SHBrowseForFolderA" (lpBrowseInfo As BrowseInfo) _
>> As Long
>>
>> Public Type BrowseInfo
>> hOwner As Long
>> pIDLRoot As Long
>> pszDisplayName As String
>> lpszTitle As String
>> ulFlags As Long
>> lpfn As Long
>> lParam As Long
>> iImage As Long
>> End Type
>>
>> Function GetDirectory(Optional msg) As String
>> On Error Resume Next
>> Dim bInfo As BrowseInfo
>> Dim path As String
>> Dim r As Long, x As Long, pos As Integer
>>
>>  'Root folder = Desktop
>> bInfo.pIDLRoot = 0&
>>
>>  'Title in the dialog
>> If IsMissing(msg) Then
>> bInfo.lpszTitle = "Please select the folder of the excel files to
>> copy."
>> Else
>> bInfo.lpszTitle = msg
>> End If
>>
>>  'Type of directory to return
>> bInfo.ulFlags = &H1
>>
>>  'Display the dialog
>> x = SHBrowseForFolder(bInfo)
>>
>>  'Parse the result
>> path = Space$(512)
>> r = SHGetPathFromIDList(ByVal x, ByVal path)
>> If r Then
>> pos = InStr(path, Chr$(0))
>> GetDirectory = Left(path, pos - 1)
>> Else
>> GetDirectory = ""
>> End If
>> End Function
>>
>> Sub CombineFiles()
>> Dim pathAs String
>> Dim FileNameAs String
>> Dim LastCellAs Range
>> Dim Wkb

$$Excel-Macros$$

[Sandeep Chhajer]

Dear Excel Gurus,

I have got this Macro from this forum for* adding worksheets from different
work book kept in a folder,* but now when i am trying this code I am
getting compiling error message" can not define a public user defined type
with an object module.

Please help.

Thanks in advance.

Option Explicit

 '32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal _
pszpath As String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BrowseInfo) _
As Long

Public Type BrowseInfo
hOwner As Long
pIDLRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Function GetDirectory(Optional msg) As String
On Error Resume Next
Dim bInfo As BrowseInfo
Dim path As String
Dim r As Long, x As Long, pos As Integer

 'Root folder = Desktop
bInfo.pIDLRoot = 0&

 'Title in the dialog
If IsMissing(msg) Then
bInfo.lpszTitle = "Please select the folder of the excel files to
copy."
Else
bInfo.lpszTitle = msg
End If

 'Type of directory to return
bInfo.ulFlags = &H1

 'Display the dialog
x = SHBrowseForFolder(bInfo)

 'Parse the result
path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
GetDirectory = Left(path, pos - 1)
Else
GetDirectory = ""
End If
End Function

Sub CombineFiles()
Dim pathAs String
Dim FileNameAs String
Dim LastCellAs Range
Dim Wkb As Workbook
Dim WS  As Worksheet
Dim ThisWB  As String

ThisWB = ThisWorkbook.Name
Application.EnableEvents = False
Application.ScreenUpdating = False
path = GetDirectory
FileName = Dir(path & "\*.xls", vbNormal)
Do Until FileName = ""
If FileName <> ThisWB Then
Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
For Each WS In Wkb.Worksheets
Set LastCell = WS.Cells.SpecialCells(xlCellTypeLastCell)
If LastCell.Value = "" And LastCell.Address =
Range("$A$1").Address Then
Else
WS.Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next WS
Wkb.Close False
End If
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

Set Wkb = Nothing
Set LastCell = Nothing
End Sub


-- 
Regards,
Sandeep Kumar Chhajer.

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.

$$Excel-Macros$$ Recognizing Number format

[Sandeep Chhajer]

Dear Excel Gurus,

 

I have a peculiar problem. When I import data in excel from Tally it shows
me number as shown below 

 


 

Balance

Debit

Credit

Balance


Aditya Birla Memorial Hospital

3049.00

3049.00

 


Aditya Enterprises

99521.00

101086.00

1565.00 Cr


Airways Corporation

39363.80 Dr

39363.80 Dr


Airways Surgical Pvt Ltd

361446.45 Dr

959168.00

1133676.00

186938.45 Dr


AL-SAMIYAH GROUP FOR MARKETING & INT. TRADE

791326.00

883606.25

92280.25 Cr


Al-Wafigroup for Marketing & Int'l Trade Co.

743036.00

754572.48

11536.48 Cr


 

 

When I check in format menu it shows me ""0.00" Cr"  for NEGETAIVE  value
and ""0.00" Dr" for POSITIVE value.

 

All I want to do is recognise the format with the help of macro and change
the value accordingly.

 

I have tried to develop a macro with my very lesser knowledge but it is not
working as value of  variable "b" is not changing according to its present
(changing) location.  

Please help.

 

My VBA Working

 

Sub format()

a = Range("e15").NumberFormat

' i want this "b" shall change value according to the new destination i.e.

'   _want to make it as variable according to the new cell format.

b = ActiveCell.NumberFormat

Do

ActiveCell.Offset(1, 0).Select

If a = b Then

ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = ActiveCell.Offset(0, -1) * -1

ActiveCell.Offset(0, -1).Select

Else

ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = ActiveCell.Offset(0, -1) * 1

ActiveCell.Offset(0, -1).Select

End If

Loop Until ActiveCell.Offset(0, -4).Value = ""

End Sub

 

Thanks in advance.

 

 

 

Thank you
Regards


Sandeep Kumar Chhajer.

  

 

 

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.


dr cr.xlsb
Description: application/vnd.ms-excel.sheet.binary.macroenabled.12

RE: $$Excel-Macros$$ Identifying Excel number format

[Sandeep Chhajer]

Dear Xltime,

 

 

The macro is working fine but the loop it is not going down to the last used
cell it is working only up to 1st blank cell can you please change the code
so as to make it run up to last used cell.

 

Thanks in advance.

 

Thank you
Regards


Sandeep Kumar Chhajer.

  

 

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of xlstime
Sent: 05 October 2013 16:59
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Identifying Excel number format

 

c

an you please example your requirement 

please find attachment hope it fulfill your requirement 




.

Enjoy
Team XLS

 

On Sat, Oct 5, 2013 at 3:14 PM, Sandeep Chhajer mailto:chhajersand...@gmail.com> > wrote:

PFA the original file. Hope it will work.

 

On 5 October 2013 13:00, ashish koul mailto:koul.ash...@gmail.com> > wrote:

i can't see custom format

 

On Sat, Oct 5, 2013 at 12:59 PM, Sandeep Kumar Chhajer
mailto:chhajersand...@gmail.com> > wrote:

Dear Ashish,

This is only original file, I have just cut down the number of entries to
certain extent as I am checking my code with lesser data.

Sent on my BlackBerryR from Vodafone

  _  

From: ashish koul mailto:koul.ash...@gmail.com> > 

Sender: excel-macros@googlegroups.com <mailto:excel-macros@googlegroups.com>


Date: Sat, 5 Oct 2013 12:55:31 +0530

To: excel-macrosmailto:excel-macros@googlegroups.com> >

ReplyTo: excel-macros@googlegroups.com
<mailto:excel-macros@googlegroups.com>  

Subject: Re: $$Excel-Macros$$ Identifying Excel number format

 

can u share the orginal file 

 

On Sat, Oct 5, 2013 at 12:53 PM, Sandeep Chhajer mailto:chhajersand...@gmail.com> > wrote:

Dear Excel Gurus,

 

I have facing a peculiar problem related to number format.

When I export any ledger from tally I got result as bellow

 

 

 


Aditya Birla Memorial Hospital

3049.00

3049.00

 


Aditya Enterprises

99521.00

101086.00

1565.00 Cr


Airways Corporation

39363.80 Dr

39363.80 Dr


Airways Surgical Pvt Ltd

361446.45 Dr

959168.00

1133676.00

186938.45 Dr


AL-SAMIYAH GROUP FOR MARKETING & INT. TRADE

791326.00

883606.25

92280.25 Cr


Al-Wafigroup for Marketing & Int'l Trade Co.

743036.00

754572.48

11536.48 Cr


 

   Now, the custom number format for negative number is ""0.00" Cr" and for
positive number is ""0.00" Dr". (I have also attached herewith the file for
your reference.)

 

Again, I have tried to build a macro with my limited VBA knowledge but it is
not working as variable "b" is not changing according to the current cursor
location.

I am pasting what i have tried.

 

 

Sub format()

a = Range("e15").NumberFormat

' i want this "b" shall change value according to the new destination i.e.

'   _want to make it as variable according to the new cell format.

b = ActiveCell.NumberFormat

Do

ActiveCell.Offset(1, 0).Select

If a = b Then

ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = ActiveCell.Offset(0, -1) * -1

ActiveCell.Offset(0, -1).Select

Else

ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = ActiveCell.Offset(0, -1) * 1

ActiveCell.Offset(0, -1).Select

End If

Loop Until ActiveCell.Offset(0, -4).Value = ""

End Sub 

 

Please Help me out.

 

Thanks in advance.

 


 

-- 

Regards,
Sandeep Kumar Chhajer.

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
 
FORUM RULES
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
 
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups
"MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to excel-macros+unsubscr...@googlegroups.com
<mailto:excel-macros%2bunsubscr...@googlegroups.com> .
To post to this group, send email to excel-macros@googlegroups.com
<mailto:excel-macros@googlegroups.com> .
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.





 

-- 

Regards

 

Ashish Koul

 

 

Visit

 <http://www.excelvb

Re: $$Excel-Macros$$ Identifying Excel number format

[Sandeep Chhajer]

Dear De Premor,

Sorry for the late reply. Your code is running perfectly.

Thanks for the code.


On 5 October 2013 18:22, De Premor  wrote:

>  try this
>
> Sub xcv()
> Dim Data As Range, Rng As Range
>
> Set Data = Range("A6")
>
> Set Data = Range(Data, Data.End(xlDown)).Offset(, 4)
> For Each Rng In Data
> Rng.Offset(, 2) = Rng * IIf(InStr(1, Rng.NumberFormat, "Cr") > 0,
> -1, 1)
> Next
> End Sub
>
>
>  Pada 05/10/2013 14:23, Sandeep Chhajer menulis:
>
> Dear Excel Gurus,
>
>  I have facing a peculiar problem related to number format.
> When I export any ledger from tally I got result as bellow
>
>
>
> Aditya Birla Memorial Hospital
>  3049.00 3049.00Aditya Enterprises
>  99521.00 101086.00 *1565.00 Cr*  Airways Corporation 39363.80 Dr
>
>  *39363.80 Dr*  Airways Surgical Pvt Ltd 361446.45 Dr 959168.00 1133676.00
> *186938.45 Dr*  AL-SAMIYAH GROUP FOR MARKETING & INT. TRADE
>  791326.00 883606.25 *92280.25 Cr*  Al-Wafigroup for Marketing & Int'l
> Trade Co.
>  743036.00 754572.48 *11536.48 Cr*
>
>
>
>
>Now, the custom number format for negative number is *""0.00" Cr"* and
> for positive number is *""0.00" Dr". (I have also attached herewith the
> file for your reference.)*
> *
> *
> Again, I have tried to build a macro with my limited VBA knowledge but it
> is not working as variable "b" is not changing according to the current
> cursor location.
> I am pasting what i have tried.
>
>
>  Sub format()
> a = Range("e15").NumberFormat
> ' *i want this "b" shall change value according to the new destination
> i.e.*
> *'   _want to make it as variable according to the new cell format.*
> b = ActiveCell.NumberFormat
> Do
> ActiveCell.Offset(1, 0).Select
> If a = b Then
> ActiveCell.Offset(0, 1).Select
> ActiveCell.Formula = ActiveCell.Offset(0, -1) * -1
>  ActiveCell.Offset(0, -1).Select
> Else
> ActiveCell.Offset(0, 1).Select
> ActiveCell.Formula = ActiveCell.Offset(0, -1) * 1
> ActiveCell.Offset(0, -1).Select
> End If
> Loop Until ActiveCell.Offset(0, -4).Value = ""
> End Sub
> *
> *
> Please Help me out.
>
>  Thanks in advance.
>
>
>  --
> Regards,
> Sandeep Kumar Chhajer.
>
>   --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>  --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and sto

Re: $$Excel-Macros$$ Identifying Excel number format

[Sandeep Chhajer]

PFA the original file. Hope it will work.


On 5 October 2013 13:00, ashish koul  wrote:

> i can't see custom format
>
>
> On Sat, Oct 5, 2013 at 12:59 PM, Sandeep Kumar Chhajer <
> chhajersand...@gmail.com> wrote:
>
>> **
>> Dear Ashish,
>>
>> This is only original file, I have just cut down the number of entries to
>> certain extent as I am checking my code with lesser data.
>>
>> Sent on my BlackBerry® from Vodafone
>> --
>> *From: * ashish koul 
>> *Sender: * excel-macros@googlegroups.com
>> *Date: *Sat, 5 Oct 2013 12:55:31 +0530
>> *To: *excel-macros
>> *ReplyTo: * excel-macros@googlegroups.com
>> *Subject: *Re: $$Excel-Macros$$ Identifying Excel number format
>>
>> can u share the orginal file
>>
>>
>> On Sat, Oct 5, 2013 at 12:53 PM, Sandeep Chhajer <
>> chhajersand...@gmail.com> wrote:
>>
>>> Dear Excel Gurus,
>>>
>>> I have facing a peculiar problem related to number format.
>>> When I export any ledger from tally I got result as bellow
>>>
>>>
>>>
>>>Aditya Birla Memorial Hospital 3049.00 3049.00Aditya Enterprises
>>> 99521.00 101086.00 *1565.00 Cr*  Airways Corporation 39363.80 Dr  *39363.80
>>> Dr*  Airways Surgical Pvt Ltd 361446.45 Dr 959168.00 1133676.00 *186938.45
>>> Dr*  AL-SAMIYAH GROUP FOR MARKETING & INT. TRADE 791326.00 883606.25 
>>> *92280.25
>>> Cr*  Al-Wafigroup for Marketing & Int'l Trade Co. 743036.00 754572.48 
>>> *11536.48
>>> Cr*
>>>Now, the custom number format for negative number is *""0.00" Cr"* and
>>> for positive number is *""0.00" Dr". (I have also attached herewith the
>>> file for your reference.)*
>>> *
>>> *
>>> Again, I have tried to build a macro with my limited VBA knowledge but
>>> it is not working as variable "b" is not changing according to the current
>>> cursor location.
>>> I am pasting what i have tried.
>>>
>>>
>>> Sub format()
>>> a = Range("e15").NumberFormat
>>> ' *i want this "b" shall change value according to the new destination
>>> i.e.*
>>> *'   _want to make it as variable according to the new cell format.*
>>> b = ActiveCell.NumberFormat
>>> Do
>>> ActiveCell.Offset(1, 0).Select
>>> If a = b Then
>>> ActiveCell.Offset(0, 1).Select
>>> ActiveCell.Formula = ActiveCell.Offset(0, -1) * -1
>>> ActiveCell.Offset(0, -1).Select
>>> Else
>>> ActiveCell.Offset(0, 1).Select
>>> ActiveCell.Formula = ActiveCell.Offset(0, -1) * 1
>>> ActiveCell.Offset(0, -1).Select
>>> End If
>>> Loop Until ActiveCell.Offset(0, -4).Value = ""
>>> End Sub
>>> *
>>> *
>>> Please Help me out.
>>>
>>> Thanks in advance.
>>>
>>>
>>> --
>>> Regards,
>>> Sandeep Kumar Chhajer.
>>>
>>>  --
>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>> https://www.facebook.com/discussexcel
>>>
>>> FORUM RULES
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>> 2) Don't post a question in the thread of another member.
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>> 4) Acknowledge the responses you receive, good or bad.
>>> 5) Jobs posting is not allowed.
>>> 6) Sharing copyrighted material and their links is not allowed.
>>>
>>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>>> members are not responsible for any loss.
>>> ---
>>> You received this message because you are subscribed to the Google
>>> Groups "MS EXCEL AND VBA MACROS" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to excel-macros+unsubscr...@googlegroups.com.
>>> To post to this group, send email to excel-macros@googlegroups.com.
>>> Visit this group at http://groups.google.com/group/excel-macros.
>>> For more options, visit https://groups.google.com/groups/opt_out.
>>>
>>
>&

$$Excel-Macros$$ Identifying Excel number format

[Sandeep Chhajer]

Dear Excel Gurus,

I have facing a peculiar problem related to number format.
When I export any ledger from tally I got result as bellow



   Aditya Birla Memorial Hospital 3049.00 3049.00Aditya Enterprises
99521.00 101086.00 *1565.00 Cr*  Airways Corporation 39363.80 Dr  *39363.80
Dr*  Airways Surgical Pvt Ltd 361446.45 Dr 959168.00 1133676.00 *186938.45
Dr*  AL-SAMIYAH GROUP FOR MARKETING & INT. TRADE 791326.00 883606.25 *92280.25
Cr*  Al-Wafigroup for Marketing & Int'l Trade Co. 743036.00 754572.48 *11536.48
Cr*
   Now, the custom number format for negative number is *""0.00" Cr"* and
for positive number is *""0.00" Dr". (I have also attached herewith the
file for your reference.)*
*
*
Again, I have tried to build a macro with my limited VBA knowledge but it
is not working as variable "b" is not changing according to the current
cursor location.
I am pasting what i have tried.


Sub format()
a = Range("e15").NumberFormat
' *i want this "b" shall change value according to the new destination i.e.*
*'   _want to make it as variable according to the new cell format.*
b = ActiveCell.NumberFormat
Do
ActiveCell.Offset(1, 0).Select
If a = b Then
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = ActiveCell.Offset(0, -1) * -1
ActiveCell.Offset(0, -1).Select
Else
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = ActiveCell.Offset(0, -1) * 1
ActiveCell.Offset(0, -1).Select
End If
Loop Until ActiveCell.Offset(0, -4).Value = ""
End Sub
*
*
Please Help me out.

Thanks in advance.


-- 
Regards,
Sandeep Kumar Chhajer.

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.


dr cr.xlsb
Description: Binary data

RE: $$Excel-Macros$$ Want to add every third row or column!

[Sandeep Chhajer]

Please find the attached file.

 

Thank you
Regards


Sandeep Kumar Chhajer.

  

 

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of VBA VABZ
Sent: 04 June 2013 10:44
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Want to add every third row or column!

 

Hi

 

Send sample file for more clarity

 

Thanks

 

On Tue, Jun 4, 2013 at 9:51 AM,  wrote:

Dear expert,
Many time I have to add every third or every forth or every second row or
column  etc...can you suggest a function  which will ease my problem! Till
now I am adding required cell one by one!

Kindly help!

Sandeep
Sent on my BlackBerryR from Vodafone

--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups
"MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to excel-macros+unsubscr...@googlegroups.com
 .
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

 

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
 
FORUM RULES
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
 
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups
"MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




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

RE: $$Excel-Macros$$ Required tutorials to learn Basics of Dashboards.

[Sandeep Chhajer]

Dear Excel gurus,

How to use this tutorials? Nothing has been provided in this to make excel
dashboards.

 

Thank you
Regards


Sandeep Kumar Chhajer.

  

 

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of NOORAIN ANSARI
Sent: 08 November 2012 11:28
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Required tutorials to learn Basics of
DashBoards.

 

Dear Sandeep,

Please find attached Dashboard tutorial pdf.
Hope it will help to you.



On Thu, Nov 8, 2012 at 10:23 AM,  wrote:

Dear Excel Gurus,
Recently I have came to know about excel dashboard and its potential while
presenting reports. Can any one send me some tutorials for the beginners
like me.
Thanking you in advance.

Sandeep Chhajer.
Sent on my BlackBerryR from Vodafone

--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel

FORUM RULES (1120+ members already BANNED for violation)

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

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

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

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

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

6) Jobs posting is not allowed.

7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.

NOTE  : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups
"MS EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com
<mailto:excel-macros%2bunsubscr...@googlegroups.com> .
Visit this group at http://groups.google.com/group/excel-macros?hl=en.






-- 
With Regards,
Noorain Ansari
 <http://www.noorainansari.com> http://noorainansari.com
<http://www.noorainansari.com> 
 <http://www.excelvbaclinic.blogspot.com> http://excelvbaclinic.com
<http://www.excelvbaclinic.blogspot.com>  

 

 

-- 
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
 
FORUM RULES (1120+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
6) Jobs posting is not allowed.
 
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups
"MS EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
 
 

-- 
Join official facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES (1120+ members already BANNED for violation)

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

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

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

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

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

6) Jobs posting is not allowed.

7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to 
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.

RE: $$Excel-Macros$$ Re: WANT TO GET BOX MARGIN IN EVERY SHEET

[Sandeep Chhajer]

Dear sir,

How to insert a line in footer.

 

Thank you
Regards


Sandeep Kumar Chhajer.

  

 

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Rajan_Verma
Sent: 15 September 2012 20:31
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Re: WANT TO GET BOX MARGIN IN EVERY SHEET

 

Absolutely

 

 

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Vabz
Sent: 15 September 2012 8:27
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Re: WANT TO GET BOX MARGIN IN EVERY SHEET

 

Hi

 

I dont know customisation for footer, however you can create Image of data
you want & Insert in footer..

 

Cheerz..

 

Rgds//Vabs


On Saturday, September 15, 2012 7:27:45 PM UTC+5:30, sandeep chhajer wrote:

Dear all,

 

I want to get box to be printed in every sheet.

Now with "repeat rows" I can get only upper margin. I want it to be same for
lower margin to enclose the whole script in a box for a page. Please help.  

 

Thank you
Regards


Sandeep Kumar Chhajer.

  

 

 

-- 
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
 
FORUM RULES (1120+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
6) Jobs posting is not allowed.
 
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups
"MS EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
 
 

-- 
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
 
FORUM RULES (1120+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
6) Jobs posting is not allowed.
 
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups
"MS EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
 
 

-- 
Join official facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES (1120+ members already BANNED for violation)

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

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

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

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

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

6) Jobs posting is not allowed.

7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to 
excel-macros+unsubscr...@googlegroups.com.

$$Excel-Macros$$ WANT TO GET BOX MARGIN IN EVERY SHEET

[Sandeep Chhajer]

Dear all,

 

I want to get box to be printed in every sheet.

Now with "repeat rows" I can get only upper margin. I want it to be same for
lower margin to enclose the whole script in a box for a page. Please help.  

 

Thank you
Regards


Sandeep Kumar Chhajer.

  

 

 

-- 
Join official facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES (1120+ members already BANNED for violation)

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

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

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

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

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

6) Jobs posting is not allowed.

7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to 
excel-macros+unsubscr...@googlegroups.com.




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

$$Excel-Macros$$ macro to make unlocked cell value zero

[Sandeep Chhajer]

Dear Excel Guru,

I have a protected sheet where some cells are unprotected.

i want a macro which can make all unprotected cells to zero value.

Thanking you in advance.

-- 
Regards,
Sandeep Kumar Chhajer.

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

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

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

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

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

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

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

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

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

Re: $$Excel-Macros$$ Apportioned of Expenses

[Sandeep Chhajer]

Please find.

On 18 May 2012 15:34, Sandeep Chhajer  wrote:

> Dear Excel Guru,
>
> Please find the attheche file.
>
>   No of Student FSLE FSB FSC FSPA Total  My Ans should be 25
> 13 40 10 88  Result requiredEXP  Amount  FSLE   FSB   FSC   FSPA
> Printing  23,456  Yes Yes Yes Yes 6,664   3,465
>  10,662   2,665  Fees344,567  Yes Yes
> 84,516260,051Salary 2,345,098   Yes  
> 2,345,098
>other  56,743  Yes Yes   32,072   24,671  
> Expenses
> needs to be apportined with respect to yes written adjoing col.
>
> --
> Regards,
> Sandeep Kumar Chhajer.
> Ph. no: 07498171901
>09339549906
>
> Wish you all happy new Year 2011.
>
>  --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Regards,
Sandeep Kumar Chhajer.
Ph. no: 07498171901
   09339549906

Wish you all happy new Year 2011.

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

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

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

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

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

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

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

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

$$Excel-Macros$$ Apportioned of Expenses

[Sandeep Chhajer]

Dear Excel Guru,

Please find the attheche file.

  No of Student FSLE FSB FSC FSPA Total  My Ans should be 25 13
40 10 88  Result requiredEXP  Amount  FSLE   FSB   FSC   FSPA
Printing  23,456  Yes Yes Yes Yes 6,664
3,465
10,662   2,665  Fees344,567  Yes Yes
84,516
260,051Salary 2,345,098   Yes  2,345,098
other
56,743  Yes Yes   32,072   24,671  Expenses needs to be
apportined with respect to yes written adjoing col.

-- 
Regards,
Sandeep Kumar Chhajer.
Ph. no: 07498171901
   09339549906

Wish you all happy new Year 2011.

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

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

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

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

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

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

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

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


Apportioned of EXP.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

$$Excel-Macros$$ Re: Macro for Insert a line for missing serial numbers.

[Sandeep Chhajer]

Dear Damimkader,

while I am running this code I am getting the follwing error code
run time error 13

type mismatch

please help.


My problem has been reproduced

   My current Data is like thisSr. No. S_DISNO S_DISC S_UNIT S_SELP Purchse
quantity  495061   358 SILK DUPPATTA 0.90m 260.00 36.00  495062   359 SILK
DUPPATTA 1.15m 385.00 33.00  495063   351 SILK DOTHI   8 cu 3.6
m 2645.00 4.00  495067   351 SILK DOTHI   8 cu 3.6 m 2745.00
3.00  495070   351 SILK DOTHI   8 cu 3.6 m 2965.00 3.00


I want my data as follows

  Sr. No. S_DISNO S_DISC S_UNIT S_SELP Purchse quantity  495061   358 SILK
DUPPATTA 0.90m 260.00 36.00  495062   359 SILK DUPPATTA 1.15m 385.00 33.00
495063   351 SILK DOTHI   8 cu 3.6 m 2645.00 4.00  495064
 351 SILK
DOTHI   8 cu 3.6 m 2645.00 4.00  495065   351 SILK
DOTHI
8 cu 3.6 m 2645.00 4.00  495066   351 SILK DOTHI   8 cu 3.6 m
2645.00 4.00  495067   351 SILK DOTHI   8 cu 3.6 m 2745.00 3.00
495068   351 SILK DOTHI   8 cu 3.6 m 2745.00 3.00  495069
 351 SILK
DOTHI   8 cu 3.6 m 2745.00 3.00  495070   351 SILK DOTHI
8 cu 3.6 m 2965.00 3.00



Copy the previous line only sr no has chamged by adding 1





On 22 March 2012 02:26, Damimkader S. Meeran  wrote:

> Hi Sandeep,
>
> My code below assumes the following
>
> 1. The serial numbers are in column A
> 2. The Excel version you are using is 2007.
> 3. The code is good for 10,000 rows. Please modify if required.
>
> Sub Serial()
> Dim i As Long
> For i = 1 To 1
>  If Cells(i, 1) <> "" And Cells(i + 1, 1) <> "" Then
> If Cells(i + 1, 1) - Cells(i, 1) > 1 Then
> Rows(i + 1 & ":" & i + 1).Select
> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
> i = i + 1
> End If
>  Else
> Exit For
>  End If
> Next i
> End Sub
>
>
>
> On Wednesday, March 21, 2012 9:40:37 PM UTC+5:30, sandeep chhajer wrote:
>>
>> Dear all,
>>
>> Please find my attachment ( I know asking question in this way is a
>>  violation of our forum rule but sorry I don't have data card to copy the
>> queries from excel)
>>
>> Thanking you in advance.
>>
>> Sandeep Chhajer.
>>
>>
> On Wednesday, March 21, 2012 9:40:37 PM UTC+5:30, sandeep chhajer wrote:
>>
>> Dear all,
>>
>> Please find my attachment ( I know asking question in this way is a
>>  violation of our forum rule but sorry I don't have data card to copy the
>> queries from excel)
>>
>> Thanking you in advance.
>>
>> Sandeep Chhajer.
>>
>>


-- 
Regards,
Sandeep Kumar Chhajer.

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

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

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

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

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

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

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

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

$$Excel-Macros$$ Re: Auto create of sheet with new name in a sheet

[Sandeep Chhajer]

Thanks krish its working great.

Sandeep

On 29 December 2011 19:22, Kris  wrote:

> Hi,
>
> Put this code in the 'Particular sheet' module. Right click on sheet name
> > view code and paste the code there.
>
> Also I set the range D2 where you enter the name of the product. adjust
> this range in code if necessary.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Const ProductNameCell   As String = "D2" '<<== adjust this range to
> suit
>
> If Target.Address(0, 0) <> ProductNameCell Then Exit Sub
>
> Dim wksNew  As Worksheet
>
> On Error Resume Next
> Set wksNew = Worksheets(CStr(Target.Value))
> If Err.Number = 0 Then
> MsgBox "Sheet '" & Target.Value & "' already exists", vbCritical +
> vbInformation
> Application.EnableEvents = False
> Target.ClearContents
> Application.EnableEvents = True
> Exit Sub
> Else
> Err.Clear
> Worksheets.Add
> ActiveSheet.Name = Target.Value
> End If
>
> End Sub
>
> Kris
>
>


-- 
Regards,
Sandeep Kumar Chhajer.
Ph. no: 07498171901
   09339549906

Wish you all happy new Year 2011.

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

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

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

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

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

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

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

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

Re: $$Excel-Macros$$ Need to copy of 1st sheet of every workbook in a single work book

[sandeep chhajer]

Dear Sir,

Sorry for the late reply. Its still not working.

On 3 November 2011 13:16, ashish koul  wrote:

>  Sub copy_first_sheet_from_different_workbooks_to_single()
> Application.DisplayAlerts = False
> Application.ScreenUpdating = False
> Dim fld As Object, fil As Object
> Dim ask As Workbook, ask2 As Workbook
> With Application.FileDialog(msoFileDialogFolderPicker)
> .Title = "Choose the folder"
> .InitialFileName = "c:\"
> .Show
> End With
> fldpath =
> Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
> Set fso = CreateObject("scripting.filesystemobject")
> Set fld = fso.getfolder(fldpath)
> Set ask = Workbooks.Add
> For Each fil In fld.Files
> If UCase(Right(fil.Path, 4)) = UCase(".xls") Or UCase(Right(fil.Path, 5))
> = UCase(".xlsx") Then
> Set ask2 = Workbooks.Open(fil.Path)
> ask2.Sheets(1).UsedRange.Copy
> ask.Activate
> ask.Sheets.Add after:=Sheets(Sheets.Count)
> ActiveSheet.Range("a1").Select
> Selection.PasteSpecial xlValues
>  ask2.Close
> End If
> Next fil
> Application.DisplayAlerts = True
> Application.ScreenUpdating = True
> End Sub
>
>  On Thu, Nov 3, 2011 at 1:06 PM,  wrote:
>
>> **
>> My files are saved in .xls format. But I am working in .xlxs.
>>
>>
>> Sandeep Chhajer.
>> Sent on my BlackBerryŽ from Vodafone
>> --
>> *From: *ashish koul 
>> *Sender: *excel-macros@googlegroups.com
>> *Date: *Thu, 3 Nov 2011 13:04:02 +0530
>> *To: *
>> *ReplyTo: *excel-macros@googlegroups.com
>> *Subject: *Re: $$Excel-Macros$$ Need to copy of 1st sheet of every
>> workbook in a single work book
>>
>> in which format ur files are saved  .xls or xlsx
>>
>>
>>
>> On Thu, Nov 3, 2011 at 11:56 AM, sandeep chhajer <
>> chhajersand...@gmail.com> wrote:
>>
>>>  Dear Ashish,
>>>
>>> I have copied it but when i am running the code it showing error msg pls
>>> help.
>>>
>>>  On 3 November 2011 10:53, ashish koul  wrote:
>>>
>>>> Sub copy_first_sheet_from_different_workbooks_to_single()
>>>> Application.DisplayAlerts = False
>>>> Application.ScreenUpdating = False
>>>>
>>>> Dim fld As Object, fil As Object
>>>> Dim ask As Workbook, ask2 As Workbook
>>>>
>>>> With Application.FileDialog(msoFileDialogFolderPicker)
>>>> .Title = "Choose the folder"
>>>> .InitialFileName = "c:\"
>>>> .Show
>>>> End With
>>>> fldpath =
>>>> Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
>>>> Set fso = CreateObject("scripting.filesystemobject")
>>>>
>>>> Set fld = fso.getfolder(fldpath)
>>>>
>>>> Set ask = Workbooks.Add
>>>>
>>>> For Each fil In fld.Files
>>>> If UCase(Right(fil.Path, 4)) = UCase(".xls") Or UCase(Right(fil.Path,
>>>> 5)) = UCase(".xlsx") Then
>>>> Set ask2 = Workbooks.Open(fil.Path)
>>>> ask2.Sheets(1).Copy After:=ask.Sheets(Sheets.Count)
>>>> ask2.Close
>>>>
>>>> End If
>>>> Next fil
>>>>
>>>> Application.DisplayAlerts = True
>>>> Application.ScreenUpdating = True
>>>> End Sub
>>>>
>>>> On Wed, Nov 2, 2011 at 3:43 PM, mrinal saha  wrote:
>>>>
>>>>> Hi Sandeep,
>>>>>
>>>>> Save this attachment in the folder where you have all the workbooks
>>>>> and try to run the macro (Button) on page 1 of this file.
>>>>>
>>>>> Hope this helps,
>>>>>
>>>>> Mrinal
>>>>>
>>>>>  On Wed, Nov 2, 2011 at 2:08 PM,  wrote:
>>>>>
>>>>>> Dear all,
>>>>>>
>>>>>> I have many work book in a single folder. I need a macro which if I
>>>>>> run will copy the 1st sheet of every workbook in a single workbook 
>>>>>> (though
>>>>>> as separate sheet).
>>>>>>
>>>>>> Like if I have 2 workbook viz WB1 and WB2 then I want the 1st
>>>>>> worksheet of WB1 as 1st worksheet in a new workbook (say WB3) and again 
>>>>>> 1st
>>>>>> worksheet of WB2 as 2nd worksheet of WB3.
>>>>>>
>>>>>>
>>>>>> Thanks in advance.

Re: $$Excel-Macros$$ Need to copy of 1st sheet of every workbook in a single work book

[sandeep chhajer]

Dear Ashish,

I have copied it but when i am running the code it showing error msg pls
help.

On 3 November 2011 10:53, ashish koul  wrote:

> Sub copy_first_sheet_from_different_workbooks_to_single()
> Application.DisplayAlerts = False
> Application.ScreenUpdating = False
>
> Dim fld As Object, fil As Object
> Dim ask As Workbook, ask2 As Workbook
>
> With Application.FileDialog(msoFileDialogFolderPicker)
> .Title = "Choose the folder"
> .InitialFileName = "c:\"
> .Show
> End With
> fldpath =
> Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
> Set fso = CreateObject("scripting.filesystemobject")
>
> Set fld = fso.getfolder(fldpath)
>
> Set ask = Workbooks.Add
>
> For Each fil In fld.Files
> If UCase(Right(fil.Path, 4)) = UCase(".xls") Or UCase(Right(fil.Path, 5))
> = UCase(".xlsx") Then
> Set ask2 = Workbooks.Open(fil.Path)
> ask2.Sheets(1).Copy After:=ask.Sheets(Sheets.Count)
> ask2.Close
>
> End If
> Next fil
>
> Application.DisplayAlerts = True
> Application.ScreenUpdating = True
> End Sub
>
> On Wed, Nov 2, 2011 at 3:43 PM, mrinal saha  wrote:
>
>> Hi Sandeep,
>>
>> Save this attachment in the folder where you have all the workbooks and
>> try to run the macro (Button) on page 1 of this file.
>>
>> Hope this helps,
>>
>> Mrinal
>>
>>  On Wed, Nov 2, 2011 at 2:08 PM,  wrote:
>>
>>> Dear all,
>>>
>>> I have many work book in a single folder. I need a macro which if I run
>>> will copy the 1st sheet of every workbook in a single workbook (though as
>>> separate sheet).
>>>
>>> Like if I have 2 workbook viz WB1 and WB2 then I want the 1st worksheet
>>> of WB1 as 1st worksheet in a new workbook (say WB3) and again 1st worksheet
>>> of WB2 as 2nd worksheet of WB3.
>>>
>>>
>>> Thanks in advance.
>>> Sandeep Chhajer.
>>> Sent on my BlackBerryŽ from Vodafone
>>>
>>> --
>>> FORUM RULES (925+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5)  Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE  : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> --
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>
>> --
>> FORUM RULES (925+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *http://www.excelvbamacros.com/*
>
>
> P Before printing, think about the environment.
>
>
> --
> FORUM RULES (925+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don

Re: $$Excel-Macros$$ use of indirect function

[sandeep chhajer]

Dear Noorain,

I have gone through your attachment and find it of a graet help. I hope you
wont mind to guide us in a *step by step* *action to be done* for your all
four examples.

Thanking you in advance.


On 25 July 2011 23:14, NOORAIN ANSARI  wrote:

> Dear Neil,
>
> Please see attached sheet with Indirect function example
>
> --
> Thanks & regards,
> Noorain Ansari
> *http://noorain-ansari.blogspot.com/*
>
>
> On Mon, Jul 25, 2011 at 9:04 PM, neil johnson 
> wrote:
>
>> Hi All,
>> Is indirect function is good function to make dashboard. Please explain
>> about indirect function and provide me some example. how can i use indirect
>> function while to make dashboard.
>>
>> Thanks
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post 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,
Sandeep Kumar Chhajer.
Ph. no: 07498171901

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

[sandeep chhajer]

dear excel guru,

thanks for the reply and sorry for late reply.


the macro only searches excel files containing the specified word but
not pdf or doc file.I do need the same for pdf and doc file.

thanks in advance.

On 08/06/2011, ashish koul  wrote:
> try this
>
> rename sheet to rename files
>
> sheet 1 to search file name using content
>
>
>
> On Tue, Jun 7, 2011 at 10:40 PM,  wrote:
>
>> To excel gurus
>> Pls do something Its become a nightmare sir to me.
>>
>> Sent on my BlackBerry® from Vodafone
>> --
>> *From: *chhajersand...@gmail.com
>> *Date: *Mon, 6 Jun 2011 18:25:17 +
>>  *To: *
>> *ReplyTo: *chhajersand...@gmail.com
>> *Subject: *Re: $$Excel-Macros$$
>>
>> Thanks for replydivya is content of the file..
>>
>> Sent on my BlackBerry® from Vodafone
>> --
>> *From: *"Rajan_Verma" 
>>  *Sender: *excel-macros@googlegroups.com
>> *Date: *Mon, 6 Jun 2011 20:09:04 +0530
>>  *To: *
>>  *ReplyTo: *excel-macros@googlegroups.com
>> *Subject: *RE: $$Excel-Macros$$
>>
>>  I Think he want to search the content in file..
>>
>>
>>
>> *From:* excel-macros@googlegroups.com [mailto:
>> excel-macros@googlegroups.com] *On Behalf Of *ashish koul
>>
>> *Sent:* Monday, June 06, 2011 8:07 PM
>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$
>>
>>
>>
>> you want to search word divya is name of file or in content of file
>>
>>  On Mon, Jun 6, 2011 at 6:50 PM,  wrote:
>>
>> Dear excel guru,
>>
>> I have a unique problem. Few days ago my hard disk was crashed. Now when I
>> have recovered all the data,instead of file name assigned to those file,
>> system has assigned file no.(i.e. system generated code to every file) I
>> have 6 pdf file to rename.
>> Is there any way to write macro which can find a particular word and give
>> me the name of such files which contains that particular word! Like if in
>> the search criteria I write divya ltd. It will give the all the names of
>> the
>> file in excel containing word divya ltd.
>>
>> Thanks in advance.
>> Sent on my BlackBerry® from Vodafone
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post 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
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.

Re: $$Excel-Macros$$ need Formula Correction !!!!!!!!!!!!!

[sandeep chhajer]

For contact person ur formula should be
=VLOOKUP($F$2,DATA!$B:$G,3,0)

And so on.
i.e instead of A it shoul start from B.as unique value is in col. B.

Hope it will haelp u.

On 17 January 2011 12:16, jocky Beta  wrote:

>
> Hi,
>
>  I made a Excel sheet with help of V lookup using the Data validation, but
> my formula shows unknown error. hence pls explain me the step by
> step procedure for the same.
>
>
> Regards
> Jocky
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post 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,
Sandeep Kumar Chhajer.
Ph. no: 07498171901
   09339549906

Wish you all happy new Year 2011.

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

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