How to run query on existing recordset in vb6

See more:

VB6

VBA

Hello,

I have a table "TableA" with column name Name,"Y/N","Value1","Value2". This data is in excel file.
And have multiple entries for each Name.
1.Want to add one column to this table and calculate value on the basis of Y/N column and fill the new column. Meaning if Y/N column has Y then place value1 in value3 column if it is N then put value2 in value 3 column
2.Now want to group name and want to calculate sum of value3
3. on that Sum value want to do some calculation.

Can you please How can we solve this in VBA.
my code is as below

Private Sub CalcFinalLoanAmount()
On Error GoTo ErrorHandler
    
    Dim xlFile As Excel.Application
    Dim xlsWB1 As Object
    Dim xlsWS1 As Object
    Dim rs As ADODB.Recordset
    
    Set rs = New ADODB.Recordset
    
    Set xlFile = New Excel.Application
    xlFile.Workbooks.Open gstrXlName
    xlFile.Visible = False
    
    Set xlsWB1 = xlFile.Workbooks.Open(gstrXlName)
    Set xlsWS1 = xlsWB1.Worksheets("Employees")
    
    'get max row count
    Dim LastRow As Long
    LastRow& = Cells(Rows.Count, 3).End(xlUp).Row
    LastRow& = Range("C" & Rows.Count).End(xlUp).Row
    
    MaxRow = LastRow&
    MaxCol = 9



With rs.Fields

  .Append "Name", adVariant
  .Append "Y/N", adVariant
  .Append "Value1", adVariant
  .Append "Value2", adVariant
  .Append "Value3", adVariant   'new column
End With
rs.Open

Dim r As Integer
For r = 2 To MaxRow

Dim dblFLA As Double
If Cells(r, 7).Value = "Y" Then
    dblFLA = Cells(r, 8).Value
Else
If Cells(r, 7).Value = "N" Then
    dblFLA = Cells(r, 9).Value
End If
End If

With rs
  .AddNew
  ![Name] = Cells(r, 1).Value
  ![Value1] = Cells(r, 2).Value
  ![Value2] = Cells(r, 3).Value
  ![value3] = dblFLA 'add new column value on basis of Y/N column

  .Update

End With
Next

'Question: How to group Name and do Sum of Value3? How to perform another calculation on it

Exit Sub
ErrorHandler:
MsgBox "Error Number: " & Err.Number & "Description: " & Err.Description
End Sub

Thanks in Advance!