Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Autofill Down In Vba

Hi Im tryin gto have a macro that autofills a selection all the way down to match the number of rows in the column next to it.


	VB:
	
Range("A2:E2").Select 
Selection.AutoFill Destination:=Range("A2:E404") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I dont want the range bit though just to go down as though I had clicked the cell corner.

Any ideas.

cheers
S


Post your answer or comment

comments powered by Disqus
Hi,
Can any one help me with the following:
If you have say 5 values in a column with different gap between them for example name “x” in c2 then "y" in c5 then "z" in c12 and so on and want to fill down to the next name (fill the gaps with the corresponding names). How can I do this in VBA?
your help greatly appreciated.
Tissa

Can someone show me the code to perform an autofill on the month in VBA? For example, If I put "June" into cell E1 what is the code I need to have it autofill backwards from D1:A1? Any help would be greatly appreciated!!

This is probably a ridiculous question - but does anyone know if it's a setting somewhere that your mouse wheel can scroll up and down in VBA editor?
My mouse wheel works fine with every other application, and I have tried various versions of the driver, with varying results with other applications. However, even with the newest drivers (standard Dell mouse) - the wheel works in every other application I've tried, just not in the VBA editor.
It drives me nuts every time I'm coding.
Anyone have any suggestions?

Hi
Quite often I use VBA code to autofill some formulas and normally it works more or less ok. But I'm having a problem with autofill down to populate value "1" from G2 as far as the rows go in column A.The code that I currently have is as below:

Sub autofill_down()
    Range("G2").ClearContents
    Range("G2").Value = "1"
    Range("G2").Select
    Dim K2 As Long
    K2 = Cells(Rows.Count, "A").End(xlUp).Row
    Range("G2:G" & K2).FillDown
End Sub

If there is some information in col A further than A2 then its not a problem and the code populates as below:

-------------- -------------- Col A ------------------------------------------------ Col G
-------------- --------- Header text 1 ----------------------------------------- Header text 7
Row 1 -------------- Text 1 ------------------------------------------------------ 1
Row 2 -------------- Text 2 ------------------------------------------------------ 1
Row 3 -------------- Text 3 ------------------------------------------------------ 1
Row 4 -------------- Text 3 ------------------------------------------------------ 1
Row 5 -------------- Text 4 ------------------------------------------------------ 1
Row 6 -------------- Text 5 ------------------------------------------------------ 1

The problem is when there is a information only on one line (e.i. only on A2 and not any further). if this is the scenario it populates the value 1 in G2, but as soon as the autofilldown kicks in, for some reason it copies the line above. Please see below.

-------------- -------------- Col A ------------------------------------------------ Col G
-------------- --------- Header text 1 ----------------------------------------- Header text 7
Row 1 -------------- Text 1 --------------------------------------------------- Header text 7
Row 2 -------------- ---------- --------------------------------------------- ----------
Row 3 -------------- ---------- --------------------------------------------- ----------
Row 4 -------------- ---------- --------------------------------------------- ----------
Row 5 -------------- ---------- --------------------------------------------- ----------
Row 6 -------------- ---------- --------------------------------------------- ----------

Is there any way to have a code that would populate value 1 as per Column A but would not have this sort of drawback.

Any ideas?

Any one here know how to create drill down in excel vba ?
excel has a function fo pivot table, know anyone knows how to create that type of menu using vba ?

I am try to tweek my macro. I am trying to have a macro copy/paste a formula in range(E2:G2) automatically down to the last cell looking at the column to it's left. Here is my code...can this be done?

thx,

j

Sheets("ERS").Select
Range("E2:G2").Select
Selection.AutoFill Destination:=Range("E2:G4569")

I need a VBA way to take a formula in cell B1 and autofill it down to the last row with data.

Selection.AutoFill Destination:=Range("B1:B4774") does not work b/c the number of rows varies from sheet to sheet. How do I first determine the total number of rows in column A and use this for the autofill range in column B?

Hi Guys,

This will easily be the simplest question ever posted here, which ofcourse just means you have to give an even more brilliant answer

How do i make a VBA code which does the following:

Moves one row down from the current cell

example: from a4 to b4

when i try recording a Macro it is to specific about the cells, i need it to work in a loop

thx guys!

Tiffany

I'm trying to set up this macro to autofill in VBA:

Sub filldateinfo()
 
'   Format the new cells
    Range("D2").Select
    Selection.Copy
    Range("F2:G2").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
'   Add the formulas to get the year and month
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
       
