CODEWITHSUNDEEP
excelvba
Robert Pogačar
Robert Pogačar

Reputation: 11

Look for a specific string in a cell and sum values in that column (VBA)

Im doing some VB programing and I cannot find a solution for my problem. Since I am very new at programing I would like to ask for some help.

I have a multiple Excel files which contain a certain string in cell and below this string there are different numbers which I would like to sum up in a last cell.

The problem is that column with string that I am looking for is variable in a spreed sheet.

I would like to know how to search for string in a certain row and when this string if found I would like to sum all the numbers in that column.

Thank you for your help.

SO if you look at the example I would like to find cell which contains string "aaa|b" and then sum the numbers in that column in a last cell beneath the last number in that column.

Since this column is is in a different places on multiple Excel files I have to find it first and do all the math.

search and sum example

EDIT: This is how far I have got, but it doesn't work...

Sub ProcessFiles()
    Dim Filename, Pathname As String
    Dim wb As Workbook



    Pathname = ActiveWorkbook.Path & "C:\AM550_search_macro\to be checked"
    Filename = Dir(Pathname & "*.xlsx")
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        DoWork wb
        wb.Close SaveChanges:=True
        Filename = Dir()
    Loop
End Sub

Sub DoWork(wb As Workbook)
    With wb
        'Do your work here



    Sheets("KSM").Select
    With KSM.Range("row(2)")
    Set c = .Find("1.0.2.8.0.255", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing
    End If
End With
End Sub

I have also uploaded a file if someone could look at it:

https://www.dropbox.com/s/oxixczj0qearf03/test_sheet.xlsx?dl=0

So to sum things up:
- I'm looking for column that has "1.0.1.8.2.255" in row 2 (static) and has "< 5kWh" in row 3 (static)
- when I find this column I have to SUM all of numbers in this column

If someone could help I would really appreciate it.

Thank you!

Upvotes: 1

Views: 482

Answers (1)

donPablo
donPablo

Reputation: 1969

a brief outline of what you have requested is below. The outline is not complete, but does illustrate that you have asked for a non-trivial solution. The outline also contains a number of questions-- e.g. What if ....? which should be resolved in the request itself.

'folder chooser

'dir on xls in this folder

    'open xls
    'find row with "HELPER" in column A  (or is it always r=3? what if it is not?)
    'loop 
        find column(s) with keyword aaa|b  (can there be multiple columns?  What if none found?)
       'find last row (down this column? or last row across all columns?)
       'insert in cell below last value a macro =sum(firstcell, lastcell)
    'loop to other columns

    'save xls

'dir to next xls in this folder
'loop to open xls

Upvotes: 0

Related Questions