Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    have a suggestion also for this.?

    =MAX(A7:A65000)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    for the future... rapid or quick response
    Sal

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
    ' Ask the user
    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. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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...")

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    TKS,...

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

    Public blnPasswordOK As Boolean

    - 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 LastLast

Posting Permissions

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