We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

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.

pwanveer
pwanveer asked
on
Medium Priority
277 Views
Last Modified: 2010-04-07
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
Comment
Watch Question

CERTIFIED EXPERT

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

-FA

Commented:
FarzadA is correct.
leclairm.Net Developer  ERP integration

Commented:
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.

Author

Commented:
You are correct on both counts, however the syntax is still incorrect.
CERTIFIED EXPERT

Commented:
pwanveer,
Replace first End Sub with Exit Sub not the second.

-FA
leclairm.Net Developer  ERP integration

Commented:
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??
leclairm.Net Developer  ERP integration

Commented:
You normally use set when dealing with objects, not values.

Author

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
leclairm.Net Developer  ERP integration

Commented:
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.

Author

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
leclairm.Net Developer  ERP integration

Commented:
Sorry I was under the impression you were working in vb with an excel object.

Perhaps the error lies with:

ShtTrmVol.Value

Author

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.

Author

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.
leclairm.Net Developer  ERP integration

Commented:
Which line in the code you last posted is it crashing on an what error message(s) are being returned??

Author

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
leclairm.Net Developer  ERP integration

Commented:
What is the value of r at this point?

Author

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.
leclairm.Net Developer  ERP integration

Commented:
I meant when it is crashing, what is the value of r??

Author

Commented:
651
leclairm.Net Developer  ERP integration

Commented:
Then perhaps the problem lies with this particular cell (U651).

Besides that, I'm not really sure where else to look.

Author

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.
.Net Developer  ERP integration
Commented:
Ah ha!!!

Good lesson learned!

You're welcome and glad you found it.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.