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. — iDevlop, Feb 18 '19 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, Feb 18 '19 at 11:09

score 1 · Answer 1 · answered Feb 18 '19 at 11:03

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 '19 at 11:03

Xabier

7,587
1
8
20

score 1 · Accepted Answer · answered Feb 18 '19 at 11:25

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

score 1 · Answer 3 · answered Feb 18 '19 at 11:41

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.

score 0 · Answer 4 · answered Feb 18 '19 at 11:00

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 '19 at 11:00

bright rajesh

41
3

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) )` – hc_dev Feb 18 '19 at 11:56

excel VBA function

4 Answers4

Why not rewrite condition in positive boolean logic ?

What about aborting the filtering after found a match ?

Looping through ranges