"=IF(OR(MID(RC4,3,1)=""."",MID(RC4,3,1)=""-"",MID(RC4,3,1)=""/""),RIGHT(RC4,4),IF(OR(MID(RC4,5,1)=""."",MID(RC4,5,1)=""-"",MID(RC4,5,1)=""/""),LEFT(RC4,4),""""))"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
       
"=IF(OR(MID(RC4,3,1)=""."",MID(RC4,3,1)=""-"",MID(RC4,3,1)=""/""),LEFT(RC4,2),IF(OR(MID(RC4,5,1)=""."",MID(RC4,5,1)=""-"",MID(RC4,5,1)=""/""),RIGHT(RC4,2),""""))"
'   Autofill to the end of the table
   Range("F2:G2").Select
   Selection.AutoFill Destination:=Range("F2:G8489")  'How do I make this dynamic based on the length of column A?
   Range("F2:G8489").Select  'How do I make this dynamic based on the length of column A?
'   Replace the formulas with values
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'   Re-arrange the table
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Cut Destination:=Columns("H:H")
    Columns("F:H").Select
    Selection.Cut Destination:=Columns("D:F")
    Range("A1").Select
 
End Sub
The length of the table will vary, so I need the autofill to determine it's own "Destination" value based on the length of column A.

See attached for sample.

Cheers,

I want to use Match/Index or VLOOKUP in VBA to locate a cell in another worksheet and perform a calculation.

Sequence of Events:

-User will select a Component from a drop-down (Cell B5) and hit an "Overhaul Completed" button
-The button will look up the selected Component in another sheet where all components (A column) are listed with lots of data about them
-The corresponding cell for that component will be selected in the "Hours till Next Overhaul" column (C column).
-For this example, lets say that the cell selected is C5
-The formula for C5 should currently be B5-(PME!G1), where the value imported from PME! is the current engine hour value and it changes every month
- The formula should now change to B5-(PME!G1)-(PME!G1). But the second PME!G1 is going to a static number that will never change. So if the hours are at 14474 this month, the formula should be B5-(PME!G1)-14474.

This is what I have so far:
Dim rngRequested As Range
Set rngRequested = Sheets("Sheet10").Range("B5")
.VLookup(rngRequested.Value, Sheets("Sheet7").Range("A3:C21"), 3, False).Select

The select and vlookup functions are probably used incorrectly here.. So once I have that fixed, I can move onto the calculation part...

The reason why I might seem to be going a bit crazy on the automation is because I want to exploit the full capabilities of excel/vba to make the workbook idiot proof. There will be many operators using this book and our goal is to have them do a little as possible, while getting all sorts of valuable data.

Hi All,

I've got a simple range of cells A1:B## (goes down various lines), Row 1 are my headers
In VBA can I sort the range of cells by the length of the string in Column A without having another column C with Len(A2) in it?

Thanks,
Ranmaru2

Hi Guys,

I would like to add the values of the formula's only into some summary sheets and the code below works:


	VB:
	
 Test() 
     
    Range("B4").Value =
[SUMPRODUCT((Datasheet!$I$2:$I$10000=B$1)*(Datasheet!$N$2:$N$10000=B$2)*(Datasheet!$H$2:$H$10000=A4)*(Datasheet!$T$2:$T$10000))]

     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My question is how do I then copy this formula down to lower rows without having to re-type the whole script again for each row? (the same as dragging down if the actual formula was entered into the cell).

Or is it simpler to enter the actual formula, drag the formula down and then copy pastespecial values?

Also, I have written to row 10,000 is there a way to always pick the last used row? (the data table is in a dynamic named range)

Thanks in advance

Hello,
I want to use the vlookup function. the problem that I am having is that the rows of the table in which I search is not constant. It changes after every calculation.

If I were to use excel, I would put the cursor in the top left cell and use 'Crtl+shift+down Key +right key' to select the table.

How would I do that in VBA?

Thanks

Hello, I need to use conditional formatting in VBA becasue the conditional formatting option in excel is restricted to only 3 seperate conditions...

I looked up a website that told me to do this but it is not very successful. The code functions the way its supposed to in terms of colours changing to the value of the cell BUT only when i modify each cell sepertly... What i mean is that if i put a formula in one of the cells and drag it down, it does nothing. (colours dont change). Only when i enter the formula seperately for each cell does it work

Heres what i got...


	VB:
	
 
 
Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim icolor As Integer 
    If Not Intersect(Target, Range("L3:L6722")) Is Nothing Then 
        If IsDate(Target) Then 
            If Date < Target Then 
                icolor = 43 'green
            ElseIf Date > DateAdd("m", 1, Target) Then 
                icolor = 3 'red
            Else 
                icolor = 45 'orange
            End If 
        Else 
            icolor = 2 ' white
        End If 
         
        If Not IsEmpty(Target.Offset(0, 1)) Then 
            icolor = 2 ' white
        End If 
         
        Target.Interior.ColorIndex = icolor 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
