Hi
Hopefully someone can help me with this problem.
I have a 3 page workbook (report,data1,data2).
data1 and data2 are loaded as External Data from 2 csv files and are updated at 10 minute intervals. Both sheets have the same layout -:
Col A = date (i.e. 12-DEC-04) (as string)
Col B = reference (i.e. ABC1234) (as string)
Col C = value1 (as integer)
Col D = value2 (as integer)
Col E = value3 (as integer)
The report page has 3 columns.
Col A = date (as Date)
Col B = reference (as string)
Cols C - F = various values
Col G = valtotal (as integer)
I've written a function that takes in 4 parameters (ref,date,page,reqcol).
"page" sets which of the sheets is used for the info (data1 or data2).
"Reqcol" sets which column from the data sheet is reqd.
(e.g. Cell G4 on the report might be =vallookup(G1,A1,"data2","E"))
This has to loop over the data checking all references, and if it finds a match then check the date. If both match, it returns the value in column (reqcol).
The data size is not known until the data is refreshed (cell A1 contains the number of rows).
A variable, 'lastrow', is used as the number of rows and a number of loops is performed.
The function appears to work sometimes, but other times won't work - on the same data!
Also, I need to have an easy way to refresh the values returned by the funcion on the report page without having to hit 'Enter' on each cell.
Here is the function as it stands......
[red]
Public Function vallookup(ref As String, dateref As Date, page As String, reqcol As String)
'Activate correct page sheet
Worksheets(page).Activate
'Get size of dataset
lastrow = Worksheets(page).Cells(2, "A").Value
'Loop data
For a = 1 To lastrow
If (UCase(ref) = UCase(Worksheets(page).Cells(a, "B").Value)) Then
If (dateref = DateValue(Worksheets(page).Cells(a, "A").Value)) Then
vallookup = Worksheets(page).Cells(a, reqcol).Value
End If
End If
Next a
End Function
Sub Refresh()
'Attempt at a Refresh function
Worksheets("report").Calculate
End Sub
[/red]
Hopefully someone can help me with this problem.
I have a 3 page workbook (report,data1,data2).
data1 and data2 are loaded as External Data from 2 csv files and are updated at 10 minute intervals. Both sheets have the same layout -:
Col A = date (i.e. 12-DEC-04) (as string)
Col B = reference (i.e. ABC1234) (as string)
Col C = value1 (as integer)
Col D = value2 (as integer)
Col E = value3 (as integer)
The report page has 3 columns.
Col A = date (as Date)
Col B = reference (as string)
Cols C - F = various values
Col G = valtotal (as integer)
I've written a function that takes in 4 parameters (ref,date,page,reqcol).
"page" sets which of the sheets is used for the info (data1 or data2).
"Reqcol" sets which column from the data sheet is reqd.
(e.g. Cell G4 on the report might be =vallookup(G1,A1,"data2","E"))
This has to loop over the data checking all references, and if it finds a match then check the date. If both match, it returns the value in column (reqcol).
The data size is not known until the data is refreshed (cell A1 contains the number of rows).
A variable, 'lastrow', is used as the number of rows and a number of loops is performed.
The function appears to work sometimes, but other times won't work - on the same data!
Also, I need to have an easy way to refresh the values returned by the funcion on the report page without having to hit 'Enter' on each cell.
Here is the function as it stands......
[red]
Public Function vallookup(ref As String, dateref As Date, page As String, reqcol As String)
'Activate correct page sheet
Worksheets(page).Activate
'Get size of dataset
lastrow = Worksheets(page).Cells(2, "A").Value
'Loop data
For a = 1 To lastrow
If (UCase(ref) = UCase(Worksheets(page).Cells(a, "B").Value)) Then
If (dateref = DateValue(Worksheets(page).Cells(a, "A").Value)) Then
vallookup = Worksheets(page).Cells(a, reqcol).Value
End If
End If
Next a
End Function
Sub Refresh()
'Attempt at a Refresh function
Worksheets("report").Calculate
End Sub
[/red]