Déclarer des cellules comme étant numériques
Bonsoir le Forum,
Encore désolé de vous ennuyer avec mes problèmes récurrents sur VBA.
Mais j'ai vraiment besoin de vous.
Je travaille sur ce code dont j'essaye d'adapter selon mon besoin.
Ce code me permet de tester des cas possibles où mes Cellules contiendraient "0" , "-", ":", "" (""= ceci représente, Cellule vide) et dans le dernier cas des chiffres comme par exemple 15.
Dans mes différent cas le code fonctionne parfaitement. Sauf pour le dernier cas qui est sensé contenir des données numérique. J'ai donc pensé qu’il fallait que je déclare mes cellules comme des cellules numériques pour que mon dernier cas puisse s'exécuter.
Alors j'ai écris:
If WorksheetFunction.IsNumber(Sheets("Sheet2").Cells(i, "B")) Then
Select Case .Cells(i, "C")
Case "0": .Cells(i, "E") = "Your total should not be 0, because the calculated total is > 0!"
Case "-": .Cells(i, "E") = "Your total cannot be not applicable ('-'), because the calculated total is > 0!"
Case ":": .Cells(i, "E") = "Your total cannot be not available (':'), because the calculated total is > 0!"
Case "": .Cells(i, "E") = "Your total cannot be empty, because the calculated total is > 0!"
End Select
End IfMais cela ne change pas grand chose au résultat final.
Sauriez vous m'aider?
Infiniment Merci.
le voici le code complet de la feuille:
Sub commentaire()
Dim nb As String
nb = IsNumeric(B25)
With Sheets("Sheet2")
Range("E5:E29").Formula = "=IF(B5<=C29,""OK"")"
For i = 5 To 29 'plage à adapter
Select Case .Cells(i, "B")
Case Is = "0"
Select Case .Cells(i, "C")
Case Is = "-", ":", "": .Cells(i, "E") = "Your total should be '0'"
End Select
Case "-"
Select Case .Cells(i, "C")
Case "0", " :", "": .Cells(i, "E") = "Your total should be not applicable ('-')"
End Select
Case ":"
Select Case .Cells(i, "C")
Case "0", "-", "": .Cells(i, "E") = "Your total should be not applicable (':')"
End Select
Case ""
Select Case .Cells(i, "C")
Case "0", "-", ":": .Cells(i, "E") = "Your total should be empty"
End Select
If WorksheetFunction.IsNumber(Sheets("Sheet2").Cells(i, "B")) Then
Select Case .Cells(i, "C")
Case "0": .Cells(i, "E") = "Your total should not be 0, because the calculated total is > 0!"
Case "-": .Cells(i, "E") = "Your total cannot be not applicable ('-'), because the calculated total is > 0!"
Case ":": .Cells(i, "E") = "Your total cannot be not available (':'), because the calculated total is > 0!"
Case "": .Cells(i, "E") = "Your total cannot be empty, because the calculated total is > 0!"
End Select
End If
End Select
Next i
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B5:C29"), Target) Is Nothing Then
Call commentaire
End If
End SubCordialement,
Abakisi
Bonjour le Forum,
Par la force des choses j'ai finalement trouvé la solution et ma macro fonctionne.
Je devais juste créer d'autres conditions supplémentaires et créer des différences.
Par contre quelqu'un saurait comment appliquer la macro à d'autres colonne de la même feuille ?
Voici le code:
Sub commentaire()
Dim nb As String
With Sheets("Sheet2")
Range("E5:E29").Formula = "=IF(H5<=I5,""OK"")"
'Range("J5:J29").Formula = "=IF(H5<=I5,""OK"")"
For i = 5 To 29 'plage à adapter
nb = IsNumeric(Cells(i, "B"))
nb2 = IsNumeric(Cells(i, "H"))
If nb Then
If Cells(i, "B").Value <> 0 And Cells(i, "B").Value > Cells(i, "C").Value And Cells(i, "C").Value = 0 Then
Cells(i, "E").Value = "Your total should not be 0, because the calculated total is > 0!"
End If
If Cells(i, "B").Value <> 0 And Cells(i, "B").Value > Cells(i, "C").Value And Cells(i, "C").Value <> 0 Then
Cells(i, "E").Value = "Your total should not be less than the calculated"
End If
If Cells(i, "B").Value <> "" Then
Select Case .Cells(i, "C")
Case "-": .Cells(i, "E") = "Your total cannot be not applicable ('-'), because the calculated total is > 0!"
Case ":": .Cells(i, "E") = "Your total cannot be not available (':'), because the calculated total is > 0!"
Case "": .Cells(i, "E") = "Your total cannot be empty, because the calculated total is > 0!"
End Select
End If
End If
Select Case .Cells(i, "B")
Case Is = "0"
Select Case .Cells(i, "C")
Case Is = "-", ":", "": .Cells(i, "E") = "Your total should be '0'"
Case Else: .Cells(i, "E") = "OK"
End Select
Case "-"
Select Case .Cells(i, "C")
Case "0", ":", "": .Cells(i, "E") = "Your total should be not applicable ('-')"
Case Else: .Cells(i, "E") = "OK"
End Select
Case ":"
Select Case .Cells(i, "C")
Case "0", "-", "": .Cells(i, "E") = "Your total should be not applicable (':')"
Case Else: .Cells(i, "E") = "OK"
End Select
Case ""
Select Case .Cells(i, "C")
Case "0", "-", ":": .Cells(i, "E") = "Your total should be empty"
Case Else: .Cells(i, "E") = "OK"
End Select
End Select
Next i
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B5:C29"), Target) Is Nothing Then
Call commentaire
End If
End Sub