VBA to autofill column

[humsboyle]

Hi All,
I am having an issue trying to autofill a column with different data.

Following report is download from our reporting system (unable to attach)

Admin Productivity Report Report executed at: 03/09/2014
Report displaying data between 02/09/2014 and 02/09/2014
		Number of Requests
ADMIN ID	Type of Request	Total Requests	Within24Hrs	Within24Hrs Average %	Over24Hrs	Over24Hrs Average %
EUROPA\Agent1	Task 1	1	1	100.00%		0.00%
Task 2	18	18	100.00%		0.00%
Task 3	2	2	100.00%		0.00%
Task 4	16	7	43.75%	9	56.25%
Task 5	7	7	100.00%		0.00%
Task 6	4	4	100.00%		0.00%
Total	48	39		9
EUROPA\Agent2	Task 1	4	4	100.00%		0.00%
Task 2	7	7	100.00%		0.00%
Task 4	1		0.00%	1	100.00%
Task 7	63	4	6.35%	59	93.65%
Total	75	15		60
EUROPA\Agent3	Task 1	27	27	100.00%		0.00%
Task 2	59	59	100.00%		0.00%
Task 3	1	1	100.00%		0.00%
Task 4	40	8	20.00%	32	80.00%
Task 5	33	33	100.00%		0.00%
Task 8	2	2	100.00%		0.00%
Task 6	13	13	100.00%		0.00%
Total	175	143		32
EUROPA\Agent5	Task 1	27	27	100.00%		0.00%
Task 2	57	57	100.00%		0.00%
Task 3	4	4	100.00%		0.00%
Task 5	56	56	100.00%		0.00%
Task 8	2	2	100.00%		0.00%
Task 6	7	7	100.00%		0.00%
Task 7	22	15	68.18%	7	31.82%
Total	175	168		7
EUROPA\Agent6	Task 4	2	2	100.00%		0.00%
Total	2	2
Grand Total		475	367		108

<COLGROUP><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY>
</TBODY>

Column A contains Staff member details (Merged Cell)
Column B Contains - Tasks worked (changes on daily basis)

First thing i do is Unmerge cells in column A and also insert new column to the left using following code

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 16/09/2014 by boylesw
'
'
    Columns("A:A").Select
    Range("A3").Activate
    With Selection
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Insert Shift:=xlToRight
End Sub

Once that is done i am then looking to autofill each staff name so that it covers all of the tasks completed by agent.
When i do this manually - each time i autofill it stops at the new name which is great but i am unable to replicate use VBA.

I have the following code which will highlight each new name

Sub test()
Range("B5").Select
Selection.End(xlDown).Offset(0, 0).Select
End Sub

i would use the above multiple times to select cell to autofill down but i am unable to find / create VBA for the autofilling


Any help would be creatly appreicated

Regards
Steve

 

[humsboyle]

I have notcied after posting that the columns go out of sinc - just to confirm tasks should be under "Type of Request Column"

 

[VoG]

Assuming that you want to fill blanks in column B

Sub b()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
With Range("B1:B" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub

 

[humsboyle]

Hi VoG

That works great - thank you very much.
Going to be pain now - but what does each part do?? i am trying to learn as much as i can about VBA

Steve

 

[VoG]

ADVERTISEMENT

Here with comments

Sub b()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'Highest filled row number in the sheet
With Range("B1:B" & LR) 'In the range B1 to the last filled row in column B
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C" 'for each blank, this formula gives the value in the row above
    .Value = .Value 'convert the formulas to their values
End With
End Sub

This is equivalent to:

Selecting the range of interest in column B

Pressing F5

Clicking Special, ticking Blanks and clicking OK

Pressing =

Pressing Up arrow

Holding down CTRL and pressing Enter

Paste Special as Values

 

[humsboyle]

Thanks for explanation

regards

Steve

 

[humsboyle]

ADVERTISEMENT

VoG - got another issue with Autofill - not sure if need to raise new post or not.

i have been using the following code to Autofill a number of columns at once down to the botton level of another but i am now receiving Run Time Error 1004 Autofill method of range class failed.

the vba seems to work as everything is moved but then error appears - i have been using this code for years

Dim LastA As Long, LastG As Long
    
    LastA = Range("A" & Rows.Count).End(xlUp).Row
    LastG = Range("G" & Rows.Count).End(xlUp).Row
    
    Range("A" & LastA & ":F" & LastA).AutoFill _
        Destination:=Range("A" & LastA & ":F" & LastG)

VBA highlights the Range part in yellow

Any ideas

 

[VoG]

I can't see why that would fail unless there are less filled rows in column G compared to column A.

I suggest that you start a new thread and post the complete code.

 

[humsboyle]

Thanks VoG.

I will start a new thread.