Need a macro for naming manager

[kishoremcp]

Hi,
I have an excel file in which I have different headings. I will be keepon adding/deleting multiple columns. So, every time I want to search for headings (Name and Value) and for that entire column I need to define in naming manager as

Please Login or Register  to view this content.

Please help....

Attaching the file for testing...

[kishoremcp]

Any help! Marcol? alur?

[kishoremcp]

I have recorded the macro but the problem is it is searching for Name and naming the column for D because it is recorded in that way. How to use the name manager for the specified search (Ex: Searching for Name then the macro should name the corresponding column only.

The code which I have is

Please Login or Register  to view this content.

[kishoremcp]

Any Help!!!

[kvsrinivasamurthy]

Pl See the attached file with macro.
Macro names the column ranges of name and value as "NameRange" & "ValueRange",irrespective of their position in sheet.

[MarvinP]

Hi kishoremcp,

It sounds like you need a line of VBA code that uses xlToLeft in it. Read
http://spreadsheetpage.com/index.php...e_size_ranges/

[kishoremcp]

Hello Murthy,
Thank you for the reply. You got the concept. But my need is a bit different. If i found the name in column A then in the name manager it shoud give the entire formula

Please Login or Register  to view this content.

.
The same way i have another formula for the values if found in another column. So please customize your macro and help me..

[kvsrinivasamurthy]

If Name is Found in column D what should be formula.

[kishoremcp]

Actually I am matching the name with multiple columns. SO in another sheet till today i am giving the formula after assigning the name manager I am using countifs formula to get my result. The main problem is there are couple of columns which may add and delete but some headings remain same like name. SO if it is name in D the formula is something like

Please Login or Register  to view this content.

please help

[kvsrinivasamurthy]

Pl see the attached file with macro.

[MarvinP]

Hi kishoremcp,

Try this code and see if it does what you want. I think you are trying to define a named range and then insert rows and/or columns and then redefine the range to where it moved. I hope that is the correct assumption for solving your problem.

Please Login or Register  to view this content.

[kishoremcp]

Please find the attached file for which i am looking for and in that file the naming manager has some formulas which i defined manually. The thing is if there are any rows added or deleted to it, in the naming manager again i have to define them. So, i want to make it automatically the way it is and from that i am taking the output in the sheet "Modality". so please go throuth the excel file which i am attaching both the pages along with the manually defined naming managers and help me to make it as an automate. Finally i am looking to automate the naming manager the way it is defined manually in the present file to avoid the changes even after some columns added or deleted. So searching for Name and defining the naming manager with customized formula. Hope you will help me....

[kishoremcp]

any help!!!!

[MarvinP]

I'm clueless after looking at your attached workbook. I get a #Ref on all formulas on your Modality sheet in columns K and L. I can't see what these formulas are supposed to be doing. I don't think you have defined your ranges correctly in the Names Manager.

[kishoremcp]

For the formulas in the name manager i am teling to validate the columns data if it matches with the name list in the second sheet modality. For that i have defined the name manager in that way manually. By doing that it is taking and validating the data from rawdata sheet with the formulas given by me in the name manager and doing the needful countifs for me in the second sheet modality.
I hope i am clear now...
Thanks for your efforts to help me...

[kvsrinivasamurthy]

Pl see the attached fle.
When Rows or Columns are changed, it automatically defines the ranges as required by you in Rawdata Sheet.
Worksheet_Change event is used.
Any clarifications welcome.

[kishoremcp]

Thank you for the reply. It seems that the formula is not the same as I required and I require the same formula which is in the original file and in the same format.

[kishoremcp]

I have worked out something. I have the following code which is searching for "Region" and putting the required formula in naming manager as required. So, Please help me in matching it with the agent name column.

The code is

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Pl see the attached file.
If OK pl mark the thread SOLVED.