Problem with VBA code which paste results in wrong Workbook

[smide]

Hello.

I have a problem with code which runs periodically (every 30 seconds).

Basically, the code compares data from two Sheets (at the same Workbook) and then paste data (under certain conditions) from Sheet4 to Sheet1.

However, when I open some other Workbook (window on that "other" Workbook is active) and I start with my new project, the code from previous Workbook then paste results into that new Workbook also!?

Any idea how to solve this?

Here is the code:

Sub Salary()
Dim Rng As Range, cell As Range, lr As Long, ws As Worksheet, sh As Worksheet, product_name As String
Set ws = ThisWorkbook.Sheets("Sheet1")
Set sh = ThisWorkbook.Sheets("Sheet4")

For Each Rng In sh.Range("B3:B" & sh.Range("B" & Rows.Count).End(xlUp).Row)
product_name = Rng.Value
For Each cell In ws.Range("A3:X3")
If InStr(1, cell, Rng.Value) > 0 Then
If Cells(ws.Cells(Rows.Count, cell.Column).End(xlUp).Row, cell.Column).Value <> Rng.Offset(, 2).Value Then Cells(ws.Cells(Rows.Count, cell.Column).End(xlUp).Row + 1, cell.Column).Value = Rng.Offset(, 2).Value
GoTo nextrng
End If
Next cell
nextrng:
Next Rng
Application.OnTime Now + TimeValue("00:00:30"), "Salary"
End Sub

 

[RickXL]

Hi,

I think you are going to need something like this:

Sub Salary()
    Dim Rng As Range, cell As Range, lr As Long, ws As Worksheet, sh As Worksheet, product_name As String
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set sh = ThisWorkbook.Sheets("Sheet4")
    For Each Rng In sh.Range("B3:B" & sh.Range("B" & Rows.Count).End(xlUp).Row)
        product_name = Rng.Value
        With ws
            For Each cell In .Range("A3:X3")
                If InStr(1, cell, Rng.Value) > 0 Then
                    If .Cells(.Cells(.Rows.Count, cell.Column).End(xlUp).Row, cell.Column).Value <> Rng.Offset(, 2).Value Then _
                        .Cells(.Cells(.Rows.Count, cell.Column).End(xlUp).Row + 1, cell.Column).Value = Rng.Offset(, 2).Value
                GoTo nextrng
                End If
            Next cell
        End With
nextrng:
    Next Rng
    Application.OnTime Now + TimeValue("00:00:30"), "Salary"
End Sub

The reason is that in this line:

Cells(ws.Cells(Rows.Count, cell.Column).End(xlUp).Row + 1, cell.Column).Value = Rng.Offset(, 2).Value

it resolves to something like:

Cells(n1, n2).Value=Rng.Offset(, 2).Value

Cells will apply to the current worksheet unless you qualify it. For instance, you could say:

ws.Cells(n1, n2).Value=Rng.Offset(, 2).Value

The same problem exists with the Cells at the start of the If statement as well.

If you use the With/End With construct it lets you make a default setting which you can have applied to every command that starts with a dot e.g.

.Cells

Many people seem to use just one Worksheet in one Workbook at a time so never see the problem. If you start using multiple Workbooks then it shows up. Being slightly paranoid, I like to qualify all my references so that Excel can be in no doubt what I meant!


Regards,