The following simple formula was written in Excel version 97. when I try to use it on a newer version (03) it does not seem to recognise it as a formula? =IF(F66="yes",1,IF(F66="N/A",1,0))
View 2 RepliesWith the kind help of this forum, I put together a nice business tool that has a fair amount of automation via macro buttons, etc. It works great in XL 2007 but some users are having issues in 2003. I appreciate this is quite general but are there significant conflicts / differences with coding in these two different versions?
View 6 Replies View Relatedi installed office 2007 beta version on my computer (for testing)
together with the already existing office 2003.
now the default program of my excel files is excel
2007 beta version (not excel 2003).
can i change this behavior so that when i open an excel file the
default program would be excel 2003 instead of excel 2007
and whenever i want to open a file using excel 2007 i do
so by right clicking the file & choosing excel 2007 from the
open with list.
I have a Find Method routine in a procedure that works fine in xl2000, but fails in xl2003 with error code 9, subscript out of range.
Is there a different format for the Find Method in xl2003?
'code in xl2000
Dim CourseI As Range 'Students taking BusinessI
Dim CourseII As Range 'Students taking BusinessII
Dim BothCoursesTrue As Range
Dim Student As Range
'code
'code ...
For Each Student In CourseI
Set BothCoursesTrue = CourseII.Find(Student, LookIn:=xlValue, LookAt:=xlWhole)
If Not BothCoursesTrue Is Nothing Then Student.Offset(, 3) = 3
Next Student
I need to find out which version of excel i am using in macro.I mean whether it is 2003 or 2007.Because i need to put 2 diff codes according to the version of excel.Can any one please provide me the macro code to findout the version of Excel.
I am trying to get my array formula to recognize the text selected in my data validation cell. The formula worked perfectly until I decided I wanted to try and use a data validation list to eliminate spelling errors. Even though the text I am trying to recognize in the cell is exactly the same, because it is in a data validation format it no longer works. Is there a way I can modify my formula so it works? Example below...
formula: {=SUM((A2:A109>DATE(2013,10,31))*(H2:H108="Joe Blow"))}
A2: 11/2/2013
H2: Joe Blow (chosen from a data validationi list)
The result of the formula should equal 1 in this example, or more if the same criteria occurs more than once within the range of A2:A109, and H2:H109.
I have a worksheet used for scheduling. When a members time is updated, it updates the counting cell for that time by subtracting 1 (thats simplified, the forumla is more complex than that).
I have five teams and five workbooks for each team to do it's own scheduling. In just ONE workbook, the Worksheet_Change() event has stopped executing. It's fine in the others. I renamed the workbook to archive it then put another workbook in it's place and now that one works just fine.
The workbook that I've archived, I hate doing that not knowing what would cause the Worksheet_Change() to stop being recognized. There is no code on the sheet or related to the sheet that would stop it or cause events to be cancelled.
I wanted to know if there is some secret keystroke combination that may have been inadvertently clicked that would cause events to firing or stop being recognized?
I have created an Excell Add-In file (.xla). I just can't seem to get the procedures to be recognized and/or run. I have even created a custom menu based on the following tip: http://www.j-walk.com/ss/excel/tips/tip53.htm
When loading Excel will add the menu to my main tool bar. What is frustrating is that when trying to run one of the subroutines from the new menu item, I constantly get "The macro 'spreadsheetname.xla!macroname' macro cannot not be found'
I've checked the security settings and have verified that all add-ins are to be 'trusted' and I've lowered macro security to low. All of the subroutines have been declared as public as well. My suspicions are that there is some setting somewhere that is causing issues, but I just can't seem to think of what it is.
I have a system that exports start and finish times into a single cell as text (eg 9:30 AM - 7:00 PM)
I am trying to do a simple count of how many shifts start before 7:00 AM, or finish after 6:00 PM, and am using the text function to extract the individual time into another cell.
My problem is that i cannot get this cell to be recognised as a time instead of text? I do a simple "is this cell less than another cell" but it tells me no, even though the time is 5:00 AM.
See attached: Text not recognised as time.xlsx
I defined two custom buttons in Workbook_Open() function:
Private Sub Workbook_Open()
Dim cb As CommandBar
Dim ctrlInit As CommandBarControl
Dim ctrlComp As CommandBarControl
On Error Resume Next
Application.CommandBars("Project").Delete
On Error Goto 0
Set cb = Application.CommandBars.Add( Name:="Project", temporary:=True)
With cb
.Visible = True
Set ctrlInit = .Controls.Add(Type:=msoControlButton, temporary:=True)
With ctrlInit
.FaceId = 68
.Style = msoButtonIcon
.Caption = "Retrieve Template Types"............
In Module1 I must to turn ".enable" properties to Turn, but Excel don't recognize these variables there.
I am exporting data to Excel from a 3rd party application - the bulk of which are date entries. For some reason they do not come across as date fields in the cells, and you need to double-click on each cell to correct it. For instance if I try to filter the dates straight after the export to Excel, the cell entries are not recognised.
Anyway, I managed to get around this by running a macro on each column, e.g. for column C:
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, FieldInfo _
:= Array(1, 4), TrailingMinusNumbers:=True
This only works however if there is at least one entry in each column. Is there a way of telling Excel to skip that column if it is empty
I have a .csv file with measurements. I can open it in excel, and have succeeded in make 3 separate columns from it and save as an .xlsx file. But the problem is I still can't do calculations with the data. it are all values from a form like "-3.44e-07" but excel recognizes this as text. I don't have a green triangle in the corner and multiplying by 1 doesn't work.
Apparently i can use them as numeric values in MATLAB, but I don't know how to do this in excel. And I have 9 files like this, each containing 3 columns and 600 rows, so changing all the cells one by one is no option.
I use the dutch language version of excel so I hope I translate the specific names of things in excel correctly.
I created in module custom toolbar with a button, see the
Option Explicit
Public ctrlGUI As CommandBarControl
Sub CreateToolbar()
Dim cb As CommandBar
On Error Resume Next
Application.CommandBars("Cust").Delete
On Error Goto 0
Set cb = Application.CommandBars.Add( Name:="Cust", temporary:=True)
With cb
.Visible = True
Set ctrlGUI = .Controls.Add(Type:=msoControlButton, temporary:=True)
With ctrlGUI
.FaceId = 3205
.Style = msoButtonIcon .............
Hopefully this isn't too vague, but almost every project I've worked on so far has not recognized Public variables in all modules. I've constantly been searching for exceptions to this rule that could be contributing to this issue but haven't been successful.
I have read a million times that declaring a variable as Public makes it visible to all modules and preserves the value throughout. I've also read that if you edit code or reset your project, the values may be lost. I also know that if you try to use a variable before you give it a value (or for object variables set it = to something), it doesn't have a value yet. But all this applied, I'm still not getting Public variables to be consistently recognized or stay with the value I want them to have throughout the lifetime of my project (while the workbook is open)
My specific current issue (one of many so far) is that I have 4 command buttons within a worksheet object. Each triggers a different group of procedures, some of these are user forms. In the first button, I create two worksheets one to contain the current fiscal period "Options" the other for "Summary" of all the totals etc... (all financial data). In this first userform, triggered by the button, I allow the user to name these worksheets: like Summer2007Options or Summer2007Summary and I declare these publically as object variables OptSheet and SumSheet. These variable names and sheets are recognized for the next 2 buttons but now that I'm on the fourth, I have object variable not set errors when I try to refer to this variable.
I have a code that runs when I open the workbook. It asks if this is a new quote, if it is it should then open userform1. The problem is that sometimes when I click yes I get a "Path/File access error" popup, and then a VBA error box with "Run-time error '75' Could not find the specified object".
When i debug, it highlights the line "userform1.show"
IF i end, and then run it from within VBA it will then run fine.
Sometimes when I open the workbook it will just crash excel, and when I reopen it, it runs fine.
Simple code -
Sub Workbook_Open()
answer = MsgBox("Is this a new quote?", vbYesNo)
If answer = vbNo Then
Else
userform1.show
End If
End Sub
I have a Dynamic Named Range (Entries) that shows in my Name Manager...but is not being recognized in formulas.
The range is defined with a standard DNR formula, and selecting the code in Name Manager highlights the Dynamic Range.
[Code].....
But, when I use the NAME in a simple formula, I get an error. (#NAME?)
[Code] .....
i have a number (%) in the cell B3, which excel doesn't recognizes as a number. Is there anything i can do "excel-wise", instead of manually rewriting the number in the cell, for excel to recognize it as a number. Changing it to a "number" or "percentage" in the cell format doesn't work.
View 5 Replies View RelatedI copied some columns of dates form a website into an excel workbook. Unfortunately excel does not recognise the text and therefore I can't sort the columns from oldest to newest.
I have tried the operations here [URL] as I though spaces might be the problem but with no success.
I have two text boxes on my sheet that I am using as a log in sheet. I am not sure what triggers them but they stop allowing me to type in them to log in and instead are recognized by excel as pictures and ask me if I want to format them. I have fixed them in the past by deleting them and adding new Active X text boxes to the sheet but then after a while they turn back into pictures.
By the way the text boxes have been named T1 and T2 and are used in VBA behind the scenes to determine if the details that are typed into them match existing accounts.
I'm trying to query Access for some data. One of the fields I need to grab is called MOD, which also happens to be a VBA function. How do I get Excel to recognize MOD as a field name instead of a VBA function? (Renaming it is not an option)
Code:
stSQL1 = "SELECT MOD FROM CST_ClaimbyLop WHERE CoCName='" & myCST & "'"
I have a column of dates that are not being recognized as dates unless if I manually select each cell and press enter. For example the cell value is "Jul/13' and isn't recognized as "01/06/2013" until I select it and hit enter. How can I get around this?
View 8 Replies View Relatedi'm working in excel 2003 and it's limited conditional format by three,
so why when i used formual like TEXT(D6,"#FFFFFF")
didn't give me results?
The following formula was, several weeks ago, very graciously offered to me from one of Excel Forum's contributors.
=SUMPRODUCT(--(MOD(ROW(E8:E6782),2)=0),E8:E6782)
My request was to find a formula that would add each 6th row starting in row e8 (e8+e14+e20+e26+e32 etc. through e6782) in column "e" when the column was 6782 rows deep from top to bottom. (i am not trying to add every number in column e, just each 6th row, starting at e8 and going through row e6782).
I entered the formula into my spread sheet and, voila, I had a sum that I assumed was accurate for my spread sheet of ticket sales. I began to question the functionality of the formula when I altered the E8:E6782 parameters (which represented the gross ticket sales) to E4:E6778, in an effort to sum up the E4 values e4,e10,e16, e22,e28,etc. . . (which represents the net values after commissions were deducted). The difference in the two sums (e8 values Versus the e4 values) was incorrect and did not represent the appropriate commissions (which should have been 15%).
I have this formula :
=SUM(F25:M25*$F$6:$M$6)
It works great in xl2003, and it works properly in XL2007 until you try and change it. I changed the column F to Column D and now it gives me a #value.
How can I re-work this for xl2007?
How would I convert the following formula into Excel 2003?
=SUMIFS($Q$7:$Q$5000,$O$7:$O$5000,"Cat",$P$7:$P$5000,"Dog")
I am having trouble converting the following Countifs formula developed in 2007 to work in 2003 Excel.
Basically, I want to count the frequency of "N" in 'Data_Q2 2008'!$S$2:$S$9806, where 'Data_Q2 2008'!$B$2:$B$9806 = 'Metric Upload'!C2.
'Data_Q2 2008'!$B$2:$B$9806 is a list of physicians. 'Metric Upload'!C2 is a pick list for selecting the physican that is in the larger data set using Data Validation.
Need convert the following formula so that it would work in Excel 2003: =SUMIFS($BB$4:$BB$500,$E$4:$E$500,"Abbeywood First",$L$4:$L$500,"Autumn"). I could play around with it for hours and get nowhere, so I'll just turn to the experts.
View 5 Replies View RelatedI would like to find out, how would look formula in excel 2003 to perform process like discribe below:
(in cell K1 formula)
=sum (A1+B1) then if sum of A1 and B1 is lower then 100 then add C1 but if sum of A1 and B1 is higher then 100 then subtract 100 and add C1.
On a excel sheet I've got columns, each column represents a weeknumber. I want to calculate the so-called 4 wk average for each row and for each week and this is the formula I use:
(value*Tvalue)+(value*Tvalue)+(value*Tvalue)+(value*Tvalue)/(Tvalue)
(this is not the actual formula but simplified, that's not really important).
It's the checks that make things a bit more complex. If a value of a weeknr is zero, skip it, but if the next value is also zero, just skip the formula alltogether and make it a zero (or text like "false"). So another thing that has to be accounted for is that if a value is zero, the next weeks value is taken instead.Example (see included file):
I want to calculate the formula (mov 4wk avg) for the third value for week 12, which will make the formula
(0.2*6)+(0.3*6) now there's a zero on week 14 so I skip it, then formula will be:
(0.2*6)+(0.3*6)+(0.6*6)+(0.9*6)/(6).
Right now I'm doing this in VBA with a lot of variables and a lot of if statements.Is there an easier more effective
I know the example sheet is a 2007/2010 version but I need to accomplish this for 2003.
Excel 2003. I have a list of names in column A, dates in column B, I need the difference between dates for the people in column A, I am doing it now with the formula shown but I need a formula to put in column C that will look at the name in column A, for that row, and then find that name above it and give me the difference between the to dates in column B. So in the example below if I put the formula in C7 it would look at A6-A2 for harry and then give me the difference between B7 and B4, 6 days, if there is not a match return blank.
Excel 2003ABCD1NamesDateDays Between2tom2/8/123****3/2/124harry3/9/125tom3/10/1231=B5-B26tom3/12/122=B6-B57harry3/15/126=B7-B48****3/26/1224=B8-B3Sheet1