Need help with VBA "IF(AND)" function

[joneil27]

please i need help i am new to VBA
problems with "IF" & "AND"
the value of textbox1 = 0.7
the value of textbox2 = 0.8

i want to make textbox3 say a message
if textbox1 is greater than textbox 2 by 0.1 and lower than textbox2 by 0.1

textbox1 can not be lower or greater than textbox2 at the range of 0.1
meaning if textbox1 is 0.69 or 0.91 then textbox3 will say "not ok"

 

[tygrrboi]

I think you mean OR... something cannot be both greater than AND less than a value.


Do you mean like this?

If ABS(VAL(Textbox1.Value) - VAL(Textbox2.Value)) > 0.1 Then
   Textbox3.Value = "Not OK"
End If

 

[joneil27]

i used on worksheet =AND(H13>B8-0.1,H13<=D8+0.1)
but i cant make the same argument on vba

 

[tygrrboi]

did you try what I suggested?

 

[joneil27]

Private Sub Label18_click()
Label18.ForeColor = RGB(255, 0, 0)
End Sub


Private Sub UserForm_Initialize()
AlwaysOnTop Me.caption
End Sub
Sub GetErr()
On Error GoTo 0
End Sub


Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Unload Me
UserForm1.Show
Application.ScreenUpdating = True
End Sub


Private Sub SpinButton1_Change()
TextBox3.Value = SpinButton1.Value
End Sub


Private Sub SpinButton2_Change()
TextBox15.Value = SpinButton2.Value
End Sub




Private Sub TextBox1_change()

ValidateNumber Me.TextBox1
addtextbox

End Sub


Private Sub TextBox17_Change()
addtextbox
End Sub


Private Sub TextBox2_Change()

ValidateNumber Me.TextBox2
addtextbox

End Sub

Private Sub TextBox3_Change()

ValidateNumber Me.TextBox3
addtextbox


End Sub

Private Sub TextBox15_Change()

ValidateNumber Me.TextBox15
addtextbox

End Sub
Private Sub TextBox5_Change()

ValidateNumber Me.TextBox5
addtextbox

End Sub
Private Sub TextBox6_Change()

ValidateNumber Me.TextBox6
addtextbox

End Sub
Private Sub TextBox7_Change()

ValidateNumber Me.TextBox7
addtextbox

End Sub
Private Sub TextBox8_Change()

ValidateNumber Me.TextBox8
addtextbox

End Sub
Private Sub TextBox9_Change()

ValidateNumber Me.TextBox9
addtextbox

End Sub
Private Sub TextBox10_Change()
ValidateNumber Me.TextBox10
addtextbox

End Sub
Private Sub TextBox11_Change()

ValidateNumber Me.TextBox11
addtextbox

End Sub
Private Sub TextBox12_Change()

ValidateNumber Me.TextBox12
addtextbox

End Sub
Private Sub TextBox13_Change()

ValidateNumber Me.TextBox13
addtextbox

End Sub
Private Sub TextBox14_Change()

ValidateNumber Me.TextBox14
addtextbox

End Sub



Sub ValidateNumber(textbox)

Dim sTxt As String

sTxt = textbox.Text
If sTxt = "" Then Exit Sub

If IsNumeric(sTxt) Then
If InStr(sTxt, ".") > 0 Then
If Len(sTxt) - InStr(sTxt, ".") > 2 Then
textbox.Text = Mid(sTxt, 1, Len(sTxt) - 1)
End If
End If

Exit Sub
End If
textbox.Text = Mid(sTxt, 1, Len(sTxt) - 1)

End Sub

Private Sub addtextbox()


With Me
sval1 = .TextBox1.Value
sVal2 = .TextBox2.Value
sval3 = .TextBox3.Value
sval15 = .TextBox15.Value
sval7 = .TextBox7.Value
sval8 = .TextBox8.Value
sval9 = .TextBox9.Value
sval10 = .TextBox10.Value
sval11 = .TextBox11.Value

If sval1 = "" Then sval1 = 100
If sVal2 = "" Then sVal2 = 100
If sval3 = "" Then sval3 = 100
If sval15 = "" Then sval15 = 100
If sval7 = "" Then sval7 = 100
If sval8 = "" Then sval8 = 100
If sval9 = "" Then sval9 = 100
If sval10 = "" Then sval10 = 100
If sval11 = "" Then sval11 = 100

dtotal = CDbl(sval1 * 2.1189)
.TextBox2 = Format(dtotal, "0")

dtotal = CDbl(sval3 / 25.4)
.TextBox5 = Format(dtotal, "0")

dtotal = CDbl(sval15 / 25.4)
.TextBox6 = Format(dtotal, "0")

dtotal = CDbl((1000 * sval1) / ((sval3 * sval15)))
.TextBox9 = Format(dtotal, "0.00")

dtotal = CDbl(sval9 * 196.8504)
.TextBox13 = Format(dtotal, "0")

dtotal = (sval7 / 25.4)
.TextBox11 = Format(dtotal, "0")

dtotal = CDbl(sval8 / 25.4)
.TextBox12 = Format(dtotal, "0")

dtotal = CDbl(((4 * (sval3 * sval15)) / ((2 * sval3) + (2 * sval15))))
.TextBox8 = Format(dtotal, "0")

dtotal = ((1.3 * (sval3 * sval15) ^ 0.625)) / Application.WorksheetFunction.Sum(sval3, sval15) ^ 0.25
.TextBox7 = Format(dtotal, "0.00")

dtotal = 6.05 * ((sval1 / 60) ^ 1.85) * 10 ^ 5 / 1.2 ^ 1.85 / (sval7 ^ 4.87) * 100000
.TextBox10 = Format(dtotal, "0.00")

dtotal = (sval10 * (0.004 / 3.28) * 100)
.TextBox14 = Format(dtotal, "0.00")

If dtotal = Application.WorksheetFunction.And(sval10 > (.TextBox18 + 0.1), sval10 < (.TextBox17 - 0.1)) Then
.TextBox16 = "Duct Size Accepted"
Else
.TextBox16 = "not ok"
End If
End With

End Sub


Sub Form_Current()
Dim curAmntDue As Currency, lngBlack As Long
Dim lngRed As Long, lngYellow As Long, lngWhite As Long


If Not IsNull(Me!txtPastDue.Value) Then
curAmntDue = Me!txtPastDue.Value
Else
Exit Sub
End If
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)
If curAmntDue > 100 Then
Me!txtPastDue.BorderColor = lngRed
Me!txtPastDue.ForeColor = lngRed
Me!txtPastDue.BackColor = lngYellow
Else
Me!txtPastDue.BorderColor = lngBlack
Me!txtPastDue.ForeColor = lngBlack
Me!txtPastDue.BackColor = lngWhite
End If
End Sub