If...Then...ElseIf If form Text box Value does NOT = Wooksheet cell value then save Text box Value to worksheet cell. Cannot seem to get the syntax right.

Here is a sample of the approach I am trying to take.

Sub TEXTBOX_AfterUpdate()
Set s = (Range("U" & r).Value) - This is a variable cell referance. R=row number Expl. U + R = U32
Set o = TEXTBOX.Value

If (o = s) Then
End Sub
ElseIf (o > s) Then
ws.Cells(Range("U" & r).Value).Value = (Me.ShtTrmVol.Value.Value)
ElseIf (o < s) Then
ws.Cells(Range("U" & r).Value).Value = (Me.ShtTrmVol.Value.Value)
' (irow, 21)
End If
End Sub
pwanveerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Farzad AkbarnejadDeveloperCommented:
Hi pwanveer,
You can't use End Sub more than one. Use  Exit Sub instead of.

-FA
0
hongjunCommented:
FarzadA is correct.
0
leclairmCommented:
You could also remove the first if altogether:

Just use:

if (o > s) Then
ws.Cells(Range("U" & r).Value).Value = (Me.ShtTrmVol.Value.Value)
ElseIf (o < s) Then
ws.Cells(Range("U" & r).Value).Value = (Me.ShtTrmVol.Value.Value)
' (irow, 21)
End If

Unless you intend to add additional code when they are equal.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pwanveerAuthor Commented:
You are correct on both counts, however the syntax is still incorrect.
0
Farzad AkbarnejadDeveloperCommented:
pwanveer,
Replace first End Sub with Exit Sub not the second.

-FA
0
leclairmCommented:
Don't see why you are assinging these values to o & s.  Try this:

if (TEXTBOX.Value > Range("U" & r).Value) Then
ws.Cells(Range("U" & r).Value).Value = (Me.ShtTrmVol.Value.Value)
ElseIf (TEXTBOX.Value < Range("U" & r).Value) Then
ws.Cells(Range("U" & r).Value).Value = (Me.ShtTrmVol.Value.Value)
' (irow, 21)
End If


Also, why do you have value twice here: Me.ShtTrmVol.Value.Value??
0
leclairmCommented:
You normally use set when dealing with objects, not values.
0
pwanveerAuthor Commented:
The Dbl ".Value" was a typo.

I tried the following:

Private Sub ShtTrmVol_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If (ShtTrmVol.Value > Range("U" & r).Value) Then
ws.Cells(Range("U" & r).Value) = (Me.ShtTrmVol.Value)
ElseIf (ShtTrmVol.Value < Range("U" & r).Value) Then
ws.Cells(Range("U" & r).Value) = (Me.ShtTrmVol.Value)
End If
End Sub

AND

Private Sub ShtTrmVol_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If (ShtTrmVol.Value) > (Range("U" & r).Value) Then
ws.Cells(Range("U" & r).Value) = (Me.ShtTrmVol.Value)
ElseIf (ShtTrmVol.Value) < (Range("U" & r).Value) Then
ws.Cells(Range("U" & r).Value) = (Me.ShtTrmVol.Value)
End If
End Sub

AND

Private Sub ShtTrmVol_Change
If (ShtTrmVol.Value > Range("U" & r).Value) Then
ws.Cells(Range("U" & r).Value) = (Me.ShtTrmVol.Value)
ElseIf (ShtTrmVol.Value < Range("U" & r).Value) Then
ws.Cells(Range("U" & r).Value) = (Me.ShtTrmVol.Value)
End If
End Sub

AND

Private Sub ShtTrmVol_Change
If (ShtTrmVol.Value) > (Range("U" & r).Value) Then
ws.Cells(Range("U" & r).Value) = (Me.ShtTrmVol.Value)
ElseIf (ShtTrmVol.Value) < (Range("U" & r).Value) Then
ws.Cells(Range("U" & r).Value) = (Me.ShtTrmVol.Value)
End If
End Sub

I still break on the first line of the If statement. Am I stating the object and cell references correctly?

I appreciate the help with this.
pw
0
leclairmCommented:
Your if is not referencing the worksheet:
If (ShtTrmVol.Value) > (Range("U" & r).Value) Then

Maybe try:

