gsmcconville
04-01-2016, 04:09 AM
Good evening.
I have been trying for a few days to find out why I get incorrect results trying to dump an array to a table.
When I dump the same array to an area outside of the table everything works fine.
Is this another but in Excel?
What I am trying to achieve is to have the end user right click on a Selection of Cells in a column, and then have the macro toggle a "Y". There cannot be a Y in both the Markdown and Stock Action, it has to be one or the other, or none at all.
There is a large amount of data to process over, so I wanted to load the data to an array, process the changes, and then dump it back onto the spreadsheet.
This is the code, but you will need to check out the attached spreadsheet to see it in action.
At the moment, it seems to add Y's where there should not be any and vice versa.
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim Arr() As Variant, Rng As Range
If Target.Columns.Count > 1 Then Exit Sub
If Target.Column = 1 Or Target.Column = 5 Then ' Column 1 is in the table, Column 5 is not
Col1 = 1
Col2 = 2
Set Rng = Target.Resize(ColumnSize:=2)
ElseIf Target.Column = 2 Or Target.Column = 6 Then ' Column 2 is in the table, Column 6 is not
Set Rng = Target.Offset(ColumnOffset:=-1).Resize(ColumnSize:=2)
Col1 = 2
Col2 = 1
Else
Exit Sub
End If
Arr = Rng
RowIndex = 1
For Each Row In Rng.Rows
If Not Row.Hidden Then
If Trim(UCase(Arr(RowIndex, Col1))) = "Y" Or Trim(UCase(Arr(RowIndex, Col1))) = "YES" Then
Arr(RowIndex, Col1) = ""
Arr(RowIndex, Col2) = ""
Else
Arr(RowIndex, Col1) = "Y"
Arr(RowIndex, Col2) = ""
End If
End If
RowIndex = RowIndex + 1
Next
Rng = Arr
Sheet1.Range("A60:B" & UBound(Arr) - LBound(Arr) + 61) = Arr
Cancel = True
End Sub
I have been trying for a few days to find out why I get incorrect results trying to dump an array to a table.
When I dump the same array to an area outside of the table everything works fine.
Is this another but in Excel?
What I am trying to achieve is to have the end user right click on a Selection of Cells in a column, and then have the macro toggle a "Y". There cannot be a Y in both the Markdown and Stock Action, it has to be one or the other, or none at all.
There is a large amount of data to process over, so I wanted to load the data to an array, process the changes, and then dump it back onto the spreadsheet.
This is the code, but you will need to check out the attached spreadsheet to see it in action.
At the moment, it seems to add Y's where there should not be any and vice versa.
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim Arr() As Variant, Rng As Range
If Target.Columns.Count > 1 Then Exit Sub
If Target.Column = 1 Or Target.Column = 5 Then ' Column 1 is in the table, Column 5 is not
Col1 = 1
Col2 = 2
Set Rng = Target.Resize(ColumnSize:=2)
ElseIf Target.Column = 2 Or Target.Column = 6 Then ' Column 2 is in the table, Column 6 is not
Set Rng = Target.Offset(ColumnOffset:=-1).Resize(ColumnSize:=2)
Col1 = 2
Col2 = 1
Else
Exit Sub
End If
Arr = Rng
RowIndex = 1
For Each Row In Rng.Rows
If Not Row.Hidden Then
If Trim(UCase(Arr(RowIndex, Col1))) = "Y" Or Trim(UCase(Arr(RowIndex, Col1))) = "YES" Then
Arr(RowIndex, Col1) = ""
Arr(RowIndex, Col2) = ""
Else
Arr(RowIndex, Col1) = "Y"
Arr(RowIndex, Col2) = ""
End If
End If
RowIndex = RowIndex + 1
Next
Rng = Arr
Sheet1.Range("A60:B" & UBound(Arr) - LBound(Arr) + 61) = Arr
Cancel = True
End Sub