Consulting

Results 1 to 10 of 10

Thread: Find All and Return related Value

  1. #1
    VBAX Regular
    Joined
    May 2010
    Posts
    6
    Location

    Find All and Return related Value

    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
    Last edited by kcince; 05-18-2010 at 04:58 PM. Reason: To add the workbook

  2. #2
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,030
    Location
    Hi, can we see your workbook? Makes it easier for people to help you.
    Peace of mind is found in some of the strangest places.

  3. #3
    VBAX Regular
    Joined
    May 2010
    Posts
    6
    Location
    Please see the original message, I added a copy of the workbook.

  4. #4
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,030
    Location
    Took your code and put it here for others
    [VBA]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
    [/VBA]
    Peace of mind is found in some of the strangest places.

  5. #5
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,030
    Location
    I entered the value in sheet1 A2 in the msgbox and got this in sheet3 2 times.

    6380508 A1
    2
    Peace of mind is found in some of the strangest places.

  6. #6
    VBAX Regular
    Joined
    May 2010
    Posts
    6
    Location
    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

  7. #7
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,858
    Location
    Are you saying that it works now or not?
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  8. #8
    VBAX Regular
    Joined
    May 2010
    Posts
    6
    Location
    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.

  9. #9
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.

    [VBA]
    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

    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  10. #10
    VBAX Regular
    Joined
    May 2010
    Posts
    6
    Location

    Changed My approach

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •