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
I have figured out how to save the workbook using a variable to saveas but I would like to create a new folder to place the new workbook into. I have tried a few things a am not having any luck. I recorded a macro and the code it produced is as follows.
View 6 Replies View RelatedI am using Selection.ClearContents command to clear data from column F and G. I know the last row as 230 but the starting row is the first empty cell which I find using Do While loop in range F31 to F230. The variable that stores the first empty cell number is 'r'.
Range("F &r:G230").Select
Selection.ClearContents
I need to create a macro that clears the contents of a range of cells. However, that range of cells is variable.
I know my starting cell (C9) and I know the selection goes over to column Z every time but the number of rows I want to clear after that may change.
Also, whatever that first cleared range is, I would need to skip the row below it then clear the same number of rows below that. (not entire row of course, just the ones from column C to column Z)
I have a macro that prints out schedule information for my production lines. I recorded the macro and made a few changes. What it does is print out the page header, delete about a dozen lines and then print the remainder of the schedule. Often the the remainder of the schedule is not all relevant data as production has completed orders. What i want to do is, using an input box, ask the user what worksheet row he wants the body of the schedule to start printing from. Here is what i have now.
Sub auto_open()
Workbooks.Open Filename:="S:BI-K11.xls", UpdateLinks:=3
Application.WindowState = xlMaximized
ActiveWindow.SmallScroll Down:=-3
Rows("7:13").Select
Selection.Delete Shift:=xlUp
Range("A1:O26").Select
Selection.PrintOut Copies:=1, Collate:=True
roww = InputBox("Rows")
Sheet1.Range("A1").Resize(roww + 10, 2).PrintOut
Workbooks.Open Filename:="S:BI-K21.xls"
Rows("7:19").Select...............................
is where i would like the number 13 to represent the input box variable so that the rows to be deleted would be from 7 to "last row to be deleted - 1"
I have a question about making a variable selection.
In cell X25 is written "B9" and in cell Y25 is written "M39".
Now i want to make a selection by a macro from cell B9 to cell M39.
If the weight selected is between the ranges e.g 1.5kg for England so that it returns the higher value 10.69. At the moment if I put in the vlookup formula it returns the lower value of 9.60.
Weight KGEnglandScotlandWales0.59.109.109.101.09.609.109.702.010.6910.6910.693.012.2812.2812.28
Is there a way to have the print dialog box come up, make a selection, and put that selection into a variable?
Ex:
Code:
Application.Dialogs(xlDialogPrint).Show
brings up the print dialog. I need to be able to select the printer and have the selected printer put into a variable in my vba code.
I have tried Ex: w=Application.Dialogs(xlDialogPrint).Show, but all I get is w=true
The reason I need it is because I will be printing via vba code, but the printer may vary for different print jobs.
How do I change the selection of a range with a variable, and not a hardcoded number in XL2003? I have to update a set of spreadsheets every month, and it's a hassle to have to constantly open my pivot table worksheet, copy, open the summary worksheet, paste ... etc. etc. etc. The code below is my attempt at creating a ComboBox with "January, February, March, etc." and every time I select a particular month it will automatically copy data from my pivot table worksheet into my new summary worksheet in the correct column. The range of data from my pivot table worksheet will never change, so I have no problem hardcoding that in, but based on which month is selected will alter which column the data goes in in the new worksheet.
I want to be able to write code for one month (say January) and then when I want to use a different month (say February) I can just change the column number and call up the originial January code.
Private Sub ComboBox1_Change()
Dim ColNum ' This is the variable I want to change based on which month is called
If ComboBox1.Value = "January" Then
ColNum = 1 ' Column number for Column A- Where my January column is
Elseif ComboBox1.Value = "February" Then
ColNum = 2 ' Column number for Column B- Where my February column is
' etc. etc. for each month
End If
Call January
End Sub
Private Sub January()
' Just a quick msgbox to make sure the previous macro is calling this one
MsgBox "Is this macro running?", vbQuestion + vbYesNo, "Check"...........................
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 RelatedI have a formula: =SUMPRODUCT(($D$18:$U$18>=$V$18)*($D$18:$U$18
View 9 Replies View RelatedI have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set"
Sub test()
Dim StartRng As Range
Dim Buffer As Range
Set StartRng = WorkSheets("Sheet1"),Cells(1,1)
StartRng.Activate
ActiveCell. CurrentRegion.Select
Buffer = rngStart.CurrentRegion.Copy
' I also tried the following line of code but that didn't work either
'Set Buffer = rngStart.CurrentRegion.Copy
..
...
End Sub
I'm trying to write a bit of code that will allow me to clear some sheets. One of the sheets has formula for x number of rows, and these need to be left intact. Under these forumla there is an area where data is pasted, and this needs to be cleared becfore starting the process.
Colum A is blank, apart from cell A1 (the column heading) and another cell (variable row) with the value 'Paste here'. I've used this variable cell in other macros on the sheet as a marker, so need this to stay. I was going to use this code below, but I don't know how to tell it to select from the current active cell to the bottom of the spreadsheet.
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.
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 RelatedHi 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?
I need an action to allow for the random selection of a predetermined series of number codes which represent a given letter.
It is probably easier to look at the attached sheet and the previous discussion to see what I mean.
Previous discussion:
http://www.excelforum.com/excel-work...-of-cells.html
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
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.
I have a workbook with a field that contains the users name in cell B1. The first time the user opens the workbook, they put their name in this cell. Is there a way that that name can be saved to disk outside of the workbook in the same directory as the workbook is saved and a formula be put in workbook.open so that it will go get this name variable and insert it in B1 so the user doesn't have to enter their name each time.
I now have them putting their name in and saving the workbook which works until I send out an upgrade. Then they have to type in their name again.
I would like to save a module level variable but do not know how to do it. I have tried using the public key word. If I put it in the sub, I get an error when it tries to compile the sub. If I put it outside of a sub, it just doesn't work. Surely this is something easy.
View 8 Replies View RelatedI found a thread that is very much similiar to what I tried to achieve that is to save 110 files into CSV file format here Save Multiple Files In Folder As Text Format.
May I know what changes should I make to this code in order to save it into CSV file format?
ActiveWorkbook.SaveAs targetFolder & Left(aFile, Len(aFile) - 4) _
& ".txt", xlTextMSDOS
I have a spreadsheet which contains our fleet information covering many vehicles in one list. The teams for these vehicles are listed in Column A. What I would like to have is:
A dropdown box to pick from All, or the various teams in A1To hide every row that doesn't match the selection of A1To reveal every row that contains the selection for A1 at any point in column A. e.g: "*Dog Team*"
This is because something might be listed as "Moved from Dog Team" or "Dog Team, temporarily on loan", or "Dog Team V66.m"
I have taken a small sample of the data I will be working from and made a very simple drop down box which displays the pages of information for the vehicles by hiding/unhiding blocks of columns:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Range("A2") = "MOT/Service" Then
ActiveSheet.Columns("A:P").EntireColumn.Hidden = False
ActiveSheet.Columns("Q:CE").EntireColumn.Hidden = True
ElseIf Range("A2") = "Contact Details" Then
[Code]....
But because there is also filters on the sheet, meaning the positions of the entries based on rows can change, I need it to search for the "*xyzzy*" method, but am unsure how to do this! While just using the Filters will work, due to some human limitations I have been asked to have a specific drop down box in a very specific location with instructions next to it.
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.
I'm trying to calculate the average for a range that begins with cell B15 and has various end points, depending on the day (since I'm pulling 2 actual years of data that strips weekends and holidays, as opposed to going back a set amount of days/years). Syntax for cell B4 to reflect the average of range B15 to LastRow? I tried several things and it didn't work. Rows 1-12 are being reserved for the summary calculations that will then be pulled into the final Dashboard.
View 1 Replies View RelatedI am still learning VBA and am wanting to know can you save a file with a variable week number?
e.g file name = 657 13082012 CP
The Following week would need to be 658 20082012 CP
I am still a novice in you standards, but have run into a problem and hoping you all will have some insight for me.
I have a workbook that I am parsing data from the first sheet and putting the data onto the second sheet. Before I place the values in I want to format the cells with boarders, colors, alignment, and in some cases validation (but I can worry about the validation later).
Currently what I am doing is using a 3rd page that has a group of cells that have the formats I want in place before I start. The code will then parse the data and when it finds the data I need go to sheet 3 and grab a range of cells and paste those cells to sheet 2 and then write the data over those cells. This allows me to format the document as I create it.
The problem is that I am going through 25k of lines and on average this function is running 40+ minutes. If I run the same script with out the formatting it only takes at most 2 minutes.
So on to the question.
Is there a way that I can start of my script by grabbing all the format information before I parse the data and save it as a variable? Then instead of doing a copy of the format it would just apply the formatting that is saved already. I would think this would be much faster of a process.
I have been trying to determine the maximum/minimum value and additional count from a variable range which then I can use to subtract the first data of the range; I have been able to do that for a fixed range but not a variable one.
I have Column A with random positive numbers.
I have Column B with random negative numbers.
I have Column C with random numbers.
I have Column D with random numbers.
I have Column E with random 0’s and 1’s.
I have a set of 1600 cells of numerical data on each of the columns and there will be times when the random 0’s and 1’s from Column D will have appear repeatedly before changing to the opposite number, fx, I will get 7 nr. 1s before I get a 0 (zero).
I have been able to obtain the values in 2 cells within a fixed range:
If the last 8 cells in Column D have been “1” then I do:
F1=MAX(A1:A8)
G1=INDEX(C1:C8;MATCH(9.99999999999999E+307;C1:C8)) ---(Which provides the first number of the range in Column C after Column D has changed from 0 in D9 to 1 in D8)
H1=(F1-G1)
If the next 3 cells in Column D have been “0” then I do:
F9=MIN(B9:B11)
G9=INDEX(C9:C11;MATCH(9.99999999999999E+307;C9:C11)) ---(Which provides the first number of this range after Column D has changed from 1 in D12 to 0 in D11)
H9=(G1-F1)
Additional to this, the COUNT has also been challenging since I want to obtain in Column I, the COUNT of repeated 1’s from each range of 1’s in Column D; and also in Column J the same but for 0’s.
Fx:I18 (size of the range of 1’s originated from D1:D8)
J93 (size of the range of 0’s which consist to be the next range before 1’s were originated)
I12X (size of the range of 1’s which consist to be the next range before changed to 0’s)
However, that only works for a fixed range and continuous updates are made.
Book1.xlsx
The first line of the code chooses the columns to select; all columns until there is no value. From there I need to have it sort those columns based on row 1. The problem is that the columns chosen are variable. It could be columns I:N (as shown below) or column G:Z or any other combination. (The code below was recorded if that matters at all.)
View 2 Replies View RelatedOn 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 Related
