Free Microsoft Excel 2013 Quick Reference

Function not returning correctly please look

=IF($J7=0,IF($F7>0,"Lost",""))

I want the cell to be blank if neither one of the criteria is met. What do I do?

Thanks


Post your answer or comment

comments powered by Disqus
We have a file which is updated monthly containing 'Vllookups' combined with
'IF, ISNA' formulae. This has worked successfully for many months but
suddenly will not return correct vlaues for a couple of entries. Nothing has
changed within the spreadsheet, except the file name. Can anyone shed any
light on this?

Hi!

I'm currently working on a program in which some sort of recursive looping seems logical.
The relevant part of the code I've currently written looks like this:


	VB:
	
 hello() 
     
    Dim Max_stream As Integer 
    Dim j, jj As Integer 
    Dim P_sum, P_min As Double 
    Dim antal_in, antal_ut, rad_in, rad_ut, ut1, ut2 As Integer 
     
    Application.ScreenUpdating = False 
    Max_stream = WorksheetFunction.Max(Worksheets("Data").Range("B65536").End(xlUp).Value) 
     
    For j = 1 To Max_stream 
         
        P_min = 10 
        P_sum = rekursivloop(j, P_min) 
        Sheets("Listor").Select 
        Range("C" & j + 3).Select 
        ActiveCell = P_sum 
         
    Next j 
     
     
    Worksheets("Listor").Range("A1").Select 
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And the used function code:

	VB:
	
 rekursivloop(jj, P_sum) 
     
    Sheets("Listor").Select 
    P_sum = P_sum + Range("B" & jj + 3).Value 
     
     
    Sheets("Noder").Select 
    antal_in = 0 
    For Each c In Range(Cells(4, "D"), Cells(3 + Max_stream, "E")) 
        If c.Value = jj Then 
            antal_in = antal_in + 1 
            rad_in = c.Row 
        End If 
    Next 
     
     
    If antal_in = 1 Then 
         
        ut1 = Range("F" & rad_in).Value 
         
        If ut1  "" Then 
            jj = ut1 
            Sheets("Listor").Select 
            P_sum = rekursivloop(jj, P_sum) 
        End If 
         
    End If 
     
    rekursivloop = P_sum 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem with the code above is the last If, in which the function "rekursivloop" calls itself. I don't know if this type of formulation is correct, but for the cases the If condition is TRUE (ie ut1 ""), the function does not return any answer. Does anyone see an error in the logic or have some example for how to write similar recursive loops?

Thanks in advance.

(PS. I've deleted a lot of irrelevant code above to make it more readable, so if anything miss a declaration or so that's probably why DS.)

I'm working on a programme which needs to locate a number in a range of value, so obviously I decided to use the MATCH function.

My data looks like this (in the range A1:A8):
13.200
53.700
63.500
74.500
86.900
0
0
0

The number I'm attempting to match is 81.600 (in cell B1) so, following from logic, it should return me 4. However, all I get from B2=MATCH(B1;A1:A8) is 8, which is incorrect.

Since this is rather peculiar, I decided to put some other values into cell B1, to see what would happen. If this is 50.000, the function returns me 1, as expected. After trying a few other values, it seems that it works fine up until the point where the lookup value is equal to 74.500. Anything above that and the function just returns an 8.

Now I came up with a workaround, involving the functions C1=COUNTIF(A1:A8;">"&B1) and C2=COUNTIF(A1:A8;"="&0). Then C3=8-C1-C2. This returns the correct value MATCH should return and seems to always works fine (with a few extra functions) due to the nature of the data in the table, but is obviously not as nice a solution as a simple MATCH function, which should produce the correct result without so much hassle.

Is there anyone who has had a similar problem and could help me fix it so I can actually use MATCH? Thanks in advance, help would be very much appreciated.

in the following function the LEFT function is not resolving correctly. when i break the individual components out they resolve correctly, yet when i bring them together the LEFT returns a FALSE when it is in fact TRUE. any thoughts?

=IF(ISNUMBER($B33),MATCH($B33,AcctNum,0),IF((LEFT($C33="P")),MATCH($B33,AcctLine,0),MATCH($B33,Repor tLine,0)))

