Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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.

Posted on 2006-03-21
22
Medium Priority
?
241 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
0
Comment
Question by:pwanveer
  • 10
  • 9
  • 2
  • +1
22 Comments
 
LVL 14

Expert Comment

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

-FA
0
 
LVL 33

Expert Comment

by:hongjun
ID: 16247593
FarzadA is correct.
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16247653
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:pwanveer
ID: 16247955
You are correct on both counts, however the syntax is still incorrect.
0
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 16248072
pwanveer,
Replace first End Sub with Exit Sub not the second.

-FA
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16248183
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
 
LVL 11

Expert Comment

by:leclairm
ID: 16248198
You normally use set when dealing with objects, not values.
0
 

Author Comment

by:pwanveer
ID: 16248338
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
 
LVL 11

Expert Comment

by:leclairm
ID: 16248473
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
 

Author Comment

by:pwanveer
ID: 16248528
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
 
LVL 11

Expert Comment

by:leclairm
ID: 16248589
Sorry I was under the impression you were working in vb with an excel object.

Perhaps the error lies with:

ShtTrmVol.Value
0
 

Author Comment

by:pwanveer
ID: 16248656
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
 

Author Comment

by:pwanveer
ID: 16248678
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
 
LVL 11

Expert Comment

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

Author Comment

by:pwanveer
ID: 16248958
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
 
LVL 11

Expert Comment

by:leclairm
ID: 16248990
What is the value of r at this point?
0
 

Author Comment

by:pwanveer
ID: 16249079
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
 
LVL 11

Expert Comment

by:leclairm
ID: 16249112
I meant when it is crashing, what is the value of r??
0
 

Author Comment

by:pwanveer
ID: 16249131
651
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16249196
Then perhaps the problem lies with this particular cell (U651).

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

Author Comment

by:pwanveer
ID: 16249478
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
 
LVL 11

Accepted Solution

by:
leclairm earned 1000 total points
ID: 16249559
Ah ha!!!

Good lesson learned!

You're welcome and glad you found it.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question