VBA Find value and return value from adjacent cell

MidzX

New Member
Joined
Jul 6, 2017
Messages
7
I've looked through a lot of other questions that are quite close to what I'm trying to achieve but aren't really the true outcome I'm looking for so my apologies in advance if this seems too close to a duplicate question.


Situation


I've got a Excel workbook with multiple sheets in. One of these sheets is functioning as a master 'search' function which is very easy to use for an end-user.


End Goal


What I'm trying to do is have a VBA macro that when an end-user inputs value into "B3" for example and hits a command button, the script would take that value from whatever is in B3 and try to locate that value(match) within column A on a hidden sheet called 'locationData' once it's found a match return whatever is in column B from 'locationData' (which happens to be a hyperlink to a file). There can be multiple results however so it would have to continue the check through to the end.


So for example here is a set of data that you would find on locationData


| A | B
|------|------------
| 1224 | ..//path/1 |
| 1224 | ..//path/2 |
| 1464 | ..//path/ |


In this above example I'd need a user that when they are on the Overview sheet be able to put 1224 into cell B3 and then press a commandbutton (Search or Go or similar) and then it would return the ..//path/1 and ..//path/2 into B4 and B5 respectively. Of course if it found more it would continue down or just stop here.


I've tried various things but am back at the drawing board now so I'm willing to try anything here. Let me know what you think might be the best approach and regardless of whether or not I've tried it already I'll give it another go and update.


Thanks in advance! I'd attach an example worksheet here but unfortunately I don't know how and I didn't want to upload to a random third party and include the link in case that was against the rules.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
380
Note there are 2 versions of this

Private Sub CommandButton1_Click() ' Version will keep a running tally
Private Sub CommandButton1_Click() ' Version to replace values

