Create a macro in VBA to determinate if some cells are selected

[disa]

Hi, I've got a big problem in Excell and I'd like to solve it with a VBA macro. This is what I have to do:
Imagine that in cell A20 I've got the formula =SUM(A1:10). As you can there some cells (from cell A11 to cell A19) that are selected. If I am in this situation I'de like to have a message that say me: "Hey man, you have not selected ALL the cells before A20". Viceversa if I've selected all the cells before A20 I don't want any message.
Is it possible to do something like that?
My english is not very good, so I hope you've understood my problem.

 

[sijpie]

Yes,, unfortunately your request is not entirely clear.
using your example:
there is a formula in Cell A20 =Sum(A1:A19)
You have selected A3:A15
The macro should check to see if the selected cells match what is in the formula
If the same then no message, but in this case a message saying you haven't selected all.

How can the macro know which formula to check?
Does every column have a formula with a range in it?
Is there only one formula in each column?

 

[disa]

For the macro is not necessary to know what is the formula, but is necessary to know in what cells this formula is applied. From the cells of the formula the macro have to recognize di column, and from the column it have to recognize if all the cells before the cell where there is the formula are selected in the formula.
Yes, in every column there is a formula with a range. Theese formulas can be different.
Yes, in a column there only one formula.
Imagine this:

Rows/columns A B C D ....
1 34
2 7
3 0.9
4 12
5 2
6 5
7 77
8 11
9 10
10 1
11 =SUM(A1:A10)
In this case I have to recive no message beacuase all the cell before A11 (the cell where there is the formula) are selected in the formula

Rows/columns A B C D ....
1 34
2 7
3 0.9
4 12
5 2
6 5
7 77
8 11
9 10
10 1
11 =SUM(A1:A7)
In this case instead (the formula is still in A11) I have to recive a warning because some cells that are before A11 (theese cells are: A8, A9, A10) are not selected in the formula.
I hope that with this example the situation will be more clear. Sorry but the editor of this forum doesn't allow me to format the text in the way I want.

 

[sijpie]

In the macro editor (Alt-F11) in the left panel you see the names of the sheets of your workbook. Double-click on the sheetname where you want the checks done.

THis will open a large blank panel to the right. Paste the following macro there.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .HasFormula Then
'            MsgBox .FormulaR1C1
            If InStr(1, .FormulaR1C1, "R[-" & .Row - 1 & "]C:R[-1]C") Then
                ' everythin is OK
            Else
                MsgBox "Your formula does not contain full range above"
            End If
        End If
    End With


End Sub

Now with every change you make to this sheet, the sheet will check to see if the changed cell contains a formula.
If it contains a formula then it will check if the formula contains all the rows above the current cell. If not the message comes up.

 

[disa]

WOW, this is perfect. Thank you very much!!!

 

[sijpie]

Hi, I don't know if you remember me, but you wrote me the macro that is in this page: Create a macro in VBA to determinate if some cells are selected
Your macro was perfect for what I had to do that day, but after I've understood that I need a little modification of your macro. To be concise I will explain you with a pseudo-algorithm what I need:
STEP 1: The macro have to check if in the worksheet there are one or more cells that contains the formula =SUM, if these cells exists it will happen the following steps.
STEP 2: The macro have to create an intersection of rows and columns that depends by the rows that are above the cell where there is the formula =SUM and by the column where the formula =SUM is applied (all the formulas =SUM that are in the worsheet will be applied just in one column). To be more clear I will do you an example: image that in the cell A17 (it means that all the rows from the row 1 to the row 16 are above the cell A17) there is the formula =SUM(B4:B10) (it means that the formula is applied on the column B). The macro have to create the following intersection: from B1 to B16.
STEP 3: In the intersection that the macro have created on the STEP 2, the macro have to check if there are one or more values and have to understand in which cells are these values.
STEP 4: The macro have to check if all the cells that have recognized on the STEP 3 are all selected in the formula =SUM that have recognized on the STEP 1. If is true that all these cells are seleteced in the formula I have to receive no message, instead if is not true I have to receive a warning message.
I hope it is all clear, I think you understood that my english is not very well. Sorry... .

'#### This Worksheet Change sub checks to see if the =SUM() formula
'#### entered on this sheet includes all the rows with values
'#### above the formula in the column mentioned in the formula
'#### It will only work with simple =SUM(F2:F18) style formula


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sFrml As String, lRstrt As Long, lRend As Long, lC As Long
    Dim rStrt As Range, rEnd As Range
    With Target
        If .HasFormula Then
            If Left(.FormulaR1C1, 5) = "=SUM(" Then
                ' get column and start end rows of sum formula
                sFrml = Mid(.FormulaR1C1, InStr(1, .FormulaR1C1, "R"), _
                    InStr(1, .FormulaR1C1, ")") - InStr(1, .FormulaR1C1, "R"))
                    'sFrml like: R[-9]C[1]:R[-1]C[1]
                    ' but if column same as formula then: R[-9]C:R[-1]C
                If InStr(1, sFrml, "C:") Then
                    lC = Target.Column - 1
                Else
                    lC = Mid(sFrml, InStr(1, sFrml, "C") + 2, _
                        InStr(InStr(1, sFrml, "C"), sFrml, "]") - (InStr(1, sFrml, "C") + 2))
                End If
                lRstrt = Target.Row + Mid(sFrml, 3, InStr(1, sFrml, "]") - 3)
                lRend = Target.Row + Mid(sFrml, InStr(1, sFrml, ":") + 3, _
                    InStr(InStr(1, sFrml, ":"), sFrml, "]") - (InStr(1, sFrml, ":") + 3))
                    
                Set rStrt = Cells(1, Target.Column + lC)
                Do While Not IsNumeric(rStrt.Value) Or rStrt.Value = vbNullString
                    Set rStrt = rStrt.Offset(1, 0) 'go to 1st cell with value in summed range
                Loop
                Set rEnd = Cells(Target.Row - 1, Target.Column + lC)
                If rEnd.Value = vbNullString Then
                    Set rEnd = rEnd.End(xlUp) 'go to last cell with value in summed range
                End If
                
                ' now check to see if celles between rStrt and rEnd are included in sum formula
                
                If rStrt < lRstrt Or rEnd > lRend Then
                    MsgBox "Your formula does not contain full range above"
                Else
                    ' everythin is OK
                End If
            End If
        End If
    End With




