none
VBA: Vlookup textbox value in sheet and return Max value from the range to a tempVariable RRS feed

  • Question

  • Hi,

    I have textboxs on userform when the value is given, the vba code must lookup textbox1.value in col A and return max value from column R. Column R is a counter with  formula =IF(A3<>"",COUNTIF(A:A,A3),"")

    VBA must return the max value to compare with another textbox.value.

    For example, If textbox1.value = "0001" then vlookup(A:A, MAx(A3:U,17,0) ) so return value must be 2. Similarly textbox2.value ="abcf" then retrun value must be 1 (I will write the same code in textbox2_change() ).  Textbox1.value & textbox2.value can be given at the same time or 1 of them can be blank. So on textbox1_change() must return the max value  found set to a temp_variable

    I have issues in the vlookup with Max function & is there a better way that I can have counters & return value? Currently the counter columns are dragged till 1000 rows but over a period the calculation may cause delay on worksheet 

    Regards,
    NewBInVb

    Private Sub AliasX_Change()
    
    Dim cntAliasx As Integer
    Dim comparex As Integer
    Dim sht As Worksheet
    
    Set sht = ThisWorkbook.Worksheets("Paired")
    
    'relaccvalue is textbox to compare how many times allowed
    If RelAccValue = "" Then
        MsgBox "Provide value of relationship accepted"
    Else
    'get max counter for textbox1.value & set to a variable
       cntAliasx = Application.WorksheetFunction.VLookup(Me.AliasX.Value, Max(sht.Range("A3:U")), 17, False)
       MsgBox cntAliasx
       comparex = cntAliasx
    ElseIf comparex < RelAccValue.Value Then
       AliasFindX
    Else
    MsgBox "comparex is greater so select other"
    End If
    End Sub

    NewBInVB



    • Edited by NewBInCoding Thursday, November 23, 2017 7:42 AM
    Monday, November 7, 2016 1:04 PM

Answers

  • Hi,

    This statement is wrong. Can you answer the following:

    1. Max is a worksheet function hence can not be used directly as Max(...). 

    2. Range ("A3:U") is not a valid range. Please let me know in which range you want to find out the Max.


    Vish Mishra

    • Marked as answer by NewBInCoding Tuesday, November 8, 2016 9:38 AM
    Monday, November 7, 2016 1:32 PM
  • Hi NewBInVB,

    I try to test the code that you had posted in the original post.

    I find mistakes with the code that I mentioned below. you can check it and correct it in your code.

    (1) you had wrote an if..else condiotion. like below.

    If RelAccValue = "" Then
        MsgBox "Provide value of relationship accepted"
    Else
    'get max counter for textbox1.value & set to a variable
       cntAliasx = Application.WorksheetFunction.VLookup(Me.AliasX.Value, Max(sht.Range("A3:U")), 17, False)
       MsgBox cntAliasx
       comparex = cntAliasx
    ElseIf comparex < RelAccValue.Value Then
       AliasFindX
    Else
    MsgBox "comparex is greater so select other"
    End If

    the flow of if..else condition is wrong.

    you can see that first you wrote IF block.

    then you wrote else block.

    then you wrote Else..If block.

    and in the last again else block.

    you need to write first If block.

    then write else..if block with condition.

    then write else block.

    do not write multiple else block.

    (2) I had notice that you had pass the Text values in "Alias" Column "A".

    The textbox on the userform will also return the text value.

    but in the line mentioned below you are comparing integer variable with string.

    ElseIf comparex < RelAccValue.Value Then
       

    you can see that you had declare the comparex as an integer.

    so how will it check for "<" value. here you will get an error.

    (3) As other community member already pointed you about this.

    cntAliasx = Application.WorksheetFunction.VLookup(1, Sheet1(sht.Range("A3:U")), 17, False)

    pass the valid range as a parameter.

    Are you going to match value with "A3:U ?" maybe not.

    I assume that you only want to match the value with one column. not with this much large range.

    visit the link mentioned below for more information regarding vlookup method.

    WorksheetFunction.VLookup Method (Excel)

    please correct these mistakes and again debug your code and let us know about the results you get.

    try to post your updated code. and we will try to help you further to solve your issue.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, November 8, 2016 3:31 AM
    Moderator
  • Hi Vish,

    Thanks for the quick response. I have written this code to solve

    Set userange = sht.Range("A3", sht.Range("U" & Rows.Count).End(xlUp))
        cntAliasx = Application.WorksheetFunction.VLookup(strfind, userange, 18, False)



    NewBInVB



    • Edited by NewBInCoding Tuesday, November 8, 2016 10:39 AM
    • Marked as answer by NewBInCoding Wednesday, November 9, 2016 12:22 PM
    Tuesday, November 8, 2016 9:37 AM

