I'm trying to run a macro on my machine (Windows 2000 and Office 2003) and I keep getting an error on one method. Now I tried to run the same macro on a different machine (Windows XP and Office 2003) and it didn't error out.
Public strControlTitle As String Public strControlTime As String Public strControlUnit As String Public intControlItem As Integer Public intLastNameOnly As Integer Public intDataType As Integer
Sub Main() Dim strControlItem, strPageName, strValue, sigma, z95, z99 As String Dim r, c, count, cmax, rmax, rt, ct, low, high, a, b As Integer Dim lesscol As Integer With ControlChartForm .ComboBox1.AddItem ("Average Turn Around Time All Patients")....................
I want to load one with data from a worksheet query table that will vary in size each time it's executed. The length will vary, but the range is continuous, and five columns wide. I'd prefer a do while loop, looping until a blank cell is encountered. Can someone get me started with general syntax and setup of the array?
I'm trying to load an array with numbers from 3 to SheetCount. If someone wouldn't mind giving me a nudge in the right dirrection, it'd be appreciated.
I have ten customers who have multiple locations where we do work. On ourinvoice I want to use Data Validation (list option) to read the input "CustomerName", and then change the validation list in the "Job Reference" cell accordingly. For example if A1 is the customer name and A2is the "Reference" where the data validation list will be. Customer ABC has 3 locations, XYZ has 5, CompanyD has 4, and so on. The range for thevalidation array is C1:C5. ABC's list oflocations is located at A10:A15, that of XYZ at A16:A20, and Company D atA21:A25 (and so on, for the others). Howcan I load the data validation range (C1:C5) with the correct list from, forexample A16:20 based on A1 being customer XYZ?
I'm trying to pass a variable from Word to Excel. Basically I have a Word document with a plain text content control in it. I'll have users populate this field. I know how to create a reference to that content contol in Word VBA that'll tell me what's in that content control (eg.
I have a VBA macro for automatically create a word report using data in Access and Excel. The macro is hosted within a Excel document but are using both Word-, Excel and Access objects/dataTtemplates for the creation.
The errors appears what it seems randomly. The macro iterates the same code a few times even in Office 365 but then it crashes. The most common error messages are:
Error 1:
Run-time error '-2147417851 (80010105)':
Automation error : The server threw an exception.
Error 2:
Run-time error '462':
The remote server machine does not exist or is unavailable
Error 3:
This action cannot be completed because the application () is busy. Choose Switch To to activate and correct the problem.
(no options are working, this hang the application)
If I debug usually the error originates from the code where I ether close the workbook template I'm using:
excelWorkbook.Close False
... or when I paste a chart from Excel to the Word report:
wordDocument.ActiveWindow.Selection.PasteSpecial
Even more strange are that sometimes if I continue to run the macro its all working again. This ONLY happens when the client office installation is O365. Office 2013 works just fine. I thought O365 and Office 2013 was fairly equal?
I have created the attached spread sheet to work out and calculate gp on products. Sheets and userform1 are working perfectly how ever userform2 wont load and I believe its something to do with the named range? The idea for this userform was to select a product from combobox1 and then change the price on sheet1 and clear the price on sheet 2 in the different sizes.
Also userform2 i would like to create it so that if any of the fields are left blank it wont change price of selected field in sheet 1 and wont clear selected area in sheet 2.
Finally is there a way on userform1 to shrink the userform size depending on how many sizes are needed?
I have an excel add-in which is a ribbon which houses a great deal of financial tools that I use on a regular basis so I don't have to hunt around excel for things I use regularly. The add-in has always loaded automatically whenever I opened excel - whether I opened excel by creating a new blank document or by double clicking a previously existing file.
Starting a few days ago though, the add-in only opens when opening excel by creating a new blank file. I can't think of anything that changed recently. Using Excel 2007.
Even when opening a file by double clicking, Excel still says the add-in is active (in excel options).
I'm doing a Vlookup and when I copy down the formula Excel asks me to update the values by opening a new document. But this I can't or don't want to do - but I can't get rid of the box that asks me update the values with a new file.
is it possible using vba ideally with a button marked save, to save a single worksheet and name it, in CSV format
Then I would like to be able to do the reverse load a CSV file back in to a work sheet
Expanding upon this
1. Click a command button with caption “save”
2. Take the name of a given sheet, in my case DataToUpLoad and append it with a number to give a sheet name of DataToUpLoad 1 the first time it is save and DataToUpLoad 2 the next time and so on
3. Click another command button with the caption “load” interact with usual dialog box.
My current work around is to copy a sheet open a new work book past the sheet in the save that work book as CSV
The reason this is required is to mate up with the php on the server side
I have been searching the web and wracking my brain trying to do this and now I am here (for one place to start) for help. Especially since I got the formula below from this forum.
1. I have a worksheet with multiple sheets. All the sheets except the last are labeled by department. The last sheet is labeled Totals. All the department sheets follow a template that I don't want to change so any totals or formulas I want to add, I want to do off of the Totals sheet.
2. For an example, I want to total all PCs with the model "Compaq 1.8" from all the sheets onto the Totals sheet. It doesn't seem like Countif can do multiple (or 3d) sheets. It can only do one sheet.
It looks like the only way to do this is to use a UDF (User Defined Function) or some other Function to do this.
I found a UDF that looks like it should work (see below). However, I have tried this function but all I get is the #NAME? error/message. I have tried multiple ways to simplify this function just to test that I can get any data from it but keep coming up with the #NAME? error.
Public Function CntIf3D(rng As Range, V As Variant, ParamArray arglist() As Variant) Application.Volatile CntIf3D = 0 For Each arg In arglist CntIf3D = WorksheetFunction.CountIf(Sheets(arg).Range(rng.Address), V) + CntIf3D Rem Next End Function If I could get the above code to work, It would be nice but I would have to add another department into every total for each new piece of equipment. Some code that would just use the sheet indexes would be nicer, that way any new department inserted before the TOTALS sheet would be automatically added in. Since I can't even get the above code to work, however, I don't want to waste my time trying to test code that would use the indexes.
I have been asked to create a spreadsheet that will contain three fields;
Date of placement (the day the person arrived) Date of release (the above + 60 days) Billable days for the current month
The first is simple, the clerk will enter the current date when the person arrives.
For the second field I am using:: =SUM(H5+(60),) (H5 is todays date, and will display the results in I5). When H5 is blank, I get the " #VALUE!" error in I5? Is there a more effective function to use?
For billable days, I am using; =SUM(J2 - (H5),) (J2 is simply the last date of the month; 12/31/09, and H5 is the placement date). I want to show the number of billable days in the current month. This will be repeated on 12 sheets (one for each month).
Any freeware (or shareware if necessary) program that will allow you to change tab colors on spreadsheets in Excel 2000. Later versions do have that ability built in, but 2000 does not.
I've created a macro that loads a listbox with the sheet names from another workbook. It works fine with Excel 2007-2010 but some reason fails with 2013. It doesn't crash, it just doesn't add items to the listbox nor doest it add the caption to Userform1.Caption. It does launch the form. Here is the code snippet:
Code: 'open read-only wbkpath = Sheet3.Cells(1, "f") 'full pathway to source workbook Workbooks.Open Filename:=wbkpath, ReadOnly:=True Set swbk = ActiveWorkbook swbk.Activate
I am creating a chart with more than 2000 dates (each day). How can I format the axis so it only displays the 1st day of each quarter (01/01/2010, 01/04/2010, 01/07/2010...).
At the moment I cannot format the axis and minor/major units remain numbers.
I record all jobs that come into my department. Column A contains the Job reference. Column B Contains the date that the job arrived and Column C contains the date that the job was complete.
The job can be split into several sections and can arrive on different days and the sections can be completed in different days.
I am looking to create a table that will show how long the complete job took to complete, so I am looking for the earliest date and the latest date for specific job reference numbers.
An example of my data is:
Column A Column B Column C Ref No Date In Date Out 2013-0055 01/03/2013 25/03/2013 2013-0061 01/03/2013 03/03/2013 2013-0061 02/03/2013 20/03/2013 2013-0055 07/03/2013 28/03/2013 2013-0061 08/03/2013 19/03/2013
From the above data I am looking to create a table that will show
Ref No First Date Last Date Number of Days 2013-0055 01/03/2013 28/03/2013 28 2013-0061 01/03/2013 20/03/2013 20
the macro works fine until it executes the paste values. At that point, the macro jumps to the "CountThem" function which is located in another workbook. The data that I am copy/pasting is in no way connected to any cells that are using that function. Although, other values in the workbook are passed down from data that uses that function.
I am still in the dark ages using Excel 2000.
This is the code for my macro.
Code: Sub Current_to_Raw() ' ' Current_to_Raw Macro ' Macro recorded 2/12/2014 by ' ' Range("N14").Select
Excel 2007 is my version. I'm trying to build an array from a dynamic range. I will know the column letter and lastrow.I think I could do this via a FOR and NEXT routine but there has to be a more efficient way. Here is my inefficient code idea:
Code: Dim orgctarray(65000) As Variant 'because I don't know how long the array will need to be Dim cnt as integer Dim startrow as integer[code]....
So as you see, technically, I need to take sheet1.range("B3:B" & lastrow) and turn it into a vb array (I don't want to copy the data to some hidden worksheet and do the work there)I'd also like to remove duplicates and sort orgctarray alphabetically after I have completed building it.
On this is a column of Categories and a Column of sizes. I want to use these in a user form. The user will select their Category from a drop down list and the second drop down list will include only the sizes that appear next to the chosen category So for example in the attachment if the user chose 'AUD' as a category they would get the size choices of, '2x4 insert', '2x4 replica' and 'A4L' in the other drop down menu.
The master list of sizes will have to remain on a worksheet as this is what other operators will amend from time to time.
Im using excel 2010 As it's 60 times quicker I was trying to speed up my code and replace all loops by putting the value into an array, and then transfer the array to the worksheet
It seems to be straightforward for math calculations like in this example:
[URL]
But no luck with the one below. I was trying to test it on a simple loop which replaces two types of string into the 3rd one:
Code:
Dim lastrow, lastrow2, i As Long With Worksheets("KPI5") lastrow2 = .Range("N" & Rows.Count).End(xlUp).Row .Range("T7:T" & lastrow2).Value = .Range("F7:F" & lastrow2).Value For i = 8 To lastrow2 If .Range("T" & i).Value = "Modification" Then