what the formula is trying to do is:
1. look at B33 and determine if it is an account number or an account name
2. if it is an account number, it is to look up a range of account numbers.
3. if it is an account name (ie not an account number), it is then to look into C33 to determine if the account name comes from a "Profit" or "Report"; it does this by determining if C33 begins with a "P" or not.
4. depending on whether it's a profit or a report item it then looks up the appropriate range.

i will include an IF to return a "Valid", "Invalid" result accordingly, yet the LEFT and MATCH is failing to return the reference. at the moment B33 is text and C33 is "Profit".

Hello,

I have a column (col A) with numbers (formatted as text) which are normally
in sequential series order from 1 to 3150 (but growing every day).
Sometimes, I have to sort the spreadsheet based on an another columns
criteria, and consequently the order from the first column A is not in
sequence anymore. When I run the =MAX(A2:A3151), it returns 3145, which as I
have just discovered, is missing in the series. Nonetheless, it should have
identified 3150 as the highest number, right?

Or does the fact that the 3145 value is missing from the selection set have
to do anything?

I then copied to column over, changed the format to General, and the MAX
returned the same result, 3145, no difference.

Just to see if there was a problem with Excel, I then ran the MAX function
on a new blank spreadsheet with some test values, and it worked. When that
worked, I inserted a new column (B) in the spreadsheet that is giving
trouble, and inserted some random values, ran the MAX function, and it works.
And they are not in sequence, nor are they contigous.

Any idea why this is happening?

I have just reinstalled my Office 2003 and now most fonts (all except the
standard Arial, Times New Roman, etc) are not displaying correctly. They
look like they are really low resolution, the edges are jagged instead of
smooth and the text is illegible if the size goes below 10 points.
These same fonts are displaying as they should in other software
applications that do not belong to the Office suite, so it has nothing to do
with screen resolution.
This problem only started after I reinstalled Office last week.
Can anybody help me please?

Hi Folks,

I've been building out a solution for my users which will allow them to
enter a date range, query a remote SQL DB via an ADO connection, and return
the results of to sheet1. I've made some great progress with the help of this
forum but I find myself perplexed as to why I do not return the correct
results when a user enters a single day as the date range for the query. For
example, If I enter 07/10/2006 for a starting date, and 07/11/2006 as an
ending date, the query returns the correct number of records for 07/10/2006.
I have verified this. But if the date range is 07/10/2006 and 07/10/2006 the
query returns an empty recordset. Below is the code I have so far...

' Define Input Date Parameters, Worksheet, and Integer object
Dim dtStartDate As String
Dim dtEndDate As String
Dim ws As Worksheet
Dim i As Integer
Dim iCount As Integer
Dim blnIsOk As Boolean

