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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,015
Messages
5,834,939
Members
430,327
Latest member
Mr_Himalayan778

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top