End Sub

 

[disa]

Thank you have answer me but this formula has got a problem: if there are some empty cells I don't receive any message.
Example:
rows/columns A
1
2
3 3
4
5 5
6 7
7 9
8 =SUM(A5:A7)
In this case I receive a warning (that's correct)
rows/columns A
1
2
3 3
4
5 5
6 7
7 9
8 =SUM(A4:A7)
In this case, instead, I don't receive any message. This is not correct because in the cell A3 there is a number and this cell is not selected in the formula

 

[sijpie]

Hi, I'm sorry to bother you again but your macro doesn't work very well. Example:
There are some values from A3 to A8. If in the cell A9 I write the formula =SUM(A3:A8) I don't receive any message (that's correct beacuase I select all the values).
If in the cell A9 I write the formula =SUM(A5:A8) I receive a warning (that's correct beacuase I have not selected all the values, remember that values start from A3).
If in the cell A9 I write the formula =SUM(A3:A6) I don't receive any message (that's not correct beacuase I have not selected all the values, remember that values end in A8).
Maybe the problem is that the variables rEnd and lRend are not setted in the correct way, but I don't know if supposition is correct. I would be more helpful but I have never programmed in <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>.

Yes you were right, I found a couple of bugs. This new code should do the trick.

<font face=Courier New><SPAN style="color:#007F00">'#### This Worksheet Change sub checks to see if the =SUM() formula</SPAN><br><SPAN style="color:#007F00">'#### entered on this sheet includes all the rows with values</SPAN><br><SPAN style="color:#007F00">'#### above the formula in the column mentioned in the formula</SPAN><br><SPAN style="color:#007F00">'#### It will only work with simple =SUM(F2:F18) style formula</SPAN><br><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> sFrml <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, lRstrt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lRend <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rStrt <SPAN style="color:#00007F">As</SPAN> Range, rEnd <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">With</SPAN> Target<br>        <SPAN style="color:#00007F">If</SPAN> .HasFormula <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Left(.FormulaR1C1, 5) = "=SUM(" <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#007F00">' get column and start end rows of sum formula</SPAN><br>                sFrml = Mid(.FormulaR1C1, InStr(1, .FormulaR1C1, "R"), _<br>                    InStr(1, .FormulaR1C1, ")") - InStr(1, .FormulaR1C1, "R"))<br>                    <SPAN style="color:#007F00">'sFrml like: R[-9]C[1]:R[-1]C[1]</SPAN><br>                    <SPAN style="color:#007F00">' but if column same as formula then: R[-9]C:R[-1]C</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> InStr(1, sFrml, "C:") <SPAN style="color:#00007F">Then</SPAN><br>                    lC = 0<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    lC = Mid(sFrml, InStr(1, sFrml, "C") + 2, _<br>                        InStr(InStr(1, sFrml, "C"), sFrml, "]") - (InStr(1, sFrml, "C") + 2))<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                lRstrt = Target.Row + Mid(sFrml, 3, InStr(1, sFrml, "]") - 3)<br>                lRend = Target.Row + Mid(sFrml, InStr(1, sFrml, ":") + 3, _<br>                    InStr(InStr(1, sFrml, ":"), sFrml, "]") - (InStr(1, sFrml, ":") + 3))<br>                    <br>                <SPAN style="color:#00007F">Set</SPAN> rStrt = Cells(1, Target.Column + lC)<br>                <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsNumeric(rStrt.Value) <SPAN style="color:#00007F">Or</SPAN> rStrt.Value = vbNullString<br>                    <SPAN style="color:#00007F">Set</SPAN> rStrt = rStrt.Offset(1, 0) <SPAN style="color:#007F00">'go to 1st cell with value in summed range</SPAN><br>                <SPAN style="color:#00007F">Loop</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> rEnd = Cells(Target.Row - 1, Target.Column + lC)<br>                <SPAN style="color:#00007F">If</SPAN> rEnd.Value = vbNullString <SPAN style="color:#00007F">Then</SPAN><br>                    <SPAN style="color:#00007F">Set</SPAN> rEnd = r<SPAN style="color:#00007F">End</SPAN>.End(xlUp) <SPAN style="color:#007F00">'go to last cell with value in summed range</SPAN><br>                End <SPAN style="color:#00007F">If</SPAN><br>                <br>                <SPAN style="color:#007F00">' now check to see if celles between rStrt and r<SPAN style="color:#00007F">End</SPAN> are included in sum formula</SPAN><br>                <br>                <SPAN style="color:#00007F">If</SPAN> rStrt.Row < lRstrt <SPAN style="color:#00007F">Or</SPAN> r<SPAN style="color:#00007F">End</SPAN>.Row > lRend <SPAN style="color:#00007F">Then</SPAN><br>                    MsgBox "Your formula does not contain full range above"<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    <SPAN style="color:#007F00">' everythin is OK</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    End <SPAN style="color:#00007F">With</SPAN><br><br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>