Find All and Return related Value

[kcince]

I have a very simple spreadsheet with values in columns A, B, and C. I have, quite unsuccessfully, been trying to write a macro to search column A for a value that is input via a text box and return the value of column B in the same row for each instance of the value input in the text box. The format of the returned values needs to be in a list format that I can copy and paste.

Any help would be greatly appreciated!

Thanks

[austenr]

Hi, can we see your workbook? Makes it easier for people to help you.

[kcince]

Please see the original message, I added a copy of the workbook.

[austenr]

Took your code and put it here for others
Sub test()
Dim wks As Worksheet
Dim myinput As String

With Sheet3
.Range(.Cells(2, 1), .Cells(Rows.Count, 17)).ClearContents



myinput = InputBox("Enter No. of weeks")

For Each wks In ThisWorkbook.Worksheets

'I would include the following code, if i did not wish for certain worksheets
'to be excluded from the macro

'If Not wks Is sheet3 _
And Not wks.Name = "sheet2" _
And Not wks.Name = "sheet4" Then

'##########################################################################

'*****My error lies here*****

Range("A1:A100").Select
Selection.Find(What:=myinput, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select

'##########################################################################

wks.Range("A" & ActiveCell.Row).Resize(, 10).Copy

Sheets("Sheet3").Select
Range("a5000").End(xlUp).Select
ActiveCell.Offset(1, 0).Select


ActiveSheet.Paste
'End If
Next wks

End With
End Sub

[austenr]

I entered the value in sheet1 A2 in the msgbox and got this in sheet3 2 times.

6380508 A1
2

[kcince]

I got that code off of this forum and was trying to adapt it for my use but I was quite unsuccessful at it.

Kcince

[Aussiebear]

Are you saying that it works now or not?

[kcince]

No it does not work. For example, the first value in A2 is 6380508. The value in B2 is A1 (this is the number that I am interested in). However 6380508 also occurs in A31 and the value next to it is A1.01. I am trying to figure out a way to find ALL instances of one user defined value in column A and return the value one cell to the right (column B). I would like to have all of the returned values either pasted into cells or in a textbox.

Thanks for trying to help.

[mdmackillop]

Add the sheet references to any ranges
Set a range to a found item
Test for the Found existence before copying and pasting
Avoid selecting ranges within the code. It's rarely required.


Sub test()
Dim wks As Worksheet
Dim myinput As String
Dim c As Range
With Sheet3
.Range(.Cells(2, 1), .Cells(Rows.Count, 17)).ClearContents

myinput = InputBox("Enter No. of weeks")
For Each wks In ThisWorkbook.Worksheets
'I would include the following code, if i did not wish for certain worksheets
'to be excluded from the macro
'If Not wks Is sheet3 _
And Not wks.Name = "sheet2" _
And Not wks.Name = "sheet4" Then
'##########################################################################
'*****My error lies here*****
Set c = wks.Range("A1:A100").Find(What:=myinput, After:=wks.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
'##########################################################################
If Not c Is Nothing Then
c.Resize(, 10).Copy Sheets("Sheet3").Range("a5000").End(xlUp).Offset(1, 0)
End If
Next wks
End With
End Sub

[kcince]

Thanks for the help so far.

I did find a bit of code on the internet and have adapted it for my needs. I know this code does what I need as i have tested it using a MsgBox to display the results one by one. There is one remaining problem though, I am trying to get all of the the results of the function to paste into colum D on the worksheet.

Please offer up any suggestions to help me get this working.