I have two columns. The first is a timestamp that comes in 15 minute intervals. The next is a column of mostly zeroes with occasional peaks (I think of 10,000, but it could change). I'd like to average the peak value out over the range of zeroes before it. The peaks aren't coming at regular intervals.
View 2 RepliesTimeForce
00.2
0.50.5
11
1.52
24
2.54
34
3.54
44
4.54
54
5.54
64
6.51
70.3
for example i have this data I want to average range of some values in force column for which i only know range of values in TIME column..
For Example I want to average the values for which the values in time column are 0 to 1.5 ((0.2+.5+1+2)/4)
I deduct a formula but it is giving error AVERAGE(LOOKUP(0,A:A,B:B):LOOKUP(1.5,A:A,B:B))
I have a row of dates going across in row 2, and random values going across in row 3 from D3 onwards. I want to return the average of values in row 3 until the third last non-blank cell in row 2 and do a similar thing for maximum row but just for the last 30 cells from the right (so latest 30 days). I have attached screenshot of part of spreadsheet for better illustration.
Capture.PNG
In my macro I can come up with a range for both, as below. Both lines of macro below select the correct range I am looking for.
[Code] .....
However when I try to apply them into an average/maximum function they returned an error 1004, Application defined or object define error:
[Code] .....
When I put it as following it returned me the value of Cells (3, 4) (Cell D3) instead of the average:
[Code] .....
Attached File : Capture.PNG‎
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
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 RelatedI 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.
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?
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].....
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)
I am using the code below to copy a range and paste it over a variable range.
View 4 Replies View RelatedI 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.
I have a range "C1:Cx" where i want x to be the value from a value "overallLastRow". How do I write this?
This is just a simple question but I can't seem to find the answer in any of the hepl files. I want to refer to a range by using a variable but can't get the syntax right.
for example instead of
I am in need of code to sum the above range. But it is different range every day. My company will add the deposit amounts each day from each deposit sheet.
ie:
48000
80000
12000
3000
200
45000
10000
And so on. I have posted an example spreadsheet if needed. (The portion is green is all that needs to be summed but keep in mind it changes amount of rows included changes each day, and the yellow is where the solution goes, but I can easily change that). There are other formulas needed but I have figured those out. I need to run this macro daily just for the last group.
I have a problem of setting a range of row that might expand regularly.
I tried a simple method but...
Code:
' Count the used range row
Dim count As Integer
Dim usedrow As String
count = ActiveSheet.UsedRange.Rows.count
usedrow = "C" & count
' The items are in A1:A105
Set AllCells = Range("C4:usedrow")
I left my Excel books at home, so I can't even look up the answer, and it's incredibly frustrating, since I know it's something small that I'm missing.
I've got a code that I want to cycle thru 12 ranges, named "Retrieve1" thru "Retrieve12". This is the snippet of code that I'm having the issue with is:
For I = 1 To X
NextRange = "Retrieval" & I
Range(NextRange).Select
Application.Run Macro:="EssMenuRetrieve"
Next I
I only made the "NextRange" variable because I originally had "Retrieval" & I in the range select statement and it didn't work.
I am using this coding
LR = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Range("A" & Row.counts & ":" & "D" & Rows.Count).Copy Destination:=Sheets("Sheet2").Range("A" & LR + 1)
And it keeps giving me the error "Object required"
I am trying to copy from A-D and the row is a variable to which row was checkmarked at that time.
I am trying to create a spreadsheet that will predict the quantity of a product sold in a given month based on the number of new clients that month and the frequency with which the existing clients will need that product to be replaced. E.g. Washing Up Liquid is required by every new client and they will need a new one each month. Toilet Cleaner is required by every new client and they will need a new one every 3 months.
I have 'hard coded' the two examples into the formula on the "Non-Variable" sheet. However the Frequency of Replacement is speculation; it may traspire that a bottle of Toilet Cleaner only lasts 2 months, in which case I would have to go into each cell and change the formula to reflect this. I believe Add or SUM Every nth Cell may hold the answer but I am failing to get it to work with my data due to being a novice and not really understanding the formulae used! The second sheet is my attempt.
As you can see, it counts 3 months forward from the first month, not 3 months BACK from the CURRENT month. It also simply doesn't work: Washing Up Liquid should equal 285. Ideally I would like to keep the format of the 1st sheet as this matches the rest of the the workbook (not included).
still trying to figure it out how the logic in VBA works
'this selects a block of sells and put them in variable rormu. Right?
Set test = ActiveSheet. Range("i65535").End(xlUp).Offset(-1, -3)
Set test2 = ActiveSheet.Range("i65535").End(xlUp)
Set formu = Range(test, test2)
'here i try to create another variable for a range after last used row
Set newg = ActiveSheet.Range("f65535").End(xlUp).Offset(2, 0)
Set newg2 = ActiveSheet.Range("f65535").End(xlUp).Offset(3, 3)
Set pst = Range(newg, newg2)
I have reached a point in my code where I have a variable range selected. I want to autofill the selected range of data to a new range.
My code is:
Range(Cells(7, LastReportColumn - 1), Cells(LastReportRow, LastReportColumn - 1)).Select
Set DestinationRange = Range(Cells(7, ReqMonths + 5), Cells(LastReportRow, ReqMonths + 5))
Selection.AutoFill Destination:=(DestinationRange), Type:=xlFillDefault
The select part works. However on the second line (set DestinationRange) I get a type missmatch.
I searched and I seached but I cant find the answer. I want to paste a set of cells in a blank range that is next to data already in the workbook. I cant get the right wording for the variables in the range I want to paste to. Here is what I got so far, the red text is what I am getting hung up on. (ya I know it sucks)
Dim mycel1 As Range
Dim mycel2 As Range
Set mycel1 = Range("A1").End(xlToRight).End(xlDown).Offset(0, 6)
Set mycel2 = Range("A2").End(xlToRight).Offset(0, 1)
Range("N2").select
Selection.Copy
Range(mycel1, mycel2).Paste
Dim varAAR As Variant
varAAR = Range(Selection, Selection.End(xlToRight)).Select
. Range(Selection, Selection.End(xlDown)).Select
But the way I understand it, varAAR will only equal the first line, I want it to be the range that is selected from both lines.
How can I do an average in Excel 2003 when it's not a range but I also don't want to count a certain cell when there's no data or zero? I have attached the spreadsheet.
View 6 Replies View RelatedHow to sum using variable start and end points?
For example,
Column A is the list of start dates, and Column B is the list of end dates to be used at the variables.
Column C is a list of dates, and Column D is the corresponding temperature data to Column C.
What I would like to do is create a new column (E) that sums the temperature data from Column D based on the start and end dates from Column A and B respectively (these dates to be used to match the dates in Column C).
I have a problem with a worksheet that my company accounting system exports every month.
Attached : sample of the worksheet.
In column ( F ) , I need a macro to do the following calculations:
1- Check for the Title - if it begins with "Cost Center"
2- Check for the Title - if it begins with "Account Code"
3- Detect the Range Start just below "Account Code" , and End with the row above "Total"
4- Concatenate the string written in each row of the range with the string in "Cost Center"
The Story is:
Each Month I've this worksheet with hundreds of Cost Centers and subsidiary Account Codes, And to be able to analyze the accounts efficiently I need to concatenate both Cost Centers & Account Codes manually ( as you see coloring sample in the attached file ). Which led to wasting many hours , and high risk of error while copying and pasting formulas.
I've attached a sample worksheet. I have a series of time values in ascending order (column A) and then 5 sets of data that correspond to the time values. I wish to be able to enter a minimum and maximum time range then selectively average the numbers from the sets of data that fall within the time range, but I can't think of a simple way of doing this.
View 5 Replies View RelatedMAX function with a variable range. I want find the highest value in a changeable range in a column. The problem for me is, how can I automatically change the range where the highest value is returned.
Here is an example:
Cell A1: 14
In cell A1 is the number entered that specifies the range. In this example the range is 14 rows =MAX(A18:A31).
A2 126.36
A3 126.16
A4 124.93
A5 126.09
A6 126.82
A7 126.48
[Code] .....
Using =MAX(A18:A31) returns 128.57. So far so fine.
But what is the MAX function if the range value refers to the number entered in cell A1? If I change the value in A1 from 14 to 20 how can I make the MAX function flexible that it refers to cell A1 as the range value?
Using the value 20 in cell A1 the MAX function would be =MAX(A12:A31). I can change this manually of course but I want a MAX function that refers to cell A1 as the range value.
I want also mention that the data series is update every day, so that each day a new value is added in column A, e.g. A32, A33, A34. and so on.
I'm trying to understand a code from work and I can't get it. I copied only a part of the code here so please don't run it. Also, I have manual inputs in columns L and M (nodes labels such as 1, 2 and 2,3) and section labels on column N (such as BarFT3, BarFT4 etc):
[Code] ..........
What is this loop doing? I just get the first For loop: goes through every lable on colum N (from 1 to ne) but then what?
What is happening to range nudo(n,j)? How works this ">" sign between nudo(n.j) and nn?
I need a simple VB Code to clear the cell :
K106 = BI210:BU269
Need to clear the range BI210:BU269
if K106 = BI271:BU341 then need to clear range BI271:BU341
I would very much like to sum a range of values using variables instead of hardcoded ranges. I have tried the following (and variations of )
Range("D2").Formula = "=Sum(Cells(rowIndex1, colIndex1), Cells(rowIndex2, colIndex2))"
where rowIndex1 is starting row number, colIndex1 is the starting column number.....
It does not work.