Hi I am having some difficulty creating a macro - i need a macro whereby when the user input a dept code, the macro will take the number, go to first worksheet, read column B until it match the dept code, then copy all the appropriate rows for the dept code. I then need this to loop until the last worksheet. The workbook has about 9 work...
Hi, I add many check boxes to the excel sheet programaticaly using the following code: With ActiveSheet.CheckBoxes.Add(rCell.Left, rCell.Top, rCell.Width, rCell.Height) .Interior.ColorIndex = xlNone .Caption = "" End With Now I need a code which would parse through all the Check boxes that are present in the sheet and...
Hi, How to pass argument to a method which is registered with the onAction event in excel VBA. The code is have is: With ActiveSheet.CheckBoxes.Add(rCell.Left, rCell.Top, rCell.Width, rCell.Height) .Interior.ColorIndex = xlNone .Caption = "" .OnAction = "CheckboxChange" End With I want to pass the "rCell" to ...
I haven't read much about it, but the author at the link below recommends that I don't use "bubble up" to centralize error handling in VBA. http://bit.ly/k2WJW (↑ Excel Programming Weekend Crash Course via Google Books) But I'm not sure why he recommends that, and he doesn't really explain. Can someone tell me why I should put error ...
hi, i have a document mydocument.docx i want to print that from a button in excel sheet. both are in same folder. i don't want users to see word document.They just click the button in excel and printing of word file starts. this is actually very urgent. and i am totally new to this so if u can explain this in steps that would be so awe...
What is an Excel macro that will check every cell in column A for whether they have unique numbers or are blank? ...
My columns have line breaks, but how can I convert these to vbCrLf when reading with Range("A" & r).Value? ...
Hello, I've been looking for a very specific Color Dialog box in Excel the last couple hours, but all examples I've found aren't exactly the same. I'm looking for this Color Dialog box to incorporate into a form: http://yfrog.com/29colorboxj I'm not too familiar with Dialogs either... any advice is greatly appreciated! ...
In Excel 2007, I have a single dataset in the form of an Excel Table from which I want to make multiple pivot tables and pivot charts. I would like to be able to synchronize the 'report filter' field across all of the pivot tables and charts simultaneously, so that by filtering, (for example) the US_Region field to 'Pacific Northwest' on...
Hi all, I was tasked with counting the number of distinct strings in a column in excel. A quick Google search later yielded the following formula found here: =SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1)) Consider the data: A B C D A B E C Now, the match function would return an array (as the first argument...
I need to concatenate a column of cells based on a variable in a previous cell. This will continue on until the specified variable changes. For example: A B C D E 1 x @1 @1+@2+@3 2 x @2 3 x @3 4 y %1 %1+%2+%3 5 y %2 6 y %3 etc. I need the macro to look at A1 and if it's x then begin a concatenated string ...
I am trying to use Excel the DateAdd function to find the next working day. It seems that Excel is not working as expected with this function, and always provides the same result wether you use d, w or y for the period argument. (by the way the explanations of the "y" value are beyond my logic). I tried the following code, and get ident...
I am currently working on a vba project that has the end user copy/paste long strings of text into a worksheet and the code to parse out data from the junk in these strings and organize it from them. The strings will always be in different lengths, and have a different number of spaces between the data. However they will always be g...
A Word mailmerge macro from excel datasource gives dialog "name or cell range" How can I hide that dialog? ...
I need to prohibit user from selecting value in some Excel 2007 combobox control inserted via Developer menu by condition. Now I managed only to show/hide the control. ActiveWorkbook.Worksheets("summary").Shapes("months").Visible = year <> "" Is it possible to enable/disable it instead? ...
I'm trying to fill an array with consecutive integers using Excel and VBA to pass to the Subtotal function. I need a dynamically sized array of consecutive ints. The fnction works when I use Array(1,2,3,4,5) for example, but not if I try to use a dynamically sized array. I've read some articles on this but none actually worked - has any...
I'm using VBA to sort columns in Excel 2003. I need to sort by column 5 ascending, then column 3 using a custom order, then by column 4 ascending. I'm having difficulty getting the sort to work and I don't totally understand how OrderCustom applies. Any pointers in the right direction would be appreciated :) My code is below. With wsDa...
I have a macro that uses the Dir function. MyFile = Dir(CurDir() & Sep & "*.xls") Do While MyFile <> "" ... MyFile = Dir() Loop How do I extract just the filename from MyFile (exclude the extension)? ...
This is a long one - apologies.... Is there a reliable way of returning lists from C# to VBA via a web service? I've got a web service that returns a 'result', which consists of some doubles, strings and so on (it's an engineering calculation). I also return a list of 'Errors' and 'Messages' which contain information on the calc and g...
I have a chart object, with 3 series. The series are getting the Y-values from ranges C1:C10, D1:D10 and E1:E10. The value depends on the values in A1:A10 (eg C1 = A1+6); but I'm charting the values against the values in B1:10 (its a Log-Normal graph). I'm calculating the values in VBA. Since there is only a discrete number of points ...