15,311,014 members
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

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

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
![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```

Posted
Updated 12-Jun-13 21:44pm
v2