If (ShtTrmVol.Value) > (ws.Range("U" & r).Value) Then

Although I referenced excel with vb in quite some time, so my syntax could be off.
0
pwanveerAuthor Commented:
Still breaking on the first line. I know the ref. (Range("U" & r).Value) works. I am using that process in a number of other places to lookup values. The following is the rest of the code on the page if it helps...


Private Sub CommandButton5_Click()
Unload Me
End Sub


Private Sub CreditS_P_Change()

End Sub

Private Sub HedgeName_Change()
Sheets("Hedges").Select
Hedge_ID = HedgeName.ListIndex
ActiveSheet.Columns(1).Find((Hedge_ID.Value) + 1).Select
Set r = ActiveSheet.Columns(1).Find((Hedge_ID.Value) + 3)
' !!Start Bloomberg Data!!
If IsError(Range("H" & r).Value) Then
Industry = "Unavailable"
ElseIf IsNull(Range("H" & r).Value) Then
Industry = "Unavailable"
ElseIf Application.WorksheetFunction.IsText(Range("H" & r).Value) Then
Industry = Range("H" & r).Value
End If

If IsError(Range("I" & r).Value) Then
Maturity = "Unavailable"
ElseIf IsNull(Range("I" & r).Value) Then
Maturity = "Unavailable"
ElseIf IsDate(Range("I" & r).Value) Then
Maturity = Range("I" & r).Value
End If

If IsError(Range("L" & r).Value) Then
CreditS_P = "Unavailable"
ElseIf IsNull(Range("L" & r).Value) Then
CreditS_P = "Unavailable"
ElseIf Application.WorksheetFunction.IsText(Range("L" & r).Value) Then
CreditS_P = Range("L" & r).Value
End If

If IsError(Range("M" & r).Value) Then
CreditMoodys = "Unavailable"
ElseIf IsNull(Range("M" & r).Value) Then
CreditMoodys = "Unavailable"
ElseIf Application.WorksheetFunction.IsText(Range("M" & r).Value) Then
CreditMoodys = Range("M" & r).Value
End If

If IsError(Range("T" & r).Value) Then
CvtRatio = "Unavailable"
ElseIf IsNull(Range("T" & r).Value) Then
CvtRatio = "Unavailable"
ElseIf Application.WorksheetFunction.IsText(Range("T" & r).Value) Then
CvtRatio = "Unavailable"
ElseIf IsNumeric(Range("T" & r).Value) Then
CvtRatio = Range("T" & r).Value
End If

If IsError(Range("R" & r).Value) Then
LstTrdPri = "Unavailable"
ElseIf IsNull(Range("R" & r).Value) Then
LstTrdPri = "Unavailable"
ElseIf Application.WorksheetFunction.IsText(Range("R" & r).Value) Then
LstTrdPri = "Unavailable"
ElseIf IsNumeric(Range("R" & r).Value) Then
LstTrdPri = Range("R" & r).Value
End If

If IsError(Range("S" & r).Value) Then
PreOpenPri = "Unavailable"
ElseIf IsNull(Range("S" & r).Value) Then
PreOpenPri = "Unavailable"
ElseIf Application.WorksheetFunction.IsText(Range("S" & r).Value) Then
PreOpenPri = "Unavailable"
ElseIf IsNumeric(Range("S" & r).Value) Then
PreOpenPri = Range("S" & r).Value
End If

If IsError(Range("N" & r).Value) Then
DivDt = "Unavailable"
ElseIf IsNull(Range("N" & r).Value) Then
DivDt = "Unavailable"
ElseIf Application.WorksheetFunction.IsText(Range("N" & r).Value) Then
DivDt = "Unavailable"
ElseIf IsNumeric(Range("N" & r).Value) Then
DivDt = Range("N" & r).Value
End If

If IsError(Range("O" & r).Value) Then
QtrDiv = "Unavailable"
ElseIf IsNull(Range("O" & r).Value) Then
QtrDiv = "Unavailable"
ElseIf Application.WorksheetFunction.IsText(Range("O" & r).Value) Then
QtrDiv = "Unavailable"
ElseIf IsNumeric(Range("O" & r).Value) Then
QtrDiv = Range("O" & r).Value
End If

