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

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

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" schreef in bericht
...
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

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