First time user of VBA here so any help would be great. Thanks

declare an array

Convert Excel Spreadsheets Into Live Calculating Web Pages

so this might be obvious, but I need things in words of one syllabul before ti will go in, hence the Q.
I am trying to quickyl and easily copy and paste special/transpose a shed load of data. I know that logically I need to tell excel to highlight 12 rows in one column, then transpose that one column over, then move 2 rows down, highlight the next 12 rows and so on etc etc.

I know this is simple, but no idea how I can write this in VBA so that I can loop it. Any suggestions?
The alternative is to manually transpose the data, but I will have to do this at least 4000 times...NO!!!!!

Many thanks

Hello,

I need help in writing a code that will autofill down column A until the next value is reached and will continue to fill down using the new value. This will continue until the last row in column B.

I have attached a small sample file for clarification.

Thank you for your help!

In Excel 2003, when I had a filtered list, I could enter text in a column and copy that text "down" to the "visible" rows- and it only copied to those rows. In Excel 2007, when I do a fill down or use the fill handle, it copies the text into the "hidden" rows as well as the visible rows.

Is there an option or alternative way to "fill down" in a filtered list to only copy to the visible rows? Auto Merged Post Until 24 Hrs Passes;

I did further research elsewhere on the web - it appears to be a known MS bug - here is the text I found that addresses the issue:

If you place your cursor in a cell within column A, before you make the
selection from the dropdown on that column, then the fill handle will behave
correctly.
If the cursor is place anywhere outside of the autofiltered range before
making the selection with the dropdown, then you do get the behaviour as
described.
It is a bug, MS do know about it.

Alternatively, you can choose Insert tab>Table to apply to your data, rather
than using Autofilter.
The Data table has the same dropdowns, and the autofill feature always works
correctly when you make a filtered selection and then fill down.

I have a column of figures in kg which I want to convert to tonnes.

In Excel I can effect the conversion by putting 1000 in a cell and copying the cell to the range using Paste Special Divide.

In VBA I could run down the column visiting every cell, or I could write a formula in an adjacent column, fill down and then copy paste values.

But is there a (quicker) VBA equivalent to the Excel method which doesn't necessitate putting 1000 in a cell?

Thanks in advance.

Hi guys!

I'm having some problems using the vlookup function between to sheets in a workbook. What I wanto accomplish is as vlookup function in vba that pulls the name of a company starting from cell A2 in sheet2 and uses this name to search for the corresponding company name in sheet1 column A.

After this is accomplished I want to be able insert a value from sheet1 that lies one column and three rows down from the name that was looked up above. This value will then be pasted into sheet2 column B alongside the correct company name.

Can anyone help me with this? I would be very thankfull for all the help I can get!

Cheers!

My problem is that i cannot get a variable into a formula in VBA. I have a counter and i need one of the cells to be in row 11 and column "c". c being my variable for the counter. so i basically need to get this =min(cells(11,3):cells(11,c). well actually i need to get it for about 100 rows down aswell so if anyone knows how to make this work and make it work for row 12, 13, 14...n i would really appriciate it.

When I try and use the following Vlookup function in VBA it runs:

but it returns Error 2042???


	VB:
	
Prices(n) = Application.VLookup(theStart, _ 
Workbooks("Platts.xls").Sheets("Data").Range("A6:BG500"), 4, False) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


The full code is shown below what am I doing wrong. You can find the Vllokup about 4/5 of the way down


	VB:
	
 asian() 
     'Function Asian(ByVal asstart As Date, ByVal asend As Date, ByVal asstrike As Double, _
    ByVal aspricemove As Double, ByVal asvolmove As Double, ByVal ascp As String, _ 
    ByVal asoptdiscyn As String, ByVal ascommno As Integer, ByVal asperiod As String, _ 
    ByVal asdiscyn As String, ByVal darray As String, ByVal verthoriz As String) 
     
    Dim Dates() As Date 
    Dim Holidays As Variant 
    Dim Prices() 'As Double
    Dim n 'As Integer
     
     
    ascommno = 93 
     
     
     
    Dim MnthAdd As Integer, MnthLess As Integer 
    Dim myCommNo As Integer, OpType As Integer 
    Dim periods As Integer, i As Integer 
    Dim theStart As Date, theEnd As Date 
    Dim thepossibleend As Date, dip As Integer 
    Dim theTime As Double, theTime2 As Double 
    Dim theTimetom As Double, theTime2tom As Double 
    Dim DF As Double, Price As Double, Volatility As Double 
    Dim RA, RAtom 
    Dim Premium As Double, Premium1 As Double 
    Dim Delta As Double, deltas As Double 
    Dim Gamma As Double, Vega As Double 
    Dim theta As Double 
     
     'On Error GoTo EH
     
    myCommNo = ascommno 
     
    Workbooks("Murex Asian Options 10.xls").Activate 
    theStart = Range("U44").Value 
    asstart = theStart 
    theEnd = Range("U45").Value 
    asend = theEnd 
     
     
     '  n = holsarray(1, 0, 1)
     
     
     'With Workbooks("Setup.xls").Sheets("HOLIDAYS")
     '  Set Holidays = .Range(.Cells(3, 2 + commset(commno, 11)), .Cells(holies + 2, 2 + commset(commno, 11)))
     'End With
     
     'n = Holidays(3)
     
     
    If ascp = "C" Then OpType = 1 Else OpType = 0 
     
     ' Select Case asperiod
     
     '  Case Is = "M"
     
     'Identifies how many different months (periods) there are
    periods = Month(asend) - Month(asstart) + (Year(asend) - Year(asstart)) * 12 + 1 
     
    For i = 1 To periods 'go for all the periods apart from the last one which _
        should be calculated separately, allowing For an End _ 
        In the middle of the month start of the option 
         
        If i = 1 Then theStart = asstart Else theStart = theEnd + 1 
         
         ' end of the option.
         ' The idea is to go to the beginning of the next month and subtract one day.
         
        If Month(theStart) = 12 Then 
            MnthAdd = 1 
            MnthLess = -12 
        Else 
            MnthAdd = 0 
            MnthLess = 0 
        End If 
         
         'the idea is that on the last period we need to allow for an odd last day
         'and it needs to be able to recognise that:
         
        thepossibleend = DateValue(Str(1) + "/" + Str(Month(theStart) + 1 + MnthLess) + "/" + Str(Year(theStart) + MnthAdd))
