Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Find in a List and move to the right?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    North Wales Coast, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Evening All,
    I am looking to find a Value in a list and then move along the row, say 5 cells to the right and paste a value into that cell.
    I have looked at a few ways of going about this but is there a 'smarter' way than considering Looping through the list of Values?
    Any comments, well most(!), appreciated.
    Cheers
    S

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you're using VBA to do this, have a look at the "Find" function in VBA help. There's a reasonable example there. It's definitely faster than looping through cells. (combine this with "Offset" and you'll be good to go)

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    North Wales Coast, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mark,
    Thank you for your swift response.
    I have had a look at the 'Find Method' Example in VBA Help and have, once again, discovered why I deal with Accounts rather than VBA programming!
    Do you happen to know of any other source of relatively easy examples and explanation so I may try to get my head around the Find Method?
    Thanks once again
    S

    [ This Message was edited by: Sam40mUK on 2002-04-18 15:42 ]

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yeah, I said that the help file example was reasonable. I don't like it myself, but it's usually a decent place to start. I've knocked up an example for you and I hope it makes enough sense to get you started. I started a new workbook and on Sheet1 I put in the values 1 to 15 in column A starting in A1. I then made this code to find the value "10" and put a message in a box 5 columns to the right.


    'Declare variables
    Dim oTargetRange As Range 'This will be the range we're searching.
    Dim sTargetText As String 'This will be text that we're trying to find
    Dim oFoundRange As Range 'This will be the range (cell) that contains the "sTargetText"
    Dim iColumnOffset As Integer 'This will be the number of columns we want to offset by
    Dim sColumnOffsetText As String 'This is the text you want to put in the offset column if the target text is found

    'Initialise variables
    Set oTargetRange = Sheets("Sheet1").UsedRange.Columns("A")
    sTargetText = "10"
    iColumnOffset = 5
    sColumnOffsetText = "Very Long Variable Name"

    'Begin Error trapping (for when we screw up)
    On Error GoTo ERR_NOT_FOUND

    Set oFoundRange = oTargetRange.Find(What:=sTargetText, LookIn:=xlValues, lookat:=xlWhole)

    MsgBox "The text " & Chr(34) & sTargetText & Chr(34) & "was found in " & oFoundRange.Address & "."
    oFoundRange.Offset(0, iColumnOffset).Value = sColumnOffsetText


    Exit Sub
    ERR_NOT_FOUND:
    MsgBox "The text " & Chr(34) & sTargetText & Chr(34) & " could not be found in the specified range.", vbInformation, "Not Found"

    End Sub


    It shouldn't take too much for you to adapt it to your specific needs. (I hope).

    HTH

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    North Wales Coast, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Firstly thanks Mark,
    I appreciate your time in sorting out a further example for me.
    I did spend some time looking at VBA Help and found it for a novice rather .. there you go .. take it or leave it!
    I am sure that others on this board will agree that a well explained example with the ability to ask, what maybe considered 'silly' questions is an immensely useful way of trying to understand Excel and glimpsing the use of VBA in the 'real world'.
    Thanks one again
    S

  6. #6
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No problems at all. I know that some parts of the help file are better than others and if it was perfect then this board wouldn't exist.

    Also, I wasn't sure of your expertise and usually I don't do lazy answers, but I think I had one eye on the golf course yesterday. (additionally, we have all went through what you're going through)

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I really liked your example, the sTargettext you set is for a set expression, if I wanted the sTargetText to be based off a cell in another worksheet, how would that look?

    sTargetText = worksheets("SheetA").[J4].Value? Range? or something like that?

  8. #8
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 14:22, heather wrote:
    I really liked your example, the sTargettext you set is for a set expression, if I wanted the sTargetText to be based off a cell in another worksheet, how would that look?

    sTargetText = worksheets("SheetA").[J4].Value? Range? or something like that?
    Heather,

    I just saw your post today, you were right first time, but you may have figred that out by now:


    sTargetText = worksheets("SheetA").[J4].Value


    Although personally, I don't really like using e.g. [J4] for ranges. I'm too used to using "Range" like this:


    sTargetText = worksheets("SheetA").Range("J4").Value


    ...but I have used the []'s in posts here because it's just too ****ed tempting to use it in a small snippet of code.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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