Results 1 to 4 of 4

Thread: Question regarding Offset
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2009
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Question regarding Offset

    Hello folks,

    im a VB novice but have taken on a project at work to put some VB code behind an excel spreadsheet. I hacked some code that I found on the web to try and do what I need.

    I need the code to create a find box into which a search string is input. The code should find the string at a particular cell, then copies this cell and four cells to the right to a location in sheet 2 of the same workbook

    The code runs but comes back with a runtime 424 error. I believe the issue is being caused by "nextCell" but I cant see why.

    Any support would be appreciated

    Sub FindStrings()
    Dim firstCell, nextCell, stringToFind As String
    ' Show an input box and return the entry to a variable.
    stringToFind = _
    Application.InputBox("Enter J Number and Stage Number. For Example J1234 ST1", "Search String")
    ' Set an object variable to evaluate the Find command.
    Set firstCell = Cells.Find(what:=stringToFind, lookat:=xlWhole, _
    searchdirection:=xlPrevious)
    ' If the string is not found, show this message box.
    If firstCell Is Nothing Then
    MsgBox "Search Value Not Found.", vbExclamation
    Else
    ' Otherwise, find the next occurrence of the search text.
    nextCell = _
    Cells.FindNext(after:=Range(firstCell.Address)).Address
    ' Show its address in a message box.
    Range(nextCell, nextCell.Offset(0, 4)).Copy
    Sheets("Sheet2").Select
    Range("B3").Select
    ActiveSheet.Paste

    End If
    End Sub

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question regarding Offset

    If you're going to do it like this, which I don't recommend, you need to treat your Offset as a String. E.g.,

    MsgBox Range(Range("A1").Address).Offset(0, 4).Address

    *Untested*

    At a glance, you can't treat Strings like they're Objects.

  3. #3
    New Member
    Join Date
    Jan 2009
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Re: Question regarding Offset

    Thanks for the feedback. What other methods do you suggest to code this?

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Question regarding Offset

    Hello, you're welcome.

    Well, if I understand the issue properly, I'd set the subsequent finds as Ranges and work from there, e.g.,

    Code:
    Sub foo()
    Dim cl As Range
    Set cl = Range("A1")
    MsgBox cl(1, 2).Resize(, 4).Address
    End Sub
    There's a nice MSDN article on this issue, here:
    http://msdn.microsoft.com/en-us/library/ff196143.aspx

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
  •