Counting and Defining Column directly to the left.

[kortex]

I have some code below that I use to locate the correct header in a spreadsheet and then cycle through the values beneath it. After I'm done with that for loop, I need to do the same thing with the column to the left. I don't want to waste time running another ".FIND" function. How can I refer to the column to the left of whatever was found here in the same manner as the code below? I tried throwing in a ".OFFSET(x, x)" but I couldn't get it to work. I always have trouble with offsets. Any ideas? I imagine I'm missing something silly.

Please Login or Register  to view this content.

[6StringJazzer]

Range.Offset(r, c) refers to the cell r rows down and c columns to the right of Range. Use negative numbers to go up or to the left.

Assuming that List_Item is a Range:

Please Login or Register  to view this content.

[kortex]

Erm... that won't work. The column to the left will have a different number of cells occupied than the column that I find with the .FIND function. I'm fine with running a separate for loop, I just don't want to use the .FIND function again because there is quite a bit of data. What I'm really looking for is a way (if possible) to modify the second line of code (the for each...) to count and refer to the column to the left of the column found in the first line.

[6StringJazzer]

You need to capture the result of the Find in a range variable, then use that variable in the call to RemoveNumbers. Then you can refer to the offset from that range. You haven't given any information about your data layout and this is a very minimal amount of code to try and understand what you're doing, but I think that will work for you. Erm.

Please Login or Register  to view this content.

[kortex]

Thank you, that worked perfectly. I will do that in the future!