Dynamic Range Setting
Oct 22, 2009
This should be easy ... can't figure it out. If I want a formula that wants to perform an action (NPV, but I don't think that matters) on the cell directly above and the next "X" (say 20 cells/years of cash flow) to the right of it, how can I set it up so that I can copy the formula across as well as change "X".
(Obviously, it's easy to select a 20 cell range and copy it across ... but what if I want the length of the range to by dynamic (ie I want to switch to a 40 year NPV instead of a 20 year)?)
View 2 Replies
ADVERTISEMENT
Jul 7, 2014
I have a graph where dates are shown on the x axis. I would like the date range always to start on the date when I am updating the chart, e.g. sourced from a cell with formula =now().
View 1 Replies
View Related
Sep 6, 2013
I made an excel document for my boss with the list of the furniture in an flat that we will rent. The document will be signed by the the guest of the apartment and will be used if anything at the end of the renting period is ruined or is missing. It's kind of insurance. The problem is that next to the cell with the list of furniture I made a list of hyperlink connected to the photos of the furniture. Everything is working on my computer where the photos are in a folder that is contained in "documents" but as soon as I try it in another computer placing the folder again in the document folder it doesn't work. It says "the file is missing". I just realized that I need a dynamic path but today I have to handle the excel file and I don't know how to do it and to change the path of every link with just few clicks I have no time to change photo by photo (there are like 300 pictures).
View 1 Replies
View Related
Nov 18, 2008
So I've tried this a few different ways and every time I get the 1004 error: "Unable to set PrintArea property of PageSetup class"
Basically I have a macro that goes through a workbook to hide certain rows and columns based on some user inputs. After doing this I would like to set up appropriate print areas on certain sheets so that when you go to print anything it comes out clean. I thought this would be easy but I'm stuck.
The Plans variable in the code below is an integer from the user input. Think of this code as grabbing two separate boxes and setting them as print areas, which I've know I can do manually because I've tried it.
What's wrong with the following code?? (I've also tried using the union function here, to no avail)
View 6 Replies
View Related
Dec 22, 2009
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
View 9 Replies
View Related
Mar 15, 2007
How do you set the range with whats below? Right now its looking at 4,2 only and if extend to look all the way to 19,2 it will just copy the whole screen. I need it to look at 4,2 and then move to 5,2 and then so on once it has copied all that information to the sheet ...
View 7 Replies
View Related
Jul 2, 2014
How can i set the range for the Sheet3 i have taken IngDataColumn. It select only particular column of fixed that is 4
[Code] ....
View 14 Replies
View Related
Apr 27, 2009
i would like to set a criteria bewteen a ranges. eg If A1 >0 but <100 then .3, Else If A1 >99 but <200 then .6, Else .9. How do i write this in the cell?
View 4 Replies
View Related
Oct 16, 2012
I am trying to set an Array to prompt user with a series of questions and input answers into a specific row. However, I want to skip over certain columns and leave those blank. How would I modify my code to do this?
Code:
Private Sub CommandButton2_Click()
Dim ArrQues() As Variant
Dim ArrInput(2) As Variant
Dim lngLstRow As Long
Dim strPNfromSales As String
ArrQues = Array("Enter the Part Number.", _ "Quantity Being Returned?", _ "What is the Reason for Rejection?")
For b = LBound(ArrQues) To UBound
[Code]...
I want the first two questions to be input into Column B & C, but then the third question to be input into column I.
View 2 Replies
View Related
Sep 27, 2009
When I'm using the Macro recorder or even after I've completed a sequence is there a way to change the range so it code always covers the complete range of the data?
View 9 Replies
View Related
Feb 3, 2014
I have a general list from which a chart is created. Column one contains a date series. There are a fixed number of dates in this series (approx 50 or so).
I would like to select a start date and an end date from this list and have the chart display the data from that range.
Typically I would simply create a new list and build the chart from the list, but I'm kind of hoping that I could do it from a single source rather than make mulitple lists.
View 2 Replies
View Related
Dec 12, 2011
I am attempting to set a variable as a range of data that sits on a non-activated worksheet. However, I am getting a runtime error of 13 (data mismatch).
Is there something in my code that may be incorrect? Here is my code:
Dim rRange As Range
Dim sRange As String
Dim tblName As String
[Code]......
View 4 Replies
View Related
Feb 29, 2012
Code:
Private Sub Worksheet_Activate()
On Error Resume Next
Set sT = Sheets("Report")
On Error GoTo 0
On Error GoTo nendR
If Not sT Is Nothing Then
[Code] ........
I get a Subscript out of range at Set cT = Sheets("RGA by Customer") line.
It baffles me that when neither sheet exists and the first part runs there is no problem, but when it gets the the above mentioned line I get this error. Same exact code, just a different sheet name.
Again, when the sheet exists there is no problem. Only when I click on Sheet1 (or start up the file) and this sheet doesn't exist do I get the error.
View 4 Replies
View Related
Oct 13, 2013
Im trying to set a range using a named range and dont know the best way to do this. so far ive got
Set rDataRange = Range(Offset("datastart",1, 0) & ":" & Offset("datastart",LastRow, 0))
datastart is the named range in the sheet and im trying to make a new range that starts 1 row below where datastart is and end where lastrow is. not sure where i messed up on this but i get errors when i try to run this
View 3 Replies
View Related
Jan 8, 2014
I'm having trouble setting the Range "UtilizationRange" using a variable, "CurrentRange" to store that range. I get the error "Run-Time error 1004: Method 'Range' of object'_Global' failed".The rest of my code works when I set the utilization range using the actual range for my purposes, but the problem is the range will change each month and I don't want to change it manually each time. There's a cell in the excel sheet this refers to (AG3) that holds the range value, which in this case is [N7:N75], and I would just like to return that value to the variable "CurrentRange" and set UtilizationRange equal to that.
The value is returned to CurrentRange, but it's the last line that's giving me the problem.
Sub RangeTest()Dim UtilizationRange As Range, Cell As RangeDim CurrentRange As String
CurrentRange = Range("AG3").Value MsgBox (CurrentRange)
'This returns the value [N7:N75], which is what I want
Set UtilizationRange = Range("CurrentRange")End Sub
View 1 Replies
View Related
Mar 17, 2008
This is the code that Recorder proffers to simply take a Range of 3 Cells and make Type Font Black/Bold and to place Thin Borders inside the range and Thick Borders aroung the range.
ActiveCell.Offset(0, -2).Range("A1:C1").Select
Selection.Font.ColorIndex = 1
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
View 9 Replies
View Related
Jun 23, 2006
I have a certain number of accounts for opening and close dates. What I wish to acheive is to fill a Range of date from the opening to the closing (or up to 200606 YYYY-MM) in another spreadsheet with the account number corresponding to that date next to it.
The VBA I have right now manages to create the list of accounts but up till 2006. It isnt taking into consideration the closing dates of accounts.
So For example What I want is to set up inside of the loop that to continue to do the loop until it reaches the closing date by YYYY-MM so if the clossing date is 200504 I will have data filled up to that date, and if the closing date is "" then fill the dates up til 200606. Ive tried out a number of ways of inserting the if statement but apparently the string create a problem.
Here is my current VBA and a Sample Sheet of what it looks like....
View 9 Replies
View Related
Jun 24, 2006
I m trying to set a range on a cell after using the FIND function in a macro and was wondering if anyone had any experience in it.
Basically, I have a spreadsheet which I am looking up a value that i stored in FID1 with the extension "_EXE"
I'm using the FIND command to find that cell and then set a range so that I can mark it but am getting an error....
View 8 Replies
View Related
Aug 8, 2006
i have compiled a multipage using some borrowed code and some code i have written myself. most of it works, but i have a problem populating listbox2.the error is in Private subCmbFindAllJobNo_Click(). i have put h1 tags around the line of code which shows the error when i debug. this code works ok as a stand alone, so i suspect i have done something wrong in the userform initialise.
Option Explicit
Dim rng As Range
Const FirstRow As Long = 2
Dim r As Long
Dim ans As Variant
Dim MyArray(100, 4)
Public MyData As Range, c, d As Range
Private Sub cmbAmend_Click()
Application. ScreenUpdating = False
Set c = Worksheets("ENTRY SHEET").Range("b4").End(xlUp).Offset(3, 0)
c.Value = Me.DTPicker1.Value
c.Offset(1, 0).Value = Me.TextBox1.Value
c.Offset(2, 0).Value = Me.TextBox2.Value ..........
View 9 Replies
View Related
Jul 6, 2013
I have a bit of code that builds a formula and uses a variable store the range:
VB:
Sheets("CONTROL").Select
lastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
[Code].....
View 5 Replies
View Related
Mar 28, 2014
I am creating a range of numbers from 1 to 2000 with increments of 0.5. Then I have to subtract certain range, for example, 100 to 350. So, the result should display, 1 to 95.5 and 350.5 to 2000.
View 4 Replies
View Related
May 18, 2009
I recently set up some functions based on Chip Pearson's tutorial for referencing worksheets from Formulas. (http://www.cpearson.com/excel/sheetref.htm)
The problem I'm now having is that I can't use VBA to set these functions in place; it returns a syntax error.
For example, I want I37 on most sheets to have the same formula. So I have the following:
View 3 Replies
View Related
Oct 7, 2009
Trying to write a macro to transcribe data from columns in Sheet1 to Rows in Sheet2. Assuming there is a blank between each record. I get an error on the "Set Rng1....." line.
View 4 Replies
View Related
Apr 2, 2009
I've got two sheet's in a workbook. The first contains data and is setup with dates in column A, day of the week in column B, and values going across by day in column's C through H. As an example it looks as follows:
View 5 Replies
View Related
Mar 2, 2012
Currently (as of Today() = 3/02/2012) in my code my autofiltered range rng is SET to:
? rng.Address
$A$4:$AJ$121
The next
rng.AutoFilter Field:=12, Criteria1:=""
View 3 Replies
View Related
Apr 1, 2009
Here is my code
Dim LastCell As Range
Set LastCell = Cells(EndRow, LastCol).Address
I get a "Object Required" when this I try to set the address. I know this is easy.
View 9 Replies
View Related
Aug 23, 2006
Im having slow performance reading a CSV file into 34000 sheet named ranges. Currently it takes about 8 minutes to read 34,000 records. After stepping through the code Ive realized there are two bottle necks.
1)finding which sheet that the range name resides see getRangeAddress() and
2) actually setting the value to the named range i.e. Range(ra) = dprecord(1)
The CSV file looks like this: <range name>,<data value>
e.g. DPA_1001,99090
Performanace actually seem to slow as the macro runs.
Public Sub readDatapoints()
'Macro readDataPoints
'This macro will read in the a comma seperated value (CSV) file of datapoints.
Dim sFile As String
Dim currentLine As String
Dim delimit As String
Dim counter As Integer
Dim ra As String
Dim fs As Object
Dim ts As Object
Dim dprecord
Dim oldStatusBar As Boolean
delimit = ","
View 9 Replies
View Related
May 9, 2007
I'm trying to find the last row on a sheet and then set the next cell, in column A to a certain value. It fails with Method 'Range' of object '_Worksheet' failed.
'activate the male page
sMalePicks.Activate
'add to last row of male baggage
lastRow = sMalePicks.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
'get next row
lastRow = lastRow + 1
sMalePicks.Range(Cells(lastRow, 1), Cells(lastRow, 1)) = Trim(bagNames(x))
View 5 Replies
View Related
Feb 17, 2014
A B
17-Feb 5.00
19-Feb 12.00
22-Feb 7.00
26-Feb 10.00
I would like to see the sum of B given it is in the range from 17-Feb to 23-Feb. My estimations so far:
=sumif(A:A,????, B:B)
How do I set up a criteria which would take values from 17-Feb to 23-Feb?
I also tried =sum(sumif(A:A,{17-Feb;?;?;23-feb}, B:B) but it wouldn't let me.
Particularly the problem is in entering the date in the array.
View 1 Replies
View Related
Feb 1, 2010
I'm trying to do a simple loop which creates charts based on an ID number. I recorded a macro and has tried to modify it but am having trouble defining the correct reange when settign the data source. Here is my
View 2 Replies
View Related
