What does this line of VBA do?

rob51852

Board Regular
Joined
Jun 27, 2016
Messages
164
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I’m trying to use a spreadsheet written by a predecessor. It is supposed to copy and paste data each time there is a change to Sheet1.

The problem is that each time a change is made the data is being pasted twice instead of once.

From my limited knowledge of VBA I think the problem may be in this line of code but I’m not clear on exactly what it does.

VBA Code:
Private Sub worksheet_change(ByVal target As Range)
    Dim KeyCells As Range
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("F2")
' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:P50")

If Not Application.Intersect(KeyCells, Range(target.Address)) _
           Is Nothing Then

Can someone explain what this does?

Thanks

Ps in case it is relevant the full code is below:

VBA Code:
Private Sub worksheet_change(ByVal target As Range)
    Dim KeyCells As Range
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("F2")
' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:P50")

If Not Application.Intersect(KeyCells, Range(target.Address)) _
           Is Nothing Then
          
'Count the cells to copy
Dim a As Integer
a = 0
For i = 5 To 100
If Sheets("Sheet1").Cells(i, 1) <> "" Then
a = a + 1
End If
Next i

'Count the last cell where to start copying
Dim b As Integer
b = 2
For i = 2 To 50000
If Sheets("Data").Cells(i, 1) <> "" Then
b = b + 1
End If
Next i

Dim c As Integer
c = 5
'Perform the copy paste process
For i = b To b + a - 1
If ThisWorkbook.Worksheets("Sheet1").Range("E2") <> "" And ThisWorkbook.Worksheets("Sheet1").Range("F2") = "" Then
Sheets("Data").Cells(i, 1) = Sheets("Sheet1").Cells(3, 14)
Sheets("Data").Cells(i, 2) = Sheets("Sheet1").Cells(2, 2)
Sheets("Data").Cells(i, 3) = Sheets("Sheet1").Cells(1, 1)
Sheets("Data").Cells(i, 4) = Sheets("Sheet1").Cells(2, 5)
Sheets("Data").Cells(i, 5) = Sheets("Sheet1").Cells(c, 26)
Sheets("Data").Cells(i, 6) = Sheets("Sheet1").Cells(c, 1)
Sheets("Data").Cells(i, 7) = Sheets("Sheet1").Cells(c, 6)
Sheets("Data").Cells(i, 8) = Sheets("Sheet1").Cells(c, 8)
Sheets("Data").Cells(i, 9) = Sheets("Sheet1").Cells(c, 15)
Sheets("Data").Cells(i, 10) = Sheets("Sheet1").Cells(c, 16)
Sheets("Data").Cells(i, 11) = Sheets("Sheet1").Cells(3, 2)
Sheets("Data").Cells(i, 12) = Sheets("Sheet1").Cells(c, 25)
c = c + 1
End If
Next i

End If

End Sub
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
You've not made it clear which line you want explained.:)

If it's this one then what it's doing is checking if the range Target refers to is within the range KeyCells, i.e. A1:P50, refers to.
VBA Code:
If Not Application.Intersect(KeyCells, Range(target.Address)) _
           Is Nothing Then

However, that doesn't make much sense because Target will always be in that range because rather than referring to the range that's has been changed, as it should, Target is being set to refer to F2 here.
VBA Code:
Set target = ThisWorkbook.Worksheets("Sheet1").Range("F2")
As for why data is being pasted twice, that's hard to tell.

Usually when that happens it means the code is calling itself because the code itself makes a change on the worksheet but as far as I can see that's not happening in the posted code.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What happens if you make the changes in red below (untested)?
Rich (BB code):
        Application.EnableEvents = False
        For i = b To b + a - 1
            If ThisWorkbook.Worksheets("Sheet1").Range("E2") <> "" And ThisWorkbook.Worksheets("Sheet1").Range("F2") = "" Then
                Sheets("Data").Cells(i, 1) = Sheets("Sheet1").Cells(3, 14)
                Sheets("Data").Cells(i, 2) = Sheets("Sheet1").Cells(2, 2)
                Sheets("Data").Cells(i, 3) = Sheets("Sheet1").Cells(1, 1)
                Sheets("Data").Cells(i, 4) = Sheets("Sheet1").Cells(2, 5)
                Sheets("Data").Cells(i, 5) = Sheets("Sheet1").Cells(c, 26)
                Sheets("Data").Cells(i, 6) = Sheets("Sheet1").Cells(c, 1)
                Sheets("Data").Cells(i, 7) = Sheets("Sheet1").Cells(c, 6)
                Sheets("Data").Cells(i, 8) = Sheets("Sheet1").Cells(c, 8)
                Sheets("Data").Cells(i, 9) = Sheets("Sheet1").Cells(c, 15)
                Sheets("Data").Cells(i, 10) = Sheets("Sheet1").Cells(c, 16)
                Sheets("Data").Cells(i, 11) = Sheets("Sheet1").Cells(3, 2)
                Sheets("Data").Cells(i, 12) = Sheets("Sheet1").Cells(c, 25)
                c = c + 1
            End If
        Next i
        Application.EnableEvents = True
 

Watch MrExcel Video

Forum statistics

Threads
1,123,321
Messages
5,600,950
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top