Posts by nb-

    I have an array that is created by loading a range from a workbook into a variant. When I try and validate the data (by checking if it matches either of two strings - see commented error line below) in the array globaldataarray I get "Run Time Error '13' Type mismatch".

    The column of data in question has a column heading (excluded by starting at row 2) and according to the quick pivot table I ran on it, to see the unique entries, contained only blanks, single letters and 3 letter strings.

    I get the same error when trying to do the same thing on a column of numbers (decimal an integer) produced by a formula in that column.

    Any ideas thoughts or suggestions much appreciated.

    In a separate module;

    Re: Add Listbox Items As Selected Without Counter

    Thanks, .ListCount was what I was evidently after.

    I now have;

    Is it possible to add items to a listbox as selected without using a counter?

    what I have is a multiselect listbox, and I am adding items and selecting them using a counter (see below), but is there a more cunning/succinct way of adding the items as selected? I keep thinking there should be...

    I have a userform in an add-in (which is loaded), when I try and call it from an excel sheet, I get the following error "Variable not defined"

    Private Sub CommandButton3_Click()
    End Sub

    The code in the userform is all private subs.

    Similarly when calling a public sub from the add-in such as;

    Private Sub CommandButton3_Click()
    Call SillySub
    End Sub

    I get the error "Sub or Function not defined".

    How should I be doing this?

    Re: Drag All References To A Cell In One Go

    Thanks, thats the way I have been doing it, but what I wanted to know was if for example, I could hold down a key while dragging a cell reference, and it would move all the references to that cell, not just one.

    Re: Drag All References To A Cell In One Go

    Thanks, but I am not moving the formula. I want to change the cell it refers to from say A1 which has say 5 in it to cell B27, which has -18.3 in it, while leaving the formula where it is (and leaving the content of cells A1, and B27 alone).

    Is there an easy way to drag all the references to a cell in a formula quickly e.g If i have a formula like this is;

    =IF(A1>0, A1+1,A1-1)

    and want to drag all the references to the cell A1 to another cell in one go as opposed to one at a time?

    Is it possible to hide some of the number labels on an axis; e.g. in on the chart below to hide the X-axis label 96? Or only start numbering from 97 while the chart displays from 96.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Well I have a solution to hiding the first value on the excel X axis values labels just using a custom number format;


    but is there anyway of formating more than a couple of the number labels individually?

    Is there a way to add comment text at a 45 degree angle to an excel chat?

    What I need to do is to add a comment parallel to a 45 degree line on an excel chart, and the text box dosen't give you the option t have 45 degree text.

    Re: Udf Not Updating As Expected

    Thanks bryce, but its not that.

    Thank you Parsnip. I just tried using Application.Caller. ... instead of activecell, and it seem to work in the little test sheet i just tried it in. I didnt know about application.caller, my limited vba knowledge didn't know any other way of getting the column number of the cell the formula was in.

    What it was intended to do was to lookup on a row (the row of the cell you give it as a parameter), and hunt back (left) along that row from the column the function is in until it finds a cell with a value in it and print that.

    I have a written the function below, but when ever I use it, and for example drag it across lots of cells, they all come up with the same value, and I have to manual click on each one and pres enter to get it to show the right value. I have tried searching but without much luck as I am not sure what I should be searching for. Using application.volatile doesn't help.

    Re: Median & Modal In Pivot Table

    What I would need to do that would be the source data that has been selected, for the cell,

    From the pivot table example data at

    Looking at cell C6, which gives the sum of Beverage sales by Buchanan in March; what I would need is the data values that go to make that total, say sales of $500, $400, $600, $1000, $1022, and I dont know how to get this data from a pivot table? ideas?