Reference data from another worksheet, conditional on two variables 1

[AnnaWnz]

Hi all

I am trying to get a function to return a value for a cell based on two variables...

In basic language, the cell that needs the value is in column "L". In the following example I refer to Row 2.

Find the value of cell A2 in the data range (which is in another worksheet), and then find the value of cell C2 in the data range, where the value of A2 has already been found. The value of L2 is then the value of column H in the data range, from the row on which both the value of cells A2 and C2 were found.

I had this running when using a sequential file, using

Do While (Not FoundFlag) or EOF(1)
Input #1, Code1, BusinessUnit, ForecastType, Mth, Cust, Code2, Code3, Qty, MthRef
If (Code2 = ItemCode) Then
If (Mth = MonthRef) Then
FGetQty = Qty
FoundFlag = True
End If
End If
Loop

Where ItemCode is the value held in A2, and Mth is the value held in C2.

I can't use the sequential file option as the informaiton needs to be available to the spreadsheet when the sequential file is no longer available (ie on and off a network), so have imported the data into a worksheet.

Thanks in advance for any help!

 

[RobBroekhuis]

Sounds like it's not too burdensome of a data table, in which case a simple brute-force approach like this will do just fine:

(assume that you have a range "data" defined which holds your table)

for i=1 to range("data&quot.rows.count
if range("data&quot.cells(i,1)=ItemCode and _ 'search in column A
range("data&quot.cells(i,3)=MonthRef Then 'and in column C
range("L2&quot = range("data&quot.cells(i,8) 'value in column H
exit for
End If
next i

Rob

 

[AnnaWnz]

Hi Rob

Have tried to work in your advice and have got this:

Function FGetQty(ItemCode As String, Month As Range, DataType As String) As Double

Application.Volatile

' Variables passed in with function:
' ItemCode - cell containing the Item Code for which the informtion relates to
' Month - Cell containing month counter (1 to 12)
' DataType - to determine which sheet the input data is on

Dim MonthRef As Integer 'the reference to the month in the spreadsheet

'based on the month need to allocate the MonthRef, being the actual Month. Month is the sequence in the financial year

Select Case Month
Case 1 To 7
MonthRef = Month + 5
Case 8 To 12
MonthRef = Month - 7
End Select

Select Case UCase(Trim(DataType))
Case "ACTUALS"
Sheet = "InputActuals"
End Select

Range("A2&quot.Select
Set Data = Worksheets(Sheet).CurrentRegion

For i = 1 To Range(Data).Rows.Count

If (Range(Data).Cells(i, 6) = ItemCode) and (Range(Data).Cells(i, 4) = MonthRef) Then
' ie trying to find a row where the value in column F = the value of ItemCode
' ie trying to find a row where the value in column D = the value of MonthRef
FGetQty = Range(Data).Cells(i, 8) 'value in column H - this should feed back into the cell in which the formula is
Exit For
End If
Next i

End Function


Brings up a #VALUE! error

Say I am trying to get the value for L2 (on the main sheet - ie not where the data range is), then the formula in the cell is =FGetQty(A2, C2, L1), therefore passing those values into the function.

Cheers

Anna

 

[RobBroekhuis]

I'm not sure if this is the main problem - but you should use:

For i = 1 To Data.Rows.Count

instead of

For i = 1 To Range(Data).Rows.Count

and similarly replace all the other references to range(data). My original code assumed a named range called "data" had been defined on the spreadsheet. You assign the proper range to a range variable, so you need the syntax of the first line above.
Rob

 

[AnnaWnz]

HI Rob

Have tried that and still get the #VALUE! error.

Is there an easy way to feed the information from the worksheet into variables like you do with a sequential file?

Cheers

Anna

 

[RobBroekhuis]

Where do you get the error? It's likely an easy problem to resolve, once pinpointed. You can assign any worksheet cell value to a variable, but it's generally easier to just work directly with the cells.
Rob

 

[AnnaWnz]

I get the error in the cell in which the formula is.

Ie using the above example, the formula is entered into Cell L2 on the worksheet - that is where the #VALUE! shows.

I wondered if I wasn't using correct variable declarations - the information is the table is imported in from a CSV file with cell format General. The cells referenced in the formula also have a cell format of General. Could there be an issue there? Do I need to declare Data as anything?

Cheers

 

[RobBroekhuis]

If Excel VBA doesn't throw you an error, my guess is that you get the #VALUE! because your function never assigns a return value, in other words, the IF condition never becomes true. You could test this by putting the statement

FGetQty = -1

before the for statement. If your cell value becomes -1, you know that for some reason your values were never found. Stepping through with the debugger will probably tell you why.
Rob

 

[AnnaWnz]

Finally got it to work. There was an issue with the way I was referencing the other worksheet, in defining my range Data. Your first post works perfectly!

Thanks

Anna