Formulas are not working fine in VBA, They return wrong calculations. Here is the piece of code.
Range("S13").Select
Selection.FormulaR1C1 = "=SUM(COUNTIFS(C[-13],{""Closed"",""Open"",""Answered""},C[-9],{""Site from CDA"";""Site from LDR""},C[-11],""System""))"
'strr2 = Range("S13").Value
Range("R13").Value = strr1
Range("T13").Select
[Code] ........
I am doing a report which analyzes a specific person's transaction per country (3 criterias). The workbook has 3 sheets. First sheet is the database, 2nd is the report itself and 3rd is a reference table. I am using a sumproduct formula to count per person's transaction per country. It is giving me slow calculations.
I cannot possibly use a pivot table since there will still be other calculations involved after determining the transactions. I tried using database functions but i can't seem to understand how to go about it when there are multiple criterias involved.
I am attaching a sample file for your reference.
I have an Excel file that has a certain amount of Array Formulas. It takes 30 seconds to calculate every time the user makes a change. Some users do not have the Status Bar showing, and they think that Excel is "locked up". I want to make a user form that will appear when the Stats Bar is "Calculating..." There are a lot of "progress indicator" tutorials on this site, but they all seem to relate to VBA code being inserted in between certain lines. That doesn't apply. My Excel file has no VBA. It just takes a long time to recalculate. I want a VBA form to appear, that will match the % progress shown on the Status Bar, whenever a change is made to the spreadsheet.
View 6 Replies View RelatedI have a huge spreadsheet requiring me to manually enter alot of fields with formulas. I am using the formula
=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))
The full formula is quite long as this pick up the data off over 300 work sheets. (there's probably an easier way to do this aswell) When I copy or drag this to another cell with on the same worksheet it gives me the correct formular with the correct cells changes as I want. However this formular does not work. I have auto calculate on and I manually press enter on the cells and still nothing. If I change the following for the entire formula (about 8min to do each cell) it works
=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))
Is there an easier alternative then manually changing each number?
I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.
I have a series of packages that are denoted by text phrases. Examples:
4/6/12
2/12/12
6/4/12
18/12
24/12
For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).
I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.
I have these two formulas I am trying to multiply
=ROUNDDOWN(AK170/Sheet1!B3,0) Returns a value of 2
=ROUNDDOWN(AL170/Sheet1!C3,0) Returns a value of 7
I need to multiply these 2 formulas together to return a value of 14
I would like to put the following two formulas in one cell, so if one condition is met, it returns 1:
Code:
=IF(IF((AND(MATRIX!C4=0.795),1,0)
Code:
=IF(IF((AND(MATRIX!C4>=-0.094,MATRIX!C4=0.795),1,0)
The formulas work fine as single formulas in one cell, but I can't figure out how to do both in one cell. I tried this but I get errors.
Code:
=IF(((AND(MATRIX!C4>=-0.094,MATRIX!C4=0.795),1,0,IF((AND(MATRIX!C4=0.795),1,0)
I've also tried using the OR function, get errors. I'm not sure which to use, IF or OR, and I can't get the syntax right on either one.
I actually have six formulas for each cell, but how to do two, then I'll know how to do all six.
=IF(ISNUMBER(VLOOKUP($E8,Su!$D:$K,7,FALSE)),VLOOKUP($E8,Su!$D:$K,7,FALSE),)*$I8
In the first vlookup it picks up a value. I need to do the same vlookup function but it should also pick up another value in column 9 and should add value in column 7 and 9 and return it. Employee id numbers are in column E in the first page.My formula checks for the employee id in Su sheet and retrieves the value from the seventh column. I need to retrieve also from 9 th column and add them together.
I am using a For Each Next to cycle through a list of names, check certain conditions and then will be adding a further lookup (instead of Result = Result+1), but all I get is #Value!
Function maxbarometer(Name As String, Round As Integer, NameList As Variant, RoundRange As Range, RoundRangeTwo As Range)
Dim Roundname As String
Dim Result As Double
Dim NameColRound As Integer
Dim ListObject As Variant
Roundname = "Round " & Round
NameColRound = Application.WorksheetFunction.Match(Name, RoundRange, 0)
For Each ListObject In NameList
If ListObject.Value = Name Then
Result = Result
ElseIf Application.WorksheetFunction.VLookup(ListObject.Value, RoundRange2, NameColRound, False) = Roundname Then
Result = Result + 1
End If
Next
maxbarometer = Result
End Function
when i try to get the final row witht the following:
KROGrow = Cells(65536, 1).End(xlUp).Row
i am getting a row that is consistently 27 rows off of when i manually do control up? i am pasting data from and existing workbook into a newly created workbook in the sub. i would assume that since the destination is new that they would never be any data in this?
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
View 9 Replies View RelatedI'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
I am trying to use the Vlookup function to return State name based on the corresponding state code.
I have the list of State along with the Codes in adjacent sheet, arranged in ascending order. I am using the Vlookup function to list the State name based on the State code.
Vlookup function: Vlookup(A4,Sheet2.A2:B51,1)
This function returns me a wrong value. It gives "Arkansas" for the state code "AZ", when it should be Arkansas.
Also, if I add "FALSE" as a 4th argument, it returns "#N/A".
I have the following formula: IF(SELL_INVESTMENT = "YES" , 1,2) It returns not 1 or 2 but 0. The worksheet contains over 1000 cells. Trace Precedents verifies that the formula evaluates the correct cell and that it contains "yes". I created a very simple worksheet to test the formula and here it correctly returns 1.
View 2 Replies View RelatedWhen copying data from a website to excel, excel recognizes the dates wrongly.
About half of the dates he sees as: DD/MM/YYYY, while it should be: nothing/MM/DD.
For example: The cell contains the date: 01/04/2029 (The first of April, 2029)
While it should actually be: 04/29 (The 29th of April, no year!)
For all dates it starts with the 1, followed by a month, followed by a year (which consists of the year 2000 + the actual day), which is totally wrong.
About the other half of the cells, it just contains text with the month and the day (which are correct), but excel doesn't recognize this as a day.
For example: The cell contains the text: June 12
When I set the cell format to date (or any other format for that matter), nothing happens, so excel does not see this as a date.
I'm trying to figure out an Internal Rate of Return for a spreadsheet. The answer is supposed to be: 29.42% however I'm getting 25.94%. Does anyone know what I'm doing wrong in my IRR function on the Profitability worksheet? I'm currently using Excel 2007 by the way.
View 4 Replies View RelatedI have a problem with my references when sorting in excel. I have been searching for the problem, but did not found a useful solution.
I'm using excel 2003.
Let me try to explain my problem with a simple exampel.
I have a column with numbers and one with formulas pointing at these.
A B
2 =A1
4 =A2
3 =A3
Now I want to sort column A, and the column B should keep pointing at the original number. So I want this:
A B
2 =A1
3 =A3
4 =A2
But I get this:
A B
2 =A1
3 =A2
4 =A3
This output is what I would expect if I used absolut reference($).
Using offset(B1;0;-1) will work, but I cannot use this, since I will add/delete rows and the reference is not neccessarily next to it. Plus it can be a rather large sheet.
I cannot use dynamically name definition either, because it is intended for other users, and this will be too much work for them. What I basically need is a simple formula they can enter.
My sorting is done via a VBA macro, so if there is anything I can do through macro/VBA that will be okay as well, since I can do this without the user seeing it.
I have modified this micro I found in a forum. All I changed was the Range on Sheet 1.
[Code].....
It works fine except when it cuts to sheet 2 it starts at on column B I need it to start with Column C. What do I need to add ?
Attached File : Survey.xlsm
I have the following code which saves my worksheet with a name extracted from particular cells:
Public Sub SaveAsMaximoWO()
ThisFile = Range("AC5").Value
ThisFile2 = Range("E3").Value
saveName = ThisFile & " - " & ThisFile2
ActiveWorkbook.SaveAs Filename:=saveName
End Sub
This (for reasons I don't understand) saves the file to My Documents by default.
What I would like it to do is save to another folder within my documents as default.
how to control this,it should be 0.55. is they a wat too round off too next highest hole number? Here is a example.
View 2 Replies View RelatedI have this workbook with 10 sheets. Each sheet has macros that are called from a floating toolbar. What I am trying to do:
I want to create a msgbox that warns me when I click in a toolbar's button to call a macro that not runs in the activesheet. Something like: "This code (or macro) doesn't match (or run) with the active sheet.
as such Excel (2003) is putting in the wrong weeknumbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem?
I am using the following formula, could someone explain this formula in
english?
=CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKNUM(BM2)-1)),(WEEKNUM(BM2)-1)))
Thers no easy way to explain this other than looking at the sheet (which is attached), but basically i have a load time start time and end time.
In the example in the sheet the load time is 01:00
The start time is 23:00
The end time is 23:35
Th eload took 35 mins and was not late as it is going 01:00 next morning, yet the answer it replies is 'N' which makes it show as late.
Does anyone have a formula to slove this issue
The code is supposed to project review dates 1 month, 2 months and 3 months after the start date, but the first "cell offset" line on is projecting the next day instead of next month, its driving me nuts!!, the others are working fine
Code:
Sub reviews()
Dim cell As Range
For Each cell In Range("L1:L200")
If IsDate(cell) Then
cell.Offset(, 10).Value = DateAdd("m", 1, cell.Value)
cell.Offset(, 11).Value = DateAdd("m", 2, cell.Value)
cell.Offset(, 12).Value = DateAdd("m", 3, cell.Value)
cell.Resize(, 10).AutoFill cell.Resize(, 11)
End If
Next cell
End Sub
I was trying to explain modulus to someone and they wanted to know why you can "flip" symbols mod(-6,7) = 1 in Excel. So I got to explaining that -6 Mod 7 is the same as -6-(|-6/7|)*7 which is how you get 1.
And that's when I realized... |-6/7| = 0 not -1. Then I looked in VBA and sure enough -6 mod 7 = -6. Apparently the problem boils down to the Integer conversion. Excel is performing the integer coversion by rounding down (INT) wheras VBA appears to be using CINT.
So here is how it work out in excel:
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(-1)*7
-6--7
-6+7=1
But in VBA you get
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(0)*7
-6-0=-6
I have a very basic spreadsheet to calculate golfer handicaps based upon a course index. For some reason, my "LOOKUP" formula is not retrieving the data from the cell (column) next to the value I am searching...?
View 9 Replies View RelatedI'm trying to parse a text file into Excel and the date is coming out as "1940" instead of "2040". In the text file the date reads "5/14/40". I've already changed my regional setting in Excel so that if I type in "5/14/40" it is understood to be "5/14/2040". For some reason this doesn't work when I parse the data though.
View 9 Replies View RelatedThe formula below was written a couple of years ago and I just discovered an error. It returns an "S" for the value 0789 when it should be a "C", (Consecutive).
IF(OR(AND(ISNUMBER(MATCH({0,1,2,9},$O215:$R215,0))),AND(ISNUMBER(MATCH(MIN($O215:$R215)+{0,1,2,3},$O215:$R215,0)))),"C",IF(SUM(IF(FREQUENCY($O215:$R215,$O215:$R215)=3,2,1))=2,"DD",INDEX({"S","D","T","Q"},MAX(FREQUENCY($O215
******** ******************** ************************************************************************>Microsoft Excel - FL MID PLAY 4 CONSECUTIVES.xlsm___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutT215=OPQRST215078907893SSheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The macro was orininally set up for the 7 days of the week. Now I want to set it up for the 31 days of a month. It's set to pull information from a register sales journal. Here is the button macro-
Sub Button2_Click()
Dim sDay As String
Dim sShift As String
sDay = InputBox("Which Day? (1 For MONDAY, 2 For TUESDAY, ... 7 For SUNDAY)")
Select Case sDay
Case "1"
sDay = "1"
Case "2"
sDay = "2"
Case "3"
sDay = "3"
Case "4" ........................
