VBA: How to write a value to a specific cell on another worksheet?

[rizzo93]

Using VBA, how do I take a value from Sheet 1 and write it to the cell I want on Sheet 2 when the row changes?

On Sheet 1, the active cell is E12.

	E	F	G	H
10	melon	Brian	pants	green
11	squash	Barbara	pants	blue
12	grape	Lois	blouse	red
13	eggplant	Bruce	shirt	red

<tbody>
</tbody>

Cell B2 below (also on Sheet 1) is the value I want to write to a cell on Sheet 2 but for the row that "grape" is on .

	A	B
1
2		7

<tbody>
</tbody>

Sheet 2 looks like this:

	A	B	C	D
1	apple	weather	John	0
2	grape	mountain	Lois	7
3	melon	car	Brian	2
3	orange	bike	Sally	2

<tbody>
</tbody>

The column I want to write the value in will always be D. I cannot figure out the code to find the row for "grape" (the value in the active cell on Sheet 1).

Sub CommandButton1_Click()​

Dim k As Long​

k = Range("B2").Select​

[mystery code to locate value of active cell and write k to the cell i want on sheet 2]​

End Sub​

If I were using a formula, I'd use INDEX and MATCH. But not sure how to do it in VBA.

 

[tonyyy]

rizzo93,

One approach would be to use the Find function...

Sub FindRow_1061388()
Application.ScreenUpdating = False
Dim ws1 As Worksheet, ws2 As Worksheet
Dim strng As String
Dim foundRow As Long

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
ws1.Activate
strng = ActiveCell.Value

On Error GoTo errHandler
foundRow = ws2.Cells.Find(What:=strng, After:=Cells(1, 1), LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
ws2.Cells(foundRow, 4).Value = ws1.Cells(2, 2)
ws2.Activate
errHandler:
    If Err.Number = 91 Then MsgBox "Search term not found."
End Sub

Cheers,

tonyyy

 

[rizzo93]

Thanks, tonyy. I'll give it a go. Much appreciated.