CODEWITHSUNDEEP
excelvba
Neelavathy
Neelavathy

Reputation: 14

Search Similar String using vba in same column

I need to search similar string which exist under same column from a input excel file and group them together and perform some calculation.

Example say : I have input file named : Predictions

FUND_NAME   CLASS_NAME  FUND_CODE    CASHFLOW_IN_FUND_CURRENCY 
AA              BB              SSIS         19200
VV              DD              SPIS         16200
QQ              NN              BNIS         15830
CC              DJ              SSIS         -6300

I want a code in VBA which should combine Similar Fund_Codes together and perform some logical calculations on CASHFLOW_IN_FUND_CURRENCY .

Say When 1st nd 4th row has same fund code (SSIS) then I need to ABS(sum(19200-6300))

I know the logic for calculations ,but couldn't find the logic to group the fund_code within same worksheet and same column.

I had tried the Rachels method fuzzy logic in stack overlow but it didn't work ,

I also tried the below code and it did not work .

Dim ws As Worksheet
    Dim lRow As Long, i As Long
    Set ws = ThisWorkbook.Sheets("TRANS")
    Dim rngFundCode As Range

    lRow = Sheet1.Range("C" & Rows.Count).End(xlUp).Row

    Set rngFundCode = Range("C:C")

    For i = 2 To lRow
        Cells(i, 3) = Application.WorksheetFunction.VLookup(Cells(i, 1), rngFundCode, 2, False)
    Next i

Could anyone ,let me know how to group the similr string within same column and prform some logic calculation on the other column 

Dim ws As Worksheet
    Dim lRow As Long, i As Long
    Set ws = ThisWorkbook.Sheets("TRANS")
    Dim rngFundCode As Range

    lRow = Sheet1.Range("C" & Rows.Count).End(xlUp).Row

    Set rngFundCode = Range("C:C")

    For i = 2 To lRow
        Cells(i, 3) = Application.WorksheetFunction.VLookup(Cells(i, 1), rngFundCode, 2, False)
    Next i

I want a code in VBA which should combine Similar Fund_Codes together and perform some logical calculations on CASHFLOW_IN_FUND_CURRENCY .

Say When 1st nd 4th row has same fund code (SSIS) then I need to ABS(sum(19200-6300))

Upvotes: 0

Views: 193

Answers (1)

Neelavathy
Neelavathy

Reputation: 14

I created a class and got the values as a collection from the input file and got this resolved.

Function CalculateReport() As Boolean

Dim bOutput As Boolean: bOutput = False
Dim colPrediction As Collection
Dim objPred As clsPrediction
Dim shtPred As Worksheet
Dim colFunds As New Collection

On Error GoTo ErrorAccessingFile
Set shtPred = g_colInputFiles("txtPredictions").GetSheets(1).GetSheet
On Error GoTo 0

'Get Data from Input Files
Set colPrediction = GetPredictionData(shtPred)
Dim col As Collection

'Split the data by FundCode
For Each objPred In colPrediction
    If ItemIsInCollection(colFunds, objPred.Fund_Code) Then
        Set col = colFunds(objPred.Fund_Code)
        col.Add objPred, objPred.getKey
    Else
        Set col = New Collection
        col.Add objPred, objPred.getKey
        colFunds.Add col, objPred.Fund_Code
    End If
Next objPred

For Each col In colFunds
    If col.Count > 1 Then
        'Apply Formula

    End If
Next col

Upvotes: 0

Related Questions