Excel VBA Search + Sum Engine -

i trying code search + sum engine on excel vba. have list of entries, , need code search specific type of cost (for example "1 equipment") , needs sum equipment costs , print in cell in worksheet. heres typed far:

sub sample() dim fnd string dim myar dim long dim rng range, foundcell range, lastcell range, myrange range  set myrange = activesheet.usedrange set lastcell = myrange.cells(myrange.cells.count)  fnd = "1 equipment"  myar = split(fnd, "/")  = lbound(myar) ubound(myar)      set foundcell = myrange.find(what:=myar(i), after:=lastcell)      if not foundcell nothing         firstfound = foundcell.address     end if set rng = foundcell         until foundcell nothing             set foundcell = myrange.findnext(after:=foundcell)                 set rng = union(rng, foundcell)             if foundcell.address = firstfound exit         loop  if not rng nothing rng.**(____in here needs sum value of cell in  column right of word found___)**  end if      next  end sub 

this picture of list of values (i have few values typed, engine has go way end of worksheet): https://i.stack.imgur.com/zpic9.png needs sum "1 equipment" values , display sum in cell, amount of entries, answer 11750.

i beginner excel vba need help. thank you!

i more custom function added sheet find cells corresponding given value , sum adjacent cell numbers:

public function suminvestments(byval invtype string) long      dim allcells range     dim singlecell range     dim totalsum long      set allcells = range("a1:a" & range("a1").end(xldown).row).find(invtype, lookat:=xlwhole)     if not allcells nothing         each singlecell in allcells             totalsum = totalsum + singlecell.offset(0,1).value         next singlecell       end if     suminvestments = totalsum  end function 

to used this:

=suminvestments("1 equipment")