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

[Balamurali]

Hi All,

Thanks Chandoo......

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.

expecting a macro which accomplish my task.

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

 

[Smallman]

Hi Balamurali

Welcome to Chandoo

I am not sure why you are so wedded to your active cell but I think from looking at your recorded code that the following is what you are trying to achieve.

Option Explicit
 
Sub Allocate()
Dim ar As Variant
 
ar = [{"Name1", "Name2", "Comments"}]
 
ActiveCell.Resize(, 3).Value = ar
Sheet1.Range("H2", Sheet1.Range("I65536").End(xlUp)).Copy
ActiveCell.Offset(1).PasteSpecial xlPasteValues
End Sub

Hope that hits the mark.

Take care

Smallman