You could use TEXTJOIN if you wanted a comma and a space between each letter. The final bit of magic is to send the 26 intermediate answers into a wrapper function such as TEXTJOIN or CONCAT. This one formula would generate the 26 letters in reverse order. You can combine the formula in cells B3 and C3 into a single formula: =MID(A2,SEQUENCE(LEN(A2),LEN(A2),-1)). It would be as if you actually typed the 26 formulas over in column D, but it all happens automatically thanks to lifting. When you cleverly force 26 values there, the MID function calculates 26 times and generates 26 results. Normally, MID is expecting a single value as the second argument. The B3# notation says that you want the entire array generated by the formula in B3. For the second argument, you aren’t passing it a single value. The formula in B3 generates the numbers 26 to 1 in a column. In Figure 1, you can see two temporary formulas in cells B3 and C3. That way, as the phrase entered in A2 changes, the formula will adapt to more or fewer letters. To make this formula more flexible, you could use LEN(A2) instead of 26. The formula =SEQUENCE(26,26,-1) would generate 26 cells in a column, with the numbers starting at 26 and counting down to 1. You want all of the letters, and you want them in reverse sequence. But you don’t want just the fifth letter. Normally, using =MID(A2,5,1) would extract the fifth character from the cell (in this case, V). In Figure 1, cell A2 contains the 26 letters of the alphabet arranged backwards. People have begun to realize that you can use a SEQUENCE formula as the argument to most Excel functions that expect a single value and cause the function to calculate multiple times. You rarely saw this happen in legacy Excel because either implicit intersection or array truncation would prevent you from seeing all the answers.īut today, implicit intersection is turned off by default in Excel, and the ability for a single formula to spill into adjacent cells eliminates array truncation. If you go back decades in Excel, there’s a little-known concept called “lifting.” If a function is expecting a single argument and you manage to pass it an array of arguments, the function will calculate once for each item in the array. =SEQUENCE(10,5,500,-5) will generate 10 rows and 5 columns starting from 500 and counting backwards by 5.=SEQUENCE(10,2) will generate the odd numbers 1 through 19.
The often-overlooked SEQUENCE function provides a solution that will work across all platforms. The LAMBDA functions are still in beta and, frankly, can be difficult to understand. Using VBA means that the solution won’t work in Excel Online.
Using Power Query means that the solution won’t work on a Mac.
#Excel vba on step back in loop range how to#
Set workingCell = Cells(k, checkRange.Columns(1).Column) which gives the right object.Recently, I was answering a question about how to write a formula that would reverse the letters in a cell.
#Excel vba on step back in loop range code#
Is there a fix/alternate method? Obviously the easiest way would just be to tell the For-Each loop to step backwards one step if deletedrow=true Įdit - removed irrelevant code for ease of reading i replaced cellvar with workingCell and each of the subroutines would see that as "object required" instead of the actual cell with all of its information. 'workingCell = cells(k,lumn)Ĭall introCheck(cellVar, countryLocation, taxLocation, deletedRow)Īs you can see, I tried making a backwards-stepping forloop instead of a forwards-running for-each loop but that broke my subroutines. Post deletion, it seems as though my For-Each loop doesnt realize the index has changed, and immediately skips a bunch of cells. So in my last post I discussed finding a cell and checking it to the eventual conclusion that I would be deleting that row.