Incorporating Variable Into Average Function To Locate Cells
Nov 12, 2012
I am trying out VBA to write a macro in order to average every 25 cells in a column. My attempt so far is
Dim myrow1 As Integer
Dim myrow2 As Integer
myrow1 = 2
myrow2 = 25
Range("G2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R " & myrow1 & " C3:R " & myrow2 & " C3)"
myrow1 = myrow1 + 24
myrow2 = myrow2 + 24
So I am hoping the first ActiveCell.FormulaR1C1 gets read as =Average(C2:C25)
However I just get Run-time error '1004' Method 'FormulaR1C1' of object 'Range' failed.
View 3 Replies
ADVERTISEMENT
Jun 29, 2013
I'm trying to use loop to activate 3 workbooks "OSB1", "OSB2", and "OSB3" and their respective sheets. "OSB1" has sheet "OSB1" in it, workbook "OSB2" has sheet "OSB2" in it etc.
The digit on the end of each OSB is the variable I am using in the loop.
What I am trying to come out with is the following 3 workbooks and sheets being activated:
OSB1.Sheets("OSB1").Activate
OSB2.Sheets("OSB2").Activate
OSB3.Sheets("OSB3").Activate
I've given up the code as I can't work out the inverted commas:
VB:
Dim x As Integer
For x = 1 To 3
OSB" & x & ".Sheets("OSB" & x & "").Activate
Next x
Is it possible to do this at all? The bit after "Sheets" is correct. It's part with the first "x" after the first "OSB" which is incorrect now...
( In my actual code I'm trying to do more than just activate the 3 sheets but need to work out the syntax for this to be able to edit the remainder).
View 9 Replies
View Related
Jan 4, 2014
I am working with Excel 2010 and I have a problem that I can't seem to figure out. I am trying to find the first nonzero cell and begin an average function from that cell and down through 29 rows (a total of 30 rows).
Sub average()
FinalRow = Cells(Rows.Count, 3).End(xlUp).Row
For I = 3 To FinalRow
If Cells(I,3).Value 0 Then
'I use the above to identify the first nonzero cell
ActiveCell.FormulaR1C1 = "average(........)
This is where I am stuck, I don't know how to code the first nonzero (which can change with different data) cell and average rows below it.
View 7 Replies
View Related
Oct 28, 2008
I'm once again here asking for a smart trick to do a difficult (for me) thing. I need to write a formula capable of averaging the n cells on its left. The problem is that the number of cells to be averaged is a variable contained in anothe cell therefore the exact number is not known (a priori) and changes at every sheet refresh.
View 3 Replies
View Related
Jun 8, 2007
I have data in Column A as follows:
A1 15
A2
A3 20
A4 56
A5 45
A6
A7 71
A8
A9 23
where cells A2, A6 and A8 are empty.
I want to be able to AVERAGE or SUM the first four nonblank cells. I know I could manually select the cells, but I have a spreadsheet with 30 columns and 40 rows, and the data (including empty cells) in each column is different.
Is there a single formula that will find the first four nonblank cells and then perform the AVERAGE or SUM function?
View 9 Replies
View Related
Jun 18, 2008
If I have numeric values in a cell examples c5 = 126, and d5 = 135. How do I place these values in c5 &d5 in another cell as a cell reference within the formula ex: =Average(f126:f135)
View 3 Replies
View Related
Jan 15, 2006
how to make the average,median,max, and/or min functions ignore cells in the referenced range that contain the #value! error? All four of the functions are returning #value! because one or more in the referenced range have the error.
View 9 Replies
View Related
Jul 10, 2008
I've got a spreadsheet that I do every month with columns of numbers that I average. This sheet has to match about 10 others similar. The columns are divided by Weekdays, Saturdays, Sundays. But some months there are no entries for certain cells on Saturday or Sunday.
I thought that if I just used the Average function, it would dismiss and not count the blank cells. Alas, apparently not. I've highlighted in yellow the one column that I'm really having trouble with.
View 7 Replies
View Related
Apr 4, 2014
I'm trying to use the MATCH function. I want to locate an exact name. I used the formula:
=MATCH(A6,A3:A10,0). This is the name cell I want to retrieve, the column in which the information is found, and 0 for the exact match. The return answer I get is 4.
View 3 Replies
View Related
Jul 7, 2006
I am trying to find the starting effective month for a workyear. The criteria for the selected month is that the data BEFORE the effective month is all zeros. I manage to get the result if the data AFTER the effective month is ALL non- zeroes. If there is any zero, the data fetch will be inaccurate due to the COUNTIF formula. Is there any other way or formula that will enable me to get the result. File attached for testing and reference
View 6 Replies
View Related
Dec 9, 2008
I have looked at many different examples of uses of the average function but I haven't found any examples of what I need it to do. Here is the code I am trying to use, but I am getting some errors.
View 6 Replies
View Related
May 20, 2014
I'm working on a workbook that will track staffing patterns.
The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden". I've attached the workbook to this thread. The password for the form is "j".
On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] ........
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] .......
I have two more functions that aren't working due to the fact that the source values are percentages and NOT plain numbers. The above functions work great for SUM but not for percentages. EXAMPLE--Let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%.
[Code] ........
How might I use the following functions to find the average of the source fields instead of the SUM?
View 7 Replies
View Related
Jun 12, 2014
have a formula or something along those lines that will look for the data you type in to the selected cell and will show you on how many workbooks it has been entered in. For example, I have workbook a, b, c, d, e, f. all have 12 sheets. On these 12 sheets there is a place to enter the serial number of an item. What I would like to be able to do is on a different workbook be able to type in a serial number and have it show which workbooks it has ever been typed in and its location on said workbooks. and if possible the results of the item which would be whether it was accepted or rejected( this bit is not totally must have. but the workbook and sheet location is a must.)
View 1 Replies
View Related
Jan 23, 2007
TotHCInv.Value = WorksheetFunction. Sum(KRInv, PBLInv, CRInv, PVInv)
If i >= 34 Then CPSCtphRMA.Value = WorksheetFunction.Average("G" & (i - 30) & ":G" & i)
The first line runs properly, but the second line bugs out with the error message "Unable to get the Average property of the WorksheetFunction class". I can simply do the math, but I thought that using the worksheet function would be easier than summing and dividing. I'm curious, though, as to why I can't seem to use the Average function.
View 3 Replies
View Related
Dec 8, 2009
I needed to convert PartNumber to a string using Cstr(PartNumber).
The following VB code searches an Excel spreadsheet for a Production Order and then looks for a Part Number in the corresponding row. The Production Order can be listed multiple times, but there is only 1 Production Order/Part Number combo. When searching for the Part Number, it will find letters, numbers with a dash and letter/number combos, but won't find integers.
For example, it will find ABC, 3500-01 and T1000, but not 200.
View 3 Replies
View Related
Dec 17, 2013
Need VBA code to start with row 5 and look through COL F for any blue cells, IF=BLUE change the percent to 0% for that cell.
The RGB color code for this particular shade of blue is: Red 153, Green 204, Blue 255.
View 2 Replies
View Related
Oct 31, 2006
1. Find a Text
2. Select the cell
3. Select the row and columns above the data we found
4. Copy it
View 2 Replies
View Related
Jun 14, 2013
E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
View 14 Replies
View Related
Aug 10, 2009
I have a spreadsheet containing all products in sheet 1. I have a list of promotional codes in sheet 2. In sheet 1 I want to say 'If cell A2 is contained in column B of sheet 2, then say YES, if not then leave blank'.
I've tried the following but it doesn't work:
=IF(VLOOKUP(A6,Sheet2!B:B,1,FALSE),"Yes"," ")
I get #N/A when the item is not in sheet 2 and #VALUE! when it is.
View 9 Replies
View Related
Sep 14, 2009
Using the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.
Macro
Public Sub FindText()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter the text that you want to search for:", "Start Search!")
If myText = "" Then Exit Sub...................
View 9 Replies
View Related
Jan 27, 2009
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 Related
May 26, 2008
I have a formula: =SUMPRODUCT(($D$18:$U$18>=$V$18)*($D$18:$U$18
View 9 Replies
View Related
Oct 2, 2007
I have the following sendmail macro. However I need the "To" section to be the result of a lookup in a worksheet. The specific function that i need it to be is: =VLOOKUP(A837,'Tenacity Jobs'!1:65536,5,FALSE)
How can I incorporate this function so it will insert the result of the lookup into the "To" box ?
View 11 Replies
View Related
Jan 13, 2012
The following code (blue font) finds specific worksheets in an active workbook, and copies them into a new workbook then names the new workbook the same name of the worksheet. It all works perfectly.
The second code below (red font) imports some vb into the new workbook. It works fine if I manually open the new workbook and run it in the immediate window. However, I would like to incorporate the red code into the blue code so it all runs as one. I've tried inserting the red line after the "ws.copy after" line and after the ".saveas" line, but no dice.
For Each ws In wb.Worksheets
If UCase(Left(ws.Name, 2)) = "CC" Then
Set NewBook = Workbooks.Add
[Code]...
View 9 Replies
View Related
Dec 3, 2009
I have the below formula to make a simple calculation:
=SUM(AC1880+AJ1880)
I would like to adapt the formula to ask IF AG1880 is empty or not. If AG1880 is empty then I would like the formula to NOT make the calculation, but instead return "--" (nothing).
If it contains information, then I want the calculation (AC1880+AJ1880) to be made. If AG1880 contains information it will be text in a text format.
View 9 Replies
View Related
Dec 18, 2009
I'd like to calculate an average over a variable range. In col.A there are grades from A4:A21. In col.C there are the values for the start row of the range and in col.D the values for the end row of the range.
For instance the value in C4=4 and D4=9. In cell F4 I want the average calculated over A4:A9. Value in C5=10 and D5=15. In cell F5 I want the average calculated over A10:A15.
View 2 Replies
View Related
Dec 13, 2008
Hi there, been using this for a number of years now, but never had to post a question - I guess most things are already answered - but this one is a bit more tricky.
I want to calculate a totals and a weighted average based on months from a data set that has duplicate ID's and variable data (attached example).
I would like to calculate the total tonnes by month and the average grade of those tonnes. Using the 'month ID table' as a reference to the 'Raw Data table'.
Is this possible using if and vlookup functions or will require a higher level of programming knowledge?
View 6 Replies
View Related
Oct 31, 2013
I have a number of files organised in a number of folders on Dropbox. I create a macro to enable me to be able to navigate to a particular folder and then get a list of all the files in that folder to be placed within certain cells on the spreadsheet I'm working with in Excel.
View 2 Replies
View Related
Aug 27, 2009
I'm trying to create a dynamic Named Range using VLOOKUP in place of a sheet name. (Using Excel 2003 & Win XP Pro SP2).
To illustrate:-
Employees.xls contains employee's details on separate sheets for each department, e.g. Production, Admin, Sales, Personnel, etc. Each sheet is 12 cols. and 1 header row. Sheet 1 of this book contains a 2-column Master List of all employees and their departments. MyBook.xls has an employee's name in A1.
I can get his department by using =VLOOKUP(A1,MasterList,2,FALSE) and what I want to do is incorporate that into the following in place of 'Admin' so that the range will refer to the correct department for whoever's name is in A1.
=OFFSET('[Employees.xls]Admin'!$A$2,0,0,COUNTA('[Employees.xls]Admin'!$A:$A)-1,12).
View 9 Replies
View Related
Jun 7, 2013
Cell D39 has the formula
=(D4+D11+D18+D25+D32)/5
I want the cell to remain blank until at least on variable is entered, but then I want it to calculate the average of only the cells that have a variable in them.
Just so I am clear
D4 = 20
D11 = 50
D 18 = BLANK
D25 = BLANK
D32 = BLANK
Average would be 35
View 4 Replies
View Related
