badgerstrader
New Member
- Joined
- Mar 18, 2020
- Messages
- 19
- Office Version
- 2019
- Platform
- Windows
ok so i have an excel sheet that has formulas in multiple cells. the formulas are all correct and working.
The excel workbook is linked into a data base that updates every few seconds and if all my criteria meet then excel is set to enter specific data into cells in one column it uses the below code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Integer
Dim triggerRow As Integer
Dim tRng As Range
Dim mRng As Range
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
With ThisWorkbook.Worksheets("Triggers")
For r = 11 To 54
If Cells(r, 29) <> "" Then
triggerRow = Cells(r, 29) + 1
Set tRng = Range(.Cells(triggerRow, 1), .Cells(triggerRow, 3))
Set mRng = Range(Cells(r, 17), Cells(r, 19))
tRng.Copy
mRng.PasteSpecial xlPasteValues
If Cells(r, 17) = "CANCEL-ALL" Then Cells(r, 20) = "ALL" Else Cells(r, 20) = ""
If Cells(r, 17) = "" Then Cells(r, 29) = "" Else Cells(r, 29) = triggerRow
End If
Next
End With
Application.EnableEvents = True
End If
End Sub
my problem occurs when i have more than one cell in column AA with data in it.
is there someone that can take a look at the workbook for me in a view of correcting this.
The excel workbook is linked into a data base that updates every few seconds and if all my criteria meet then excel is set to enter specific data into cells in one column it uses the below code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Integer
Dim triggerRow As Integer
Dim tRng As Range
Dim mRng As Range
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
With ThisWorkbook.Worksheets("Triggers")
For r = 11 To 54
If Cells(r, 29) <> "" Then
triggerRow = Cells(r, 29) + 1
Set tRng = Range(.Cells(triggerRow, 1), .Cells(triggerRow, 3))
Set mRng = Range(Cells(r, 17), Cells(r, 19))
tRng.Copy
mRng.PasteSpecial xlPasteValues
If Cells(r, 17) = "CANCEL-ALL" Then Cells(r, 20) = "ALL" Else Cells(r, 20) = ""
If Cells(r, 17) = "" Then Cells(r, 29) = "" Else Cells(r, 29) = triggerRow
End If
Next
End With
Application.EnableEvents = True
End If
End Sub
my problem occurs when i have more than one cell in column AA with data in it.
is there someone that can take a look at the workbook for me in a view of correcting this.