-how to use VBA to search strings and return values or cells nearby

**dp256** · 05-20-2009, 02:51 AM

So, think your hot stuff.

Im needing to search through the attached document which is truck logs at a mine and use vba to search through the list for each of the different shovels eg SHVL1, SHVL2 & SHVL3, and when the code finds that string, to display the tonnage 2 colums back from it. Im about half way there a i think but am having trouble with strings, if it was numbers i could do it no problem

I have the idea in my head but need a genius to help me with the fine print

Heres what i have so far (that doesn't work) but it gives an idea of what i want

Sub trucks()
Dim a(1000) As String

tonsum = 0

For i = 1 To 548

    a(i) = Cells(i + 1, 7).Value
    
    If a(i) = "SHVL1" Then
        Range(i + 1, 5).Select
        tonsum = tonsum + ActiveCell
    End If
     Cells(1, 9).Value = tonsum
    
Next i

End Sub

Hopefully someone konws what to do here..or has done it before
Thanks
Dan

**antoka05** · 05-20-2009, 03:06 AM

You can solve with Sumif function. See attached file.

Regards,
Antonio

**dp256** · 05-20-2009, 03:54 AM

only because it is going to be part of a larger chunk of code

**dp256** · 05-20-2009, 03:36 AM

lol.

you mean

Sub trucks()
Dim a(1000) As String

tonsum = 0

For i = 1 To 548

a(i) = Cells(i + 1, 7).Value

If a(i) = "SHVL1" Then
Range(i + 1, 5).Select
tonsum = tonsum + ActiveCell
End If
Cells(1, 9).Value = tonsum

Next i

End Sub

I figured sub and end sub gave it away?

**DonkeyOte** · 05-20-2009, 03:41 AM

Yes but we meant revise your original post... given you went to the effort of re-posting with tags I edited your first post.

Please be sure to read the Rules - they are for everyone's benefit to ensure things run as smoothly as possible. Often, VBA within tags is much easier to follow / analyse ...

**dp256** · 05-20-2009, 03:43 AM

ok no problem, thanks. I see *sumif* but no attached file?

**DonkeyOte** · 05-20-2009, 03:45 AM

Yes, I've PM'd antoka05 and asked them to reload... I appear to have removed the original (my bad)

**dp256** · 05-20-2009, 03:46 AM

no problem, ill upload the final copy once i finish it.
Thanks
Dan

**antoka05** · 05-20-2009, 03:49 AM

I am sorry... I missed attachment in my previous answer.

Regards,
Antonio

**dp256** · 05-20-2009, 03:52 AM

thats awesome, your a champion... just out of curiousity, do you know how to do it in VBA?

**antoka05** · 05-20-2009, 04:00 AM

You can use SumIf function:

Sub Macro1()
   myShovel = "SHVL1"
   tonSum = Evaluate("SUMIF(loads!g:g,""" & myShovel & """,loads!e:e)")
   Cells(1, 9).Value = tonSum
End Sub

or simply:

Sub trucks()
   Dim a(1000) As String
   
   tonSum = 0
   For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
      If Cells(i, "g") = "SHVL1" Then
         tonSum = tonSum + Cells(i, "e")
      End If
   Next i
   Cells(1, 9).Value = tonSum
End Sub

Regards,
Antonio

-how to use VBA to search strings and return values or cells nearby

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

-how to use VBA to search strings and return values or cells nearby

re: -how to use VBA to search strings and return values or cells nearby

Re: -how to use VBA to search strings and return values or cells nearby

Re: -how to use VBA to search strings and return values or cells nearby

Re: -how to use VBA to search strings and return values or cells nearby

Re: -how to use VBA to search strings and return values or cells nearby

Re: -how to use VBA to search strings and return values or cells nearby

Re: -how to use VBA to search strings and return values or cells nearby

Re: -how to use VBA to search strings and return values or cells nearby

Re: -how to use VBA to search strings and return values or cells nearby

Re: -how to use VBA to search strings and return values or cells nearby

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions