I have a spreadsheet that has 7 columns starting at B and 12 rows. Column B has numbers from 1 to 12 down the rows. I have an inputbox that asks the user for a number from 1 - 12 that relates to a tax period. What I'd like to do is depending on what number they give have that define the range. If they gave me the number 2 my range would be C11:F11, if the number was 10 the range would be C7:F7. The inputbox returns strTaxPeriod.
View 9 RepliesTrying to add a named range at run-time
Here's what I have so far ...
I am having some trouble with a variable range selection within a regression. I keep getting an "application-defined or object-defined error." I've isolated each statement to find that the code that is causing the regression not to work is below (the error for that line of code states that the Select method of Range class failed):
View 6 Replies View Relatedthis is the line it gives the error on.
Range("A1").End(xlDown).Offset(1,0).Select
So I'm correcting macro at work and keep running into this error:
"Application-defined or object-defined error"
Need a code using application.inputbox to get a range, then use that range to copy and paste the range's link and format to a different sheet? The specifics don't matter, I just can't figure out the syntax. Here is what I have currently:
View 2 Replies View RelatedI have the inputbox so i can set a string value,
When the inputbox Cancle button is pressed i want to exit sub,
If the inputbox value is nothink and ok button, I want the msgbox displayed then goto newname.
If the inputbox has a value do >>>>>>That>>>>>
I am using below code to copy certain data(Range("C4:R46")) and paste after certain row(After 43 rows) with 50 times
I want to popup a inputbox who asked for how many time u want to paste data, if i choose 4 then paste data after certain row(After 43 rows) with 4 times
If I choose 14 then paste data after certain row(After 43 rows) with 14 times
[Code] .....
I am trying to select a range using this method. I am indicating type:=8, but isn't the box itself supposed allow you to select a range on a sheet? I cannot do this.
My only thought is that I have disabled events and updating; could that be it?
I am using an inputbox to get a range a data from the user.
View 10 Replies View RelatedUsing InputBox Method to Select Range from Other Sheets
I need to select a range of cells from a second workbook via a InputBox or similar.
I'm trying to do that with the following
Sub InputBoxTest()
Dim MySelection As Range
Set MySelection = Application.InputBox(prompt:="Select a range of cells", Type:=8)
MySelection.Select
End Sub
But I can't select a cell range if it is located in other workbook.
I have the following code in Destination.xlsm that is intended to:
request the user to select a multi-cell range in a column of single-sheet Source.xlsm, in which some but not all cells contain "Y" (to indicate that this row of data relates to a National Account, versus a Territory Account)loop through the selected range, and whenever a cell contains "Y", copy the entire row and insert it in Destination.xlsm above a cell named "rngDest".
Code:
Sub Copy_NationalAccounts_Rows()
Const Message As String = "Select the entire range containing National Account Y flags, then press Enter or click OK."
Const Title As String = "Copy National Accounts"
Dim rngSource As Range
Dim rngDest As Range
Dim r As Range
Dim c As Integer
Dim wbk As Workbook
[Code] ........
Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:
=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)
When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....
I am looking for a Macro that will allow me to update the range of a Defined name range such as every time I add a name into the list, the only thing I will be doing is passing the client name and the list of client will be automatically updated and my drop down ListBox will show the new client name, which can be selected by the user.
View 14 Replies View RelatedI need to add a value to a Defined Name Range
ThisWorkbook.Names ("Test"), RefersTo = 10
How do I add a value to "Test" storing a tracking value
I use to keep this value on a hidden worksheet.
but my co-working messing around change this value.
hopefully using a Named Range would help prevent them from messing with it. I am not allowed to password protect the worksheets.
Say i have a range A1:B3 and i define it as "Fruit", but then later decide i want to call it "food", or even just revert back to cell numbers. How would one go about doing this?
View 5 Replies View RelatedIf I have a defined named called list that refers to range A1-A6
And A1-A6 is sheet names. How would I use this with indirect function in Vba?
Say I wanted to add the values in b6 on each cell from my defined named range, how would I go about this in vba?
I need the macro to create a named range based on a last column and row that can change. It won't change once the report is set up, but since this is a template that will be used for hundreds of projects, it needs to be flexible to accomodate a varying numbers of columns or rows
The starting cell will always be A10, but the ending cell can change depending on how many columns and rows are in the table.
Right now, the code I've created is
Sub NamePivotData()
Dim LCol As Long
Dim LRow As Long
Dim RngEnd As Long
[Code].....
I need it to refer to ='Calendar Setup'!$A$10:$*$** where * is the last column in the table and ** is the last row in the table.
How does one select a range with multiple rows and columns, then select the cell in any one corner of that range. What does the "Set" command do?...is it used when an inputbox command is assigning a range instead of a value to a variable
View 2 Replies View RelatedIn several places in my code I need to define ranges and save them in Application.Names. I need to be able to reference the last row and column in that range but I don't know how. Is there a reference within the Range object for the boundaries used to create it?
ie, the B3 in Range("A1:B3")
Not all of the ranges will have content in them and many of them will overlap, so looking for the last non-blank cell won't work.
I have a number of data sheets in excel 2010 and I have price lists for various countries I need to automate a price book type thing whereby I create a formula which reads something like: Vlookup(X,=B1,3,true) whereby the cell B1 will be the result of a formula to return the country I need the pricing for. I have the ranges setup as things like: "Australia", "France" etc and the cell B1 shows this its value based on a previous lookup. Is it possible to get this to work?
View 3 Replies View RelatedIn the attached sheet. Cell C6 represents the concatenation of aspects of a product. I want to know if it is possible to use that cell as the named range (there is a corresponding named range) in a lookup. The lookup gives me the product code.
View 5 Replies View Relatedi created list in Excel and i wrote into (a1 cell-income, b1 cell-expence, c1 cell-kredit and d1 cell-debet). i enter a2 cell income (for ex:1000) and b2 cell expence (for ex: 100) and I give condition in VBA that if income greater than expence then VBA writes a2.value-b2.value into d2 cell else if expence greater than income then it writes b2.value-a2.value into c2 cell but i only define this one row (for ex: c2, a2. b2. d2).
i would like to assign it the rows as long as i want
that is i would like to create loop
(for ex: a(i), b(i) and so on
here is
Why can't I alphabetize all of my defined name ranges in the Name box?
View 8 Replies View RelatedI need to obtain the max value in a column of numbers (DJ3:DJ242) with the range of the cells considered, set by a slider that produces a value between 1 and 500 in cell BD51
View 4 Replies View RelatedIn Sheet2 I have list:
columnA (name)
ColumnB (region)
Audi
Euro
Ford
USA
Chevi
USA
VW
EURO
Porshe
EURO
I used manager name to define range:
Cust_Name = Sheet2!$A:$A
Reg_Name = Sheet2!$B:$B
Now, in Sheet1 I did data validation and I have:
A2 = list of name (from Sheet2, columnA)
B2 = list of region (from Sheet2, columnB)
How to do, like if I in A2 I will choose Audi or Porshe then automatically in B2 will show Euro?
I would like to alter this code so that the user chooses the column where the numbers are. They aren't always in column D....
View 9 Replies View RelatedI am trying something out the ordinary, instead of hardcoding the Range, I want o find the last used row of the Range and append, then filldown.
I think my idea is good, but either syntax is off or not going to be allowed to do this.
.Range("F" & lLrwT + 1: "F").FillDown
>>I have posted this on VBAx too.<<
I have a list of numbers in Column A and in Column B I have a standard Sum formula which sums the adjacent number in Column A and the four numbers before it.
A B
1 =Sum(A1:A5)
3 =Sum(A2:A6)
5 ...
3 ...
5
6
I would like to have a formula which references another cell to define how many rows to sum from the starting cell. So in the above example, the total number of rows which are added together is 5. If I wanted to change all of the SUM formulas to add 6 rows, I would like to be able to change the number in the reference cell to 6 and all of the sum formulas would switch to adding 6 rows.
I keep running into this error when I try to create a pivot table.
The debugger highlights the following line: