-2

I would like to change some Book2 value with respect to Book1's value.

Macro code in Book1:

Dim i As Integer
Dim k As Integer
k = Range("Z1")
For i = 1 To k
    If Cells(i, 22).Value = "Yes" Then
        Windows("Book2").Activate
        Cells(i, 11) = ""
        Cells(i, 13) = ""
    End If
Next i
Community
  • 1
  • 1
user3373951
  • 203
  • 1
  • 2
  • 12
  • If you want help, please start by reading this: [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask) – Jean-François Corbett Jun 23 '14 at 08:42
  • I think your problem is just referencing the workbook. To make your code work. Just add another `Windows("Book1").Activate` before your loop proceed with the next iteration. Or if my assumption is correct, just properly qualify your objects by [checking this out](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) to further improve your code. – L42 Jun 23 '14 at 08:51

1 Answers1

1

As commented, you can try re-writing your code like this:

Dim i As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Workbooks("Book1").Sheets("Sheet1") '~~> change sheet name to suit
Set ws2 = Workbooks("Book2").Sheets("Sheet1")

With ws1
    For i = 1 to .Range("Z1").Value
        If .Cells(i, 22).Value = "Yes" Then
            ws2.Cells(i, 11).Value = ""
            ws2.Cells(i, 13).Value = ""
        End If
    Next
End With
L42
  • 19,427
  • 11
  • 44
  • 68
  • great but this question should have been closed instead of answered :P –  Jun 23 '14 at 14:53
  • @mehow True :D But I just can't hold myself to share what I thought which might be of help to the OP. – L42 Jun 24 '14 at 00:13