VBA Autofill if not blank, then format

siperwrx

New Member
Joined
Mar 14, 2014
Messages
15
Using Excel 2010

This is going to sound really messy, so I hope you'll forgive me as I'm new to VBA.

Let me outline what I'm trying to do:
Column A: User fills in file name
Column B: Formula to concatenate file name to external file path to create formula (it references cell A2 in different workbooks): =IF(A3<>0,CONCATENATE("='\\Network\Folder\[",A3,".xlsm]CT Data'!A2"),"")
Column C: Formula from Column B is copied over to C and has "=" replaced in order to force calculation of formula using Macro
Columns D-W: I want to autofill based upon the external cell reference from Column C
Column D: ='\\Network\Folder\[File Name.xlsm]CT Data'!B2
Column E: ='\\Network\Folder\[File Name.xlsm]CT Data'!C2
And so on.​

THEN
Column C needs to be formatted as a Percentage
Columns J and K need to be formatted as Short Dates
All others need to be formatted as General


I've gotten this to work using 1 macro but it's really slow and I'm sure it's not the most efficient code. Essentially, I've identified the range as 50 rows, so it's performing these functions for all 50 every time I perform the macro. So I'd like to insert code that says "If C3 in each row <> 0, then autofill each column through Column W, then perform required formatting" so that it doesn't repeat for all 50 rows each time I run it. Any help is much appreciated!

Here's the code I'm currently using:
Sub Fill()
'
' Fill Macro
'
'
ActiveSheet.AutoFilterMode = False

Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("C3").Select
Range("C3:C50").Select
Selection.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("C3").Select

Range("C3:C50").Select
Selection.Autofill Destination:=Range("C3:W50"), Type:=xlFillDefault
Range("C3:W50").Select

Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0.00%"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0%"
Range("D3:I3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "General"
Range("J3:K3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "m/d/yyyy"
Range("L3:W3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "General"
Range("C3").Select

End Sub
 

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
Back
Top