Financial calculation using combobox and label in vba,VBA Teacher Sourav,Kolkata 09748184075

Private Sub ComboBox1_Change()

End Sub

Private Sub CommandButton1_Click()
Dim pos As Integer
Dim result1 As Double
Dim firstval1 As Double
Dim combodata As String
Dim count1 As Integer
Dim covar1 As Double

count1 = 0

If CStr(Me.ComboBox1.Text) <> CStr(Me.ComboBox2.Text) Then
combodata = Me.ComboBox1.Text
'MsgBox (combodata)

Sheets("Data").Select

'Sheets("Data").Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=MATCH(""" & combodata & """,listrange,0)"
pos = ActiveCell.Value
ActiveCell.Value = ""
Sheets("Data").Select
Range("A1").Select

For i = 1 To pos
ActiveCell.Offset(1, 0).Select
Next i
'MsgBox (ActiveCell.Value)
ActiveCell.Offset(0, 2).Select
firstval1 = CDbl(ActiveCell.Value)

While ActiveCell.Value <> ""

result1 = result1 + ActiveCell.Value
count1 = count1 + 1

ActiveCell.Offset(0, 1).Select
Wend
covar1 = (result1 - firstval1) / count1
MsgBox ("count1 is " & count1)
MsgBox ("firstval1 is " & firstval1)
MsgBox ("sum is " & result1)
MsgBox (covar1)
'this is for combobox2


Dim pos2 As Integer
Dim result2 As Double
Dim firstval2 As Double
Dim combodata2 As String
Dim count2 As Integer
Dim covar2 As Double

count2 = 0


combodata2 = Me.ComboBox2.Text
'MsgBox (combodata)

Sheets("Data").Select

'Sheets("Data").Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=MATCH(""" & combodata2 & """,listrange,0)"
pos2 = ActiveCell.Value
ActiveCell.Value = ""
Sheets("Data").Select
Range("A1").Select

For i = 1 To pos2
ActiveCell.Offset(1, 0).Select
Next i
'MsgBox (ActiveCell.Value)
ActiveCell.Offset(0, 2).Select
firstval2 = CDbl(ActiveCell.Value)

While ActiveCell.Value <> ""

result2 = result2 + ActiveCell.Value
count2 = count2 + 1

ActiveCell.Offset(0, 1).Select
Wend
covar2 = (result2 - firstval2) / count2
MsgBox ("count2 is " & count2)
MsgBox ("firstval2 is " & firstval2)
MsgBox ("sum is " & result2)
MsgBox (covar2)
Me.Label1.Caption = CStr(covar1 / covar2)
Else

End If


End Sub

Private Sub UserForm_Click()

   
    
    
    
    
End Sub

Private Sub UserForm_Initialize()
 Me.ComboBox1.List = Sheets("Data").Range("A2:A11").Value
    
 Me.ComboBox2.List = Sheets("Data").Range("A2:A11").Value
    

End Sub