- 1 
        If thepossibleend < asend Then theEnd = thepossibleend Else theEnd = asend 
         
         ' days in the accounting period
        dip = splits(theStart, theEnd, myCommNo) 
        Splita = dip - Splitday(theStart, theEnd, myCommNo) 
        expday = futexdates(theStart - firstd, 2, commset(ascommno, 7)) 
         
        Redim Preserve Prices(dip) 
         
        For n = 1 To Splita 
             
            If Date >= theStart + n Then 
                 
                Prices(n) = Application.VLookup(theStart, Workbooks("Platts.xls").Sheets("Data").Range("A6:BG500"), 4, True) 
                 
            Else 
                 
                Prices(n) = EnergyPrice(theStart, theStart, theStart, ascommno, "Q", "y") 
                 
            End If 
             
             
             'End If
             'Prices(n) = Application.WORKDAY(theStart, n)
             
        Next n 
         
         'Prices(n)
         
         
         
         
         
         
         
        theTime = (theStart - Date) / 365 
        theTime2 = (theEnd - Date) / 365 
        theTimetom = (theStart - (Date + 1)) / 365 
        theTime2tom = (theEnd - (Date + 1)) / 365 
         
         ' discount factor if applicable
        If asoptdiscyn = "Y" Then 
            DF = intDF(theEnd, "D", myCommNo) / 100 
        Else 
            DF = 1 
        End If 
         
    Next i 
     'End Select
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello

I have 2 spreadsheets that I plan to link. In spreadsheet A I have hidden certain columns (not in any series).
I now link data from Spreadsheet B into spreadsheet A. I would like to autofill across in Spreadsheet A to populate the linked cells but would like to miss (or skip) the hidden columns in spradsheet A.

I have very limited knowledge of VBA. Would be willing to give VBA a go, only if this is the only option.

Thanks!

Hi all,

I am trying to use a dynamic range in VBA. But I am getting compile error.


	VB:
	
Range("D19:G19").Select 
Application.CutCopyMode = False 
Selection.AutoFill Destination:=Range("D19").Offset(0, 0, _ 
Application.WorksheetFunction.CountA( activesheet.name!Master)) , 3) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
In the above code Master is a dyanmic range. I need to autofill the copied data in the 3 coloumns.

Can anybody please suggest something.

Thanks,

MHR

Hi

i am new to VB. Can any one tell how can i copy down the data as explained by below,in VBA:

i have data in first Row only in columns A to E except b, in case of column B data is also down to some rows. How can i copy data of columns A,C,D and E upto the last row of data b.
----------A----------B----------C-----------D---------E
1---------421--------545------4-------------%---------545
2--------------------5854---------------------------------
3-------------------545487-------------------------------------
4-------------------5455554-----------------------------------
5-------------------------------------------------------------
6-------------------------------------------------------------

"----------" just to indent my data and they represent nothing
Now i want to copy what is appearing in respective column in the first row down to row number 4 where B's data is ending.

Regards


No luck finding an answer? You could always try Google.