# Function Back Variables: Function Give Two Or More Output Variables

Jul 27, 2006
Can a Function give two or more output variables. e.g.

Sub a()

x = 5

result = Y(x)

End Sub

Function Y (x As Integer) As Integer

Dim B

B = ... * x

Y = ... * B

this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?

I need this because function works with large matrix and I want to extract some values appeared in between.

View 2 Replies
ADVERTISEMENT
Jul 10, 2009

What I would like to do is output the first 4 characters of a cell into another cell. eg Cell A1 has "1234 Sydney". If I just use the manual formula "=left(A1,4)" it would give me the correct output "1234" in say cell B2, but as a formula. As I need to automate this in a macro, how could you code this in VBA to output the String "1234" into cell B2(ie the actual output rather than a formula)?

View 2 Replies
View Related
Feb 14, 2013

I'm having trouble with my logic again :/

AREA

Tp std

Tp

30

5

So I want the Tp in the 3rd column to show the result:

IF AREA is less than or equal to 20, Tp=Tp std / 2

IF AREA is between 20 and 40, Tp=(((AREA-20)/(40-20))*(Tp std/2))+(Tp std/2)

IF AREA is 40 or greater Tp = Tp std

But as one equation

I have just been struggling with the range part.

View 2 Replies
View Related
Feb 14, 2007

I have and Indirect function that works.... I need to modify it to include a cell address reference, but this requires the use of a Vlookup function to find the address ....

I have this formula: but it does not work

I'm not sure how to include the VLOOKUP function in my argument to

[/quote][/code]=INDIRECT(CELL("contents",B3)&"!&Address(VLOOKUP(B16,'1'!B:AE,match(RAY_ANALY!D2,'1'!B2:AE2,0),0)")

View 9 Replies
View Related
Dec 9, 2013

I need to figure out a vlookup function that searches for two variables and return an answer. such as the example attached;

vlookup -- 12253 & 605.34 (if these are matching then) 131001

View 5 Replies
View Related
Aug 30, 2006

I wrote my function as it is shown below:

Function MyFunction1(r, n, xrange, yrange)

If r <= n Then

For i = 1 To 5

MyFunction1 = MyFunction1 + xrange(i)

Next

Else

For i = 1 To 5

MyFunction1 = MyFunction1 + xrange(i) * yrange(i)

Next

End If

End Function

This is just a very basic idea of what I need to do, so it might not make sense why I am doing it, but I am just trying to test that the function works. Anyhow, the thing is that it works with something like this when I input it in a cell in a worksheet: =MyFunction1(RAND(),0.5,{5,6,7,8,9},{10,20,40,50,100})

However, if I try to define the range or set {5,6,7,8,9} with other cells, it does not work. so for example if I try the following:............

View 3 Replies
View Related
May 14, 2008

I am creating a worksheet and I have a formlua where the value is text. I can make it work standing alone but not with other variables. =IF(E11=S1,0)+(IF(E12=S1,0)+(IF(E13=S1,0.0025))+IF(E14=S1,"Call for Prciing"))

View 2 Replies
View Related
Aug 25, 2009

I am trying to make the "A4:A" portion of this line of code generic so it can work in multiple different files. I have defined a variable FirstGridRow that will take care of the '4' but I need to find a way to splice it all together. Essentially I am stuck trying to tack on the ":A". The first piece shows where I am coming from and the second is where I am trying to go to.

View 3 Replies
View Related
Mar 4, 2008

I am using VB to insert a function into a cell on an excell worksheet. The relative references work fine. For the absolute reference, I want to use a named range (LowTotal). If I simply put the name into the function, then I just get the name as text in the function. The second insert formula is were the named range is intended to go. Here is the code so far...

I need to know how to use the varriable name in the .formulaR1C1, so that VB knows it is a variable and not just text....

View 4 Replies
View Related
Jun 2, 2014

I am having a hard time getting my match functions to work with letters and numbers in the same range. I am also trying to figure out a way to have the ranges switch if there is an error. because the data is in multiple columns. It is hard to explain so I have attached a sample workbook.

ExampleFile.xlsm

View 13 Replies
View Related
Nov 21, 2005

