Hard to tell exactly what you are trying to do, but could you use
For i = a to b and set the values of a to b accordingly?
Posts by fifijazz
Not sure if this is exactly what you are looking for, but what I do is use an auto_open macro in the workbook I want to run automatically each day. Then I use Windows Task Scheduler to open the workbook at 6am every morning. This will open a new instance of Excel, though, so if you have Excel already open, you could get error messages when Excel tries to re-open files in your XLStart folder. The way I handle this is that I save and close all open workbooks to temporary files and close Excel every day as follows:
This is in my in Personal.xls ThisWorkbook:
Private Sub Workbook_Open()
'Application.OnTime TimeValue("5:00"), "My_Close"
This is in a standard module within Personal.xls:
Dim wb As Workbook
Dim i As Integer
i = 1
Application.DisplayAlerts = False
For Each wb In Workbooks
If Left(wb.Name, 6) = "orders" Then
wb.SaveAs Filename:="c:\TossOrKeep" & i & ".xls"
i = i + 1
Set wb = GetObject("C:\Documents and Settings\Myname\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLS")
Set wb = Nothing
Copy the used range of your worksheets then paste them into Access. You'll have to create the Joins manually, though. If you get Import Errors when you paste, most likely you have numerical entries in an alpha field or vice versa. If you have a field that contains text that some times looks like a number, make sure that the very first record contains an entry that is clearly text, e.g., use the record containing "12345AB" as the first record, not the record containing "23456".
After highlighting your input range, let's say A1:A500. Choose Data>Data Validation from the menu. On the Settings tab: Allow = Custom.
In the formula box: =COUNTIF(A1,A$1:A$500)=1. (Watch the $ signs.) Type your warning message on the Error Alert tab.
Don't know why you are having this problem summing your column, but the COUNTA function should give you the result you want. COUNTA "doesn't care" whether there the cell contents are text or numbers.
As to your second question, you could use the IF function: something like
Have you tried OnCallTec.TextBox1.Value = Format(DateAdjust, " dd/mm/yyyy")?
If you make the formula bar visible, you will be able to read your text in the formula bar when you select the cell. (Tools>Options>View. Formula Bar checked.)
Don't select the paragraph before inserting. Just click where you want the comment to go. Works for me, anyway.
Here you go.Code
'automatically answers "yes" to the dialog security box in Outlook Set fso = CreateObject("Scripting.FileSystemObject") Set fsofile = fso.CreateTextFile("ByPass.vbs") fsofile.writeline "set fso = createobject(""wscript.shell"")" fsofile.writeline "while fso.appactivate(""microsoft outlook"") =false" fsofile.writeline "wscript.sleep 1000" fsofile.writeline "wend" fsofile.writeline "fso.sendkeys ""a"", true" fsofile.writeline "fso.sendkeys ""y"", true" fsofile.writeline "wscript.sleep 7000" fsofile.writeline "while fso.appactivate(""microsoft outlook"") =false" fsofile.writeline "wscript.sleep 1000" fsofile.writeline "wend" fsofile.writeline "fso.sendkeys ""y"", true" fsofile.Close Set wshShell = CreateObject("Wscript.Shell") wshShell.Run ("ByPass.vbs") 'send out chart Set varOutApp = CreateObject("Outlook.Application") Set varSendIt = varOutApp.CreateItem(0) With varSendIt .To = "whoever" .Subject = " Report" .Body = " put message here" .Attachments.Add ("\\server\share\directory\" & varFileName) .Send End With Set varOutApp = Nothing Set varSendIt = Nothing End Sub
You could use MS Query to get the data. You will have to name the database range in the source file. Then in the destination file, choose Data>Get External Data>New Database Query. You can let the query wizard create your query (easy), or do it manually (infinitely more flexible). If you've used Access at all, the process for building a query in MS Query will not be foreign to you.
If your quotes are in col a estimator in b, and date in col c, then:
=SUMPRODUCT((MONTH(C5:C10)=8)*(B5:C10="Bill")) will count all of the estimates Bill did in August. If you want to total the estimates then:
For starters, go to Edit>Links and make sure it is set to automatic. Don't know why you are having to point to the file every time, but if you are linking to the file via an assigned drive letter, I would try going the long way round and use the full path name (\\fileserver\share\directory\myfile.xls).
Don't have a clue as to why this is happening, but have you tried importing from Excel (using MS Query) instead?
range("a1").End(xlToRight).Offset(0,1) will get you to the first blank cell in row 1.
Hope you are feeling better. This is probably not really what you meant, but if you truly want to return both the quantities and total values with just 8 formulas, you'll need to create a string something like: SUMPRODUCT((M8:M50="quarter")*(N8:N50>0)) & " discounted quarters totalling " & "£" & SUMPRODUCT((M8:M50="quarter")*(N8:N50>0)*(1-N8:N50)*(O8:O50))
Click the Autosum button. (You should see a yellow Tooltip that says "AutoSum" when you are over the correct command button.) If you don't go to the Tools Menu, choose Options, click the general tab, check the Function tooltips box, click ok.
The only other way that I know of is to digitally sign the macro. Then the other users would still have to designate you as a "trusted source". Sorry, I'm not sure exactly how it works, having never tried it. Take a look at "Troubleshoot security and protection in Excel Help" if you want to evaluate this method.
There is a nice example of this type of form in the Northwind sample database that comes with Access. (mine is located in C:\Program Files\Microsoft Office\Office10\Samples).
As to your second question (if I understand it correctly), I would concatenate the name with whatever other field or fields will create a unique key field for searching.