Determine the distance between 2 cells in a column
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 |
All times are GMT +1. The time now is 01:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com