Re: Variant Array Checking Type Mismatch
Thanks Richard, I thought I had to be being silly.
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
'///..............
Display More
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...
...
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
...
Display More
Re: Add-in Userform Gives Variable Not Defined
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"
The code in the userform is all private subs.
Similarly when calling a public sub from the add-in such as;
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
Display More
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?