nesting a loop to overwrite value if needed

[Mr_Ragweed2]

Hello and thanks,
This should be easy, i just need a jumpstart i think. I have a code that copies certain data from one worksheet and pastes it to the finalrow on another worksheet. This is initiated via a button click and everything works great. My issue is that if you push the button twice you get the data twice and what i would want to do is to overwrite any previously copied data. The paste location is dynamic (hence the finalrow statement.) What i need to do is look for a value in column c, if it does not exist then proceed with my current code. If it does exist then i want to overwrite it with what is currently being copied. My code is below:

Dim ThisFinal As Long
    Dim I As Integer
    Dim OSumWS As Worksheet
    Dim DekalbWS As Worksheet


    Set OSumWS = Sheets("Order Summary")
    Set DekalbWS = Sheets("Dekalb Seed Order Form")
    
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row 'new line
    
    For I = 19 To 31
        
        If DekalbWS.Cells(I, 3).Value <> "" Then
            With Application.Intersect(DekalbWS.Rows(I).EntireRow, DekalbWS.Range("C:U"))
                .UnMerge
                .Copy
            End With
    
  '12-19-22 Need to look first to see if vendor already exists. if "yes" then overwrite based on location, if "no" then proceed as normal.
        'Dim Overwrite As Integer ???
        If OSumWS.Cells(I, 3).Value = "Dekalb" Then
           'set Overwrite = the range i found "dekalb" in
 
 'then maybe i Else If the rest of this?
   
            OSumWS.Cells(ThisFinal + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            ThisFinal = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row 'new line
            
        End If
    Next I

As you can see i tried to start a little segment there that is half in laymans terms (i have to lay things out logically in regular words and then convert those steps to vba since i don't do this often.)
Any help is appreciated.

 

[Legacy 143009]

ı still don't understand why do you need to find the last row if you are going to write to same row each time? Just try to find the vendor name and write one line below? Isn't it supposed to be?

Ahh ok, if there is no existing vendor then, it will write to the new line

Could you please show me some data sample from Dekalb Seed Order Form? I want to see how does your data structure look like. It can be much more easy than you may think.

 

[Mr_Ragweed2]

here are some screenshots.
This is is from the dekalb seed order form as you requested. here is if they are only selling one product:

Here is if they are selling two ( but this could go to as many as 5 or 6 in some cases).

the issue we are working on has to do with the OSumWS:
as you can see below 2 products equals three lines with the third line being some totals that are taken from noncontiguous cells from the dekalb sheet.

and correctly you have guessed there may be more than one vendor as seen below:

so to summarize: number of products is dynamic, number of vendors is dynamic, order of vendors is variable.
Now, to reset the stage for my issue. A user is inputting a dekalb sales order and select products. There is a button to push when they are done that takes the appropriate info from the dekalb sheet and puts in on the OSumWS. They could at that point be done or move onto writing an order for another vendor. (Each vendor has its own sheet and all are summarized together on the same OSumWS.) If at some point they realize they need to edit the order i need the copy and paste to find the data on the OSumWS and overwrite it.
BUT......
As i am typing this i think it may be easier to find any existing data, delete those rows, shift the cells up, and then paste the new data on the next available row. Sorry for the left turn here in what i am wanting but i believe this could/will be easier. The code we added only works on a single product order. When i ran the scenario with multiple products it overwrites the first one with the second product because that's what we are now telling it to do. So sorry for this. So there is one good thin to know there is always 1 more line than there are products (ex 1 product = 2 rows, 3 products = 4 rows) to account for some totals. Based on this new thought process, here is the original code we are working with:

Dim ThisFinal As Long
    Dim I As Integer
    Dim OSumWS As Worksheet
    Dim DekalbWS As Worksheet

'12-19-22 Need to look first to see if vendor already exists. if "yes" then delete based on location, if "no" then proceed as normal.

    Set OSumWS = Sheets("Order Summary")
    Set DekalbWS = Sheets("Dekalb Seed Order Form")
    
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row 'new line
    
    For I = 19 To 31
        
        If DekalbWS.Cells(I, 3).Value <> "" Then
            With Application.Intersect(DekalbWS.Rows(I).EntireRow, DekalbWS.Range("C:U"))
                .UnMerge
                .Copy
            End With
    
            OSumWS.Cells(ThisFinal + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            ThisFinal = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row 'new line
            
        End If
    Next I
    OSumWS.UsedRange.Columns.AutoFit
    Sheets("Dekalb Seed Order Form").Activate
    '----------------------------------------------------------------------------------------
    'below this line needs relocate to next available row after all product rows have been copied - works
               
     Dim copyRange1 As Range
     Dim copyRange2 As Range
     Dim copyRange3 As Range
     Dim copyRange4 As Range
     
     Dim cel As Range
     Dim pasteRange1 As Range
     Dim pasteRange2 As Range
     Dim pasteRange3 As Range
     Dim pasteRange4 As Range
     
     Dim FinalColumn As Long
     
     Set copyRange1 = Sheets("Dekalb Seed Order Form").Range("T39")
     Set copyRange2 = Sheets("Dekalb Seed Order Form").Range("T47")
     Set copyRange3 = Sheets("Dekalb Seed Order Form").Range("T57")
     Set copyRange4 = Sheets("Dekalb Seed Order Form").Range("N61")
     
     Set pasteRange1 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     Set pasteRange2 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     Set pasteRange3 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     Set pasteRange4 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
 
     For Each cel In copyRange1
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -6).Column
        pasteRange1.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     Next
     
     For Each cel In copyRange2
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -5).Column
        pasteRange2.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Next
      
      For Each cel In copyRange3
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -4).Column
        pasteRange3.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Next
      
       For Each cel In copyRange4
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -3).Column
        pasteRange4.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Next
      
        Application.CutCopyMode = False
    
    End If

