Reputation: 393
I needed to select column "D:D" so I did. Then I needed to find in column value of other cell. So what I need to do:
Everything is in one sheet but the result will be used in other sheet. It's for tommorow school project. I was trying all weekend to grab answer, but I can't do it on my own and my brain is washed.
Code:
Columns("B:B").Select
Selection.Find(What:="VA22GU1", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Upvotes: 0
Views: 938
Reputation: 5782
So what I need to do:
1.I got value in "J5" cell.
2.I need to find that value in D:D
3.I need to move one cell to right
4.I need to copy everything from cell (for example - E2)
5.Paste it in "J6"
Use this:
'1. Variant with copy method
Sub test()
On Error Resume Next
If [J5].Value <> "" Then
Columns("D:D").Find([J5].Value).Offset(, 1).Copy [J6]
Else
MsgBox "Cell [J5] is empty!"
End If
If Err.Number > 0 Then
MsgBox "Column [D] does not contain search criteria: " & [J5].Value
Err.Clear
End If
End Sub
'2. Variant without copy method
Sub test2()
On Error Resume Next
If [J5].Value <> "" Then
[J6].Value = Columns("D:D").Find([J5].Value).Offset(, 1).Value
Else
MsgBox "Cell [J5] is empty!"
End If
If Err.Number > 0 Then
MsgBox "Column [D] does not contain search criteria: " & [J5].Value
Err.Clear
End If
End Sub
Upvotes: 0
Reputation: 2473
Why does everyone want to bring a macro to a formula fight?
Type this in cell J6
=OFFSET(D1,MATCH(J5,D:D,0)-1,1,1,1)
Now look up the OFFSET
and MATCH
functions in the help.
=INDEX(D:D,MATCH(J5,D:D,0))
Same result, more compact, just goes to show there is more than one way to skin a cat.
Upvotes: 1