In this i want to copy Col E cells when changed/entered, to another sheet on the empty row available . Before copying the cell should be matched to column on the other sheet, and if the value is not matching then only i need to paste it, else not.
In this what i want is to have a dynamic array for the sheet where i want to compare and paste the value.
Essentialy i want to compare the current cell/target with the dynamic array of the next sheet, if there is no match i want to paste in the next free cell..
And then when next target is compared, the dynamic array should include the new range too..
How to do it?
Hi Ross,
the code is my existing one. In that i need to add an event target for other column. As two separate macros cannot be used for same event like worksheet change. I want to include for other column to.
Existing event is when you click on the cell in target column then it copies the value .
Now what i want is column where copied value goes is my new target. I need to check the cell value(text) in the column to another sheet.
THe another sheet contains, a group of text value which matches with the new target.
When the new target has new value or text, that should be copied to this sheet column A.
I am of the idea to use loop to check for matches, but since the another sheet has around 1000 rows, i want to put this in a dynamic array and compare this array to the target cell ..
Here my issue , is how to create dynamic array of the column, then how to compare the array. Then after new value found, and copied, how to add that row in the array(ie how to increase the array size).
The file is too huge, if you want i will create a dummy sample and provide you, if you are familiar and good with array, as mine is poor.
Hi Ross and All ,
I have attached the sample file.
What i want is, if i add the data in sheet 2 col A and B.. it should compare with sheet 1 in respective columns
and should include as an addition to in next row, if it is not available.
In the sheet2 coloured cell, is a new case and school, which doesn't exist in sheet1.
So it should be added. It should be compared and if there is no match, then it should be added.
Both the cells(col a and b in sheet2) needed to be added in the same row of sheet 1.
I have more than 500 rows of sheet 1 in the original file. Hence i want to put the sheet 1 both columns in array and then comparison take place.
This event takes place when the cell changes.
THis is to facilitate, when an already existing detail is entered in Col A of sheet 2, automatically col B should be updated.
This will be done, only when new entries are added to the sheet 1 next empty row.
According to your attachment a starter to paste to the Sheet2 worksheet module :
PHP Code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim Rg As Range, A$ If Target.CountLarge = 1 Then With Sheet1.UsedRange.Columns(1) If Target.Column = 1 Then If Target.Row = Me.UsedRange.Rows.Count Then Set Rg = .Find(Target.Value2, , xlValues, xlWhole) If Not Rg Is Nothing Then Application.EnableEvents = False Target(1, 2).Value2 = Rg(1, 2).Value2 Application.EnableEvents = True Set Rg = Nothing End If End If ElseIf Target.Column = 2 Then Set Rg = .Find(Target(1, 0).Value2, , xlValues, xlWhole) If Not Rg Is Nothing Then A = Rg.Address Do If Target.Value2 = Rg(1, 2).Value2 Then Set Rg = Nothing: Exit Sub Set Rg = .FindNext(Rg) Loop Until Rg.Address = A Set Rg = Nothing End If Target(1, 0).Resize(, 2).Copy .Cells(.Rows.Count + 1, 1) End If End With End If End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !
I also have another event for Col A in sheet 2 . Since both same change event, i cannot do separately, i need to merge both the events(mine-old and your code)
how to incorporate(merge) the above code also in your code.
A bit late : nothing in the initial post neither in the attachment !
So just replace the old event by mine as it seems to not need to merge anything.
But add the same EnableEvents codelines for the Copy in the Column = 2 part …
Re: Macro Event when cell changes it should copy to another sheet after there is a no matc
Hi Marc,
In the above. as the new entries are copied to another sheet. I want one another.
When in sheet 2 col A user types, if it is already available in the previous row of the same column, then for column B value of the target row should be automatically updated.
For eg:
If i type case 4 in col A, if this exist in prev row.. the corresp. value in col B for this..
should be automatically populate in col B of the target row when i type case 4 .
Re: Macro Event when cell changes it should copy to another sheet after there is a no matc
Hi Marc,
And one doubt.. sometimes the event does not pickup and copies to another sheet.
When i debug, in those cases i see RG as nothing.. i am not able to understand why the range rg does not pick up the find value?
This happens sometime and not always, and not able to find pattern as to why this happens.
Bookmarks