I'm in the macros dark ages because I can't get any of my VBE Macros to work. I guess I could use a serious tutorial is anyone has pointers.
MEantime, I open old macro sheets that were constructed by placing programs in a column of cells in a macro worksheet that I learned in Excel 2.x
The syntax is slight different and the VBE style doesn't use the RETURN() funtion to end the macro anymore, I believe, but otherwise this macro that I'm having trouble with will, I hope, be recognizable by the assembled wisdom.
I have a list of records that I will ultimately apply a pivot table command to thanks to help I received here. But first, the data that came in without delimiters had text of varying and unpredictable length and the fixed width import turned it into several fields based on no deciherable algorithm I can find. So I am manually reformatting these entries and filling down. There are between 200 and 600 consecutive entries that are identical in the text fields. At first I thought that each ran for the same number of entries so I wrote a macro that just jumped 607 lines, but for varying reasons, some of the groupings do not have the full 607 entries.
So I'm trying to restructure the macro so if I'm at the first occurence of a particular text value I can have the macro run through succeeding rows in that column until it finds a value that is not equal to the original and then jump there. By using a split window and running the macro on the bottom window this gives me the opportunity to easily select my fill down range instead of scrolling and trying to catch the change in text as it flies by my eye.
My proposed code looks like this:
=FOR("counter",1,607,1)
=IF(SELECTION()="R["counter"]C",NEXT(),SELECT("R["counter"]C")
=RETURN()
My problem is that I can't seem to get the quote marks right to call the variable and EXCEL doesn't like the formula I've written in line 2 of the code (unoriginally I'm using "counter" which is the sample variable excel uses in tutorials on FOR() NEXT() loops.) My notion is to start "counter" at 1 so the first time through the IF function compares the SELECTION to R[1]C. If they are equal, the true value is NEXT(). Counter is incremented to 2, so the IF function compares the SELECTION to R[2]C and so forth, until it encounters a different value. Then the False value is to SELECT that cell using the counter variable. Of course this approach would actually select not the last same value, but the 1st of a different value in that column. This could be accounted for by making the selection R["counter"-1]C, but the point is that it will put the lower screen view right in the vicinity of where I need to be and then I can take it quickly from there.
It may be that there is an easier or more logical way to do this by loading the value in the current selection and using a FIND.COLUMN that looks for the not equal, <> value.
So, I'm certainly open to other ways up the mountain. For now I'm going to be pasting them into an old previously named macro in an old macro sheet because I just cannot get the VBE macro thing to work for me. I would be glad to read tutorial on that but also trying to forge forward on this project so I can finish it for the end of the day.
Brian
Bookmarks