none
vba code to search a range for a string and return the value to the right of the location of the value RRS feed

  • Question

  • I am looking to incorporate some code that will match a variable and return the value from the cell to the right of the matched name.

    so I am capturing a string variable called myVal from a combobox selection, I want to search for that string in column D of a sheet called "Validation" and capture that number into a variable called "NumberOfRegs"



    MEC

    Sunday, July 17, 2016 8:48 PM

Answers

  • For example:

        Dim rng As Range
        Set rng = Worksheets("Validation").Range("D:D").Find _
            What:=myVal, LookAt:=xlWhole)
        If rng Is Nothing Then
            MsgBox "Value not found!", vbInformation
            Exit Sub
        End If
        NumberOfRegs = rng.Offset(0, 1).Value


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, July 17, 2016 9:24 PM
  • Immediately below the line

    myVal = myDropDown.ControlFormat.List(myDropDown.ControlFormat.Value)

    add

        If myVal = "Choose One" Then
            MsgBox "Please make a selection, then try again.", vbExclamation
            Exit Sub
        End If


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, July 19, 2016 5:22 AM

All replies

  • For example:

        Dim rng As Range
        Set rng = Worksheets("Validation").Range("D:D").Find _
            What:=myVal, LookAt:=xlWhole)
        If rng Is Nothing Then
            MsgBox "Value not found!", vbInformation
            Exit Sub
        End If
        NumberOfRegs = rng.Offset(0, 1).Value


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, July 17, 2016 9:24 PM
  • Hi mecerrato,

    you had mentioned that ,"code that will match a variable and return the value from the cell to the right of the matched name."do you mean combobox contains the cell Address. you want to select the value from combobox then you want to find that address in column D and print the value in the right side cell that is contains by the cell that you had found.

    am I right or still I misunderstood something?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 18, 2016 1:18 AM
    Moderator
  • Deepak thanks for replying but the cells I was metnioning are not in the combobox and the solution provided by Vans worked.

    MEC

    Tuesday, July 19, 2016 12:54 AM
  • This worked thank you.

    Related question: In my combobox my first selection is "Choose One" when borrower makes selection from combobox the below code executes. But I also have a macro button that when pressed will read the value of the combobox and execute based on the value. The problem is if I hit the macro button and I left the combobox on the "Choose One" value the excel sheet freezes for a bit and I get an out of memory error. So I want to write something that says if the combobox value = "Choose One" then display a message box that says "You have to make a selection" and exits the code completely. I am not quite sure how to write it or where to put it.

    Here is my code:

    sub emailHLO()

    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Signature As String
    Dim mysht As Worksheet
    Dim myDropDown As Shape
    Dim myVal As String
    Dim RegRng As Range
    Dim PrevRegRng As Range
    Dim ManagerRng As Range

    Set mysht = ThisWorkbook.Worksheets("Pipeline")
    Set myDropDown = mysht.Shapes("Drop Down 261")
    myVal = myDropDown.ControlFormat.List(myDropDown.ControlFormat.Value)
    Set RegRng = Worksheets("Validation").Range("D:D").Find(What:=myVal, LookAt:=xlWhole)
    Set PrevRegRng = Worksheets("Validation").Range("D:D").Find(What:=myVal, LookAt:=xlWhole)
    Set ManagerRng = Worksheets("Validation").Range("D:D").Find(What:=myVal, LookAt:=xlWhole)

    '
    If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
      ActiveSheet.ShowAllData
    End If
    ActiveSheet.Range("$a$6:$AQ$1000").AutoFilter Field:=34, Criteria1:="<>Pre-Approval"
    ActiveSheet.Range("$A$6:$AQ$1000").AutoFilter Field:=1, Criteria1:=myVal
    NumberofRegs = RegRng.Offset(0, 1).Value
    PrevNumberofRegs = PrevRegRng.Offset(0, 2).Value
    Manager = ManagerRng.Offset(0, 3).Value

    Set rng = Nothing
    ' Only send the visible cells in the selection.
    Set rng = ActiveSheet.Range("a6", ActiveSheet.Range("H6").End(xlDown))

    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected. " & _
               vbNewLine & "Please correct and try again.", vbOKOnly
        Exit Sub
    End If
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
       .Display
    End With
    Signature = OutMail.HTMLBody
    strbody = "<br />" & ActiveSheet.Range("A1") & " " & ActiveSheet.Range("B1") & "<br />" & ActiveSheet.Range("A2") & Split(ActiveSheet.Range("B2").Text, ".")(0) & "<br />" & "Previous Month Registrations = " & PrevNumberofRegs & "<br />" & "Current Registrations MTD = " & NumberofRegs

    With OutMail
        .to = myVal
        .cc = Manager
        .Subject = "Your Net Reg Pipeline"
        .HTMLBody = "<BODY style=font-size:12.5pt;font-family:Calibri>" & "</p>" & strbody & RangetoHTML(rng) & Signature
        .Display
    End With
    On Error GoTo 0
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    'range("A7:AQ625").Borders.LineStyle = xlNone
    'range("A7:AQ625").Borders(xlEdgeLeft).LineStyle = xlNone
    'Selection.Borders.LineStyle = xlNone

    Set OutMail = Nothing
    Set OutApp = Nothing

    Exit Sub


    MEC

    Tuesday, July 19, 2016 1:14 AM
  • Immediately below the line

    myVal = myDropDown.ControlFormat.List(myDropDown.ControlFormat.Value)

    add

        If myVal = "Choose One" Then
            MsgBox "Please make a selection, then try again.", vbExclamation
            Exit Sub
        End If


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, July 19, 2016 5:22 AM
  • Thank you Vans, worked great.

    MEC

    Tuesday, July 19, 2016 11:25 AM