Solved vba instruction to search to the left for 5 cells

February 2, 2019 at 11:37:53
Specs: Macintosh
Variables
Home ‘location of cell where calculation result is stored and where procedure starts
Ttl ‘ obvious
Count ‘ “

Begin
Home=location of current cell ‘where calculation result will be stored
Ttl=0
Count=0
Do until count = 5
Move one cell to left
If cell contents > 0
Ttl=Ttl+value of cell contents
Count=Count+1
Repeat
Move back to home
Put ttl value in home
Assign Home to “”
Done one cell
End


See More: vba instruction to search to the left for 5 cells

Report •

#1
February 2, 2019 at 14:38:16
This may sound like a silly question, but why not just SUM the 5 cells? Since your example "code" seems to always check all 5 cells, just SUM them. Any zeros won't make any difference.

Perhaps if you used some actual sentences in your post, we'd have a better idea of what you are trying to do.

I used to read people minds, but I saw too much stuff that was really scary.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
February 4, 2019 at 12:20:22
I thought I had already replied. Summing the 5 previous consecutive cells is not applicable. The columns are dates and the cells are golf scores for the date column. People do not play or score every week so just summing the last 5 cells may contain some blanks. I need the previous 5 cells which have numbers to total up so I can look up that total in a table which returns a handicap based on the last 5 scores.

Report •

#3
February 5, 2019 at 09:08:23
✔ Best Answer
That's why words are so much better than the example "code" you posted above.

However, the words that are still missing are the location of "home", i.e. the cell where you want the sum of the scores to be stored. I also don't know where the names of your players are stored. In other words, I don't know the layout of your worksheet, so it's really difficult to offer a specific solution.

Generically, with your scores stored in A2:Z2, this array formula will sum the last 5 non-zero values.

This is an array formula, so it must be entered via Ctrl-Shift-Enter.

=SUM(Z2:INDEX(A2:Z2,LARGE(COLUMN(A2:Z2)*(A2:Z2>0),5)))

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Related Solutions

#4
February 5, 2019 at 10:32:02
This looks like it will work after I wrap my head around it I will see if I can adapt your formula to my sheet. Thank you for getting back to me.. I will let you know how this works. I am not sure about your statement "This is an array formula, so it must be entered via Ctrl-Shift-Enter"

Thanks again


Report •

#5
February 5, 2019 at 11:17:15
If you want to learn about array formulas, you can do a Google search and take it from there. It wouldn't make sense for me to repeat all of the fine information available on the web.

For now, I'll just address your specific issue.

Most formulas in Excel are entered into a cell and confirmed by hitting enter or tabbing to another cell or clicking the Check mark, etc.

An array formula must be confirmed by holding down the Control and Shift keys and then pressing Enter. Once you do this, Excel will place place brackets { } around the formula in the Formula bar. You can't just manually type the brackets to create an array formula, you must use Ctrl-Shift-Enter and let Excel add the brackets.

In addition, any edits that you make to the array formula once it has been created must be confirmed via Ctrl-Shift-Enter. If you don't use Ctrl-Shift-Enter you will get an error, most likely a #VALUE error, in the cell. When you click in the formula bar to edit the array formula, the brackets will disappear. The only way to get them back is to use Ctrl-Shift-Enter.

I hope that helps.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#6
February 5, 2019 at 11:35:28
Thanks again! Used to be able to do a lot in Lotus, but those jobs dried up and after having to go to Excel have just needed to use the spreadsheets for relatively simple chores. Obviously some of the macro commands in Lotus were not available in Excel, thus my attempt at my "program". Anyway this will get some of my 76 yr. old neurons working. May have to get back to you again though!

Report •

#7
February 5, 2019 at 11:45:56
This can also be done with a macro, but we would really need to know more about your data layout/sheet set up before we could offer any code.

If you are going to post any sample data, please be sure to click the following link and read the instructions in that How To.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#8
February 9, 2019 at 09:03:46
I created a blank sheet and placed your formula in it. It worked perfectly. But when I put it in my sheet I excluded the first 2 columns which were used for row headers. Of course it did not work correctly not adding all 5 columns. After I finally figured that the calculation required all of the preceding columns it works like a charm. THANKS AGAIN. This will save me a lot of time and errors this summer.

Report •

Ask Question