If IsError(Range("P" & r).Value) Then
NxtPutDt = "Unavailable"
ElseIf IsNull(Range("P" & r).Value) Then
NxtPutDt = "Unavailable"
ElseIf Application.WorksheetFunction.IsText(Range("P" & r).Value) Then
NxtPutDt = "Unavailable"
ElseIf IsNumeric(Range("P" & r).Value) Then
NxtPutDt = Range("P" & r).Value
End If

If IsError(Range("Q" & r).Value) Then
NxtPutPri = "Unavailable"
ElseIf IsNull(Range("Q" & r).Value) Then
NxtPutPri = "Unavailable"
ElseIf Application.WorksheetFunction.IsText(Range("Q" & r).Value) Then
NxtPutPri = "Unavailable"
ElseIf IsNumeric(Range("Q" & r).Value) Then
NxtPutPri = Range("Q" & r).Value
End If

If IsError(Range("J" & r).Value) Then
NxtCallDt = "Unavailable"
ElseIf IsNull(Range("J" & r).Value) Then
NxtCallDt = "Unavailable"
ElseIf Application.WorksheetFunction.IsText(Range("J" & r).Value) Then
NxtCallDt = "Unavailable"
ElseIf IsNumeric(Range("J" & r).Value) Then
NxtCallDt = Range("J" & r).Value
End If

If IsError(Range("K" & r).Value) Then
NxtCallPri = "Unavailable"
ElseIf IsNull(Range("K" & r).Value) Then
NxtCallPri = "Unavailable"
ElseIf Application.WorksheetFunction.IsText(Range("K" & r).Value) Then
NxtCallPri = "Unavailable"
ElseIf IsNumeric(Range("K" & r).Value) Then
NxtCallPri = Range("K" & r).Value
End If

If IsError(Range("AB" & r).Value) Then
LiveStkPri = "Unavailable"
ElseIf IsNull(Range("AB" & r).Value) Then
LiveStkPri = "Unavailable"
ElseIf Application.WorksheetFunction.IsText(Range("AB" & r).Value) Then
LiveStkPri = "Unavailable"
ElseIf IsNumeric(Range("AB" & r).Value) Then
LiveStkPri = Range("AB" & r).Value
End If

If IsError(Range("AL" & r).Value) Then
ParVal = "Unavailable"
ElseIf IsNull(Range("AL" & r).Value) Then
ParVal = "Unavailable"
ElseIf Application.WorksheetFunction.IsText(Range("AL" & r).Value) Then
ParVal = "Unavailable"
ElseIf IsNumeric(Range("AL" & r).Value) Then
ParVal = Range("AL" & r).Value
End If
' !!End Bloomberg Data!!
' !!Start User Entry Data!!
If (Range("U" & r).Value) = "" Then
ShtTrmVol = "Unavailable"
ElseIf IsNumeric(Range("U" & r).Value) Then
ShtTrmVol = Range("U" & r).Value
End If

If (Range("V" & r).Value) = "" Then
LngTrmVol = "Unavailable"
ElseIf IsNumeric(Range("V" & r).Value) Then
LngTrmVol = Range("V" & r).Value
End If

If (Range("W" & r).Value) = "" Then
Selection = "Unavailable"
ElseIf IsNumeric(Range("W" & r).Value) Then
Selection = Range("W" & r).Value
End If

If (Range("X" & r).Value) = "" Then
Delta = "Unavailable"
ElseIf IsNumeric(Range("X" & r).Value) Then
Delta = Range("X" & r).Value
End If

If (Range("Y" & r).Value) = "" Then
Trigger = "Unavailable"
ElseIf IsNumeric(Range("Y" & r).Value) Then
Trigger = Range("Y" & r).Value
End If

If (Range("BR" & r).Value) = "" Then
TrdDays = 1
ElseIf IsNumeric(Range("Y" & r).Value) Then
TrdDays = Range("BR" & r).Value
End If

' !!Mid Step Price!!
If IsError(Range("BB" & r).Value) Then
EqPriMid = "Error"
ElseIf IsNumeric(Range("BB" & r).Value) Then
EqPriMid = Format(Range("BB" & r).Value, "$######.00")
End If
' !!End Mid Step Price!!
' !!Start Deltas!!
If IsError(Range("AU" & r).Value) Then
DeltaMinus2 = "Error"
ElseIf IsNumeric(Range("AU" & r).Value) Then
DeltaMinus2 = Format(Range("AU" & r).Value / 1, "#,%######.00")
End If

