This could be solved without VBA by the following technique. In this example I am counting all the threes (3) in the range A:A of the sheets Page M904, Page M905 and Page M906. List all the sheet names in a single continuous range like in the following example. Here listed in the range D3:D5. … Read more
A variant is the only type of variable that can store an empty value or a Null value, and is declared thus: Dim aVar as Variant Dim aVar2 ‘ if no type is given then it’s declared as a variant Immediately after declaration a variant stores no value and it is empty. Also you can … Read more
You are getting this error because the value cannot be found in the range. String or integer doesn’t matter. Best thing to do in my experience is to do a check first to see if the value exists. I used CountIf below, but there is lots of different ways to check existence of a value … Read more
I agree with @Richard Morgan … what you are doing should be working, so more information may be needed. Microsoft has some suggestions on options to protect your Excel 2003 worksheets. Here is a little more info … From help files (Protect Method): expression.Protect(Password, Structure, Windows) expression Required. An expression that returns a Workbook object. … Read more
Here is a method that will return the hyperlink text whether it has been created by a formula, or by the Insert/Hyperlink method. If the former, we merely have to parse the formula; if the latter, we need to iterate through the hyperlinks collection on the worksheet. The formula will return nothing if there is … Read more
Don’t know what was wrong with your union, but it would have created the same range, which you stated in your first attempt. The problem is, you have now multiple areas. Which you can, and as far as I know, has to address now. Here is an example, which will resolve in an array of … Read more
If this is not a particularly changeable workbook (i.e. worksheets are not being added and deleted all the time), you could store the names of the worksheets in a range on a hidden sheet and loop through them by name. However, it sounds like they are stored consecutively in the workbook so, building on Mathias’ … Read more
Here is my ConcatenateRange. It allows you to add a seperator if you please. It is optimized to handle large ranges since it works by dumping the data in a variant array and working with it within VBA. You would use it like this: =ConcatenateRange(A1:A10) The code: Function ConcatenateRange(ByVal cell_range As range, _ Optional ByVal … Read more
I do this for my work currently with VS 2012 .NET 4.5 for reporting automation for PDF reports. A. For ease of use compiling your own proxy class is easier than referencing the web service each time as you may forget the service name. From Visual Studio Command Prompt: wsdl /language:CS /n:”Microsoft.SqlServer.ReportingServices2010″ http://<Server Name>/reportserver/reportservice2010.asmx?wsdl reference: … Read more
If the only problem is sharing it with your Polish co-worker, then you can simply use the international placeholder “e” to replace “YYYY”: =TEXT(A1,”e-MM-DD”) Now, while this would work fine for your co-worker in Poland, there are countries where “m” and “d” would also need replacement. If in general we need to return the locale … Read more