Click here to Skip to main content
15,311,014 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

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

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
If Cells(r, 7).Value = "N" Then
    dblFLA = Cells(r, 9).Value
End If
End If

With rs
  ![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


End With

'Question: How to group Name and do Sum of Value3? How to perform another calculation on it
Exit Sub
MsgBox "Error Number: " & Err.Number & "Description: " & Err.Description
End Sub

Thanks in Advance!
Updated 12-Jun-13 21:44pm

1 solution

I would suggest you to read this article: How To Use ADO with Excel Data from Visual Basic or VBA[^]

There you'll find a way to perform many "calculations" on Excel data using T-SQL commands.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900