Ann_BBO
08-03-2007, 03:53 AM
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
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