VBA to autofill column

humsboyle

Board Regular
Joined
Aug 4, 2010
Messages
53
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 IDType of RequestTotal RequestsWithin24HrsWithin24Hrs Average %Over24HrsOver24Hrs Average %
EUROPA\Agent1Task 111100.00% 0.00%
Task 21818100.00% 0.00%
Task 322100.00% 0.00%
Task 416743.75%956.25%
Task 577100.00% 0.00%
Task 644100.00% 0.00%
Total4839 9
EUROPA\Agent2Task 144100.00% 0.00%
Task 277100.00% 0.00%
Task 41 0.00%1100.00%
Task 76346.35%5993.65%
Total7515 60
EUROPA\Agent3Task 12727100.00% 0.00%
Task 25959100.00% 0.00%
Task 311100.00% 0.00%
Task 440820.00%3280.00%
Task 53333100.00% 0.00%
Task 822100.00% 0.00%
Task 61313100.00% 0.00%
Total175143 32
EUROPA\Agent5Task 12727100.00% 0.00%
Task 25757100.00% 0.00%
Task 344100.00% 0.00%
Task 55656100.00% 0.00%
Task 822100.00% 0.00%
Task 677100.00% 0.00%
Task 7221568.18%731.82%
Total175168 7
EUROPA\Agent6Task 422100.00% 0.00%
Total22
Grand Total 475367 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

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
Code:
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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

humsboyle

Board Regular
Joined
Aug 4, 2010
Messages
53
I have notcied after posting that the columns go out of sinc - just to confirm tasks should be under "Type of Request Column"
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Assuming that you want to fill blanks in column B

Code:
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

Board Regular
Joined
Aug 4, 2010
Messages
53
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

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Here with comments

Code:
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

Board Regular
Joined
Aug 4, 2010
Messages
53

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


Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,650
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,904
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top