Macro To Return Matrix

Aug 18, 2008

I have made a function (udf), called linjenr. It's input is an n by n matrix, and it returns a n by 2 matrix. This works fine in the worksheet. However, when I'm trying to use this function (udf) in a sub it fails, i.e. I get the error "object does'nt support this property or method". How do I use an own made function that returns an array in a sub macro? (ctrl shift + enter..)



Sub linjegenerator()

Dim nodematrise As Variant
Dim antlinjer As Integer
nodematrise = Range("F6:T20")
antlinjer = WorksheetFunction.Sum(nodematrise) / 2

If antlinjer < 3 Then
MsgBox "This is not a network. Minimum 3 lines."
ElseIf antlinjer > 2 Then
If antlinjer = 3 Then
'Range("F25:G27") = linjenr(nodematrise)
ElseIf antlinjer = 4 Then
'Range("F25:G28") = linjenr(nodematrise)
ElseIf antlinjer = 9 Then
Range("F25:G33") = WorksheetFunction.linjenr(nodematrise)
End If
End If
'antlinjer = WorksheetFunction.Sum(nodearray) / 2

MsgBox "Antall linjer = " & antlinjer

End Sub

View 9 Replies


ADVERTISEMENT

Return Inverse Matrix Of Large Matrix

Aug 22, 2008

Using VBA, I wish to work out the inverse matrix of a large matrix (100*100), but keep getting the # Num! Error. I am using the minverse function. I have defined variable as "variant", does this give me the same possiblities in terms of number size as the variable "Double"?

View 9 Replies View Related

Find & Return Intersection Of Matrix

Aug 20, 2008

I have a 10 x 15 matrix named "Range1"

I have named the range of Column headings in that matrix as "Weight", and the range of Row headings as "Height". And then all the matrix cells contain some values.

Now I need to find the value from intersection of height and weight (please see the attached excel file)

Attention: I need to use excel VBA code to do the trick. (Not Worksheet formula using for example INDEX and MATCH)

View 6 Replies View Related

Return Corresponding Value Of Found Match In Matrix

Mar 15, 2008

A have a table like this:

X 1 2
A 9 5
B 8 4

I would like to have a condition that when is equal to B and 2, return the corresponding paragraph in the matrix (in this case 4)

View 5 Replies View Related

Return Row Number Of Complex Matrix Values?

Oct 30, 2013

In the attached sample file, one can see the setup of my data. I'm trying to reference the row number of a certain cell in a matrix with many cells of the same value. I have different Headers of each row, which should make it possible to use INDEX, MATCH or VLOOKUP. but i don't seem to be able to get it right.

Description.I need to return the "class" och each of the capability elements, i.e. If the capability in "Soups" is marked ("X"), at class 3, i need the integer "3" to be shown here.

View 12 Replies View Related

Return Sum Of Array Within Matrix Based On Independent Value?

Mar 8, 2013

I'm trying to write a formula that will return the sum of multiple cells within a larger matrix, based on a number that is manually inputted into a separate table. As you can see in the top below, I have data that is grouped by both "Zone" and by individual "Stations". The Zones/Stations on the y-axis are "Start" points, while the Zones/Stations on the x-axis are "Finish" points. What I'd like to be able to do is input a Zone ID number into the bottom table (Shown as "1" below) and have the formula either return the greatest sum of "Finish" numbers. In this example, the greatest sum is 12, which is all numbers going from Station 1 and 2 in Zone 1 to Stations 1/2 in Zone 2 (3+2+4+3). In the bottom table, you'll see the "Greatest Finish" as "2", which is Zone 2. And the total from this sum is 12, as described previously. If there is a way to return either the "Total #" or "Greatest Finish" zone.

The actual matrix I am working with is much larger (150x150), and not all zones have the same amount of stations. I'm assuming this might complicate things a bit.

Finish
Zone 1
Zone 1
Zone 2

[Code]...

View 6 Replies View Related

Matrix To Return Risk Rating - Array Returns Opposite Result

Nov 20, 2013

I am looking up a risk matrix to return the risk rating... it is very simple, it works on one sheet, but on a different sheet (looking up different data, but same basic format) it returns exactly the opposite rating.

My formula is: =INDEX($G$6:$K$10,MATCH(I19,$G$5:$K$5,0),MATCH(J19,$F$6:$F$10,0))

Here is F4 to K10

I have data validation on I19 and J19 based on the cells below

Consequence

Priority
Very Low
Low
Medium
High
Very High

[Code] ..........

BUT... when I put Very Low and Very Low in the two cells (I19 and K19) I get Very High as the return.