Code:
[COLOR=#ff0000]Private Sub CommandButton1_Click() [/COLOR]' Version will keep a running tally
Dim sValue As String ' Search Value
' Determine what your searching for
sValue = Sheets("Search Sheet").Range("B3").Value


Dim hRange As String
' Get range to search in Hidden Sheet
hRange = Sheets("Hidden Sheet").Cells(Rows.Count, 1).End(xlUp).Row


'Determine how to sort the sheet for best performance
Dim hValue As String ' High Search Range Value
Dim lValue As String ' Low Search Range Value


MiddleNum = WorksheetFunction.Median(Sheets("Hidden Sheet").Range("A1:A" & hRange))


' This will allow it to reach the results faster and exit the loop once it is done
' finding the values rather than running through the remainder of the list.


If sValue > MiddleNum Then


ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Add Key:=Range( _
    "A1:A" & hRange), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Hidden Sheet").Sort
    .SetRange Range("A1:C" & hRange)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With


Else


ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Add Key:=Range( _
    "A1:A" & hRange), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Hidden Sheet").Sort
    .SetRange Range("A1:C" & hRange)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With


End If


'Loop through that range and check for values
For i = 1 To hRange


If sValue = Sheets("Hidden Sheet").Range("A" & i).Value Then


sRange = Sheets("Search Sheet").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Hidden Sheet").Range("B" & i).Copy Destination:=Sheets("Search Sheet").Range("B" & sRange + 1)


End If


Next i


End Sub
Code:
[COLOR=#ff0000]Private Sub CommandButton1_Click()[/COLOR] ' Version to replace values


Dim sValue As String ' Search Value
' Determine what your searching for
sValue = Sheets("Search Sheet").Range("B3").Value


[COLOR=#ff0000]Do Until Range("B4").Value = "" Or Range("B4").Value = Empty
Range("B4").Delete xlShiftUp
Loop[/COLOR]


Dim hRange As String
' Get range to search in Hidden Sheet
hRange = Sheets("Hidden Sheet").Cells(Rows.Count, 1).End(xlUp).Row


'Determine how to sort the sheet for best performance
Dim hValue As String ' High Search Range Value
Dim lValue As String ' Low Search Range Value


MiddleNum = WorksheetFunction.Median(Sheets("Hidden Sheet").Range("A1:A" & hRange))


' This will allow it to reach the results faster and exit the loop once it is done
' finding the values rather than running through the remainder of the list.


If sValue > MiddleNum Then


ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Add Key:=Range( _
    "A1:A" & hRange), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Hidden Sheet").Sort
    .SetRange Range("A1:C" & hRange)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With


Else


ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Add Key:=Range( _
    "A1:A" & hRange), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Hidden Sheet").Sort
    .SetRange Range("A1:C" & hRange)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With


End If


'Loop through that range and check for values
For i = 1 To hRange


If sValue = Sheets("Hidden Sheet").Range("A" & i).Value Then


sRange = Sheets("Search Sheet").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Hidden Sheet").Range("B" & i).Copy Destination:=Sheets("Search Sheet").Range("B" & sRange + 1)


End If


Next i


End Sub
 
Last edited:

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
380
Just in case :)
Note: You will need to replace the text "Search Sheet" and "Hidden Sheet" with your sheet names.
 

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
380
Terribly sorry, realized I forgot to add something to that to actually leave the loop once it is done finding the values

Code:
Private Sub CommandButton1_Click() ' Version will keep a running tally
Dim sValue As String ' Search Value
' Determine what your searching for
sValue = Sheets("Search Sheet").Range("B3").Value

Dim hRange As String
' Get range to search in Hidden Sheet
hRange = Sheets("Hidden Sheet").Cells(Rows.Count, 1).End(xlUp).Row

'Determine how to sort the sheet for best performance
Dim hValue As String ' High Search Range Value
Dim lValue As String ' Low Search Range Value

MiddleNum = WorksheetFunction.Median(Sheets("Hidden Sheet").Range("A1:A" & hRange))


' This will allow it to reach the results faster and exit the loop once it is done
' finding the values rather than running through the remainder of the list.


If sValue > MiddleNum Then

ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Add Key:=Range( _
    "A1:A" & hRange), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Hidden Sheet").Sort
    .SetRange Range("A1:C" & hRange)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Else

ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Add Key:=Range( _
    "A1:A" & hRange), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Hidden Sheet").Sort
    .SetRange Range("A1:C" & hRange)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End If

'Loop through that range and check for values
For i = 1 To hRange

If sValue = Sheets("Hidden Sheet").Range("A" & i).Value Then
[COLOR=#ff0000]Found = True

Do Until Found = False[/COLOR]
If sValue = Sheets("Hidden Sheet").Range("A" & i).Value Then
sRange = Sheets("Search Sheet").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Hidden Sheet").Range("B" & i).Copy Destination:=Sheets("Search Sheet").Range("B" & sRange + 1)
i = i + 1
Else
Found = False
End If
Loop

If Found = False Then Exit Sub

End If

Next i

End Sub
Code:
Private Sub CommandButton1_Click() ' Version to replace values

Dim sValue As String ' Search Value
' Determine what your searching for
sValue = Sheets("Search Sheet").Range("B3").Value

Do Until Range("B4").Value = "" Or Range("B4").Value = Empty
Range("B4").Delete xlShiftUp
Loop

Dim hRange As String
' Get range to search in Hidden Sheet
hRange = Sheets("Hidden Sheet").Cells(Rows.Count, 1).End(xlUp).Row

'Determine how to sort the sheet for best performance
Dim hValue As String ' High Search Range Value
Dim lValue As String ' Low Search Range Value

MiddleNum = WorksheetFunction.Median(Sheets("Hidden Sheet").Range("A1:A" & hRange))

' This will allow it to reach the results faster and exit the loop once it is done
' finding the values rather than running through the remainder of the list.

If sValue > MiddleNum Then

ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Add Key:=Range( _
    "A1:A" & hRange), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Hidden Sheet").Sort
    .SetRange Range("A1:C" & hRange)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Else

ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Hidden Sheet").Sort.SortFields.Add Key:=Range( _
    "A1:A" & hRange), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Hidden Sheet").Sort
    .SetRange Range("A1:C" & hRange)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End If

'Loop through that range and check for values
For i = 1 To hRange

If sValue = Sheets("Hidden Sheet").Range("A" & i).Value Then
Found = True

Do Until Found = False
If sValue = Sheets("Hidden Sheet").Range("A" & i).Value Then
sRange = Sheets("Search Sheet").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Hidden Sheet").Range("B" & i).Copy Destination:=Sheets("Search Sheet").Range("B" & sRange + 1)
i = i + 1
Else
Found = False
End If
Loop

If Found = False Then Exit Sub

End If

Next i

End Sub
 

Forum statistics

Threads
1,136,334
Messages
5,675,178
Members
419,552
Latest member
jsanjur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top