Set the default file path and Extract the last value in nth occurence

[Ann_BBO]

Hi all
How to modify below vba to show the default file path (such as C:\Lab\Staff File) when we click the "Input File" button.
Private Sub cmdInput_Click()
Dim sFiles As Variant
Dim Title As String
Dim Finfo As String

' Setup lists of file filters
Finfo = "Comma separated Files (*.csv),*.csv," & "Excel Files (*.xls),*.xls," & "All Files (*.*),*.*"

' Set the dialog box caption
Title = "select a File to Import"
sFiles = Application.GetOpenFilename(Finfo, , Title, MultiSelect:=True)

End Sub

The next question is the extract value from the other workbook.
In before, i can extract the average value from the 2nd or nth Occurenece according this vba


'Loop through names in column A
For Each cel In Range(.Cells(21, 1), .Cells(Rows.Count, 1).End(xlUp))
If Not cel = "" Then
Set c = Source.Range("A1")
Set Rng = Nothing
Do While c.Row < Source.Range("A" & Source.Rows.Count).End(xlUp).Row
If c = cel Then
If Rng Is Nothing Then Set Rng = c.Offset(1)
Set Rng = Union(Rng, Range(c.Offset(1), c.Offset(1).End(xlDown)))
Set c = c.Offset(1).End(xlDown).Offset(1)
Else
Set c = c.Offset(1)
End If

Loop
cel.Offset(, 1) = Application.Average(Rng.Offset(, 7)) / 1000
cel.Offset(, 2) = Application.Average(Rng.Offset(, 8))
cel.Offset(, 4) = Application.Average(Rng.Offset(, 9))
End If
Next

Now, i change to get the last value in the range, so i change the vba from
cel.Offset(, 1) = Application.Average(Rng.Offset(, 7)) / 1000
to
cel.Offset(, 1) = Application.Average(Rng.Offset(, 7).End(xlDown)) / 1000
However, i only get the last value in the range in 1st occurence. I don't the problem occur in where.

Thanks

[Bob Phillips]

Dim sFiles As Variant
Dim Title As String
Dim Finfo As String
' Setup lists of file filters
Finfo = "Comma separated Files (*.csv),*.csv," & "Excel Files (*.xls),*.xls," & "All Files (*.*),*.*"
' Set the dialog box caption
Title = "select a File to Import"
ChDrive "C:\Lab\Staff File"
ChDir "C:\Lab\Staff File"
sFiles = Application.GetOpenFilename(Finfo, , Title, MultiSelect:=True)

[Ann_BBO]

Thanks xld

i don't know why the problem occur in the second question. Is it my wrong setting for getting last value in the nth occurenece. Anybody know that this problem since i had try to solve this problem for long time.

Thanks

[Bob Phillips]

By moving down from your targetted range, you are changing to a single cell.

Try



cel.Offset(, 1) = Application.Average(Range(rng.Offset(, 7).Cells(1, 1), rng.Offset(, 7).End(xlDown))) / 100

[Ann_BBO]

It is not work.
According your suggestion, it only calculate the average value between the first value and last value in the 1st occurence only and not calculate any number in 2nd occurence.
I only want to extract the last value (H17) in the 1st occurence (H4:H17), last value (H121) in 2nd occurence(H100:H121),... then calculate the average value with H17 and H121

Anyway thanks to you