# Thread: count value... (2000 sr 1)

1. ## count value... (2000 sr 1)

i have insert in H1 this formula.
=CONTA.VALORI(C7:C65000)

and this in H2
=CONTA.SE(F7:F65000;36)

From another macro i delete entire line of sheet where is present this formula.

after the macro i see this in H1 =CONTA.VALORI(C7:C64812) H2=CONTA.SE(F7:FC64812;36) ????

in effect the difference is exactly the numebr of line deleted...

How to correct this error?

2. ## Re: count value... (2000 sr 1)

This is not an error, it's the way Excel works: it automatically adapts the ranges in a formula when you inssert or delete cells.

One way around it is to refer to the entire column, and subtract the number of values in rows 1:6:

=CONTA.VALORI(C:C)-CONTA.VALORI(C1:C6)
=CONTA.SE(F:F;36)-CONTA.SE(F1:F6;36)

Another one is to use the INDIRECT function (in Italian INDIRETTO):

=CONTA.VALORI(INDIRETTO("C7:C65000"))
=CONTA.SE(INDIRETTO("F7:F65000");36)

Dynamic named ranges would be another way, but that's more complicated.

3. ## Re: count value... (2000 sr 1)

have a suggestion also for this.?

=MAX(A7:A65000)

4. ## Re: count value... (2000 sr 1)

Similar to the last one:

=MAX(INDIRETTO("A7:A65000"))

Wherever you don't want Excel to adjust a range when inserting, deleting or moving cells, you can use INDIRECT (INDIRETTO) and put the range between quotes. Another example: =MIN(B7:B65000) can be replaced by =MIN(INDIRETTO("B7:B65000"))

5. ## Re: count value... (2000 sr 1)

clear and.... Speedly or is correct - fastly - (as usual)

6. ## Re: count value... (2000 sr 1)

Speedy or fast response. Or even rapid or quick response. English has no word "Speedly" or "fastly"

Steve

7. ## Re: count value... (2000 sr 1)

for the future... rapid or quick response
Sal

8. ## Re: count value... (2000 sr 1)

Private Sub CommandButton4_Click()
If TextBox2 = "" Then Exit Sub
ActiveCell.Select
Dim irisposta As Integer
irisposta = MsgBox("Vuoi cancellare il Nominativo: " & ActiveCell.Value & " ?", vbYesNo)
If irisposta = vbYes Then
ActiveCell.EntireRow.Delete

Dim CA As Object
Range("A3:A152").ClearContents
Range("A3").Value = 1
For Each CA In Range("A3:A152")
If CA.Offset(1, 0) = "" Then
CA.Offset(1, 0) = CA + 1
End If
Next

End If
End Sub

This is the (original for test i have delete this option, this is the last step...) command click to delete a record from idirizzario is possible to arrange this in the our project USERFORM TO SEARCH????

Note: this use only the range A3:A152 but in my modified script the problem is "i doint know the number of records...)

9. ## Re: count value... (2000 sr 1)

In the version of the workbook you made available last week, there is no line number in column A, so I don't think you need the code to erase and fill A3:A152.

You could put a command button on Indirizzario, named cmdDelete. To write code for it, double click the command button (in the Visual Basic Editor) and make the code look like this:

Private Sub cmdDelete_Click()
Dim iRisposta As Integer
' Check if scroll bar has valid value
If ScrollBar1.Value = 0 Then Exit Sub
iRisposta = MsgBox("Vuoi cancellare il Nominativo: " & Cells(ScrollBar1.Value + 6, 5) & " ?", vbYesNo + vbQuestion)
If iRisposta = vbYes Then
' Delete the row corresponding to the scrollbar value.
ActiveSheet.Unprotect "SAL21"
Rows(Scrollbar1.Value + 6).Delete
ActiveSheet.Protect "SAL21"
End If
End Sub

To make this work correctly, you must modify the code in two other places:

1. In CommandButton1_Click in the code for the form Indirizzario. Below the line containing only

Do

insert this line:

ScrollBar1.Value = c.Row - 6

2. In the procedure PRIMO in the module CERCA_PRIMO. Below the line containing only

Else

insert this line:

Indirizzario.ScrollBar1.Value = CL.Row - 6

10. ## Re: count value... (2000 sr 1)

they are sure, I will not abandon never this forum, why you here!!!!!!!!!!!!!!!
Best code!

11. ## Re: count value... (2000 sr 1)

Hi HansV,
in the Delete Macro is possible to insert this condition in the msgbox similar this:

MsgBox "INIZIO ELENCO, IMPOSSIBILE PROSEGUIRE!", , "ATTENZIONE..."

in effect, instead "MICROSOFT EXCEL" this "ATTENZIONE..." in the label of message...
Tks.

12. ## Re: count value... (2000 sr 1)

Change

iRisposta = MsgBox("Vuoi cancellare il Nominativo: " & Cells(ScrollBar1.Value + 6, 5) & " ?", vbYesNo + vbQuestion)

to

iRisposta = MsgBox("Vuoi cancellare il Nominativo: " & Cells(ScrollBar1.Value + 6, 5) & " ?", vbYesNo + vbQuestion, "Attenzione...")

TKS,...

14. ## Re: count value... (2000 sr 1)

HansV, if you have a little time for me for this CMDDELETE i have this question:

My Chef, decide that this option to delete a record is possible only for a user whit a superior Key.

I suggest:

If you press on delete botton appear a userform whit a cell where you insert a passoword (hide this in a sheet SERV- is hide in the wbook for example in A1) if the password is the same in A1 (i decide to assign one for eample TEST001) ok, contunue to dlete a record if not is the same skip the controll and appear a msg box"ATTENTION ONLY THE ADMIN MAKE TIS OPTION!!!)...

Have anothe r suggestion?
Tks for ever and sorry for my request, i hope dont create a disturbe you for this...;-)

15. ## Re: count value... (2000 sr 1)

You can do it like this:
- In a standard module, declare a public variable:

- Create a new userform (you can copy it from the attached workbook), with a text box, an OK button and a Cancel button.
- Set the PasswordChar property of the text box to *.
- In the On Click code for the OK button, compare the password entered by the user to the one in the hidden sheet.
- Set the blnPasswordOK variable to True if they match, then close the form.
- In the On Click code for the Cancel button, just close the form.
- In the code for cmdDelete on the Indirizzario form, set blnPasswordOK to False, then open the password form.
- Then test the value of blnPasswordOK, and warn the user if it is False.

You will have to protect the Visual Basic code in the workbook with a password, otherwise everybody can unhide the worksheet, and look at the code. You do this in the Visual Basic Editor. Select Tools | VBAProject Options... In the Protection tab, tick the "Lock project for viewing" check box and enter the password for viewing VBA code in both text boxes.

See attached workbook. The password to view the code and the password to enter in the box are displayed in the worksheet. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•