I have a worksheet that has 6 columns of numerical data that all column

datasets are referenced together for each row. So for example the data

in row 5 in all colums is related.

Column C through F have 4 numbers that refer to the data in the first 2

columns. These are also ordered by row.

I need to look in column A for 0.00, and column B for 3.14 Both of

these must be in the same row or I need the closest value. When these

values are found in columns A & B, return the values from the same row

in Columns C through F.

View 13 Replies
View Related
Apr 10, 2012

I have quite a number of 4th degree polynomials and attempting to extract the variables from every equation. I used the formula below (from another thread);

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

but it only extracts the first variable.

4th degree polynomial function: f(x) = ax^4 + bx^3 + cx^2 + dx + e

Example of one of the equations: Y = -42.4276 + 319.989 X - 809.094 X^2 + 942.247 X ^3 - 397.587 X^4

I'll like to have the final result like below (Based on the Example equation above)

edcba-42.4276319.989-809.094942.247-397.587

View 4 Replies
View Related
May 22, 2008

I would like to use a varible Cell for the following forumula:

ActiveCell.Formula = "= ROUND(PIExpDat(""TimeEq('""&Cells(RowNdxG,4)&F$2,F$3,F$4,F$5,0,)/3600,1)"

I have underlined the variable. This is not working but is rather showing up as a string value Cells(RowNdxG, 4).

View 6 Replies
View Related
Aug 10, 2012

I have a query where the correct name is mixed up by each single click of a button and finally the mixed characters reverts back to correct name. I will give the examples below.

1) Beautiful --> next click of a button

2) utifulBea --> next click of a button

3) Beautiful --> the final output will be back to same name.

View 3 Replies
View Related
Nov 3, 2009

I have a question. Can this be done. Pass a couple of varibles back from a called sub routine?

The calling sub calls the called sub does which has code plus a couple of varibles needing to come back.

Scope: The called sub is a series of case statements with code and sets varibles which are needed back in the calling sub.

View 6 Replies
View Related
May 22, 2013

I have a macro running in a workbook that gathers some data (a date, a string and a few arrays). Towards the end of this macro, I need it to open another workbook and run a macro that sits in this other workbook, using the data from the first workbook. I then need it to return some results (several integers) back to the first macro to be pasted into the first workbook.

I gather that I can't use 'Call' as the second macro is in another workbook.

I've found that I can use Application.Run but I'm unsure how to carry variables back and forth using this.

How to move the variables between macros / workbooks using the Application.Run option, or maybe another way of doing things?

View 1 Replies
View Related
Mar 20, 2008

For what I'd like to do, the attached workbook actually represents 3 worksheet. one input sheet and two output sheets (output1 and output2)

I plan to use command button " CommandButton1" on input sheet to start the calculations

Here's what I want to do after pressing the " CommandButton1" :

1. start with case 1 and find the value of Input A to Input J from input table using Vlookup and paste those values to respective palceholder for inputs in blue colour area above the input table for each input.

2.for empty cell in the input table paste 0 (zero)

3. after this will give results for case 1 inputs on sheets "output1" and "output2" on basis of formulas used for each output

4.then copy the results for case 1 inputs from sheet "output1" and "output2" and paste them on "input sheet " in Output table (its below Input Table) next to case1

5.Do this for all cases (in actual workbook there will be more than 100 cases)

View 9 Replies
View Related
Feb 5, 2009

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View 2 Replies
View Related
Feb 6, 2009

I'm looking to work out an IF statement based on a series of dates entered to give 1 of 3 possable outcomes. Where:

A1: todays date (exmaple =NOW())

B1: due date (exmaple 10/02/09)

C1: completion date (example 12/02/09)

D1: status (overdue, outstanding or completed)

D1: =IF(A1>B1,"overdue",IF(A1<B1,"outstanding",IF(C1<=>A1 & B1,"completed")))

I know the last part is totally wrong (symbols arranged in that manner), but to clarify i would like the date entered in C1 to override the other statements in the fomula to make it read "completed". If no date is entered in C1 then the formula will return either "overdue" or "outstanding" depending on the other dates in A1 and B1.