blnIsOk = False
Do Until blnIsOk
dtStartDate = InputBox("Enter a starting date for the report
range.", "Beginning Date Range", "XX/XX/XXXX")
If IsDate(dtStartDate) Then
GetDate = Format(CDate(dtStartDate), "mm/dd/yyyy")
blnIsOk = True
End If
Loop
blnIsOk = False
Do Until blnIsOk
dtEndDate = InputBox("Enter an end date for the report range.",
"Ending Date Range.", "XX/XX/XXXX")
If IsDate(dtEndDate) Then
GetDate = Format(CDate(dtEndDate), "mm/dd/yyyy")
blnIsOk = True
End If
Loop

' Create the connection object and query object
Dim dbConnection As Object
Dim strSQL As String
Set dbConnection = CreateObject("ADODB.Connection")

' Create the recordset object and initiate a new instance of it
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Sheet1.Range("A:D").ClearContents
Range("H6:H12").ClearContents
'Range("H7").ClearContents
'Range("H9").ClearContents

' Make the connection and run the query
dbConnection.Open "Driver={SQL Server}; Server=##;Database=##;Uid=##; Pwd=##;"
strSQL = "SELECT DISTINCT order_date,order_no,completed FROM oe_hdr WHERE
oe_hdr.order_date >= '" & dtStartDate & "' AND oe_hdr.order_date <= '" &
dtEndDate & "' ORDER BY order_no ASC"
'strSQL = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
BETWEEN '" & dtStartDate & "' AND '" & dtEndDate & "' ORDER BY order_no ASC"
dbConnection.Execute (strSQL)
rs.Open strSQL, dbConnection

For i = 0 To rs.Fields.Count - 1
Sheet1.Cells(1, i + 1).Value = rs.Fields(i).Name
Next
Sheet1.Range(Sheet1.Cells(1, 1), _
Sheet1.Cells(1, rs.Fields.Count)).Font.Bold = True
Sheet1.Range("A2").CopyFromRecordset rs

' Close the recordset object and release the memory space by setting the
object to nothing
rs.Close
Set rs = Nothing

' Close the connection object and release the memory space by setting the
object to nothing
dbConnection.Close
Set dbConnection = Nothing

As you can see I've tried using both >= and <=, and a BETWEEN in my query,
both with the same strange results.

Any advise is always greatly appreciated.

Thanks!

I am using Entourage and I am having problems using Vlookup with three letter codes which consist of both numbers and text. When I run a vlookup the value returned is bogus and does not match the information which it should be returning. I have tried copying and pasting special into other workbooks. I have tried converting everything to text and to general format. I even double checked using the iftext function. If I remove the three character codes and insert numbers instead and use the vlookup function to look up those number, the returned information is correct. It seems that excel/entourage is not recognizing three character codes. Can anyone please help??

Thanks!

Hello,

I have a macro that opens some xls file from Local Network and reads some info from each file same sheet, same cells.

My problem is this:

If I run step by step the macro works great. When I just run it it skips a few files.
It opens the file but its like the file is not opened correctly and all the variables that are supposed to be read from the file turn out empty.

The function looks like this:
full = full path + filename ("testpclocalsharefile.xls
pj = just the file name ("fiel.xls")
this_file = the excel where the macro is and where the data is copied to.


	VB:
	
 citire(full, pj, i) 
    Workbooks.Open full, 0, 1 
    Workbooks(pj).Activate 
     
     'only one sheet?
    If Workbooks(pj).Sheets.Count > 1 Then 
        Sheets(1).Activate 
    End If 
     
     'reading data
     'Application.Wait Now + TimeValue("00:00:02")
    trno = Range("C41").Value 
    pc = Range("D41").Value 
    db1 = Range("E41").Value 
    db2 = Range("F41").Value 
     
     'writing data
    Workbooks(this_file).Activate 
    Range("B" & i).Value = last_d 
    Range("C" & i).Value = trno 
    Range("D" & i).Value = pc 
    Range("E" & i).Value = db1 
    Range("F" & i).Value = db2 
     
    Workbooks(pj).Close 0 
     
    Workbooks(acest_fis).Activate 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Ty,

Daniel

LE:

I think I found out why it doesn't work:
The problem is in:


	VB:
	
If Workbooks(pj).Sheets.Count > 1 Then 
    Sheets(1).Activate 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It seams that if the workbook had older sheets that have been deleted they still can be found in VBAProject under Microsoft Excel Objects

Here I have some deleted sheets:
"Sheet1 (Apr 08)" - deleted
"Sheet2 (Feb 12)" - OK
"Sheet3 (Jan 12)" - OK
"Sheet4 (Sheet1)" - deleted
"Sheet5 (comp.pontaj) - deleted

Ho can I select the first sheet that is not deleted?
I think that sheets(1).Activate was trying to activate (Apr 08) a sheet that was deleted.

Excel 2007 Lookup function returns correct values except for the 1st 2 lookups in the list

I have a very basic lookup of US States.  I have added a non-state value in the 1st field but the list and the lookups are otherwise consistant.  For some reason I am not able to understand, the lookup for the 1st non-state value and the lookup for "Alabama" do not return the correct corresponding values.  Alabama actually return an "N/A" which certainly is not the lokup value in the list.  Any help would be appreciated.

Olympicbill

I'm trying to get Worksheet_Calculate to show changes in column D formula results in Column E, row by row. I've tried two options so far but both are not returning the date/time on the correct row:

Updates all rows with same latest update, not the specific update for that row:


	VB:
	
 Worksheet_Calculate()[/FONT][/SIZE] 
[SIZE=3][FONT=Arial]Dim Target As Range[/FONT][/SIZE] 
[SIZE=3][FONT=Arial]Set Target = Range("d6:d8")[/FONT][/SIZE] 
[SIZE=3][FONT=Arial]If Not Intersect(Target, Range("d6:d8")) Is Nothing Then[/FONT][/SIZE] 
[SIZE=3][FONT=Arial]Range("e6:e8" & Target.Row).Value = Now[/FONT][/SIZE] 
[SIZE=3][FONT=Arial]End If[/FONT][/SIZE] 
[FONT=Arial][SIZE=3]End Sub[/SIZE][/FONT] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Only updates first row of selection:


	VB:
	
 Worksheet_Calculate()[/FONT][/SIZE] 
[SIZE=3][FONT=Arial]Dim Target As Range[/FONT][/SIZE] 
[SIZE=3][FONT=Arial]Set Target = Range("d6:d8")[/FONT][/SIZE] 
[SIZE=3][FONT=Arial]If Not Intersect(Target, Range("d6:d8")) Is Nothing Then[/FONT][/SIZE] 
[SIZE=3][FONT=Arial]Range("E" & Target.Row).Value = Now[/FONT][/SIZE] 
[SIZE=3][FONT=Arial]End If[/FONT][/SIZE] 
[SIZE=3][FONT=Arial]End Sub[/FONT][/SIZE] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi All,

I'm new to writing VBA and am having trouble coding a Find Function to look for one reference and if it does not return a result look for a different one.

My data set is based on payment types received over a period of days and I need to set a find function that looks for the first reference "Card" then looks for the second "Cards". I have managed to do this but the issue I have is that if "Cards" is not available then the script should looks for the reference "Cash" as it is the next is the logical sequence. The reason for this is that the data is only found if the particular payment type is used in my sales system.

Once I have found the two points I need to select all rows in between them and set a Subtotal function, again this works fine when the data references are found.

The code if have writen so far (probably not the most efficient) is below:


	VB:
	
 handlecard() 
    Dim cardfirstRowno As Integer 
    Dim cardendRowno As Integer 
    Range("A50").Select 
     'Look for the first media type which is Card
    cardfirstRowno = Cells.Find(What:="Card", After:=ActiveCell, SearchOrder:=xlByRows, LookIn:=xlValues, _ 
    LookAt:=xlWhole, SearchDirection:=xlNext).Row 
    cardendRowno = Cells.Find(What:="Cards", After:=ActiveCell, SearchOrder:=xlByRows, LookIn:=xlValues, _ 
    LookAt:=xlWhole, SearchDirection:=-xlNext).Row 
     
     'This section is my attempt
    If cardendRowno = "" Then 
        cardendRowno = Cells.Find(What:="Cash", After:=ActiveCell, SearchOrder:=xlByRows, LookIn:=xlValues, _ 
        LookAt:=xlWhole, SearchDirection:=-xlNext).Row 
    End If 
     
     'Selects all rows within media type
    cardendRowno = cardendRowno - 2 
    cardfirstRowno = cardfirstRowno + 1 
    Range("A" & cardfirstRowno & ":A" & cardendRowno).EntireRow.Select 
     
     'Add sub totals to the media type
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7), _ 
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True 
    ActiveSheet.Outline.ShowLevels RowLevels:=2 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've added a sample of the data I am using which does not contain the reference "Cards" to assist if anyone has the time to help me.

Thanks in advance for any assistance or suggestions that can be made as I'm banging my head on this one,

Cheers

Phil

I am using a VLOOKUP function and it is returning the correct information for some of the fields, but not all. I am very confused because one that returns correct, looks exactly the same as one that does not return correct.
S070746647 OLM Pics (Does Not Return Correct)
S070748915 OLM Pics (Returns Correct)

S070746647 #N/A
S070748915 OLM Pics

Does this make sense.. seems like the same data?

Thanks,
Keri

Crossposted:
http://www.mrexcel.com/forum/showthr...96#post2965296

Hi, I have a somewhat simple function put together that has one purpose. It takes the arguments as provided inserts them as strings into an SQL statement, then executes that SQL on a recordset connection to an Access DB. I am using office2007 on windows XP.

The problem is the SQL statement never returns any records even though when I copy the SQL from the immediate window and paste it into Access directly and run the query it returns exactly 1 record as expected (After changing the % wildcard to * for Access). I have tried * and % as the wildcard in the Excel SQL code and neither one works. I have also tried replacing the like statement with = and it still returns nothing. Specifically, I am testing each change by checking the Rs.Recordcount value which never changes from -1.

The code is below. Thanks for any help!


	VB:
	
 
     'PNReturnType can be Int_PN or Cust_PN as string
     'Requires reference to Microsoft ActiveX Data Objects xx Library
    ConnectAndFillPN = 0 
    Dim Cn As ADODB.Connection, Rs As New ADODB.Recordset 
    Dim MyConn, sSQL As String 
     
    Dim Rw As Long, Col As Long, C As Long 
     
    MyConn = "C:NewVar Processor.accdb" 
     'Create query
    sSQL = "SELECT tbl_ship_part." & PNReturnType & " FROM tbl_ship_part WHERE (tbl_ship_part.Cust_Name = """ & Cust & """
AND tbl_ship_part.ShipTo = """ & Ship & """ AND tbl_ship_part.Cust_PN Like """ & CPN & "%"");" 
     'Create RecordSet
    Set Cn = New ADODB.Connection 
    With Cn 
        .Provider = "Microsoft.ACE.OLEDB.12.0" 
        .Open MyConn 
        Set Rs = .Execute(sSQL) 
    End With 
    Select Case Rs.RecordCount 
    Case Is > 1 
        MsgBox "Caution!  There are multiple " & PNReturnType & " Part Numbers detected for:" & vbCr _ 
        & Cust & vbCr _ 
        & Ship & vbCr _ 
        & CPN & vbCr _ 
        & "Using first found " & PNReturnType & " part number.", vbOKOnly 
        ConnectAndFillPN = 1 
    Case Is < 1 
        MsgBox "Result " & PNReturnType & " not found for:" & vbCr _ 
        & Cust & vbCr _ 
        & Ship & vbCr _ 
        & CPN 
        ConnectAndFillPN = 1 
        Exit Function 
    Case Else 
    End Select 
     'The following statement may not be correct, but i can't get the recordset to populate yet.
    ResultCell.Value2 = Rs!Int_PN 
    Set Location = Nothing 
    Set Cn = Nothing 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello

I'm writing a custom lookup function to deal with certain requirement. This is my use case.

I've an Excel Workbook with multiple sheets. First Sheet is the "Master Data". In one of the other sheets I've a value which I want to look up in "Master Data" sheet and get the row number. Simple.

I've written this function

Function foo(lookup As Range)
Dim foundCell As Range

With Sheets("Master Data").UsedRange
    Set foundCell = .Find(lookup.Value)
    If Not foundCell Is Nothing Then
        foo = foundCell.Address
    Else
        foo = " :( "
    End If
End With

End Function
But for some reason my function always returns , meaning it didn't find the value in the "Master Data" sheet. I'm pretty sure it is there.

Same Function when converted to SUB and with hard coded lookup value returns correctly.

Please help me out... what am I missing???

Hello,

I have a what I believe to be a consistant formula on lines S12-S18 for gathering the sumproducts for dates on column O. Only problem is that the sumproduct formula is not working on line S18 correctly. I keep looking it over and I can't figure out why it is not returning the correct amount for that cell.

Here is the formula I am using:

=SUMPRODUCT((TEXT($O$12:$O$500,"dddd")=R18)*1)

Please see attached line S18 for the error I am getting.

thanks for any help.
Nick

Hello,

I use MS Excel 2002 (10.6809.6804) SP3
and have some troubles:

when I use Search function (Ctrl + F) in WorkSheet, Excel could not find
same data. Randomly it may find same or not /with same conditions of search
on same data/.
I make example (Screenshot please look here:
http://img205.imageshack.us/my.php?image=555jv4.jpg ) in my WorkSheet - I
ask Excel to look position with No 68361310 (you may see it below in sheet)
and it could not find it. Why?

Thank you for yours reply’s

Hi,

I'm a technology teacher trying to help our physical education teachers be just a little more efficient with their fitness testing. The fitness grading requires looking at 2 variables before calculating scores:
- Student's ***
- Student's age

After looking at those variables, the sheet will then compare the student's performance to a set group of standards based on the *** and age. Our end goal is to have the teachers input student performance and be given a output grade (x-, x, x+) depending on the student performance. Currently, the spreadsheet we have created does not return the correct grades (i.e. a 9 year old girl runs a 6:00 mile and only gets a x, not an x+). That is just one of the errors. A sample function looks like this:

=+IF($C3="M",IF(E3>VLOOKUP(DATA!$D3,MALE,2),"x-",IF(DATA!E3<=VLOOKUP(DATA!$D3,MALE,3),"x+","x")),IF(DATA!E3>VLOOKUP(DATA!$D3,FEMALE,2),"x-",IF(DATA!$D3<=VLOOKUP(DATA!$D3,FEMALE,3),"x+","x")))

We're currently using Office 2003.

The Norms or Fitness Standards are on a separate sheet than the inputted data/class lists. I can email the file if you need more details. Thank you for any advice as grades are approaching!

I am trying to create a function that will return the word YES if a discount is applicable and the word NO if a discount is not applicable.

In the sheet Winter -- Trip A, F6 should say YES because G6 is greater than 4. However, it is returning with NO. This is very important as it determines whether a member is required to pay the discounted amount or full price.

Winter -- Trip A, F6:
I really need to sort this out. This excel file is going to take a lot of work because there is a lot of references to other
sheets. I'm determined to get it to work correctly though.

Any help is greatly appreciated!

Hi guys,

This is my first post here and I am pretty new to VBA and completely new to UDF. Today I built this function to be used in a code, but I don't understand why it is not working.
This function should return true if finds in the array the value (myvalue) and false otherwise.


	VB:
	
 
     
    For i = 1 To UBound(myarray) 
        If myvalue = myarray(i) Then 
            ismember = True 
        Else 
            ismember = False 
        End If 
    Next 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
then I called the function

	VB:
	
 Equity(3) 
 
Equity(0) = "Common Stock": Equity(1) = "Mutual Fund": Equity(2) = "Preference": Equity(3) = "Depositary Receipt" 
X=Ismember(Equity,typ) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
typ is a string that takes the value from a cell.
The function returns false also when it should be true.
I managed to achive my goal using match, but I would like to understand why it is not working so I can learn a little more about UDF

Thanks

Hello all,

this works fine

	VB:
	
 print_recap() 
    PrintOut Copies:=1 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

this returns a "sub or function not defined" on the printout line

	VB:
	
 print_All_recaps() 
    Set ws = Worksheets("Employees") 
    If UCase(InputBox("Enter y to print ALL the records"))  "Y" Then Exit Sub 
    For n = 2 To ws.Range("A65536").End(xlUp).Row 
        Cells(2, 2) = ws.Cells(n, 1) 
        PrintOut Copies:=1 
    Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If I remove the PrintOut line I do not get an error.

anyone have any ideas?

Thanks,

Ross

Hi
the following vlookup is not returning a value in the specified cell (C39). From the threads I have searched through it looks OK but not working


	VB:
	
) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Im sure from past experience I have missed a comma or something but buggered if I can find it!

Hi. I am trying to write a function that returns a single dimension array from inputed data. I want this to work for any data, i.e. a single cell, an array in vba or a range. I am using the "for each" staement. However, when I pass an array in the "for each" returns the same range, rather than the elements of the range. I've played around a bit, and the "for each" does what I want on an range if I am not passing the range to a function. Is it because I am passing the range into a function as a variant? Here is the code for the function.


	VB:
	
 
     
    Dim vHolder                         As Variant 
    Dim vArray                          As Variant 
    Dim lElementCount                   As Long 
     
    lElementCount = 0 
    For Each vHolder In dataToConvert 
        lElementCount = lElementCount + 1 
        Redim vArray(1 To lElementCount) 
        vArray(lElementCount) = vHolder 
    Next vHolder 
    CreateSingleDimensionArray = vArray 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am writing a function that returns a two element array. It works if array type is not explicitly stated, i.e. Variant.

But if I try to define the array type as an array of doubles I get: Complie error: Expected ")"

Here is test code that does work:

	VB:
	
Function ArrayTest(dNum1 As Double, dNum2 As Double) ' as Double(1) commented out
     
    ArrayTest = Array(dNum1, dNum2) 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here is the code that won't compile:

	VB:
	
Function ArrayTest(dNum1 As Double, dNum2 As Double) As Double(1) ' type not commented out
     
    ArrayTest = Array(dNum1, dNum2) 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am running Office 2000 in Win XP. Help About in the VBE says: Version 9969 VBA: Retail 6.4.8869 Forums3: 2.01.

THANKS MUCH for the help!


No luck finding an answer? You could always try Google.