If IsError(Range("AV" & r).Value) Then
DeltaMinus1 = "Error"
ElseIf IsNumeric(Range("AV" & r).Value) Then
DeltaMinus1 = Format(Range("AV" & r).Value / 1, "#,%######.00")
End If

If IsError(Range("AW" & r).Value) Then
DeltaMid = "Error"
ElseIf IsNumeric(Range("AW" & r).Value) Then
DeltaMid = Format(Range("AW" & r).Value / 1, "#,%######.00")
End If

If IsError(Range("AX" & r).Value) Then
DeltaPlus1 = "Error"
ElseIf IsNumeric(Range("AX" & r).Value) Then
DeltaPlus1 = Format(Range("AX" & r).Value / 1, "#,%######.00")
End If

If IsError(Range("AY" & r).Value) Then
DeltaPlus2 = "Error"
ElseIf IsNumeric(Range("AY" & r).Value) Then
DeltaPlus2 = Format(Range("AY" & r).Value / 1, "#,%######.00")
End If
' !!End Deltas!!
StkOvRid = Range("AC" & r).Value
MUMD = Range("AE" & r).Value
' !!End User Entry Data!!
' !!Start Calculated Values!!
If (Range("BS" & r).Value) = "" Then
DV01 = "Unavailable"
ElseIf IsNumeric(Range("BS" & r).Value) Then
DV01 = Range("BS" & r).Value
End If

If (Range("BT" & r).Value) = "" Then
Durration = "Unavailable"
ElseIf IsNumeric(Range("BT" & r).Value) Then
Durration = Range("BT" & r).Value
End If

If (Range("BU" & r).Value) = "" Then
Impact = "Unavailable"
ElseIf IsNumeric(Range("BU" & r).Value) Then
Impact = Range("BU" & r).Value
End If

' Will not work until Ticker2 is linked to a value
' If IsError(Ticker2.Value) Then
' StkTicker.Value ="Unknown"
' ElseIf Ticker2.Value = Ticker2.Value Then
' StkTicker.Value = Ticker2.Value
' End If

' Ticker2 = StkTicker.Value

If IsError(Range("AZ" & r).Value) Then
EqPriMinus2 = "Error"
ElseIf IsNumeric(Range("AZ" & r).Value) Then
EqPriMinus2 = Format(Range("AZ" & r).Value, "$######.00")
End If

If IsError(Range("BA" & r).Value) Then
EqPriMinus1 = "Error"
ElseIf IsNumeric(Range("BA" & r).Value) Then
EqPriMinus1 = Format(Range("BA" & r).Value, "$######.00")
End If

If IsError(Range("BC" & r).Value) Then
EqPriPlus1 = "Error"
ElseIf IsNumeric(Range("BC" & r).Value) Then
EqPriPlus1 = Format(Range("BC" & r).Value, "$######.00")
End If

If IsError(Range("BD" & r).Value) Then
EqPriPlus2 = "Error"
ElseIf IsNumeric(Range("BD" & r).Value) Then
EqPriPlus2 = Format(Range("BD" & r).Value, "$######.00")
End If


If IsError(Range("BE" & r).Value) Then
ParityMinus2 = "Error"
ElseIf IsNumeric(Range("BE" & r).Value) Then
ParityMinus2 = Format(Range("BE" & r).Value, "$######.00")
End If

If IsError(Range("BE" & r).Value) Then
ParityMinus2 = "Error"
ElseIf IsNumeric(Range("BE" & r).Value) Then
ParityMinus2 = Format(Range("BE" & r).Value, "$######.00")
End If

If IsError(Range("BF" & r).Value) Then
ParityMinus1 = "Error"
ElseIf IsNumeric(Range("BF" & r).Value) Then
ParityMinus1 = Format(Range("BF" & r).Value, "$######.00")
End If

If IsError(Range("BG" & r).Value) Then
ParityMid = "Error"
ElseIf IsNumeric(Range("BG" & r).Value) Then
ParityMid = Format(Range("BG" & r).Value, "$######.00")
End If

