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

 

[Guest]

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]

Ben, Thank you for yr quick reaction.
But it does not work.
Selected cell A1 iso required cell.
But Tnx
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