Calculation by VBA

[steve_so]

Question: How to calculate the means value of the Range(B4:B10) and transfer to the range in my excel file~~
http://img519.imageshack.us/my.php?image=55760948lb8.jpg
Also my vba command are show below:
Private Sub cmdAnalysis_Click()
Dim wb As Workbook
Set wb = ThisWorkbook
Workbooks.Open Filename:=Me.ListBox1.List(Me.ListBox1.ListIndex)
If ListBox2.Value = ("115 Vac 60 Hz") Then
Range("B4:B10" / 7).Select
Selection.Copy
wb.Activate
Range("c4").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
End Sub
http://img519.imageshack.us/my.php?image=21184488jr3.jpg

Of course the Range("B4:B10" / 7) is impossible in vba. Therefore, i want to get this value, then transfer to the range in my excel file~~

P.s. i am the beginner of the VBA~~That' why i have many questions~~Sorry

[Bob Phillips]

Private Sub cmdAnalysis_Click()
Dim wb As Workbook
Set wb = ThisWorkbook
Workbooks.Open Filename:=Me.ListBox1.List(Me.ListBox1.ListIndex)
If ListBox2.Value = ("115 Vac 60 Hz") Then
Range("C4").Value = Application.Sum(Range("B4:B10")) / 7
End If
End Sub

[steve_so]

Thank you xld~~
Also, i want to know that the result must be the integer or not??
If i want the result should be contain 3 decimal places~~ What can i do it in VBA setting~~

[Bob Phillips]

Actually, I'm dumb, should have used



Range("C4").Value = Application.Average(Range("B4:B10"))


What does the other question mean?

[steve_so]

OK~~Thank first~
I means that my final value by the vba calculation in the range is "9". But actutally, the correct value is "8.879" (3 decimal places). I had checked that the formula cells in excel which is either "General" or "Number". Why have this observation. Also, have any suggestion to solve its problem. Thank You~~

P.S. You do not dumb, i think you are very smart as well.

[Bob Phillips]

ADd



Range("C4").NumberFormat = "#,##0.000"


and see if that changes it

[steve_so]

Unfortunately, the result is no change~~~

If u don't mind, i can send my file to you to check it~~

http://download3-5.files-upload.com/2007-07/11/12/MycsvMarco.zip

ThankS:friends:

[Bob Phillips]

Says page is unavailable. Why not just post it here?

[steve_so]

OK~~I post here
Private Sub cmdOpen_Click()
Dim sFiles As Variant
Dim sFileShort As String
Dim i As Long
Dim Title As String
Dim Finfo As String
Dim Msg As String

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

' Set the dialog box caption
Title = "select a File to Import"

sFiles = Application.GetOpenFilename(Finfo, , Title, MultiSelect:=True)

' Exit if dialog box canceled
If Not IsArray(sFiles) Then
MsgBox "No file was selected."
Exit Sub
End If
'Display full path and name of the files
For i = LBound(sFiles) To UBound(sFiles)
sFileShort = Right(sFiles(i), Len(sFiles(i)))
With Me.ListBox1
.AddItem sFileShort
.List(.ListCount - 1, 1) = sFiles(i)
End With
Msg = Msg & sFiles(i) & vbCrLf
Next i
MsgBox "You selected:" & vbCrLf & Msg

End Sub
Private Sub cmdDelete_Click()
Me.ListBox1.RemoveItem (Me.ListBox1.ListIndex)
End Sub
Private Sub cmdEnd_Click()
Unload UserForm1
MsgBox "Bye!!! End of the Adaptor Msg Log User Interface"
End
End Sub
Private Sub cmdAnalysis_Click()
Dim wb As Workbook
Set wb = ThisWorkbook
Dim Idc As Integer
Dim Vdc As Integer
Workbooks.Open Filename:=Me.ListBox1.List(Me.ListBox1.ListIndex)
' (i) Case for "115 Vac 60Hz"
If ListBox2.Value = ("115 Vac 60 Hz") Then
' Extract the means DC Current(Idc) from this file
Idc = Application.Average(Range("H4:H28"))
wb.Activate
Sheet1.Range("b4").NumberFormat = "#,##0.0000"
Sheet1.Range("b4").Value = Idc
' Extract the means DC voltage (Vdc) from this file
Workbooks.Open Filename:=Me.ListBox1.List(Me.ListBox1.ListIndex)
Vdc = Application.Average(Range("I4:I28"))
wb.Activate
Sheet1.Range("C4").NumberFormat = "#,##0.0000"
Sheet1.Range("C4").Value = Vdc
End If
End Sub

[steve_so]

Have other question is:
' Calculate the means Efficiency (n) in "115 Vac 60Hz/400 mAdc" Measurement
n = Range("D9").Value / Range("E9").Value
Sheet1.Range("F9") = n
In the above case , i want the n result =n*100~~What action can be take in VBA calculation~~

Thx~

[steve_so]

Oh.....Too Big Problem for me~~
xld~~ Plz help me~~

Since my supervisor said that all extract data from the workbooks are standard form. It means that All data either in row and column are the same.
Such as this
http://img174.imageshack.us/my.php?image=49356301tw9.jpg
All workbooks should be the same in row and column
However, i find that the data in row which is different in the different workbooks~~Some part may be more or less~~
http://img403.imageshack.us/my.php?image=13398630qj7.jpg
Therefore, i can't use the above VBA way to analysis..~~ It means that it should be write the many VBA command to Search the region in different work book to do it.
Oh my god~~Plz help me~~

[steve_so]

i have the idea about this but i don't know how to write in VBA~
http://img166.imageshack.us/my.php?image=untitledvb6.jpg
Hope can help u

[Bob Phillips]

Post the workbook here, not just the code, not just an image, but the workbook as you did on the other site.

[steve_so]

6198

[steve_so]

6199

Open "Marco.ver.1.4.1.xls" to get the data from the Library.xls

[Ann_BBO]

Steve~
U try to change "Dim Idc as Integer" to "Dim Idc as Single"~~
It may solve the decimal places problem~~