So my problem is i have a program that searches for (and finds) certain cells
in my worksheet. it then transfers this data (and the data from a couple of
cells around it) to another sheet. Part of the other information is a sub
header i have. (i have a main header, 3 sub headers (2 different types) and
between 3 and 5 rows of information under each sub header) what i want is to
find the nearest sub header above the cell i just found.

i have come to the conclusion that i am in need of a function that can
determine the distance between the two cells, then i take the variable with
the closest negative (or mabye positive) distance and transfer this data into
my sheet.

to make this clearer i will copy a portion of my code
With Selection
Set a = .Find("Elder", , , , xlByColumns)
Set b = a.Offset(0, 3)
Worksheets("PhoneList").Cells(rowvar, 1) = a.Offset(0, 1).Value
Worksheets("PhoneList").Cells(rowvar, 2) = a.Offset(1).Value
Set branch = .Find("Zweig", a, , , xlByColumns, 2)
Set gemeinde = .Find("Gemeinde", a, , , xlByColumns, 2)
***'this is where i run into the problem of finding the closest cell above
the cell i have (note it can either have "Zweig" in it or "Gemeinde" in
it)******

Vielen Dank