Overwrite or append data

[BRISBANEBOB]

I have solved half the problem through help on this forum when data is appended to an existing data array. The second part - overwriting the record if it already exists - has me beaten. I have attached an example, and grateful for any help.

[Winon]

Hello BRISBANEBOB,

It would be of great help if you Could Post the Code you have for your sample Workbook which is totally stripped of all Code. That would enable us to approach your issue from the same "Page", without Coding any conflicts.

Thank you.

[sktneer]

You may try something like this....

Sub CopyToDataArray()
Dim sws As Worksheet, dws As Worksheet
Dim rng As Range
Dim r As Long
Dim wht
Set sws = Sheets("DataEntry")
Set dws = Sheets("DataArray")
wht = sws.Range("R8").Value
sws.Range("C8:O8").Copy
With dws.Range("Q:Q")
   Set rng = .Find(what:=wht, LookIn:=xlValues, LookAt:=xlWhole)
   If Not rng Is Nothing Then
      r = rng.Row
   Else
      r = dws.Cells(Rows.Count, 2).End(xlUp).Row + 1
   End If
End With
dws.Range("B" & r).PasteSpecial xlPasteAll
dws.Range("Q" & r).FormulaR1C1 = "=RC[-14]&RC[-12]&RC[-9]&RC[-10]"
End Sub

[Winon]

Post duplicated, and deleted.

[Winon]

@ sktneer,

Nice work!

Before End Sub, I would add:

Application.CutCopyMode = False

Maybe also adding ScreenUpdating to False and True.

Kind Regards.

[BRISBANEBOB]

That is genius! I wouldn't have got it in a thousand years. Thanks, rep added

[Winon]

Hi BRISBANEBOB,

You are welcome, glad sktneer could come to your rescue, and him not minding me contributing a bit.

Regards.

[sktneer]

@Winon
Thanks for the appreciation.
And yes you are correct. Those changes make the code perfect. I forgot to add those lines. Thanks for pointing that out.

[Winon]

Hi BRISBANEBOB,

Re your Message,

I am battling to get one last part to work. At the moment the system will append a new record or ignore it as the record already exists. How do I get it to recognise that if one of the cells in the sheet has TRUE, it means the record should not be overwritten and ignored, but if the cell reads FALSE, the record should be overwritten? I've tried a number of tacks and all have failed! Any advice you have would be much appreciated.

It is difficult to elaborate on how you should do what where, to try and get you to solve your issue, hence the attached sample Workbook for you perusal.

Regards.

[BRISBANEBOB]

Hi Minon

Somehow I missed your additional help - only found it this morning.

Thanks for the explanation on how it works - it's all helping me to get to grips with my awful ability (inability) to write code.

[Winon]

Hello BRISBANEBOB,

Thank you for the feedback, and also for adding to my Reputation. Much appreciated!

You are welcome, glad I could help.

Regards.