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.

Any ideas.

cheers

S

VB:I dont want the range bit though just to go down as though I had clicked the cell corner.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

Any ideas.

cheers

S

- Fill down in VBA
- How do I autofill months in VBA?
- Mouse Wheel Not Functioning In Vba Editor
- VBA. Autofill down, counting up column A. Problem when there is just 1 row.
- Drill down in excel vba
- Autofill down in macro
- Need to autofill down to the last row
- Move one cell down in VBA
- Autofill in VBA with a dynamic "Destination" range
- Match/Index or VLOOKUP in VBA?
- Sorting by String Length in VBA
- Drag formula down in vba
- Vlookup Function In Vba
- Conditional Formatting In Vba
- Declaring an Array that I can loop in VBA
- Autofill Down Until Next Value
- Fill Down In Filtered List-2007
- Divide A Range By A Constant In Vba
- Using vLookup in Vba
- Trying to write min and max formulas in VBA
- VLookup in VBA
- Skip hidden columns when dragging autofill across in excel 2010
- Use of worksheet function in VBA code
- Fill Down in Blank cells only

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

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?

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?

excel has a function fo pivot table, know anyone knows how to create that type of menu using vba ?

thx,

j

Sheets("ERS").Select

Range("E2:G2").Select

Selection.AutoFill Destination:=Range("E2:G4569")

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?

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

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 SubThe 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,

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.

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

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

VB: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).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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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

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:First time user of VBA here so any help would be great. ThanksPrivate 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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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!

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.

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.

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!

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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!

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

VB:In the above code Master is a dyanmic range. I need to autofill the copied data in the 3 coloumns.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

Can anybody please suggest something.

Thanks,

MHR

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