I think I'm using the correct terminology with the term "variable", but to explain what I'm trying to do, I want to get RAND() to hold the first returned variable so I can compare it to other places RAND() has been used & if the returned value is the same as the other place, then run RAND() again.
I'm trying to say in the formula above that if rand = what was already in A4 then run random again -- but this doesn't keep if from returning the same value as A4 on the second pass.
What would work is something like (where X is the assigned variable):
How to assign a "WS(i)" worksheet name and have the assigned variable (i) in the name equal the consecutive steps in a total page count.
Here's the code that does not work:
Code: Dim WB as Workbook Dim WS as Worksheet
Set WB = ThisWorkbook For i = 1 To WB.Sheets.Count Set WS(i) = WB.Sheets(i) Next
I can see why it does not work. I can't figure out what to declare in my Dim or how to word the statement using the (i) variable in order to capture the value to attach to "WS."
Details: The issue is that the number of pages created will vary day to day depending on staff needs. There's an array of staff names to be assigned to page tabs and there will be skips and deletions along the way depending on daily attendance so "WS(i)" worksheet names can't be assigned at creation. What happens if they are is that the succession of worksheet names winds up being (e.g.) "WS1, WS2, WS5, WS12," etc. instead of consecutive.
There's a very complex data-sort-and-assign system that depends upon how many staff-assigned sheets there are, and as such each sheet has to have a "WS" worksheet name for the process to stay smooth. If the names are not consecutive, though, the sort-and-assign process becomes confused. This step takes place at the end of the initial workbook set up process, immediately before the data are addressed, so there really isn't an earlier opportunity. It can't be later since from this point on the system depends upon the "WS(i)" names being there.
It started fine when there were 5 people. Now there are 37.
I know that I cannot automatically assign UniqueCount the value of that formula. How would I format the line such that I can indeed assign UniqueCount the row value of that line?
What I'm trying to so is track weekly labor costs- my crew makes a rate for 6 days then an additional amount for a 7th day. I have attached an image of what I'm trying to do. Basically I want to add up the crew members "a" rate plus the "b" rate (if applicable) over multiple weeks in the total column.
is there any way wer i can assign the name of a sheet to cell so that watever i typ in the cell automatically becomes the name of that sheet? i have more than 2000 sheets
G:G contains a list of integars, though some cells are blank; lets say 75 of the 100 cells in data range are < 0. I want a macro which copies a range where the number of rows = the number of values in G:G. my Macro:
Sub myMacro() Dim rowRange As Integer rowRange = Count("G:G") End Sub
This, as you guessed, comes back with an error. Sub or Function not defined. Anyone the proper syntax for assigning a formula to a variable?
I could really use some excel function. Within my office, we work with several hundred files. Each employee is assigned files based on the last two digits of the file number. What I need is a way to identify what file is assigned to which employee based on the term digits of the file.
So for example, I have the following list of files:
I'm able to do a formula to get the term digits (meaning the last two numbers), but i'd like to have another column that can put names based on the term digit column. For example, Tom might work 00-04, Sally works 05-09, Greg works 10-15, Lucy works 16-21.. etc
I came across the below IF formula that is exactly what i need, except it only works for two associates and not the multiple that i need.. but it looks to be a good starting point nonetheless.
------------------------------ =IF(C2<50,"Sheryl","Lisa"). You should enclose Sheryl and Lisa with quotation marks as these are string values.
You can also use (if A2 is where the Loan # is):
=IF(Right(A2,2)*1<50,"Sheryl","Lisa")
The formula will acquire the last 2 digits of the loan and check it if it's for Sheryl's or Lisa's. -----------------------------
I want to have formula with: ActiveCell:formula = I want to sum columns in a row having a column variable called: col The col variable will be the far right column and the other colunm in Row 2 will be col-3. What is the syntax to create this formula? If col = 5, formula normally would be :sum(b2:c5), I want to use col as the varialbe.
Just got a problem with auto-populating cells with a formula that is made up of variables. My formula references the cells on another sheet, but this will be dynamic.
I'm trying to get the formula to reference e.g. cell A1 in the Calls Taken sheet, with the column held in the srtshortname variable and the row held in the formularow variable.
Is it possible to find the formula of a result that is based on three other values.
Here's my example:
I give three cases, but I am sure all three use the same formula. I made the team names up but the values are 'real'.
MATCHES TEAM NAME TEAM RATING OPP. RATING POINTS DIFF. NEW RATING
Match1 Blumps -2.62 5.05 8 -1.28
Match2 Champs 2.8 4.08 -18 2.37
Match3 Sharps 8.26 -3.53 -16 3.68
I want to know what formula was used to calculate the New Rating in the last column. I am not 100% sure if the formula to calculate the New Ratings as shown above includes a value for home ground advantage of which I might be unaware. For what its worth, the Blumps and Sharps were home teams and the Champs was a visiting team for the three matches shown.
In column H: =IF(ISERROR(L4-J4-K4),"",IF(OR(K4="0", K4=""), L4-J4, L4-J4-K4)) So, if formula is going to come up with an error, I don't need to know. If it's not an error: L - J - K. Except that sometimes there isn't information in J or K, and the formula won't calcluate L - 0 - 0, unless I physically enter in zeros.
=IF(ISERROR(L4-J4-K4),"",IF(K4=""), L4-J4, L4-J4-K4)) and =IF(ISERROR(L4-J4-K4),"", L4-J4-K4))
I don't want the people using this to have to enter in a plethora of zeros in order to force the calculation. What am I doing wrong? I've attached workbook with the relevant information in it. You can see in the middle that if there is nothing entered, it won't work right.
correctly using variables within functions used in formulas specified by VBA. Here's an example:
I want to place a formula within a workbook file called "Books 2003.xls" that goes to the version of the file for the previous year (or whatever year is specified) and does a Sum of a particular range (where that range is also specified as a variable) so it sums up the Total for the previous year for the same number of months that have data in them to date for the current year. Once VBA places the formula where it belongs, it should be able to always provide a running comparisson with the current year to date total and the totals for the same period for the previous year. Here's an example of the intent of this simple formula:
The workbook files will all be named the same way: Books 2002.xls, Books 2003.xls etc.... The range to be summed will always start at R4C2, but could then end anywhere from R4C2 (same as B4) up to R15C2 (same as B15).
I already have a routine that captures the value for the variable CurrentYear in the form "2003" and thus have another variable for PrevYear (= CurrentYear -1), and so then have a way to correctly specify the variable "BookName" to get the correct filename needed. I also have a variable for the CurrentMonth in the form of "1 through 12", and can use that to specifiy the correct RowNum needed to determine the end of the range (RowNum = CurrentMonth + 3).
I've tried to substitute the variables "BookName" and "RowNum" into the above formula with various combinations such as what follows, but I'm not getting the correct syntax with the right number of quotes etc...:
I can't quite get this formula to give me the correct value.
imgur: the simple image sharer
I tried defining as a Range and I can't get it to work right either way. When I hover over my r1 and r2 values the range is correct but it does have "$A$227:$A$447" quotes around the range so I think that is messing me up.
I have written some VBA code that is functional, but yields a formula that is somewhat confusing to the user.
I have two string variables called TippityTop and Anchor, both of which refer to specific cell addresses in the R1C1 style.
I attempted to define other variables that reference cells that are offset from TippityTop and Anchor without using the ActiveCell route. Try as I might, I could not produce the cell addresses that I wanted these new variables to reference.
I consulted many Excel Help screens and referred to a book on Excel VBA, but I finally took another route.
I now have:
ActiveCell.FormulaR1C1 = _ "=IF(AND(ISNA(MATCH(RC[-2],OFFSET(" & TippityTop & ",1,-2):OFFSET(" & Anchor & ",-2,-2),0)>0),ROUND(RC[-1],2)0),""DOV not in Treasury"","""")"
The problem is that this code yields this formula in Excel:
=IF(AND(ISNA(MATCH(L587,OFFSET($H$585,1,-2):OFFSET($H$798,-2,-2),0)>0),ROUND(M587,2)0),"DOV not in Treasury","")
Rather than OFFSET($H$585,1,-2):OFFSET($H$798,-2,-2), I would like the formula to say F586:F796, so that the end user can easily understand the logic.
I am using follwoing vb code to enter formula to sum a range. However it is giving sum of different range.
Cells(Row, Column).FormulaR1C1 = "=SUM(R[" & a & "]C[" & b & "]:R[" & x & "]C[" & y & "])"
Where a, b , x and y are variables containg starting row, starting column, last row and last column value like a=19; b=3; x=24 and y=3. When i check the formula in that cell, it appears as Sum(F25:F30)
I am creating a worksheet and I have a formlua where the value is text. I can make it work standing alone but not with other variables. =IF(E11=S1,0)+(IF(E12=S1,0)+(IF(E13=S1,0.0025))+IF(E14=S1,"Call for Prciing"))
Basically I need a formula that takes into effect many variables. It needs to check a size, thickness, and material, to determine a cost...
1.5 O.D. - 16GA - 304SS = $X
but
1.5 O.D. - 18 GA - 304SS = $Y
and such.
The problem I have is not only am I not understanding a lot of Excel jargon, the data set is not in perfect "example" conditions.
O.D. is in N:N yet there are blanks between N(x) and N(y), GA is in O:O yet blanks between O(a) and O(b), and so on.
Here is what I have at the moment, it uses a second sheet to derive the cost based on the parameters. Please ignore the first few terms, they are used with this cost lookup to give me my final. There also may be incorrect syntax in this, but since I get a nesting error first, I really don't know.
I've tried this in formulas (if, if/and, if/vlookup, if/vlookup/and) and I've been trying to write a macro for this (which I'm thinking needs to loop).
Problem: I have a report that is broken into three categories.
Column A includes locations, Column B includes vendors. Across the top, the monthly report is broken into four classifications (trans types), which are then further subdivided by quantity and cost. (Rough estimation is provided below)
Locations Vendor Z53
Z50
Z51
Z52
[code]....
My data comes in sorted by location, and subtotaled by vendor and trans type. So I'll have a row of data that includes:
What I need to do is pull the data for Z53 based on Location & Vendor. So from the data is would read Location 1 & Vendor A and then find them on the report and paste into the appropriate row (in my report Z53 qty is col E and $ is col F).
I was trying to do a macro that within a determined range (finding the first and last instance of Z53) would match location and vendor to the monthly report and paste the values of Qty and $ into the appropriate fields. Below is what I managed to mangle:
Code: Dim Holder1 As Range Dim Holder2 As Range Dim Holder3 As Range Dim Holder4 As Range Dim Found As Range Dim y As Range Dim x As Range Dim Z0 As Range
[code]......
I was originally trying to match from the report to the data and then copy back to the report, but I had the idea of instead taking the location and vendor from the data and finding the match on the report and pasting. That way I wouldn't have to worry about errors (unless a new location was added that was not included on the report - which I think is an easier fix than trying to go the other way, but maybe not).
I have three variables; Independent variables x & y, and dependent variable z. x & y can take the values 1,2,3 and can equal each other z can also take on values 1,2,3 but can never equal x or y. I tried using if/and statements to solve this (see below), but I would have to do it for each case and it exceeds Excels embedded function limit.
I want the range in the VLOOKUP below to display its actual value - ie "'6 June 08'!$A:$C" in the formula rather than the actual variable name 'strResult'.
Sub PreviousCount() strDate = "6 June 08" strColumnRange = "$A:$C" strResult = "='" & strDate & "'!" & strColumnRange
Dim i As Long i = Range("A2"). CurrentRegion.Rows.Count Range("D2:D" & i).FormulaR1C1 = "=IF(RC[-3]="""", ""Column A blank!"", IF(ISNA(VLOOKUP(RC[-3],strResult,3,0)), ""NEW INSTALL"", VLOOKUP(RC[-3],strResult,3,0)))" End Sub
I have a drop down list in a column called Report Type (example below).
Report Type - Drop-Down Menu in Column F Business/Operational/Work Plan Budget Report Performance Report Program Quarterly Report Program Mid-Year Report Program Annual/Year-End/Final Report Service Quarterly Report Service Mid-Year Report Service Annual/Year-End/Final Report Financial Quarterly Report Financial Mid-Year Report Financial Annual/Year-End Report Auditied Financial Statements In-Year Reallocation Annual Reconciliation Report SRI Report Other Report
I need to count all the cells that have: Budget Report, Financial Quarterly Report, Financial Mid-Year Report, Financial Annual/Year-End Report, Audited Financial Statements, In-year Reallocation, and Annual Reconciliation Report
In my worksheet, I know the last line of my report AND I know how many "reporting segments" (all equidistant and identical) there are in the report based on the last row using the below (which I tested in several reports in messages):
VB: Dim vListEnd As Variant Dim vTaskCount As Double vListEnd = ActiveSheet.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row vTaskCount = ((vListEnd - 5) / 22)
My "reporting segments" are ALWAYS 22 rows each and the first one always starts in row 5. Example: A document where the last row (containing an End of Report special character) is 247 would have 11 reporting segments.
Within each reporting segment, there are two different formulas needed.
For the first reporting segment located in rows A5 thru J27:
I need FORMULA1 in all cells of the range (D5:D15 dragged out to H5:H10(not 15) and D18) and FORMULA2 in cells (F20:F22,J20:J22).
Then I need to repeat that vTaskCount number of times (number of segments) and at intervals of 22 (segment repeater offset). Some reports may have 2 segments, some might have 50, but the structure is always identical.
My formulas are all pretty simple if it makes a difference (originally using FormulaR1C1 = "=RC[-1]+RC[-3]" --- but because I now know solid cell locations I suppose I don't need to make them relative, too.)