Does any of this help or make sense? I realize i may be rambling a bit. Sorry for the long post.

 

[Legacy 143009]

Ok, I see. What is the formula for N, O, P and Q columns? Or are they coming from somewhere else directly?

 

[Mr_Ragweed2]

They are coming from somewhere else and they are noncontiguous. There were a lot of ws formatting issues i had to deal with and this was the cleanest way to get there. Not ideal, but it's what i was given.

 

[Mr_Ragweed2]

SInce we are searching for and deleting a dynamic number of rows, this is a new topic. Should i start a new thread?

 

[Legacy 143009]

Ok, I prepared a sample workbook for you. I write comments next to each line.
1. Since I don't know where column N, O, P, Q values come from, I input them into vendor sheets. Eveytime last record discounts will be taken into account. I hope you'll figure it out by your self according to you data.
2. At first, the Summary sheet wil be empty. Run the code. You will see non-existing records will be added.
3. Then add a new record to Dekalb sheet. You may add more than one. You will see it will insert records as you want. And last discount amounts will be displayed.

Good luck!

WeTransfer | Send Large Files Fast - Up To 2GB Free

The simple, quick and secure way to send your files around the world without an account. Share your files, photos, and videos today for free.

we.tl

 

[Mr_Ragweed2]

Nice. And thank you, but it is not fully doing what i need. It adds a product no problem. However, if i edit or delete an existing product there is no change. I am wanting basically to reset the Order Summary page each time the macro is run, (but only reset it for the particular vendor page i am on.) Each time i run the "finish" button, i want to delete any existing info from that vendor on the Order Summary sheet and then paste the new info on the next available row.
We only need concern ourselves with dekalb in this example as each vendor sheet has its own button and i can change names accordingly. I have allegiant on there there to remind myself it is not a simple delete the whole page solution

 

[Legacy 143009]

I see. Is it ok for you to remove all related vendor rows and insert fresh rows to the very end of the summary sheet? Or do you want them with the old location they were? I hope I could explain.

I mean let's say you have Dekalb from 10th to 50th rows. I will remove them. Let's say Summary sheet have empty rows at 400th row. Is it ok to insert new Dekalb information after 400th row? Or do you want to insert them after 10th row again?

 

[Legacy 143009]

Ok, I prepared two examples for you. One, inserts the new data to the old position. The other one appends the new information to the end of the summary sheet. I hope it helps you.

Append To End.xlsm and 1 more file

2 files sent via WeTransfer, the simplest way to send your files around the world

we.tl

 

[Mr_Ragweed2]

Thank you very much for all of your help. Both versions worked. I ended up choosing the "Append to end" version in case i run into a scenario later down the road that would become a problem. After tweaking around a few names and ranges it seems to work perfectly. At this point i think we are done. I will let you know if i run into any issues.
Thanks again.
And to any moderators that may be reading this - This place is excellent. I love using this forum although the one drawback is my bosses now think i have more ability than i actually do. haha. At least i have an army of helpers when i need it. Maybe one day i will be able to contribute back.