If IsError(Range("BH" & r).Value) Then
ParityPlus1 = "Error"
ElseIf IsNumeric(Range("BH" & r).Value) Then
ParityPlus1 = Format(Range("BH" & r).Value, "$######.00")
End If

If IsError(Range("BI" & r).Value) Then
ParityPlus2 = "Error"
ElseIf IsNumeric(Range("BI" & r).Value) Then
ParityPlus2 = Format(Range("BI" & r).Value, "$######.00")
End If

' NutrlMinus2 = Range("BJ" & r).Value
' NutrlMinus1 = Range("BK" & r).Value
' NutrlMid = Range("BL" & r).Value
' NutrlPluse1 = Range("BM" & r).Value
' NutrlPluse2 = Range("BN" & r).Value


' NeuBndPri = Range("AD" & r).Value

' Parity = Range("AF" & r).Value
' StkChg = Range("Z" & r).Value
' StkPctCng = Range("AA" & r).Value
StkDelta = Range("AG" & r).Value
AggDelta = Range("AH" & r).Value
OptDelta = Range("AI" & r).Value
StaticStk = Range("AJ" & r).Value
BndMk = Range("AK" & r).Value
If IsError(Range("BQ" & r).Value) Then
StpPct = "Error"
ElseIf IsNumeric(Range("BQ" & r).Value) Then
StpPct = Format(Range("BQ" & r).Value / 1, "#,%######.0")
End If
StpCalc = Format(Range("BB" & r).Value * Range("BQ" & r).Value, "#,######.00")



' !!End Calculated Values!!

End Sub
0
leclairmCommented:
Sorry I was under the impression you were working in vb with an excel object.

Perhaps the error lies with:

ShtTrmVol.Value
0
pwanveerAuthor Commented:
I am using an Excel form to access data held on an Excel worksheet. The majority of the data is static, however a number of values must be editable. Basically, show the user whats there and if they change the value save it to the worksheet.
0
pwanveerAuthor Commented:
Sorry, I am not a VB programmer. I normally work in SQL or Access for this sort of thing. In this case I am stuck using Excel.
0
leclairmCommented:
Which line in the code you last posted is it crashing on an what error message(s) are being returned??
0
pwanveerAuthor Commented:
Run Time Error '1004':
Method 'Range' of Object'_Global' failed

on (First line of If statment)

If Me.ShtTrmVol.Value > Range("U" & r).Value Then

of

Private Sub ShtTrmVol_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.ShtTrmVol.Value > Range("U" & r).Value Then
ws.Cells(Range("U" & r).Value) = Me.ShtTrmVol.Value
ElseIf Me.ShtTrmVol.Value < Range("U" & r).Value Then
ws.Cells(Range("U" & r).Value) = Me.ShtTrmVol.Value
End If
End Sub
0
leclairmCommented:
What is the value of r at this point?
0
pwanveerAuthor Commented:
r = The variable row number of the excel worksheet depending on which record you are viewing. There for if r = 37 the result of Range ("U" & r).Value = U37. The value of cell U37 on the worksheet should be replaced by the value in Me.ShtTrmVol (Form text box). This process works in all the circumstances where I've used it to lookup values on the wooksheet and display them on the form.
0
leclairmCommented:
I meant when it is crashing, what is the value of r??
0
pwanveerAuthor Commented:
651
0
leclairmCommented:
Then perhaps the problem lies with this particular cell (U651).

Besides that, I'm not really sure where else to look.
0
pwanveerAuthor Commented:
I got it. How is this for lack of experiance. The variable r set in the privious Sub routine does not flow to other Sub's. R needed to be set again.

The following works:

Private Sub ShtTrmVol_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Set r = ActiveSheet.Columns(1).Find((Hedge_ID.Value) + 3)

If Me.ShtTrmVol.Value > Range("U" & r).Value Then
Range("U" & r).Value = Me.ShtTrmVol.Value
ElseIf Me.ShtTrmVol.Value < Range("U" & r).Value Then
Range("U" & r).Value = Me.ShtTrmVol.Value
End If
End Sub

Thank you for all your help.
0
leclairmCommented:
Ah ha!!!

Good lesson learned!

You're welcome and glad you found it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.