View 1 Replies View Related

Creating Adjacent Matrix From Incidence Matrix

Aug 26, 2007

I have a data chart on 200 people with overlapping membership in 20+ groups, represeted as binary (1=member, 0=notmember), for example: ...

View 9 Replies View Related

Running A Macro For A Matrix Of Variables

Oct 6, 2006

I have a matrix that has variables in the rows and in the columns. Then I have a regular table. For each row in the table, I need to run a macro using the combinations in the matrix. I just don't know how to combine these two to make my macro run appropriately with the right combos for the right amount of time.

View 2 Replies View Related

Matrix - Identify Cell Content In Column For Row If Matrix Content True?

Mar 21, 2014

For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.

ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe

View 10 Replies View Related

Create Matrix From A Matrix

Apr 29, 2013

Below there are all the information needed to understand my problem.

M(1 to R,1 to C): matrix with R rows and C columns [element known]

M(i,j): elements of the matrix M in position i(row)-j(column) [element known]n

View 3 Replies View Related

How To Get Value From Matrix

Jan 26, 2013

I need to get the fee value, if I write "Name" and "Time".

Please read the simple example file named "Fee from matrix".

View 3 Replies View Related

Sum Matrix

Jun 15, 2009

Given one array variable (matrix n x n) M, I want to use the function sum, to sum along the rows.

I can use Application.WorksheeFunction.Sum(M)

but this will sum all the elements

