Finding & using row number to calculate averages

Hi,

First Post!! Working with Excel 2007

I'm quite new to this whole VBA thing, and I'm trying to write a code that will return the average wind speed between two set dates. The dates are in "dd/mm/yyyy hh:mm" format. The dates have a 10 minute difference, so there is a different number of cells for different months (obviously).
The dates are in Row A, of worksheet called "wind speed". I am making a simple date selection tool on worksheet called "yeild".
Column C in "wind speed" has as you might think wind speed data. I thought I could get all the values into a temporary sheet, sum them and then average them, but the problem I'm having is "collecting" the values. I can create the "start date" in the temporary sheet, and all the date points (10 minute increments) up to the "end date", but can't get the corresponding values from column C in "wind speed"

What I'm trying to do is to get the user to input two dates in different cells in the worksheet "yeild", and for the macro to work out the average of the wind speed points between those two dates.
Example of what I'm trying to do is:

user inputs "start date" in "yeild", and also "end date" in "yeild". A macro is then run that finds the row for the "start date" in "wind speed" sheet, sums up all the wind speed values up until the "end date" row in "wind speed" and returns the average of all those values.

It really doesn't sound that hard, but as I'm new to this, I just can't get it done properly!! help???

This is what I've got so far (it does not work... so don't have a go at me saying it's wrong!)

Sub Main()

Dim points As Integer
Dim i As Long
Dim sheet_name As Variant
Dim col_val As Variant
Dim row_num As String


' Set Date format
start_TimeStamp = Range("start_date")
End_timestamp = Range("end_date")
start_TimeStamp = Format(start_TimeStamp, "dd/mm/yyyy  hh:mm")
End_timestamp = Format(End_timestamp, "dd/mm/yyyy  hh:mm")


sheet_name = Range("list")
points = Range("points")
row_num = ActiveSheet(sheet_name).Range("A:A").Find(start_TimeStamp).Row

'col_val = ActiveWorkbook.Worksheets(sheet_name).Value

               'Remove any old sheet called "TEMP" and create a new one
                strDestSheet = "TEMP"
                Application.DisplayAlerts = False
                On Error Resume Next
                Sheets(strDestSheet).Delete
                Application.DisplayAlerts = True
                On Error GoTo 0
                Sheets.Add After:=Worksheets(Worksheets.Count)
                ActiveSheet.Name = strDestSheet
                
For i = 1 To points

        'Sheets(sheet_name).Range(i, 1).Value
        
 Range("A" & i).Select
   If i = 1 Then
   Range("A" & i).Select
   Cells(i, 1) = Range("start_date")
     ActiveCell.NumberFormat = "dd/mm/yyyy hh:mm"
     Cells(i, 2) = ActiveWorkbook.Sheets(sheet_name).Range((i + 20), 3).Select
     'col_val =
       'Cells(i, 2) = col_val
    
   End If

  If i > 1 Then
  Range("A" & i).Select
  Cells(i, 1) = Cells(i - 1, 1) + TimeValue("00:10")
  ActiveCell.NumberFormat = "dd/mm/yyyy hh:mm"
  End If
Next i

End Sub

IGMM_hlp_1.jpg

THANK YOU!!!

Originally Posted by martindwilson

why not just use a formula?

so how do I put together a formula to calculate (user input is in "master worksheet"):
user inputs date range (fields names 'start date' and 'end date')
user inputs general category (field name 'list') -> raw data is held in different named worksheets
user inputs the parameter to calculate -> each worksheet has a number of parameters and data associated with it, column A has dates, B an index, and C onwards different parameters.
average is calculated.

might help if you see the image attached in first post?