Posts by nb-

Re: Pivot Table - Percentage Of Data Of Type

Thanks, I was being very silly. I will have a play, looks as though I am going to have to use several pivot tables to get what I want, which is Male/Female as a percentage of A or B in cat1 and both A&B.

I have a pivot table based on data in the form (simplified);

Year Cat 1 Sex
1 a F
1 A M
1 B U
1 B F
2 A M
2 A M
ETC. ETC. ETC.

So in the pivot table we have year 1 in the first row year 2 in the second etc. cat 1 as the column headings.

What i want to do is get the data item area of the pivot table to display the percentage male female etc by year, something like =(Count of F in Sex)/(Count of Sex), how can I do this?

So year 1 would be 50% Female, 25% Male, 25% Undefined.

Re: Loop Through Variables

I am trying to get variables passed to a function from sheets throughout a workbook into an array without having to write out a page of if ismissings as i dont know how many there will be.

Is it possible to pass these as an array?

This has sort of been asked before

http://www.ozgrid.com/forum/showthread.php?t=38479

but is there a way to loop through a series of variables like var0...var9? You can obviously loop through and make a set of strings with the right names,

Dim I As Integer
Dim Var As String
For I = 1 To 5
Var = "Var" & I
Next I

Is there a way to use the contents of a string variable to call a variable with the same name as contents of the string? or somehow concatenate the a string with a number like
array[I]=var.[I]?

Re: Vba Function Sheet References

Thanks

How do I select different sheets from within a function?

I have a function that takes the parameter of a cell, and then looks up and down from that cell, and returns a value based on those values. This works fine until I select a cell on a different sheet from the cell the function is written in.

In outline it is as below;

Option Explicit 
 '// Test  Function
Public Function testfunction(targetcell As Variant, par1 As Integer, par2 As Integer) As Double 
    Dim row%, column% 
     
    row= targetcell.row
    column = targetcell.column   
     
     
    testfunction =  Cells(row, column) / (Cells(row - par1, column) + Cells(row + par2, column)) 
     
End Function

Thanks

Re: Getting The Cell Index For A Function Not The Cells Value

Thanks very much Andy :).

& the Okk, thanks, yes the the function shown could be done with a formula, but it is just a grossly simplified example .

Re: Getting The Cell Index For A Function Not The Cells Value

Thanks Andy, but all I can get out of that is the cell the formula is in, I dont know how to set a cell passed to the function as the active cell to use activecell.xxxx which would be one way of doing it if its possible.

What I want to do is have in an Excel cell;

=testfunction(B6, X, Y)

And be able to drag it down a column, across a row etc, and have B6 change accordingly. But what I want is the row and column index of the cell as i would get in Excel by typing

=row(B6)
&
=column(B6)

as the result depends on the values X cells above it and Y cells below it. See example below;

Option Explicit
'// Test Function
Public Function testfunction(targetcell As Variant, par1 As Integer, par2 As Integer) As Double
Dim row%, column%


row= ???????????????.targetcell
'//Equivalent of =row(targetcell)
column = ??????????????.targetcell
'//Equivalent of =column(targetcell)




testfunction = Cells(row, column) / (Cells(row - par1, column) + Cells(row + par2, column))


End Function

Re: Getting The Cell Index For A Function Not The Cells Value

Thanks, the problem is they are worksheet functions that are not available to VBA, so I cant use them. Anyone got some bright ideas?

I am trying to get the cell index from a cell reference passed to a function and I have no idea how.

eg. In excel

=UDF(D4,...,...,)

What I want is to get is the row and column index for cell D4, not whatever is in cell D4, so I can just click the cell in excel, and drag the formula about etc.