I want to create a loop that goes down all the rows in my spreadsheet and does the formula (end/beginning -1) for all the rows of cells. The "end" cell is the cell that is the farthest right in the row (some sort of end.xlright) and the "beginning" cell is column D of the row that is being calculated.
View 4 RepliesI wanted to do a multiple return vlookup so I used an Index formula. I have account names and am trying to vlookup contacts associated with those accounts (4 max). So I have a list of Accounts all with 3 blank rows in between them.
How do I loop an array formula? So far I have the code..
[Code] .....
This finds the 4 contacts associated with the first account in C5 which I named the cell "myVar". I now want it to find the contacts associated with the accounts in C9, C13, C17 and so on until the first empty cell.
[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation.
I'm using dynamic name ranges, as follows, for the detail:
WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1)
Other detail data is defined as these examples show:
GM_X =OFFSET(WO_Num,0,8)
OpenDate =OFFSET(WO_Num,0,1)
All detail data begins in Row 6 in the detail spreadsheet.
In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand.
Here are two examples of the calcs I'm using in the summary:
=COUNTIF(WO_Num,">0")
=COUNTIF(GM_X,"X")
Can someone tell me what is going on? What I'm doing wrong?
I need to figure out a formula for cell F17 that will calculate a percentage change only for the months that have data in 2009. The way it is set up right now I have to go in every month and change the cell reference of the formula to include the latest data. Since the 2008 data is totally populated the formula gets messed up if I include the months of 2009 that have not yet occurred.
View 2 Replies View RelatedI have a code and I want to run this macro whan a specific cell change (which has sum formula) and this code also has some calculation. And I m not understanding to overcome this problem through Calculate event.
View 2 Replies View RelatedIs there an excel formula that can calculate a proposed selling price by entering a desired profit margin percentage and knowing the cost of goods sold?
I know that gross profit is calcualted by subtracting the cost of goods sold from the selling price and that the gross profit margin is calculated by dividing gross profit by the selling price..but not coming up with a way to back out a selling price by just knowing the cost of goods sold and entering a desire gross profit margin?
I have a piece of code attached to a combo box. When selected the code loops through 3, even 4 times for no apparent reason. Has anyone come across this before? The loop can start either after the final "end sub" or part way through (.clearcontents mainly) Have included the code just in case it is something obvious.
Private Sub cmbDI_GrowthBasis_Change()
If UCase(Worksheets("Tables"). Range("GrowthNo")) = "NIL" Then
Worksheets("Detail Inputs").Range("DI_GrowthRateTitle") = ""
With Worksheets("Detail Inputs").Range("DI_GrowthRate")
.ClearContents
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Interior.ColorIndex = 15
.Locked = False
End With
Else
Worksheets("Detail Inputs").Range("DI_GrowthRateTitle") = Worksheets("Tables").Range("GrowthNo") & " :"
End If
End Sub
I'm trying to write a code to make a number of buttons visible depending on a cell value
I have 10 command buttons all are invisible and I want to show only the first x x is the value of cell "A1" in "Sheet1" (will be from 1 to 10) Command buttons names are default names (CommandButton1, CommandButton2, ... , CommandButton10)
Note: I'm working with a worksheet not a userform
i want a macro that will start on D25 and enter a 1 for five rows up to D29
than change and enter a 2 starting on d30 and enter a 2 for five rows
up to D34 than change an enter a 3 up to D39 ....... on and on up D225.
I'm trying to wrap my head around this. I have in a formula in one sheet pointing to a cell in another. Once I insert a row the formula adjusts. I need them to remain static.
I have tried searching, however I'm not 100% on the terminology.I have tried the F4 shortcut and put the $ signs in, however they still change.
I have the below code which converts a range to PDF and saves it on our network drive. I would like to change my code to a loop so I don't have to do so much typing. There are 68 rows between each range.
VB:
Sheets("CPS CSR Dashboards").Range("A2:K69").ExportAsFixedFormat xlTypePDF, "G:Call Center ReportingWeeklyAgent DashboardsTemp" & [ 'CPS CSR Dashboards'!M3] & ".pdf"
Sheets("CPS CSR Dashboards").Range("A70:K137").ExportAsFixedFormat xlTypePDF, "G:Call Center ReportingWeeklyAgent DashboardsTemp" & [ 'CPS CSR Dashboards'!M71] & ".pdf"
A2:K69 is the range for Agent 1 which will be saved as as a PDF M3 is the cell which contains Agent 1's name for which the file is to be named.
A70:K137 is the range for Agent 2 which will be saved as another PDF M71 is the cell which contains Agent 2's name.
I'm working out of the book, and I'm close to what I need but at a beginner's block. I want to delete all rows of a table of stock information where the symbol (column b) contains a "."
Here's what I've got:
Sub Earnings_SymbsClnUp()
'Deletes the newly posted trades that have a "." in the symbol.
Dim finalrow As Integer
Dim i As Integer
finalrow = Cells(Rows.Count, 1).End(xlUp).row - 8
MsgBox finalrow
For i = finalrow To 9 Step -1
If Cells(i, 2).Value = "ITRA" Then
Cells(1, 2).EntireRow.delete
End If
Next i
End Sub
I have a formula that I'm using which works and gives me the right value.
Code:
=COUNTIF(bommech[PO1 No],"")+COUNTIF(bomelec[Procurement Status],G4)
However when I try and enter the exact same formula through VBA using, I get "Run-time error 1004: Application defined or object defined error". I don't see why the formula will work when I enter it directly into Excel but it won't enter the formula through VBA.
Code:
ActiveSheet.Range("H4").formula = "=COUNTIF(bommech[PO1 No],"")+COUNTIF(bomelec[Procurement Status],G4)"
My VBA sub contains
DIM sFormula As String
DIM rCell as Range
at some point in the code, the value of sFormula is set to something like
=ROUND(SUM($A$5:$A$10*$B$5:$B$10),2)
(not really this formula but of that flavour)
later in the code I have an instruction
rCell.FormulaArray = sFormula
(rCell is set to a single cell at the time)
When I run the code and then examine the cell, I find that the cell's formula has been correctly set to
=ROUND(SUM($A$5:$A$10*$B$5:$B$10),2)
however there are no {curly brackets} surrounding the formula to suggest that it has been entered as an array.
I have been looking for a forumla that will put an 'and' between the last two bits of information.
Joe Bloggs
Peter Smith
Matt Jones
I have list of names in individual cells like above. I have figured out a formula that will put them into a single line (Joe Bloggs, Peter Smith, Matt Jones). But what I need is a formula that will put it into single line and insert an 'and' ALWAYS between the last two people. So it should show like (Joe Bloggs, Peter Smith and Matt Jones) but if I remove Matt Jones it should now display as (Joe Bloggs and Peter Smith).
I would like to prepare mid-term grades for my history class, and have three categories, which I would like to insert into a spreadsheet to calculate the grades, as follows:
Mid-term 1: 30%
Mid-term 2: 40%
essays: 30%
each of the three grades is a number from 1-100; the final answer will be a number which I can then convert to a letter grade.
I'm just not sure what function to use, in that last column, to get Excel to calculate the final answer/grade.
Let's just assume that Mid-term 1 is in column A; Mid-term 2 is in column B; and the essay grade is in column C. How can I create a function that will allow me to give the above percentages to the respective assignments, when calculating a grade?
I will enter a figure into A1. I will also enter a figure into C1. In E1 I would like to enter the lower of: 0.2% of A1 or 75% of C1
View 5 Replies View RelatedI'm creating a maths workbook in Excel. I want the user to workout the answer in his/her head and enter the answer into Excel. The only problem is, of course, that there is nothing stopping a user from simply typing a formula to obtain the answer! I have tried all data validation, apart from Custom which I can't figure out. Is there a way to prevent a user entering a formula on a worksheet?
View 5 Replies View RelatedI have a normally easy task that is causing me some grief. In column f of my data I either have a number or this "____________". My goal is to delete the entire row if that line is present but my code is not finding that value in my range.
I am wondering is this some format value or something, but it shows up in the formula line as a line. Doesn't appear to be an underline, but I could be wrong. Below is my code.
[Code] ........
I'm working on setting up a chart. The formula I'm using for cell B21 is this: =ROUND(F3,0) & " " & IF(E3<F3 ...
View 2 Replies View Relatedhow to do the formula for highlighting whole row after entering a date in one of the columns.this spread sheet had over 300 rows so I can't do the conditional formatting, I need a formula for whole spreadsheet.
View 11 Replies View RelatedFirst, I have to say that I am very weak in VBA.
I have a section of code where I need to count the cells from J through BF in rows 42 through 76. The code snippet I'm working on is:
Code:
For r = 42 To 76 Step 1
if Application.WorksheetFunction.Count(Range("J" & r:"BP" & r)) = 0 Then
Err = Err + 1
[Code]....
The code checker highlights the ':' in the 'Range .... = 0' with the message "Expected: list separator or )".
I have a database that needs breaking down in order to fulfill a request.
I've been trying to create a macro to copy one row to a new workbook (starting from row 3), file name save as a value of the cell (C1), and move onto the next row. However, my code appears to only loop through 26 entries and then stops.
Code:
Dim row As Long
Dim refname
row = 3
Do While Cells(row).Value ""
[Code] .....
I'm using a formula to copy a time from one cell to another
across sheets. The format of the time is h:mm AM/PM.
However, when the formula references an empty cell, it puts in a
default value of 12:00 AM and I need it to remain blank, (just as
the referenced cell)
It's such a simple copy formula. ie:
=sheet1!A1
e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from www.skype.com)
is it possible to loop in a formula like I do beneath?
The code will not run. What is wrong?
the problem is variable aa - that should be a1,a2,a3 etc.. It is no problem if I write a1 etc. in the formula. but if the loop is long I cannot write cell reference for every cell a1,a2 etc.
For k = 1 To 10
aa = Cells(k, 1)
Cells(k, 3).Value = "=KOMPLEKS(B13, 1 / (2 * pi() * aa * B14))"
Next k
I want to convert a range of cells to "number" format from text, and then refer to them in a formula that's somewhere else. However, if I do the format from VBA, I can't get the change to be reflected without the following
View 4 Replies View RelatedI have a workbook with just one worksheet. It's just a list of data imported from Access and saved as a worksheet. When I try to enter a formula ie '=4+4' it goes in as text and will not calculate. I have tried various formatting to no avail. I added a second worksheet to see what happens and this works fine. (XP home, Excel 2003).
View 12 Replies View RelatedI am trying to create a formula that will allow me to enter a zip code and have excel return the specific tax rate for that zip code. I have zip codes in excel for all of California along with the corresponding tax rates. I am creating a form that I can just input the zip code and have the tax rate automatically pop in.
View 1 Replies View RelatedI have code that looks at F1 to see if there is a value and then performs code where it looks down column D and if the condition is met it puts in one of two formulas
I then do the same thing for G1 Unfortunately, I am not VBA smart enough to recreate this code to adjust itself so I copied it and manually tweaked it.
Here is my dilemma, I need to do this for every column that has a value in row 1 from column F:BZ this would take me forever.
We are attempting to use the macro below.
We believe that the problem is "memory related."
However, we can't find the correct workaround to the issue.
In Excel 2002, the behavior was for the macro to work great on small numbers of rows, but to just stop on large numbers of rows. After it stopped, the user would manually run the macro throughout the spreadsheet, by holding down Ctrl + N.
In Excel 2007, the behavior of the program is different, and Excel actually freezes up.
We've narrowed down the problem to be possibly "memory related."
However, we don't know the correct workaround for this.
For example, in the posting below (the referenced link) they suggest using "variant arrays" to address memory limitations type of issues ... but I'm not sure of how to implement those.