+ Reply to Thread
Results 1 to 4 of 4

How to use the "found" cellvalue in VB.

  1. #1
    Frank Groenendijk
    Guest

    How to use the "found" cellvalue in VB.

    Hi there, please, help required with VB
    I want to use a macro to copy the cell-contents from a row, found with the
    "Find" command using following code: (using an inputbox to get the
    Find-value).

    Dim SDorderno, Title, MyValue
    Message = "Fuctuurinvoer voor Salesordernr.>"
    Title = "Demo InputBox"
    MyValue = InputBox(Message, Title, Default)

    Columns("B:B").Select
    Selection.Find(What:=SDorderno, After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
    ActiveCell.Select
    ActiveCell.Offset(0, 1).Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Invoerscherm").Select
    Range("K10").Select
    ActiveSheet.Paste

    "SDorderno" contains the value to find in column B.
    However, the cell with the matching data is not activated. Instead, the
    column B remains activated
    How can I get the required cell to be activated?
    Tx.
    Frank





  2. #2
    ben
    Guest

    RE: How to use the "found" cellvalue in VB.

    you could try using this

    row1 = activecell.row
    cells(row1,1).select
    that should select the cell to the left of the matching data


    "Frank Groenendijk" wrote:

    > Hi there, please, help required with VB
    > I want to use a macro to copy the cell-contents from a row, found with the
    > "Find" command using following code: (using an inputbox to get the
    > Find-value).
    >
    > Dim SDorderno, Title, MyValue
    > Message = "Fuctuurinvoer voor Salesordernr.>"
    > Title = "Demo InputBox"
    > MyValue = InputBox(Message, Title, Default)
    >
    > Columns("B:B").Select
    > Selection.Find(What:=SDorderno, After:=ActiveCell, LookIn:=xlValues, _
    > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    > MatchCase:=False).Activate
    > ActiveCell.Select
    > ActiveCell.Offset(0, 1).Activate
    > Application.CutCopyMode = False
    > Selection.Copy
    > Sheets("Invoerscherm").Select
    > Range("K10").Select
    > ActiveSheet.Paste
    >
    > "SDorderno" contains the value to find in column B.
    > However, the cell with the matching data is not activated. Instead, the
    > column B remains activated
    > How can I get the required cell to be activated?
    > Tx.
    > Frank
    >
    >
    >
    >
    >


  3. #3
    Frank Groenendijk
    Guest

    Re: How to use the "found" cellvalue in VB.

    Ben, Thank you for yr quick reaction.
    But it does not work.
    Selected cell A1 iso required cell.
    But Tnx
    Frank

    "ben" <ben@discussions.microsoft.com> schreef in bericht
    news:27CAEF92-BAAF-4111-8160-58A830B66903@microsoft.com...
    > you could try using this
    >
    > row1 = activecell.row
    > cells(row1,1).select
    > that should select the cell to the left of the matching data
    >
    >
    > "Frank Groenendijk" wrote:
    >
    >> Hi there, please, help required with VB
    >> I want to use a macro to copy the cell-contents from a row, found with
    >> the
    >> "Find" command using following code: (using an inputbox to get the
    >> Find-value).
    >>
    >> Dim SDorderno, Title, MyValue
    >> Message = "Fuctuurinvoer voor Salesordernr.>"
    >> Title = "Demo InputBox"
    >> MyValue = InputBox(Message, Title, Default)
    >>
    >> Columns("B:B").Select
    >> Selection.Find(What:=SDorderno, After:=ActiveCell, LookIn:=xlValues,
    >> _
    >> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    >> MatchCase:=False).Activate
    >> ActiveCell.Select
    >> ActiveCell.Offset(0, 1).Activate
    >> Application.CutCopyMode = False
    >> Selection.Copy
    >> Sheets("Invoerscherm").Select
    >> Range("K10").Select
    >> ActiveSheet.Paste
    >>
    >> "SDorderno" contains the value to find in column B.
    >> However, the cell with the matching data is not activated. Instead, the
    >> column B remains activated
    >> How can I get the required cell to be activated?
    >> Tx.
    >> Frank
    >>
    >>
    >>
    >>
    >>




  4. #4
    Dave Peterson
    Guest

    Re: How to use the "found" cellvalue in VB.

    Is there a reason you're searching for SDorderno, but using "myvalue" in the
    inputbox?

    I'm not sure if this does what you want, but maybe it'll help:

    Option Explicit
    Sub testme()

    Dim SDorderno As Variant
    Dim Title As String
    Dim myValue As Variant 'long '????
    Dim Message As String
    Dim FoundCell As Range

    Message = "Fuctuurinvoer voor Salesordernr.>"
    Title = "Demo InputBox"
    SDorderno = InputBox(Message, Title, Default)

    With ActiveSheet.Range("B:B")
    Set FoundCell = .Cells.Find(What:=SDorderno, _
    After:=.Cells(.Cells.Count), LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)
    End With

    If FoundCell Is Nothing Then
    'do nothing
    MsgBox "not found"
    Else
    FoundCell.Offset(0, 1).Copy _
    Destination:=Worksheets("Invoerscherm").Range("k10")
    End If
    End Sub




    Frank Groenendijk wrote:
    >
    > Hi there, please, help required with VB
    > I want to use a macro to copy the cell-contents from a row, found with the
    > "Find" command using following code: (using an inputbox to get the
    > Find-value).
    >
    > Dim SDorderno, Title, MyValue
    > Message = "Fuctuurinvoer voor Salesordernr.>"
    > Title = "Demo InputBox"
    > MyValue = InputBox(Message, Title, Default)
    >
    > Columns("B:B").Select
    > Selection.Find(What:=SDorderno, After:=ActiveCell, LookIn:=xlValues, _
    > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    > MatchCase:=False).Activate
    > ActiveCell.Select
    > ActiveCell.Offset(0, 1).Activate
    > Application.CutCopyMode = False
    > Selection.Copy
    > Sheets("Invoerscherm").Select
    > Range("K10").Select
    > ActiveSheet.Paste
    >
    > "SDorderno" contains the value to find in column B.
    > However, the cell with the matching data is not activated. Instead, the
    > column B remains activated
    > How can I get the required cell to be activated?
    > Tx.
    > Frank


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1