How to use the "found" cellvalue in VB.

[Frank Groenendijk]

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

[ben]

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
>
>
>
>
>

[Frank Groenendijk]

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
>>
>>
>>
>>
>>

[Dave Peterson]

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