Paste Large number of data and run the Macro

[agonysWeeper]

Hi,
i found this code from google and trying to work and apply to my worksheet, I have a large data around 35K rows and being updated everyday.
column A to AU are being updated by pasting value the large data all the way down. I am trying to apply the below code to populate the data from column number 30 which is AD and populate the result from AV2 (row 1 is the header) based from the logic below, the problem is that every time I paste the large data, it's doesn't work at all, it will just work when I manually enter in first data in AD2 and press enter, then its populates.

I am not much good with the macro so sorry about this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Target.Count = 1 Then
        If Target.Column = 30 Then
            Application.EnableEvents = False
            For Each cell In Range("AV1").Offset(Target.Row - 1)
                If cell.Value <> "" Then cell.ClearContents
            Next cell
            Select Case Target.Value
                Case "Black"
                    Range("AV1").Offset(Target.Row - 1).Value = "Out"
                Case "Red"
                    Range("AV1").Offset(Target.Row - 1).Value = "In"
                Case "Green"
                    Range("AV1").Offset(Target.Row - 1).Value = "Processing"
                Case Is <> "Black", "Green", "Red"
                    Range("AV1").Offset(Target.Row - 1).Value = Range("AD1").Copy
            End Select
            Application.EnableEvents = True
        End If
    End If
End Sub

 

[Joe4]

It is because of this line here:

If Target.Count = 1 Then

That is limiting it so that it only runs when one single cell is updated at a time. If you are copying and pasting large ranges, that won't work.

With Worksheet_Change event procedure code, we usually look to add conditions for it to run based on a certain cell, column, row, or range being updated (this is the "trigger").
What do we want the trigger to be in this case?
And then what columns do we want to update when that condition (trigger) is met?

 

[agonysWeeper]

Hi Joe,
Thanks for the response, all I just wanted is when I paste the data from column A to AU all the way down, column AV will be updated based from column AD value which play the case above, please note that I am working in table with existing data, pasting the newly downloaded data from System won't guaranteed that the record in row 1 will remain in row 1, it could be in row 2 or the last row (row 35,000).

 

[Joe4]

OK, I am still not quite certain I follow everything you are saying, but I think this should help with the structure.
The following code will loop through each cell in column AD that is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRange As Range
    Dim cell As Range


'   Check for changes to column AD
    Set myRange = Intersect(Target, Range("AD:AD"))
    
    If Not (myRange Is Nothing) Then
        Application.EnableEvents = False
        For Each cell In myRange
'           [I]do something there[/I]
        Next cell
        Application.EnableEvents = True
    End If


End Sub

So, to make updates or references to other columns in that row, simply use the Offset function.

Since the Target is always in column AD, to reference column AU, you would use:
Target.Offset(0,17)

Likewise, referencing column AV would be:
Target.Offset(0,18)

Does that help?

 

[agonysWeeper]

Sorry to confuse, I was trying to convert my current excel formula to vba.
For now, I wanted to replace the formula in column AV with a vba, it has the vlookup formula to show the result based on the value in column AD, so for example, AD2=Black then value in AV2 should be "Out", or if AD2=Red then AV2 should be "In" or if Green then "processing" and if not in these 3 colors, then just get what is the value in AD column.

 

[Joe4]

For now, I wanted to replace the formula in column AV with a vba, it has the vlookup formula to show the result based on the value in column AD

If you turn on the Macro Recorder, and record yourself entering the formula manually into a cell on your sheet, it will create the VBA code you need for that formula.
So then you should just be able to take that formula and plug it in to your code.