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:
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

VB
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
VB
Exit Sub
ErrorHandler:
MsgBox "Error Number: " & Err.Number & "Description: " & Err.Description
End Sub


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

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