and I would like smtg like this
Application.WorksheeFunction.Sum(M(1,)

Just sum the first line, or the i-th line.

View 9 Replies View Related

Correlation Matrix In VBA

Jan 16, 2006

I have code to create a correlation matrix (NxN, where N is the number of columns). This is done by selecting an area that is NxN, entering the function and range, then hitting ctrl +shift + enter (array formula).

However, I want to convert this to accept VBA arrays, rather than a data range, and give the output in form of an array as well.

VB:
Function CorrmatK(dataRange As Object) As Variant
On Error Goto 20
Dim r As Integer, n As Integer, rr As Integer, i As Integer, j As Integer, k As Integer, doit As Integer
Dim x() As Variant, mc() As Double, ss() As Double, m() As Double, ob As Object
r = dataRange.Rows.Count
n = dataRange.Columns.Count

[Code] ......

View 7 Replies View Related

From Matrix To Column!

Jan 29, 2009

I NEED some code to convert a matrix to a singel column and it should be reversed to!
see attachment!

example:

FROM:

A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3

TO:

D1
C1
B1
A1
D2
C2
B2
A2

View 4 Replies View Related

Matrix From Columns

Mar 29, 2009

I have to create a matrix from columns in this format:

A1 A2 1
A1 A3 4
A1 A4 6
A1 A8 4
A2 A5 2
A2 A8 3

with a lot of empty cells (here, for exemple A1,A6 & A1,A7 ) and (A1,A2) = (A2,A1)
I'd like to have :

_ A1 A2 A3 A4 A5 A6 A7 A8 .....
A1 0 1 4 6 0 0 0 4
A2 1 0 0 0 2 0 0 3
etc

The source data is in the attachment. Would it be possible to do it automatically?

View 3 Replies View Related

Matrix To Column..?

Nov 23, 2009

I have this code to take an area of data and perform the SUMXMY2 formula and output the results to another sheet. My problem is that the results are being outputted in a semi-matrix and i needed them in a column so i can them perform a sort. Is this possible and can anyone shed a light on the best way to do it?

View 2 Replies View Related

Inverse Of Matrix Using VBA?

Jan 23, 2014

I'm trying to do the inverse matrix of a matrix captured by an inputbox, but it doesn't work

This is the code:

Code:

option explicit
option base 1
sub matrix()
Dim M() As Double
Dim B() As Double
Dim Y() As Double
Dim columnas As Range

[code]......

the error is: Error 13 the types do not match

View 7 Replies View Related

Moving In A Matrix

May 23, 2007

I have a matrix A with 12 rows and 10 columns. My problem is if in the cell(i,j) there is data then the same data should appear in a similar matrix B, but in a cell which is 15 cells behind the cell(i,j).

That is it should start counting upwards from cell (i,j) in B and once it reaches the top of the matrix it should continue counting from the bottom of the immediate left column and go up. When it reaches the 15 cell from cell(i,j) in b, it should print there the value that was in cell(i,j) of A.

View 9 Replies View Related

Mileage Matrix

Mar 4, 2010

I am trying to figure out a better way to do my mileage for when I drive for work.

Currently I need to look at a sheet and see where I started and where I stopped and then I’ll see the distance.

Kinda look something like this.

Home
Work
School
Home
031Work
304School
140

What I would like to do is type in the “to” and “from” cell and have it automatically know the miles based on the chart above.
DateToFromDirectionTimeMiles3/4/10Home
Work

View 9 Replies View Related

Correlation Matrix

Jul 25, 2006

I wasn't able to attach the file because it was too big, but you can download it from here www.easygcc.com/correl.rar. On the sheet called " Correlation" there is matrix, I am baiscally trying to fill in the correlation formula into every cell so the matrix is filled out. The data for the correlation calculation comes from the sheet called "Tech Data". I have filled in the correlation formula into a few of the cells as an example, but I don't want to continue doing this manually but rather have a macro do it for me. Otherwise this will take for ever. My macro is also a part of the file, if you would liek to look at it and maybe fix what I already have.

View 4 Replies View Related

Matrix Lookup

Dec 4, 2006

I have a 5 x 5 matrix. When values are entered in 2 cell there needs to be a matrix lookup and the corresponding value needs to be entered into the 3 cell. Example :

- A B C D E
V 1 2 2 3 3
W 2 2 3 3 3
X 2 3 3 4 3
Y 3 3 4 4 5
Z 3 3 4 5 5

So if X and D are entered into the 2 cells the 3rd cell should show 4.

View 7 Replies View Related

Matrix Transformation

Feb 14, 2007

I would like to do the following transformation in excel:

imagine the following matrix:

1 2 3
4 5 6

I would like to transform this into:

1
4
2
5
3
6

This is a simple version of my matrix, in reality it is much bigger, and I do not want to copy paste each column by hand. Any ideas?

View 2 Replies View Related

Return To Sheet Before Ran Macro

Feb 16, 2014

The hyperlink can be activated from a number of difference sheets. Once the user reaches the instruciton sheet they need to be able to scroll around to read it fully but they will not need to enter any data or activate any cells. How can I get it so once they have read the instrucditons they can return to the sheet they started from?

View 6 Replies View Related

Macro To Return To Last Worksheet?

Jan 12, 2012

I have a workbook with multiple worksheets. Several have hyperlinks to one sheet called Calculator where users can perform certain calculations using a calculator I have made. I want to use a macro to take the user back to the sheet they were previously viewing before they came to Calculator. In other words if they got there from Sheet2 they go back to Sheet2, from Sheet3 to Sheet3 etc. Is there a way to do this?

View 3 Replies View Related

Macro To End With Return To Top Of Worksheet?

Nov 22, 2012

When I run my macro, it always ends leaving me having to scroll my worksheet back to the top manually. Is there a way to simplify this by adding a line of code to the end?

View 9 Replies View Related

Function / Macro To Return Above Row

Jul 25, 2013

New to creating macro's but looking for a macro or a function to get the below results. The first 2 columns are givens and looking to return the 3rd column. Is there any code that can do this?

Hierarchy
Path
Expected results
000000010HIER_NODE

[Code]....

View 4 Replies View Related

Macro: Return 1st X Characters

Nov 9, 2006

I am trying to extract some data based on two criteria. The first is that one row cell value is greater in than an agreed date/time, in my case this is 7:00am yesterday. The second is that the first 5 characters of a second cell, in the same row but several columns along, are not equal to 'Monit'. If both of these are met then the code will strip out several cells of data from that row.

I can do the first and last parts of this but the 5 characters check is proving a little more difficult in my limited VBA knowledge. The second cell is simple text format and I can't do a LEFT equation in a different cell to get those 5 characters (restricted workbook range so no room). how to go about running a check? I'm thinking it's probably quite simple but trying to search for an answer to it is tricky.

View 4 Replies View Related

How To Create A Risk Matrix

May 1, 2014

I'm trying to create a risk matrix like the attached example. I want to be able to enter a value and to have it to appear in the right quadrant.

View 2 Replies View Related

Extract Data From Matrix

Jun 22, 2008

I'm running a small messenger company and I've got a geographic area divided into five zones: 1,2,3E,3W,& 4

Deliveries within 1 zone will be charged for one zone.
Deliveries from Zone 1 to Zone 2 will be charged for 2 zones. Etc.

I've got a list of zones in my From column and also in my To column.

I need to write a formula that will give my customers the Zones Charged in a seperate cell.

I learned how to do something like this back in school, but don't even know what to call it anymore. Maybe it's some kind of comples IF function.

FREE Deliveries to anyone in Long Beach, CA to the winner!!!

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved