VBA Relative Referencing

[Littlemalky]

Ok, so here's the situation:

I have one static cell, in this case it's "F48" and I need it to equal the sum that I have at the bottom of another range of data. That range of data always changes in the amount of rows, however the columns are static. In column "J" is the part number, column "K" is the description, column "L" is the quantity, and the only purpose of column "M" is to label the total quantity at the very bottom of the data where everythign is summed.

I can't simply just do a sum formula in the F48 cell because of how everything is arranged and how it changes. Basically, I just need code that looks for the term "Polarized" in column "M", and takes the total quantity that is in the cell directly to the left of it, and copy that quantity into F48.

Any ideas?

 

[Andrew Poulsom]

Does this formula work for you?

=INDEX(L:L,MATCH("Polarized",M:M,FALSE))

 

[VoG]

Try

Sub test()
Dim Found As Range
Set Found = Columns("M").Find(what:="Polarized", LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Range("F48").Value = Found.Offset(, -1).Value
End Sub

 

[Littlemalky]

Andrew, I didn't even think about using an Index match...that works perfectly. Thanks.