Need modification in recorded macro, to desired total number of data

[bmbalamurali]

Hi All,

Please find my attached example excel...where i have some data which are need to be allocated to different persons in different numbers.. so i used index match function to get it and i also recorded my macro to fill Name1 and Name2 column....

Why i used relative reference macro recording is because the column H and I where i placed my formula may vary due data sufficiency.... it may change to L and M column or E and F column.. so i need the macro should run anywhere i place the cell selected.

Difficulty i face is the number of data ie: in example excel i have 34 data so i auto filled the formula to all data.

Daily i have different number of data, that is total number of data is found in Sheet2 in B14 and E14...i cant record a macro to auto fill the desired range that is total number of data (rows).

What i need is if i run the macro it should apply the formula and fill the formulas to the total number of data that changes every time... and all formula cells should be pasted special value.

Thanks in advance... Expecting a urgent help...

[patel45]

Please Login or Register  to view this content.

[bmbalamurali]

Hi Patel,

Thanks... looks good...

When i tried it showing run time error "1004"Autofill method of range class failed... and when i click debug it highlights the below line in Blue...

Kindly look and provide me solution... Thanks in Advance...

Please Login or Register  to view this content.

[patel45]

I did not test your code because:
1) i dont know the sheet you want start the code in
2) your code start with activecell, what cell ? it's very deprecated using activecell without any reference.

[bmbalamurali]

Hi Patel,

Thanks...able to understand the difficulty.... Lets forget the macro i recorded...can you please give a macro solution accomplish my task...

In my attached excel...i have match index formulas to allocate the data to different persons... and what i need is that the macro should post headings name1 and name2 and comments and apply the formula and it should fill up to total number of data with allocated person names...

As my data changes daily i want the macro to perform formulas and allocate to all data with paste value special.... Thanks in advance...

[patel45]

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[bmbalamurali]

Hi Patel,

Thanks same error showing.... am not well versed in coding i cant able to find.

Hi Davis,

Thanks.....for your code...

But actually its not doing the core objective of my need.. its updating something sheet 2 and not allocating names in Sheet1...
I fear is my explanation is totally showed off a wrong overview on need... Actually i needed a macro to allocate Names to certain number of times in sheet 1 based on data available in sheet 2...

Am too much searching internet as a hell.. but cant understand what error comes in Patel code.... Please come back if my explanation not clear... seeking a solution.. Thanks....

[patel45]

I tested my code on your attached file, it works

[bmbalamurali]

Thanks for all your support.... It worked..