I have made a very basic spredsheet which using an assumption table makes a particular column of values which works out the revenue. Then I just drag the box down to the number of rows I want it to go down.
However, I want to be able to enter a number in a CELL away from the tale eg. 50 and have the number of rows in the column go down to 50
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
View 4 Replies View RelatedI reckon this should be easier than I'm finding it, but I just can't seem to find the right combination of functions to solve this. In column A I will have an entry every 5 or 6 or 7 rows with blanks rows between. In Column B starting at every row corresponding with an entry in Column A there will be a short column of figures could be 1 2 3 or four or more of them. (the're will always be a blank between the last number in a group in B before the next entry in A). In column C starting at every Row corresponding to an entry in column A I want the sum of the group of figures in Column B.
But here's the rub.... The most important thing is that I can use the same formula all the way down column C. So click the one formula at the top and drag it all the way down 1000 rows.
I'm trying to write a macro with a variable number or rows depending on the total number of rows the workbook has.
I tried like this:
Code:
Sub provaanova()
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range(("$J$2:$J$") & Range("J" & Rows.Count).End(xlUp).Row) _
, ActiveSheet.Range(("$J$2:$J$") & Range("J" & Rows.Count).End(xlUp).Row), False, False, 99, "ANOVA", False, _
False, False, False, , False
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
and:
Code:
Sub Provaregress()
r = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$J$2:$J$r") _
, ActiveSheet.Range("$K$2:$M$r"), False, False, 99, "ANOVA", False, _
False, False, False, , False
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
But it gives me an error. The only way i was able to do it without error was:
Code:
Sub Provaregress()
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$J$2:$J$53968") _
, ActiveSheet.Range("$K$2:$M$53968"), False, False, 99, "ANOVA", False, _
False, False, False, , False
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
But as u can see it has a fixed number of rows. ( I need variable number of rows detected on column J, and then run regression with K,L,M as depending variables).
I'm trying to write a macro that will validate data in variable amount of rows but will always be in a specific column. what i'd like to do is count the amount of row entries in Column A to give me "iRows." Then validate data in column E from E1-E"iRows." Currently my data validation runs for the entire column which means i can not validate blank entries. Once I can limit the validation process from E1 to E "iRows" then I can consider blank fields and mark them as invalid.
Columns("E:E").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator _
:=xlGreater, Formula1:="1"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Award Amount"
.ErrorTitle = "Award Error"
.InputMessage = _
"Please enter the current expected total value or current award amount for this contract."
.ErrorMessage = _
"Award amount may not be set to 0.00. If you do not have an amount awarded simply make the award amount equal to the paid amount."
.ShowInput = True
.ShowError = True
End With
I've tried using different ways of counting the rows and have been able to define "iRows" the problem is defining the range to only column E from E1 to E"irows."
As you can see in the attached excel doc, I have calculated a column for "Number of Claims Per week" and also "Dollar amount Per claim". This was done using VLOOKUP and distributions. That is not the issue.
The issue I am having is creating the "TOTAL" for the week. So for example, if week one generates 4 claims, I need to be able to add up claim amount #1-#4.
It seems as though this would be a basic function, but I can seem to find a way to add up a certain number of cells, based on a variable generated from a random number.
If you take a look a my excel sheet, I am trying to solve for the "Claim Total" column on the far right. This column should be the sum of (Number of claims * The individual amount per claim). Note that if there is 4 claims in that week, I would have to add up claim 1-4 to get that weeks total.
Question2.xlsx
I have a sheet with a company name in column A, and a list of comma separated values in column B. I can easily convert column B to multiple columns with the Text to Columns function, but then I'd like to insert each individual value in a new row in column B.
So, at the moment I have data that looks like this:
Company A
Value 1, Value 2, Value 3, Value 4, Value 5
Company B
Company C
Value 1, Value 2, Value 3
[Code].....
I am working on a macro where I am creating a formula to string together some text columns and then copy the formula down the entire column. The data source I will be performing this on will change in number of rows period to period. The data would be in columns A,B & C and the formula is in D. The formula in D is stringing together the data in AB & C and then I want to copy and paste that formula down to the bottom of all of the data. What would the code be for the copy and paste with variable rows?
Inserting Variable Number Of Rows and Populating Data
View 10 Replies View RelatedI have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
View 2 Replies View Relatedi hv following code , which is working fine.
Sub M_0_1_project_20080107_ask_how_to_make_last_row_as_variable()
Range("F1") = "month"
Range([e2], [e6].End(xlDown)).Offset(0, 1).FormulaR1C1 = _
"=month(RC[-1])"
Range("g1") = "yr"
Range([f2], [f6].End(xlDown)).Offset(0, 1).FormulaR1C1 = _
"=YEAR(RC[-2])"
End Sub
i like to change to code as following , but it cannot work , hope some one can help me.
Private Sub Worksheet_BeforeDoubleClick _
lastrow = 6
Range("F1") = "month"
Range([e2], [e[lastrow]].End(xlDown)).Offset(0, 1).FormulaR1C1 = "=month(RC[-1])" _
Range("g1") = "yr"
Range([f2], [f[lastrow]].End(xlDown)).Offset(0, 1).FormulaR1C1 = "=YEAR(RC[-2])" _
End Sub
As i have many variable with same function, tried of keep change last row number.
So I'm trying to use the SEARCH function in excel to search for a particular text in a cell. I am trying to create a macro for it.
View 9 Replies View RelatedI am trying to make a spreadsheet that will have a weekly schedule and will recognize and make some sort of message box or something when a person's name is used twice in the same range. For example, if Smith is in B2 and in B4 then message box. I'm not sure how to do this.
My thought was to make a variable range to check if anything in B2:B7 is equal to say, B3. But the range B2:B7 would have to exclude the cell that is compared, B3. Otherwise it is always true.
I have 2 worksheets - "Results" and "Historical". I am trying to record a macro that copies data from "Results" and then pastes into Historical. The data I want to copy will always be in "Results" A1:A10. When I want to update the macro (which will be weekly), I want it to copy A1:A10 and then paste into "Historical". Simple enough. Here is the problem:
Since this will be updated weekly, the first time I update I will want "Results" data copied into "Historical" A1:A10. The next time I update, I want the data copied into "Historical" B1:B10; then C1:C10, etc. Essentially I want the keep a running total of all of my prior data.
So I need the macro to be able to say something like - paste date into the first available column. If there is data in A1:A10, then paste into B, then C, D. How do I make the Macro variable?
I would like have a input box in which a user enter a number and then I would like to be able to use that number in other worksheets within the same workbook. How do I declare the variable for use with other sheets. I know I would first use
Dim intRows As Interger
But now how do I make it global.
I am trying to make a calculation using variable text.
My outcome cell is D3 and my two variable cells are C3 and F3. In C3 I want to be able to insert a staff members name, of which we have 6 different members of staff. In F3 i need to insert the week number which runs 1 through 6. The outcome is the individuals place on the week roster which needs to be a number between 1-6 inclusive. SO basically if F3=1 & D3=J.SMITH then I need D3 to equal 1 keepng in mind that if F3 & D3 have 6 possible variables.
How to make + symbol variable in the below formula?
This formula is in G25 and symbol is in F25 has + & - symbols
=(NOW()-E$592)+E614
How do I make a column array variable within a function? I've done nested vlookups to get a column number, but the function I'm using doesn't use a "number", it uses a column aray. '=AVERAGEIFS(L:L,K:K,">="&B$2,K:K,"<="&B$3). The "L:L" needs to be moved according to Market, (or column choosen).
View 2 Replies View Relatedi am looking for excel custom number format for 0 (zero) number that make center alignment..
for example ;
sample (when type 0 (zero) number)
after custom number format
- (right alignment)
- (center alignment)
how make center alignment with custom number format for 0 (zero) number..
I have read meanwhile dozends of articles and comments about absolute and relative cell references.
But I found nowhere an example on how to make a the column part of a cell reference variable and the row absolute.
Is
K$10
a valid expression?
In opposite to $K10 is must work this way
I would like to run a calculation based on selected active range in sheet. I tried several methods and cannot make it work.
View 2 Replies View RelatedI have used the formatting option to make numbers that once appeared as 1, 2, 3, 13, etc into four digit numbers, to appear as 3001, 3002, etc.
The problem is, I have other data whose original values are the 3000 formats, and Excel won't combine the two or sort them correctly.
Is there a way I can format it to change the actual value?
I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range
Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
Is there a way to a formula that would show the following? A plus B = C but if A is below 0, stop the value at 0 then add column B:
Columns:
A B C
-2 5 5
1 5 6
0 5 5
I'll try to keep this brief. Our part #'s look like this. XP014567. Where the first pair of characters stand for a value(say XP=Bolt), the second pair stand for a value(01= Grade 2), and so on. What I would like to be able to do is type in the part numer and have the description automatically populate. Is there an easy way to do this? I'm running Excel 2003.
View 4 Replies View RelatedI like to make my numbers with no decimals, with a comma. This means I have to constantly press a number of buttons to put them in the format I want. Not too big a deal but is there a way I can make one of the buttons in the number group default to the way I want the numbers to appear?
View 9 Replies View RelatedI import a sheet and some records have 2 rows some 3. is there a way to add the extra row as needed based on column A?
how do i make a number list. i want to start at 8000 then i want excel to auto generate up to 9000. it will take ages to sitt and punch 8000 8001 8002 ......
Next problem
how can i sett that cell A1 is connected with B1. so when i sort by number it follows. Example:
I have a cell that is going to be used for the quantity of a certain item. I would like to dummy proof this sheet as much as possible so that if someone puts in 21.5, that it will do something like change the value to either 21 or 22, since there can't really be a .5 of this item.
View 5 Replies View RelatedHow do I make a row highlight based on a given number in a cell...
For example my spreadsheet has a balance column...when the balance shows 0.00 for that line i would like it to hightlight gray