I’m developing an application that will be distributed amongst several clients all running different versions of Excel.
I am trying to employ Late Binding, but am runing into an issue with some string functions (like Mid, Right and UCase) and am getting a ‘Can’t find project or library’ error.
These functions work as expected in a blank project.
I have some code that run on Excel 2003, and fail on Excel 2000. It happens on functions Left, Right, Mid and Trim. I've found that I must use in "Late Bindings".
If exist any convertion for above functions?
I have set variable A and B up as integers
When I input the variables into the function
Code:
Rows(A:B).EntireRow.Hidden = True
I get a mistmatch error. I've used a MsgBox to confirm that both variables are indeed returning integers.
I am trying to use the enter key to populate a range with a userform's textbox data.
View 7 Replies View RelatedIs there a way to attach the VBA references on opening the workbook. The users of my workbook can be both excel 2003 users and excel 2010 users.
I want my macro, when you open this specific workbook. Check the Application version and if the version is 2003, than attch Microsoft ActiveX Data Objects xx Library and Microsoft ActiveX Data Objects (Multi-dimensional) xx Library and if 2010 than Microsoft ActiveX Data Objects xx Library and Microsoft ActiveX Data Objects (Multi-dimensional) xx Library.Where xx is relevant version no. for each 2003 and 2006.
I've got a long list of account numbers of varying length and ultimately need to add a dash and a dot into these numbers. Here's an example
12345678 to> 1-2345.678
123456789 to> 12-3456.789
1234567890 to> 123-4567.890
The standard format is always 3 numbers after the . and 4 numbers between the - and .
I can get to this by doing a series of functions starting with this:
=LEFT(A1,LEN(A1)-3)&"."&RIGHT(A1,3) to get 12345.678 or 123456.789
copy/pasting that value into another field and then doing this
=LEFT(E1,LEN(E1)-8)&"-"&RIGHT(E1,8) to get the results above
I can't quite figure out the format to combine the multiple steps/functions into one so that I'm not copy/pasting values and re-doing the function.
I am working on a file composed of 3 userforms.
Userform1 = Made of 2 Option buttons namely Data 2 and Data 3 and the usual command buttons.
Userform2 = I would like this to be the one that appears if I choose Data 2 and click Ok.
Userform3 = I would like this to be the one that appears if I choose Data 3 and click Ok.
I want to late coming and early going of employees. eg. office incoming time is 9 am and 5 mints late coming is allowed 9.05am . and outgoing time is 6pm i am not able to calculate late coming if outgoing is 5.30pm and if put formula =IF(E3>=6,0,6-E3) not working properly.
View 1 Replies View RelatedSee the attached spreadsheet and the comment boxes inserted containing my questions.
View 10 Replies View RelatedAll i want is to give solution for this time in and out.how to deduct late minutes to the salary of staff.
Date IN OUT
Thu 4/3/20149:1418:39
Sat 4/5/20149:0117:56
Sun 4/6/20148:5817:58
Mon 4/7/20149:1217:50
Tue 4/8/20149:1618:00
Wed 4/9/20149:0216:06
Thu 4/10/20149:1017:54
I have a very large spreadsheet with the following columns: WO# (number field); Start date (date format MM/DD/YYYY); Frequency (text); and craft (number). I am trying to have code that checks the frequency and if is "Monthly" or "Weekly" it just goes on to the next row; if it is "Annual", it adds 163 to the start date (start date needs to changed to a numeric field); if it is "Semi-annual", it adds 82 to the start date; if it is "Quarterly", it adds 45 to the start date; and so on, there about 20m different frequencies. After it adds the above value to the start date, I need to check if that number is less than today's date (the day I run the code). If it is, it needs to flagged as "LATE" and the whole row of info copied to another worksheet with LATE as the title and all the column headings and info copied to the worksheet. I hope this makes sense to someone because I am a beginner in Excel and even less informed when it comes to VBA. Any help would be greatly appreciated.
The way the process must work is that I need to check the frequency and if it is "Weekly" or "Monthly" , it is ignored and goes on to the next row. All other frequencies are cut in half, i.e., "Annual" is 183 days, "Semi-annual is 92 days", "Quarterly" is 45 days, "2-Year" is 365 days, and so on. This number needs to added to the scheduled start date (now formatted as a number, not a date, and checked to see if it is smaller than today's date (also a number). If it is, it is reported on the second worksheet (titled Late).
I cannot figure out the syntax for the life of me.
This should work...
Dim DatObj as Object
Set DatObj = CreateObject("MSFORMS.DataObject")
...but it does not.
Also, if it is not possible to late bind to this class,
I'm working with data as displayed below. My objective is to create a table by Model with summed quantitiies in two columns representing On-Time and Late. On-Time is achieved when Date Shipped is on or before the Promised Date. I have included the required format to be achieved in the second example. I'm willing to work with macros or VBA for the solution. I just need guidance to learn how to accomplish this task.
CREATE TABLES LIKE BELOW?
----B---- -----C------ --D-- ----E-----
6 Promised Date Shipped Inspection
7 Date Mod #
8 11/30/07 11/01/07 780 15216
9 10/10/07 11/01/07 230 15174
10 11/20/07 11/07/07 665 16314
11 9/13/07 11/07/07 230 15008
12 12/10/07 11/08/07 780 14452
13 11/15/07 11/08/07 780 15233
14 12/18/07 11/12/07 665 15219
15 12/3/07 11/12/07 780 15224
16 10/14/07 11/12/07 230 15011
CREATE TABLES LIKE BELOW?
-N-- ---O--- -P--
7 Mod On Time Late
8 225
9 230
10 335
11 555
12 665
13 780
14 1120
So I have a date in cell A1 for example.
Now if cell B1 is either blank, or more than 6 days from the date in cell A1 I want it to turn red.
Tried with index & Match formula but it throws errors in few cases becoz Cell value are either stored in general or H:mm formats
For ex.
Cell value "05:07" has a custom format applied (hh:mm), and in the formula bar it is displayed as "05:07:00" which is not what we want. When I change the format to 'General' Excel converts the 'time' to decimal, eg "0.213194444444444".
When I manually enter 05:07 the problem is nearly resolved as it changes to 'general' without turning to decimal, but displays as 05:07:00. The formula converts it to decimal where it throws error.
I have a formula in Excel2003 that is working fine except for when D12 is blank. When D12 is blank I want the result to equal either zero or blank.
=IF(ISBLANK(E12),NOW()-D12,E12-D12)
I am putting together a simple table to display current week's data vs previous weeks. The current week's data is drawn from a status chart which changes frequently. The constant change is fine for 'Current' as I only want the current data displayed.
The problem I am having is calculating the number of late jobs that existed during the previous week.
The status log has a due date which is compared to the current date to determine 'on time' status for the current week.
Due dates are reissued regularly so I can't use
=COUNTIF(RANGE,WEEKNUM(NOW()-1)) to return data about last week from my status chart.
I have available a 'Movement Log' (in the workbook but a separate worksheet) which tracks the changes in the due date field, but I'm not sure how to integrate that data to calculate the # of jobs that were running late from the last week.
My thought is that I need to perform a count of the # of late based on a comparison of 'due date' to 'date of the last day of last week' with a way to insert the "old due date" from the movement log to replace what is shown in the status log if necessary.
Movement Log.JPG
Status Chart.JPG
I want to grab everything left of the last occurrence of "." in a string, and in the next cell everything right of the last occurrence of "."
so say the string is 111.111.1.222
column 1
111.111.1
column 2
222
my current code (which works, but its messy) for the first cell is
I had a spreadsheet developed for me at work but the lad who developed it for me has subsequently left and I'll be honest I would not have any idea of macro's or some executables in excel. The only problem I'm having with this is it is not flagging item's as late unless they have gone over 2 months past the target date (would be ideally looking for 1 day past target date for it to flag late). Could anyone just have a quick look at this, I'm sure that this could be a quick fix but I would not have the relevant expertise for this. If someone can rectify the problem could you also just give me a quick explaination as to how this was done as I would like to have an idea myself and not have to continue to bug people with simple problems like this.
View 3 Replies View RelatedI am aware of the following topic in the VBA Help file:
"Using Microsoft Excel Worksheet Functions in Visual Basic
You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.
Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."
And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)
However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.
In cells BS8 I have the following =100*SUBSTITUTE(BR16,"metres","") to get centremetres from cell BR8 and in cell BT8 I have the following
=IF(BS8,ROUND('Under 6 Boys'!D11*(BS8-'Under 6 Boys'!E11)^'Under 6 Boys'!F11,0),"") I get a #Value error
I have also tried
=ROUND('Under 6 Boys'!D11*(BS8-'Under 6 Boys'!E11)^'Under 6 Boys'!F11,0)
In BT8 but I get same error
I have used it in another sheetsheet and it works I don’t understand why is is happening ...
I am using a combination of validation, vlookup and simple formulas to lay out a workorder. If I leave something blank in one of the feilds then everything that comes after say #n/a and won't allow the calculation to complete. Is there a way I can set a default value or something so to make calculate all the feilds that ARE filled in?
View 9 Replies View RelatedI have a workbook with data tabs (one shown here) and a summary tab. Essentially, one inputs hours for people in the Data tab (Tech) that are then calculated/summed to total $ based on rates I have hidden elsewhere in the workbook (not included). I'm trying to bring the subtotal lines (highlighted green) into the Summary tab based on which subtotal and what month/year it is. My formula works fine, except for the fact that it is returning a #Value! due to the "Hrs" heading for each Phase. I don't know why it's doing this because I've not run into sum array issues when mixing numbers and letters before. The error formula is highlighted in yellow in my attached sample, but below is a copy of the formula.
View 10 Replies View RelatedI can not get the following formula to work - I keep getting #Value errors and I've checked the fields and the values are correct
=SUM(IF(Input!$A$6:$A$4006=1,IF(Input!$I$6:$I$4006="DM",IF(Input!$K$6:$K$4006="Bid",Input!$L$6:$L$40 06,0),0),0))
My intension is that if A=1 and I=DM and K=bid then add the corresponding values in L and display. I can't figure out why this formula in another cell and works fine
=SUM(IF(Input!$A$6:$A$4006>0,IF(Input!$A$6:$A$4006<1,IF(Input!$I$6:$I$4006="DM",IF(Input!$K$6:$K$400 6="Bid",Input!$L$6:$L$4006,0),0),0),0))
it is just checking an additional condition of the value in column A
I am using a SUM function with multiple conditions as an array formula.
View 14 Replies View RelatedI am creating a spreadsheet which shows the date of when the training was cpmpleted. I have added an expiry column which gives the date 3 years from start date no problems. The trouble i am having is that the column will return an #VALUE when no training date is entered. ie: the person has had no training so no date can be entered.
I've been using some code I wrote at the start of March to open some files that I've created earlier in the day in order to add to them.
The code worked fine until the month changed. Here is the code I have to open the file I need.
strdate = Format(Now, "dd-mmmm-yyyy")
ChDir "C:DesktopTodays ReportsReports " & strdate & ""
Workbooks.Open Filename:= _
"C:\DesktopTodays ReportsReports " & strdate & "Date Report " & strdate & ".xls"
Since April 1st I have been getting a compile error saying I have the "wrong number of arguments or invalid property assignment".
I am at a loss as to why the code doesn't wok as nothing chaged between March 31st and April 1st.
Having a strange problem with vLOOKUP. Please see the attached file. The result is in cell M1 of the sheet (Link to the file below).
Vlookup works fine if the data is entered manually in cell H1, but with the formula it errors up .how i could make this work with the formula?
Sub indX()
For rwindex = 1 To 4
For collindex = 1 To 10
With Worksheets("sheet1"). cell(rwindex.collndex)
If Value < 0.001 Then .Value = 0
endwith
Next collndex
Next rw
End Sub
I have a code here and i have to spot what the errors are.
I know there is an error with 'endwith' but not sure what to do to fix it
What i'm looking to do is input a VBA command to comb the activesheet (which contains approximately 1400 rows, and 32 columns of mixed data and formulas), and determine if the text value "#N/A" appears anywhere on the sheet. If it occurs one or more times, i want it to set a boolean flag to true.
I know of ways to do so by setting a = countif(A:AG,"#N/A") formula in a cell, but unfortunately this is not my solution here, as it needs to be done before the formula is converted to text. A countif does not show a visible value of #N/A if its true value is a formula.