I was hoping somebody would be able to take a look at my spreadsheet and help me out with a problem I was having. I have tried to make the sheet so that I would have a searchbox in Sheet 1, that would look at the data in Sheet two and Return the results in Sheet 3.
The Search would look at Column A in Sheet 2 and Return all Instances of that Result in Sheet 3 (Including all other data in the Row).
If anyone was able to tell me the code that would help, I am very new to VB coding and had been trying to tinker with the code that I found on a website
'Standard Sheet Module code, like: Sheet1.
Dim Message$, Title$, Default$, myCode$
Message = "Enter Workstation/Laptop Number:" ' Set prompt.
Title = "Workstation Search" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
myCode = InputBox(Message, Title, Default)
MsgBox "Var1: value is " & myCode
ifFound = False
Application.ScreenUpdating = False
ifFound = True
'Check data sheet for data wanted!
For Each r In Worksheets("Sheet2").UsedRange.Columns
n = r.Column
If Worksheets("Sheet2").Cells(1, n) = myCode Then
MsgBox "Var2: value is " & ifFound
ifFound = True
'Copy the found data from the starting row = myStart to
'the ending row = myFinish for the Found Date [myDate]
'to Sheet2 in column "C" change as needed!
'Start data import just below any entery in this column!
Worksheets("Sheet2").Range(Cells(2, n), Cells(4, n)).Copy _
If ifFound = False Then MsgBox "Not Found!"
Application.CutCopyMode = True
Application.ScreenUpdating = True
What I had managed to do so far was prove that the Data I entered was being stored in the variable but had not really got any further than that.
I have got a lot further with this after checking some other code on the internet and have somehow managed to come up with the spreadsheet attached, at the moment, on opening the sheet, it will bring up the box to search, I click in my search Criteria (eg LT00359) and then click on ok, this will copy the data into sheet 2 from Sheet 1 that matches that criteria, however this only works once. I had set another procedure to call the module code again upon reselecting the Search sheet, this works but when i put the search criteria in again (eg LT00010) it appears to run but does not do anything with the data, as soon as I close and open again it will again work once.
In fact, testing it now, that has now also stopped working, it only appears to work some of the time, is there something that I am doing wrong with this.
It seems it only appears to work reliably when I run it as the macro in the module, and not when I try to load it on events.
After a bit more testing, I have set the macro to run off a button from Sheet 1, Search sheet itsellf has now gone, and this does work everytime, however I have been unable to edit the macro so that it clears the data in sheet 2 before it runs again so that If the second search has less data, it does not keep the old data below it on Sheet 2. Is anybody able to assist with this.
Attached is my Excel VBA solution to this post. From Sheet1, it searches Sheet2 for the entered target, and adds found target data, 3 columns in my example, to Sheet3. It assumes Sheet2 has no blanks in column A within the data rows. I hope this will help.
Private Sub cmdSearch_Click()
Dim i As Integer, j As Integer, rowCount As Integer
Dim arrValues As Variant
strTarget = InputBox("Enter the search target")
If strTarget = "" Then Exit Sub
rowCount = Sheets(3).UsedRange.Rows.Count
'clear all but the heading row on Sheet3
If rowCount > 1 Then Sheets(3).Range("A2:C" & rowCount).ClearContents
i = 2 'or set i to the appropriate starting data row for Sheet2
j = 2 'starting data row on Sheet3
While Sheets(2).Cells(i, 1) <> ""
If CStr(Sheets(2).Cells(i, 1).Value) = strTarget Then
'put values from 3 columns on Sheet2 into array
arrValues = Sheets(2).Range("A" & i & ":C" & i).Value
'put the 3 array values in columns A,B,C in Sheet3
Sheets(3).Range("A" & j & ":C" & j) = arrValues
j = j + 1
i = i + 1
If j = 2 Then
MsgBox "No data found for the target " & strTarget