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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]melon[/TD]
[TD]Brian[/TD]
[TD]pants[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]squash[/TD]
[TD]Barbara[/TD]
[TD]pants[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]grape[/TD]
[TD]Lois[/TD]
[TD]blouse[/TD]
[TD]red[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]eggplant[/TD]
[TD]Bruce[/TD]
[TD]shirt[/TD]
[TD]red[/TD]
[/TR]
</tbody>[/TABLE]

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 .

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2 looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]apple[/TD]
[TD]weather[/TD]
[TD]John[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]grape[/TD]
[TD]mountain[/TD]
[TD]Lois[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]melon[/TD]
[TD]car[/TD]
[TD]Brian[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]orange[/TD]
[TD]bike[/TD]
[TD]Sally[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


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.