excel VBA function

Question

I have form with three text to put this values on same row to different column. this works fine. I have created this function to verify if combination of text1 and text2 exists on some row yet. but it verify only the first of condition.

Function kontr() As Boolean
Dim endRow As Long
endRow = ActiveSheet.Range("C:C").End(xlUp).Row
For i = 1 To endRow
    If (ActiveSheet.Range("C" & i).Value <> UserForm1.TextBox1.Text And ActiveSheet.Range("F" & i).Value <> UserForm1.TextBox2.Text) Then

   kontr = True
Else
    MsgBox ("Exists!")
    kontr = False
    End If

Next i
End Function

can someone help with this?

instead of a for loop, you could use COUNTIFS(). Probably simpler and faster. — Patrick Honorez
– Patrick Honorez, Commented Feb 18, 2019 at 11:06
Are you sure the logic is correct? From the description it sounds to me like you want If (ActiveSheet.Range("C" & i).Value = UserForm1.TextBox1.Text And ActiveSheet.Range("F" & i).Value = UserForm1.TextBox2.Text) Then kontr = True Exit Function. — Andrew Morton
– Andrew Morton, Commented Feb 18, 2019 at 11:09

Siddharth Rout · Accepted Answer · 2019-02-18 11:25:11Z

Use .Find. It is faster. Your code is checking in the same row at the same time. I am guessing that you want to check if the values exists anywhere in the respective columns.

Is this what you are trying?

Public Function kontr() As Boolean
    Dim ws As Worksheet

    Set ws = ActiveSheet

    With ws
        Set aCell = .Columns(3).Find(What:=UserForm1.TextBox1.Text, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        Set bCell = .Columns(5).Find(What:=UserForm1.TextBox2.Text, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        If aCell Is Nothing And bCell Is Nothing Then
            kontr = True
        Else
            MsgBox ("Exists!")
            kontr = False
        End If
    End With
End Function

Xabier · Accepted Answer · 2019-02-18 11:03:13Z

1

I believe that it is to do with the way you calculated the endrow, amend that line to:

endrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row

To get the last row on Column C with data.

answered Feb 18, 2019 at 11:03

Xabier

7,7251 gold badge11 silver badges21 bronze badges

Comments

hc_dev · Accepted Answer · 2019-02-18 11:41:51Z

Why not rewrite condition in positive boolean logic ?

Suppose you want to check if both UserForm1 texts (TextBox1 and TextBox2) do match the values of two columns (C and F). In other words you want to check if the combination exists in some row.

Then you should check for:

If (ActiveSheet.Range("C" & i).Value = UserForm1.TextBox1.Text _
  And ActiveSheet.Range("F" & i).Value = UserForm1.TextBox2.Text) Then
   matchFound = True
   MsgBox ("Combination Exists in C and F!")
   Exit For
Else
   matchFound = False;
End If

What about aborting the filtering after found a match ?

The Exit For will exit the for-loop after match was found. See Excel VBA - exit for loop.

Looping through ranges

Dim C_to_F_Columns As Range, cRow As Range
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Set C_to_F_Columns = Range("C2:F" & lastrow)

For Each cRow In C_to_F_Columns.Rows
   cValue = cRow.Offset(0,0).Value
   fValue = cRow.Offset(0,2).Value

   ' insert your matching logic here
Next

See looping through a range of 2 columns.

bright rajesh · Accepted Answer · 2019-02-18 11:00:43Z

0

Not sure might be there are whitespaces in the text, so this wasn't satisfying the condition

Try using trim TRIM( text ) method

answered Feb 18, 2019 at 11:00

bright rajesh

413 bronze badges

1 Comment

hc_dev Over a year ago

good point! Comparing _text_s can be always spiced-up using some trimming or even ignoreCase using UCase or LCase string-functions: equalsIgnoreCase = ( LCase(str1) = LCase(str2) )

Collectives™ on Stack Overflow

excel VBA function

4 Answers 4

Comments

Comments

Why not rewrite condition in positive boolean logic ?

What about aborting the filtering after found a match ?

Looping through ranges

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Why not rewrite condition in positive boolean logic ?

What about aborting the filtering after found a match ?

Looping through ranges

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related