All replies

  • Hi,

    Can you specify the issue with vlookup which you are facing?


    Vish Mishra

    • Marked as answer by NewBInCoding Monday, November 7, 2016 1:20 PM
    • Unmarked as answer by NewBInCoding Monday, November 7, 2016 1:20 PM
    Monday, November 7, 2016 1:12 PM
  • Hi Vish,

    The issue is am not able to return the max value from column R(count how many time col A value is present) into the tempvariable when textbox1.value = in col A.  col A can hold more than 1 textbox1.value. 

    cntAliasx = Application.WorksheetFunction.VLookup(Me.AliasX.Value, Max(sht.Range("A3:U")), 17, False)


    NewBInVB


    Monday, November 7, 2016 1:22 PM
  • Hi,

    This statement is wrong. Can you answer the following:

    1. Max is a worksheet function hence can not be used directly as Max(...). 

    2. Range ("A3:U") is not a valid range. Please let me know in which range you want to find out the Max.


    Vish Mishra

    • Marked as answer by NewBInCoding Tuesday, November 8, 2016 9:38 AM
    Monday, November 7, 2016 1:32 PM
  • Hi.

    1. Max is a worksheet function so I corrected that in the code now.
    2.  The range I have given is value in sheet Paired with columns A to U. Basically I have the sheet "Paired" from column A to U

    3. Col A has values where the count is generated fro each row in col R = IF(A3<>"",COUNTIF(A:A,A3),""). The formula is dragged till end.

    4. col A may have a1 =001, a2 = 002, a3 = 0001 so r1 = 2, r2= 1, r3 = 2 is the result from point 3

    5. in userform when textbox1.value is given 001, the value is lookup in col A & return 2 

    6. In case (new row is inserted by other function) a4 = 0001 then r4 = 3 then when textbox1.value = 0001 return 3 

     

    NewBInVB




    Monday, November 7, 2016 2:01 PM
  • Hi.

    In your code you are using range as "A3:U"  this is not a valid range. Based on your data I can see that you can Range("A3:U9") or Range("A3:U100") if you think your data may go up to 100 rows or so.


    Vish Mishra

    Monday, November 7, 2016 2:07 PM
  • Hi,

    Did it solve your question? If yes.. kindly mark them as Answered in order to keep the forum neat.


    Vish Mishra

    • Marked as answer by NewBInCoding Tuesday, November 8, 2016 7:23 AM
    • Unmarked as answer by NewBInCoding Tuesday, November 8, 2016 7:23 AM
    Monday, November 7, 2016 3:02 PM
  • Hi NewBInVB,

    I try to test the code that you had posted in the original post.

    I find mistakes with the code that I mentioned below. you can check it and correct it in your code.

    (1) you had wrote an if..else condiotion. like below.

    If RelAccValue = "" Then
        MsgBox "Provide value of relationship accepted"
    Else
    'get max counter for textbox1.value & set to a variable
       cntAliasx = Application.WorksheetFunction.VLookup(Me.AliasX.Value, Max(sht.Range("A3:U")), 17, False)
       MsgBox cntAliasx
       comparex = cntAliasx
    ElseIf comparex < RelAccValue.Value Then
       AliasFindX
    Else
    MsgBox "comparex is greater so select other"
    End If

    the flow of if..else condition is wrong.

    you can see that first you wrote IF block.

    then you wrote else block.

    then you wrote Else..If block.

    and in the last again else block.

    you need to write first If block.

    then write else..if block with condition.

    then write else block.

    do not write multiple else block.

    (2) I had notice that you had pass the Text values in "Alias" Column "A".

    The textbox on the userform will also return the text value.

    but in the line mentioned below you are comparing integer variable with string.

    ElseIf comparex < RelAccValue.Value Then
       

    you can see that you had declare the comparex as an integer.

    so how will it check for "<" value. here you will get an error.

    (3) As other community member already pointed you about this.

    cntAliasx = Application.WorksheetFunction.VLookup(1, Sheet1(sht.Range("A3:U")), 17, False)

    pass the valid range as a parameter.

    Are you going to match value with "A3:U ?" maybe not.

    I assume that you only want to match the value with one column. not with this much large range.

    visit the link mentioned below for more information regarding vlookup method.

    WorksheetFunction.VLookup Method (Excel)

    please correct these mistakes and again debug your code and let us know about the results you get.

    try to post your updated code. and we will try to help you further to solve your issue.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, November 8, 2016 3:31 AM
    Moderator
  • I have not yet solved it. I will try it today.

    NewBInVB

    Tuesday, November 8, 2016 7:23 AM
  • Hi Deepak,

    Thanks for the code analysis. I have made it much simple way to solve it.  


    NewBInVB

    Tuesday, November 8, 2016 8:30 AM
  • Hi Vish,

    Thanks for the quick response. I have written this code to solve

    Set userange = sht.Range("A3", sht.Range("U" & Rows.Count).End(xlUp))
        cntAliasx = Application.WorksheetFunction.VLookup(strfind, userange, 18, False)



    NewBInVB



    • Edited by NewBInCoding Tuesday, November 8, 2016 10:39 AM
    • Marked as answer by NewBInCoding Wednesday, November 9, 2016 12:22 PM
    Tuesday, November 8, 2016 9:37 AM