Posts by nb-

Re: Variant Array Checking Type Mismatch

Thanks Richard, I thought I had to be being silly.

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.

Option Explicit
Option Base 1


Public Sub ocalc()
Dim formulastore As String
Dim startcell, firstcellrange, fullrange As String
Dim sourcedatarange As Range
Dim loopglobaldata As Long
Dim i As Long
Dim j As Long
Dim newarraysize As Long
Dim newarray As Variant




firstcellrange = "V11"
fullrange = "V11:AI35"


'Worksheets("Parameters").Range("R11:R110").Calculate
'Worksheets("Parameters").Range("A11:D35").Calculate
Sheets("48_sheet4").Activate
Set sourcedatarange = Sheets("48_Sheet4").Range(Cells(2, 1), Cells(Range("A65536").End(xlUp).row, 31))
Sheets("parameters").Activate


Application.ScreenUpdating = False
ActiveWorkbook.PrecisionAsDisplayed = False


startcell = activecell.Address


formulastore = Range(firstcellrange).Formula
Range(fullrange).ClearContents


    With Application
        .Calculation = xlAutomatic
        .MaxChange = 0.001
    End With


'// Load date once sheet updated.
Call loaddata(sourcedatarange)
    
    With Range(firstcellrange)
        .Formula = formulastore
        .Copy
    End With
    


If Sheets("Parameters").Range("H6").Value = 0 Then
    For loopglobaldata = 1 To UBound(globaldataarray, 1)
    
    Debug.Print globaldataarray(loopglobaldata, 30)
    
        If globaldataarray(loopglobaldata, 30) = "OHS" Or "DHS" Then '// <-------Error here.
            For i = LBound(globaldataarray, 2) To UBound(globaldataarray, 2)
            globaldataarray(loopglobaldata, i) = ""
            Next i
        End If
    Next loopglobaldata
End If
'///..............

In a separate module;

Option Explicit
Option Base 1


Global globaldataarray As Variant
Global jointarray As Variant




Sub loaddata(Table As Range)


On Error GoTo loaderror


globaldataarray = Table
'Debug.Print "OK"
    
Exit Sub


loaderror:
Debug.Print Err.Number


End Sub

Re: Add Listbox Items As Selected Without Counter

Thanks, .ListCount was what I was evidently after.

I now have;

...
With ListBox1
    .AddItem "Sample1"
    .AddItem "Sample2"
    .AddItem ""
   For Each CurCell In Worksheets(datasheet).Range(tempstring)
   .AddItem CurCell.Value
    If CurCell.Offset(0, 2).Value = 1 Then
        .Selected(.ListCount - 1) = True
    End If    
   Next
End With
...

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...

...
With ListBox1
    .AddItem "Sample1"
    .AddItem "Sample2"
    .AddItem ""
    iCount = 2
   For Each CurCell In Worksheets(datasheet).Range(tempstring)
   iCount = iCount + 1
    
    If CurCell.Offset(0, 2).Value = 1 Then
        .AddItem CurCell.Value
        ListBox1.Selected(iCount) = True
    Else
        .AddItem CurCell.Value
    End If
    
   Next
End With
...

Re: Add-in Userform Gives Variable Not Defined

http://www.fontstuff.com/vba/vbatut08.htm

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()


UserForm1.Show


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?

Re: Conditionally Hide Chart Axis Labels

Thanks.

Re: 45 Degree Comment On A Chart

Thanks, is there anyway to make word art look exactly like normal text, I can never get it to look quite the same.

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;

[Black][>96]General;

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

Re: 45 Degree Comment On A Chart

Imagine a graph with the line on it X=Y, easiest to show with the picture below. Unfortunately data labels didnt allow me to get the text where I wanted it.

What I am trying to do is get text like that in red.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]And the attachment...

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.

Option Explicit


Public Function FirstLinePickUp(inputrow As Variant) As Variant


Dim n As Integer
Dim testcell As Variant


n = 0
testcell = ""


Do Until testcell <> "" Or ActiveCell.Column - n <= 0
testcell = Cells(inputrow.row, ActiveCell.Column - n)
n = n + 1
Loop


FirstLinePickUp = testcell


n = 0
testcell = ""


End Function

Re: Median & Modal In Pivot Table

Thanks, but I couldnt find anything that was much 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 http://office.microsoft.com/en-us/excel/HP052091071033.aspx

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?

Re: Pivot Table Median And Modal Data?

Are there any plugins, addons or cunning ways/bits of code anyone knows of to get this functionality?

Excel pivot tables only seem to have a mean (average) function for the data field, is there anyway I can get the median & modal data values for the data?