View 2 Replies
View Related
May 11, 2014

I have two workbook, test1 (master file) and the other one is test_10_05_2014 with 100 some of them being updated daily. The last portion of the file "test_10_05_2014" is the date and we have different file on daily basis.

using Indirect function in vlookup should give corresponding value for the particular name. Formula used is giving error,

[VLOOKUP(A2,Indirect(" ' "&E1&" ' !$A$2:$X$1000),6,FALSE)]

View 2 Replies
View Related
Apr 27, 2006

I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...

Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset)

Dim fd As FileDialog

Dim xl As New Excel.Application

Dim wb As Excel.Workbook

Dim ws As Worksheet

Dim iFilePicked As Integer

Dim strFilePath As String

fd.Filters.clear

fd.Filters.Add "Excel files", "*.xls"

fd.ButtonName = "Select"

iFilePicked = fd.Show

If iFilePicked = -1 Then

strFilePath = fd.SelectedItems(1)

Else ..................

View 3 Replies
View Related
Dec 3, 2009

I want the A4 cell contains the calculation of B4 (but the number gained from the funtion row and if the B1 cell contains the number 10 the K(B1)=K10

[A4]=B(row())*K(B1)

View 4 Replies
View Related
Feb 11, 2013

I am using this formula to output data into a cell:

=MID(A1,4,2)

The cell value is 50. I am wanting it to appear as 5.0 instead. Can I do this by adding to the formula above or will I need to utilize another cell to accomplish this?

View 2 Replies
View Related
Jul 24, 2009

I am having trouble using the MATCH function in my code. I am looking for a match in one worksheet from a cell in another. I have defined these cells and ranges as variables. However, it doesn't seem to be working with my application. I would like to find the row of the matching cell and use the output of the MATCH function to determine the next step in my code with an IF function. Here is my code. (NOTE: this is my edited code, I removed irrelevant pieces. The major problem is the MATCH function.)

View 4 Replies
View Related
Jul 28, 2009

I am having some trouble with the "mid()" function. I have it saved as a variable which pulls 6 digits from another variable. I was trying to paste from the mid variable into my excel worksheet but it keeps on telling me that there is an object required error.

The goal is to input the "mid()" output in my code into a cell in the spreadsheet.

Here is some sample

View 4 Replies
View Related
Feb 16, 2006

is there a formula that would have as it's output the cell address of the

same cell where the formula has been entered.

e.g. In cell A10 I input a formula whose output is the cell reference A10.

View 14 Replies
View Related
Oct 2, 2012

I've in cell A1 an entry like this: 123*456*7890

=SUBSTITUTE(A1) in cell 'A2' gives me: 1234567890

=ISTEXT(A2) in cell 'A3' gives me: TRUE

BUT, =A3+1 gives me: 1234567891

Hows that happening? Substitute function gives me the output which is a TEXT, and how is it that when I add 1 to it, I get an answer? Shouldn't I get a #VALUE! error instead?

View 2 Replies
View Related
Oct 8, 2009

I would like to know if it is possible for the output from a match function to be correlated to a location? The output I receive is just a number. I would like to use this number as the row, but I would like to hyperlink it into a formula so that I can create a macro for this process.

In essence I would like for a number in one cell to become a location in another cell.

View 9 Replies
View Related
May 10, 2006

I want to pass an array to a function, and am unclear how to do it. For example, the following declaration does not work:

Public Function xyz(a() as Variant) as Double

There is an additional requirement that I would like the output also to be a range of the same dimensions, typically one column and 100 rows. Is this possible?

A further twist is that the function I am writing calculates two values for every element in the input array and I need the output of them both.

An inelegant solution would be to write two separate functions, but I was wondering if it is possible to get two array outputs from a single function.

View 7 Replies
View Related
Mar 5, 2014

Here's my formula:

=AND([@[Tenure (Yrs)]]>=5,[@[2016 Sales]]>=150000)

(Where should I enter high and low? Do I need an if formula nested into it?)

I'm trying to get it to read if the customer reads both criteria which is they've been there 5 years and over 150000 sales =

High if not Low.

View 4 Replies
View Related