200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Please Help! I have been trying for two days to get this
Set Rng = dataIDCol.Find(What:=searchValue, _
to work and I keep getting (Compile Error: Syntax error) Everything else works beautifully!!! If you could tell me how to fix this i would really appreciate it!
Hi MsBernie and welcome to the Forum.
That line should end with a right bracket (and possibly something else) but without context/ a file it's very difficult to reply. The line ends
Set Rng = dataIDCol.Find(What:=searchValue, _REVISION: From your full code, I see you have deleted or failed to copy the remainder of the line (and so the arguments needed by the .Find method). Please use the Select All below then Ctrl+C to copy this:
Set Rng = dataIdCol.Find(What:=searchValue, LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False)and use it to replace your line above. It should go from red (with error) to black in your revised code. Then run the macro.
Please note that the combination of " _" followed by Return can by used in VBA code to make it easier to read the arguments, so the tutorial may show (and you could use the code below instead of that above):
Set Rng = dataIdCol.Find(What:=searchValue, _
LookIn:=xlValues, _
Lookat:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False)where the combination of a space+underscore at the end tells VBA to include whatever is in the next line of code.
If that does NOT fix your problem, I supsect that your question relates to a paid course. If so, please don't post the whole code or file on the Forum. Instead you can ask the question directly to the course author Don by using the Contact link above (and sticking with the Subject selection "Premium Course Questions").
If it does not relate to one, please edit your question to include the full macro (using the CODE button) or preferably attach a representative Excel file (with no personal data) and use the Add Files.. button to attach it.
Sub Select_Data()
'Search the data repository worksheet and return the found record interger
Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim searchValue As Variant
Dim dataIdCol As Range
Dim recordRow As Integer
'Make some sheet variables so we can use those instead of hard-coding
Set sourceSheet = Sheets("Form")
Set dataSheet = Sheets("Data")
'Column that contains the value for which we will search.
Set dataIdCol = dataSheet.Range("A:A")
'Value to search for.
searchValue = InputBox("Input an Id.", "Record Search")
'Check if the user input a value and hit the OK button.
If searchValue <> vbNullString Then
'Value input, so search for it.
'Clear Data
sourceSheet.Range("F4").Value = ""
sourceSheet.Range("F6").Value = ""
'Search
Set Rng = dataIDCol.Find(What:=searchValue,)
'Check if a record was found.
If Not Rng Is Nothing Then
' Record Found!
' Get the row of the record.
recordRow = Rng.Row
' Put the records information back into the form.
sourceSheet.Range("F4").Value = dataSheet.Cells(recordRow, 1).Value
sourceSheet.Range("F6").Value = dataSheet.Cells(recordRow, 2).Value
Else
' Nothing found, tell the user.
MsgBox "Record not found."
End If
End If
End Sub