Autofill In Macro Range Is Constant How Can I Code To Be A Variable Range?
Feb 19, 2010
I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.
The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?
View 6 Replies
ADVERTISEMENT
Apr 4, 2008
I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this
View 9 Replies
View Related
Nov 1, 2006
I have worksheets that refernce values in one column off columns in others and do this on daily basis. each day there are blank amounts in random fields which I inturn use a macro to delete rows with blank amounts
I then want to autofill the numbers from 0001 to the bottem line of the sheet where the word "END" always is, as the position of the end word is random each time and autofill always needs a definite range how do I get my macro to autofill down to the word "end" ?
here is the code that doesnt work for me and I've been trying to fix
Sub Macro1()
For counter = 1 To 30
Set curCell = Worksheets("Sheet1").Cells(counter, 6)
If curCell.Text = "END" Then Range(Cells(1, 6), Cells(counter-1, 6)). _
Selection.DataSeries Rowcol:=xlColumns, Type:=xlAutoFill, Date:=xlDay, _
Trend:=False
Next counter
End Sub
View 3 Replies
View Related
Oct 12, 2007
I am creating a loop that will autofill 16 cells down. It copies the formula from one cell (Z230), pastes that 16 cells down (cell Z246), then changes the lock properties of the cell (Z246) before autofilling down 16 (to cell Z261). At which point the loop starts over again. (copies cell Z246,pastes it to Z262, fills down 16 , etc)
Because the cell I am copying from changes in each loop, I set a variable to grab the address of the starting cell and last cell of the copy.
I then put this into a String so it would return something like this
Range("Z245:Z259")
Problem is I get extra quotes around Range("Z245:Z259") - it comes out as string "Range("Z245:Z259")"
This gives me a problem when I try to replace
Selection.AutoFill Destination:=Range("Z245:Z246"), Type:=xlFillDefault
with
Selection.AutoFill Destination:=rangevariable, Type:=xlFillDefault
Below is my total code, but I get stuck on the selection line. I have to do this 300 times so I would prefer not to do it manually!
View 9 Replies
View Related
Sep 17, 2013
Currently my Macro should: Turn off any filtersNumber column A from 1 to 1000 (starting in A14)Drags formula from K14-O14 down to last row of data shown in column Athen puts cursor in last empty cell in column B ready for user to enter data
On point 3 - I want the range to be K14-O14 if active worksheet equals "EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be L14-P14
I have found bits and pieces of macros on the internet and put them together so if my macro below is not the most effective for my needs but here it is in it's current state:
Here is my macro:
Sub AddNewEntry()
'TURNS OFF FILTER IF FINDS ONE ON
Dim wks As Worksheet
[Code]....
View 8 Replies
View Related
Jun 12, 2008
I am working with a large report that needs to be broken out and sent as separate files to recipients for confidentiality purposes. I'd rather not use views/protection since there are many different ways particular people need to see the data, plus it is a very large file and flattening it works to everyone's advantage. My goal is a macro that will copy each tab into separate workbooks, paste special values, and save as each as Cell A1 (or the tab name-same thing). I have tried recording macros and editing (I'm very new to VBA) many times but it's a mess.
View 2 Replies
View Related
Jun 22, 2009
is there a way to do this in VBA? I've also read: - Dave Hawley's recommendation of using: Sheet1.UsedRange.SpecialCells(xlCellTypeConstants).ClearContents
from another thread (which is excellent!)
- SHG's recommendation of using a named range, for example:
Range("Inputs").ClearContents
Given my limited knowledge of VBA, how would I now combine the two to write a VBA sub-routine that clears a named range entitled "Entry" on a sheet entitled "Data"? Would the following be the correct syntax: Worksheets("Data").Range("Entry").SpecialCells(xlCellTypeConstants).ClearContents
View 6 Replies
View Related
Jul 1, 2009
In a macro - how can you copy a cell and keep a data range absolute?
In the statement below, the range denoted by: DATA!RC[-52]:R[829]C[-52] is D2:D829. When I use the macro to Autofill using the following:
(Selection.AutoFill Destination:=Range("BG2:BG220"), Type:=xlFillDefault)
the range progresses. I need to have the range as an absolute.
Sheets("Report").Select
Range("BG2").Select
Selection.FormulaArray = _
"=SUM((DATA!RC[-52]:R[829]C[-52]=""APPOINTMENT"")*(DATA!RC[-55]:R[829]C[-55]>=Report!RC[-1])*(DATA!RC[-55]:R[829]C[-55]
View 9 Replies
View Related
Nov 25, 2008
I recorded one for an autofill series which basically came out as
View 4 Replies
View Related
Aug 7, 2007
Just a niggling problem, I've got lstRow as a Long and it contains the value of the last row offset by (1, 0). The problem is i'm trying to add it into a range
Range("C500:K500").Select
Selection.autofill Destination:=Range(C" & lstRow &":K" & lstRow &")Type:=xlFillDefault
Like that.. Except i've tried a million different combinations of " and & in different places to try and get it to compile. It refuses too. It always gives the error "Expected list seperator or )" I know I could just do it individually from C to K but I'd like to learn how to do it this way as well.
View 6 Replies
View Related
Feb 12, 2010
I suppose this is kind of SQL as much as anything so apologies if this isn't really the right place for it.
I had an idea of how to use a concatenated string in Excel to utilise the SQL IN statement for listing parameters that vary from day to day.
Annoyingly my code does not work:
If my range of codes is for example
XS0330509760
XS0242821717
XS0241172948.................
View 9 Replies
View Related
Jun 25, 2008
I'm trying to set named range as a constant in VBA, to allow me to recall the same range as an output target in different subs, without having to 'set' it in each module. I have tried to declare it at the top of a module like this...
Public Const tpnb_range = Worksheets(" SQL").Range("tpnbs")
and get a "Compile Error : Constant expression required"
View 3 Replies
View Related
Sep 13, 2012
I need to calculate a formula in a constantly changing range. In the example below, the formula in column C would yield the % change in column A between the first "P" value in column B and the minimum value prior to the next "P" value.
So, the first result would be =(A2-A1)/A1, which is (1896.3274 - 1973.4764)/1973.4764. The cells in column C would be blank until the next formula, which is =(A8-A7)/A7, then it gets tricky. The next would be =(A14-A10)/A10.
A B C
1 1973.4764 P
2 1896.3274
3 1922.5499
4 1905.2061
5 1985.6797
[Code] ...........
View 7 Replies
View Related
May 13, 2006
I'll do what I can to explain this mess I want to clear up...I have a series of excel reports I have to download and work thru daily and I need to see if there is a code to autofill down thru a column, based on multiple statements throughout the column..here goes:
f1 msp
f2 msp
f3 blank **
f4 mct
f5 mct
f6 mct
f7 blank **
f8 cci
f9 blank **
Regardless of the actual f cell, I always will need to fill the Blank ** cell w/ the values from the previous cell....Hope this makes sense...& thanks in advance....
have_a_cup@cox.net
View 5 Replies
View Related
Oct 25, 2009
When I'm recording a macro it records the range I'm currently doing... example - whatever figure is in Column A I want to multiply by 2 - the result being in Column B. I have figures down to Row 10 in Column A, so the range is A2 - A10. (A1 being a header).
Is there a code I can put in the macro that doesn't set the range to only A2 - A10, and will multiply anything that's found in Column A - except for when the Cell is blank?
View 9 Replies
View Related
Sep 20, 2009
I have a list of filenames in Sheet1, range A1:A20. These files can be found in URL
http://mysite/x/y. Where y is the filename (including the extension) and x is the 1st 4 characters of the filename. So if in A1 I have the filename AB1234.pdf, this can be found in the link: http://mysite/AB12/AB1234.pdf. Is there a was in VBA where I can loop thru the list in A1:A20, and copy each from their respective URLs and save into C:Documents and Settings ?
View 4 Replies
View Related
Aug 23, 2006
I have tried various syntaxes but nothing is working for me. I am using XL 2000 so it doesn't have the relative address function in the macro! So here is what I have
ActiveCell.Range("A1").Select
ActiveCell.R1C1
Range("R1C1:R1C85").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application. ScreenUpdating = True
Range("A1").Select
I need to be able to select from the originating cell which will always be in "T" Col thru to "CZ" col but will be on different rows.
View 2 Replies
View Related
Dec 15, 2006
I download some data from a commercial real estate site about properties and their owners and process it in Excel. Out of 1,000 records, maybe 20 or so will have the data end up in the wrong fields. This is an artifact of the data source the commercial site uses.
Anyway, what I need to do is to get the data back in the right fields. So, I sort the data to pull together at the top of the sheet all the records with data where it's not supposed to be. So far, so good.
Now from one data download to another the number of records which end up in the sort will be different. And, here's the problem.
I try to record a macro mimicking my selection of the range of the data that needs to be moved. Fine, no problem. And, on the same dataset it works like a charm. But as soon as I put a different dataset into the spreadsheet with a different number of records that need to be corrected the macro fails.
Apparently, this is because the macro has been defined with a certain range of cells selected in the first data set and this same range is used for subsequent datasets with different numbers of errant records.
Basically, what I'm trying to record in a macro is the Shift/Control End and Shift/control arrow commands. But they don't record as such.
View 9 Replies
View Related
Oct 12, 2011
I wish to copy a range which has the variable adderss in a cell.
Example the address is located in D2 and the address in that cell is B4:R113
I want to select the information in D2 for copying.
View 4 Replies
View Related
Nov 25, 2009
1. I need to define a variable range of all the USED cells in a column (starting at row 2 as the row 1 is the header)
2. I need to do this for columns A through to P inclusively (all using the same lastrow reference as that of column A)
3. I need to call the range name by the contents of the header in that column (Row 1)
4. Some of the data in the header row (row 1) contains spaces - can we change this to an underscore for the range name only?
5. Does a range only work for the active worksheet? Because i will have multiple worksheets, all with the exact same information in row 1, but different numbers of rows (and different data as well), but i will need the range defined on each worksheet......
I've found the following code and thought it was it, but it only defines on 1 worksheet, and for some reason only defines ranges for columns 1 and 2. Also it defines the range as A1:A65535 - not the USED range.....
View 9 Replies
View Related
Aug 15, 2007
I am trying to write a very basic macro to copy and paste, but when I record the keystrokes, step 4 (see below) seems to be recording absolute cells and not relative (?). At any rate, I can't seem to figure out how to get it to select the text the way I want it to. These are the keystrokes I want:
1. Ctrl-c in the current cell <copy the current cell>
2. DownArrow <move down one cell>
3. Shift(hold)-End-Down <select the blank spaces from here down until the next occupied
cell>
4. Shift(hold)-Up <move the selection up one from the bottom to exclude the occupied cell>
5. Ctrl-v <paste>
6. End-Down <move down to the last occupied cell>
I have set the record tool to record "relative" references.
Below is the code that was recorded. It's line 5 that's not working - how do I fix it? What is the command to tell the program to press the shift key and an up arrow?? Such a simple thing, and yet I can't find the answer anywhere! Things sure have gotten complicated since the old Lotus 1-2-3 macro language...
Sub Fill()
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A17").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
End Sub
View 8 Replies
View Related
Jun 13, 2008
The message shows me "Can not get the vlookup property of worksheetfunction class" and don't know the reason. The 'plancomm' and 'bonus' is the range name in different worksheets of the same workbook as the rngcell.
Case Else:
.Offset(0, 1).Value = WorksheetFunction.VLookup(rngCell.Offset(0, -3), Range("plancomm"), 2, 0)
.Offset(0, 2).Value = WorksheetFunction.VLookup(rngCell.Offset(0, -4) & rngCell.Offset(0, 1), Range("bonus"), 6, 0)
View 3 Replies
View Related
Jun 16, 2008
I have 2 columns 'C' and 'D' which I want to apply Conditional Formatting to (i.e. colour the background of the cell in column 'D' for the respective row in colum 'C') if they contain different values. I have the following
Sub CellCCondFormatting()
Dim j As Long
Range("C2").Select
j = Range("C2"). CurrentRegion.Rows.Count
MsgBox ("1st: " & j)
Range("C2:C" & j).FormatConditions.Delete
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=D" & j
Range("C2:C" & j).FormatConditions(1).Interior.ColorIndex = 3
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=D" & j
Range("C2:C" & j).FormatConditions(2).Interior.ColorIndex = 4
MsgBox ("2nd pass: " & j)
End Sub
It iterates through all rows in my CurrentRegion OK but the Conditional Formatting 'formula' operates on the wrong value in column 'D'. For example, when viewed via menu option Format > Conditional Formatting... row 2 column 'D's Conditional Formatting value is D1714, row 3 column 'D's value is 'D1715' and so on....................
View 2 Replies
View Related
Apr 21, 2013
I've recorded this Macro to sort the cells in a column alphabetically if any cells have content.
I would like to use the Macro on the whole workbook however the AB column range varies between the worksheets.
How should I adapt this Macro to sort simply to the END of AB column??
' PWRII Macro
'
'
Cells.Select
[Code]....
View 5 Replies
View Related
Jan 27, 2012
I am trying to write code to select a range in a worksheet where the last cell in the range is variable.
Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range
[Code].....
View 8 Replies
View Related
Sep 27, 2013
I have multiple worksheets each with varying amounts of data. I have a number in cell A1 that I need to autofill down to the bottom of each worksheet. All of which have varying amounts of data.
RA-0151
0
765804
3308345
168
0
5.74E-07
8.8E-09
1.371696
0
0
[Code] ........
When I record a macro it sets the autofill range as the bottom of what ever worksheet I recorded it on.
How can you write a macro that goes to the last row of each worksheet.
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+a
'
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A122"), Type:=xlFillCopy
Range("A1:A122").Select
Range("B1").Select
End Sub
View 2 Replies
View Related
Jan 25, 2013
I have a named range, called SubjectNamesPastoral on a worksheet called Worksheets("Group to Teacher")
I can't assign the named range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable in vba.
the first two lines of code work fine, the msgbox shows "E100:E105", happy days!
However when I try to assign the same range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable, the debugger runs past the 'Set' line without error, but throws 'error 91' at the second msgbox.
VB:
thisString = "SubjectNames" & strSubjectFamilyOfGroup
MsgBox Range(thisString).Address
Set rngSubjectFamilyRangeOnSubjectUsedSheet = Worksheets("Group to Teacher").Range(thisString)
View 1 Replies
View Related
Jul 16, 2009
I am using the code below to copy a range and paste it over a variable range.
View 4 Replies
View Related
Mar 6, 2008
I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:
Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))
Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.
View 3 Replies
View Related
Mar 24, 2014
I am trying to autofill cells across a column that sum a range but move over 5 columns from another worksheet. for example, i want to take this range:
Schedule!AG6:AK6 and when I autofill, the next adjacent column will fill with Schedule!AL6:AP6, the next with Schedule!AQ6:AU6, and so on.
I have tried offset, but am not sure if that's the right formula.
View 5 Replies
View Related
