none
Formula for Custom Validation/Help RRS feed

  • Question

  • Hi! I'm pretty new to VBA in EXEL and also new to this forum. I have a problem with validation. I am trying to create a formula to Customer validation as I tear my hair with. It works take at all and I do not understand what I am doing wrong. Could someone have mercy on and explain what is w
    Dim ws As Worksheet
    Dim text As Worksheet
    Dim dC As Worksheet
    
    Dim rng As Range
    Dim LastRow As Long
    Dim MyValidationFormula As Variant
    Dim sheetName As String
    Dim rngValidation As Range
    
    On Error GoTo ErrHandler
                            
    Set text = ThisWorkbook.Worksheets("konfigkonfig")
    Set dC = ThisWorkbook.Worksheets("projektB")
    
    LastRow = ActiveSheet.Range("D" & Rows.Count).End(xlUp).row
                            
    Set rng = ActiveSheet.Range("K27" & ":K" & LastRow)
    
    '"OCH(" = AND(,      		 "ÄRTAL(" = ISNUMERIC(
    
    MyValidationFormula = "=OCH(K$8=1;ÄRTAL(K27);BH27<>1)"     
    
       
    With rng.Validation
    .Delete
    .Add Type:=xlValidateCustom,  _
    AlertStyle:=xlValidAlertStop, _
    Formula1:=MyValidationFormula          'This row faile
                                         
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With
    

    rong so I'll be forever grateful/Anders
    Thursday, March 29, 2018 12:05 PM

All replies

  • AndersViggen,
    re: formula used in Validation

    "IsNumeric" is used in VBA,
    "IsNumber" is used by Excel in formulas.

    I can't get Google to translate "ARTAL" into English

    '---
    Jim Cone
    https://goo.gl/IUQUN2
    Thursday, March 29, 2018 1:55 PM
  • Thanks for your reply Jim. I'm sorry, bad English on my part. This is VBA. I have a Swedish Excel and ' ÄRTAL ' is translated to ' ISNUMERIC '. The formula is recorded in Excel./Anders
    Thursday, March 29, 2018 4:57 PM
  • AndensViggen,

    The formula must be identical to the one you would use if typing it into Excel.
    Do not use... IsNumeric,
    use... IsNumber

    One way to be sure is to enter the formula into Excel and get it working properly and then transfer it to VBA...
    MyValidationFormula = "=AND(K8=1,ISNUMBER(K23),BH23<>1)"

    '---
    Jim Cone

    Thursday, March 29, 2018 5:37 PM