Repeated IF Statements Too Large 1

[blounty]

Hey All,
I am using an IF statement to get a value from a text box on a form into a cell If an adjacent cell is equal the value in a combobox on the same form. so i have used this code:

Sub Analysis_Area()

If Worksheets("Proposal").Range("C4") = UserForm4.ComboBox1.Value Then
UserForm4.TextBox2.Value = Worksheets("Proposal").Range("K4")
Else
If Worksheets("Proposal").Range("C5") = UserForm4.ComboBox1.Value Then
UserForm4.TextBox2.Value = Worksheets("Proposal").Range("K5")

[COLOR=red]'This would repeat until C500 and K500!![/color]


End Sub

Is there anyway of shortening this code?

I appreciate any input at all. Thank you for looking.

Regards

Alex

 

[JustinEzequiel]

untested

Dim i As Long
Dim src As String
Dim dst As String

With Worksheets("Proposal")
    For i = 4 To 500
        src = "C" & i
        dst = "K" & i
        If .Range(src) = UserForm4.ComboBox1.Value Then
            UserForm4.TextBox2.Value = Worksheets("Proposal").Range(dst)
            Exit For
        End If
    Next i
End With

 

[Fenrirshowl]

You can use a FOR loop

For x=4 to 500
......Range("C" & x)....Range("K" & x)...
Next x

You code doesn't match your aim. You are checking col C is equal to the combobox entry and then filling in the text box on the form with the value in col K. Your aim was to fill in K with what's in the textbox so you might want to reverse it.

Fen

 

[blounty]

Hi Guys,

Thanks for the quick replies, i have tried the following but my command button does nothing .

Private Sub CommandButton1_Click()

Dim i As Long
Dim src As String
Dim dst As String

With Worksheets("Proposal")
    For i = 4 To 500
        src = "C" & i
        dst = "K" & i
        If .Range(src) = UserForm4.ComboBox1.Value Then
            Worksheets("Proposal").Range(dst) = UserForm4.TextBox2.Value
            Exit For
        End If
    Next i
End With

End Sub

my head hurts! thanks guys!

 

[Fenrirshowl]

Looks like the "Exit For" will stop the macro on the first change. (If the if statement is true for i=4 then you don't even get to x=5)

 

[blounty]

Hi Fenrirshowl,

I get the concept of what your saying, how could i look at changing the code to rectify this, i tried moving the Exit For between the Next i and End with but this gave me an error.

thanks for your help! i really appreciate your time.

 

[Fenrirshowl]

Looking at where you were working, did you remove the End IF instead of the Exit For?

The removal of the Exit For will do nothing as there is nothing dependent upon it, the removal of the End If means you have an unclosed IF statement which will throw you an error.

 

[blounty]

I tried the following:

Private Sub CommandButton1_Click()

Dim i As Long
Dim src As String
Dim dst As String

With Worksheets("Proposal")
    For i = 4 To 500
        src = "C" & i
        dst = "K" & i
        If .Range(src) = UserForm4.ComboBox1.Value Then
            Worksheets("Proposal").Range(dst) = UserForm4.TextBox2.Value
             End If
             Next i
            Exit For
End With

End Sub

 

[Fenrirshowl]

Get rid of the Exit For entirely, you are currently saying to exit a For loop that you are already out of.

The For....Next now continues until it finishes at 400, THEN you are telling it to exit the loop.

 

[blounty]

Ok my coode is now:

Private Sub CommandButton1_Click()

Dim i As Long
Dim src As String
Dim dst As String

With Worksheets("Proposal")
    For i = 4 To 500
        src = "C" & i
        dst = "K" & i
        If .Range(src) = UserForm4.ComboBox1.Value Then
            Worksheets("Proposal").Range(dst) = UserForm4.TextBox2.Value
             End If
             Next i
            
End With

End Sub

[/cool]
still get no action from my command button. this is so frustrating.... thank you for replying s quickly. regards

 

[Fenrirshowl]

Ok, so there must be something wrong with the IF statement.

Add a line before it:

If i=4 then Msgbox(.Range(src) & "," & UserForm4.ComboBox1.Value)

This will show you what you are dealing with. Actually, substitute i=4 with whatever value should give you the first hit and see what the two values are. Look out for spaces that may not be obvious.

 

[blounty]

Ok using the extra If statement with i=4 i get a msgbox with "101,101"

But Nothing appears in my K Column.

Any Ideas?

 

[blounty]

Ok i changed the value in combobox to 102 but i still get "101,102" so it would appear that Range(src) is not changing. any ideas?

 

[Fenrirshowl]

Can only think there is a problem with comparing numbers with text.

If you are only dealing with numbers try using a CINT(number) function to force the values into being numbers. If you still get nothing, add in a

If .Range(src) = CINT(UserForm4.ComboBox1.Value) Then
MSGBOX UserForm4.TextBox2.Value
Worksheets("Proposal").Range(dst) = UserForm4.TextBox2.Value
End If

 

[blounty]

You cracked it! neede to use the CInt function

Private Sub CommandButton1_Click()
Dim i As Long
Dim src As String
Dim dst As String

With Worksheets("Proposal")
    For i = 4 To 500
        src = "C" & i
        dst = "K" & i
            If .Range(src) = CInt(UserForm4.ComboBox1.Value) Then
            Worksheets("Proposal").Range(dst) = UserForm4.TextBox2.Value
             End If
             Next i
            
End With

End Sub

Thank you so much!!!
Have a star!!!

 

[Fenrirshowl]

Apologies for not thinking about it before.

Fen

 

[Fenrirshowl]

One last thing - if you are using non-integers anywhere in the comparison use CDbl rather than CInt.