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.

 

[Mr_Ragweed2]

Ok. I changed the I's to J's in your code. no errors but it is not doing anything at all. it just pastes to the next available row. it does not overwrite. When i step into the code, ColumnCValueFound is always False. I have verified that column C is the correct location we should be looking. I am just not sure we are looking at the OSumWS where the value exists or if we are looking at the DekalbWS where it does not.

 

[Legacy 143009]

Ley me check the code again.

 

[Mr_Ragweed2]

Another update. I changed the code to this (to make sure we are looking in the right place) and now ColumnCValueFound is TRUE, but it does not overwrite it still pastes to the next available row. I'm guessing it is because we exit the For and end the IF and then proceed to my ThisFinal statement when we paste?

 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
    
    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
            
  'starts here
  Dim ColumnCValueFound As Boolean
  Dim rng As Range
  Dim lRow As Long
Dim J As Integer

  lRow = OSumWS.Cells(Rows.Count, 3).End(xlUp).Row
  For J = 1 To lRow
    If OSumWS.Cells(J, 3).Value = "Dekalb" Then
      ColumnCValueFound = True
      Exit For
    End If
  Next

  If Not ColumnCValueFound Then
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
  End If
 'ends here
    
            OSumWS.Cells(ThisFinal + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            ThisFinal = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row
            
        End If
    Next I

 

[Legacy 143009]

I am totally lost. Can you please explain step by step from the very beginning. What do you want achieve in general?

 

[Legacy 143009]

Another update. I changed the code to this (to make sure we are looking in the right place) and now ColumnCValueFound is TRUE, but it does not overwrite it still pastes to the next available row. I'm guessing it is because we exit the For and end the IF and then proceed to my ThisFinal statement when we paste?

 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
   
    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
           
  'starts here
  Dim ColumnCValueFound As Boolean
  Dim rng As Range
  Dim lRow As Long
Dim J As Integer

  lRow = OSumWS.Cells(Rows.Count, 3).End(xlUp).Row
  For J = 1 To lRow
    If OSumWS.Cells(J, 3).Value = "Dekalb" Then
      ColumnCValueFound = True
      Exit For
    End If
  Next

  If Not ColumnCValueFound Then
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
  End If
 'ends here
   
            OSumWS.Cells(ThisFinal + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
           
            ThisFinal = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row
           
        End If
    Next I

It just exists j For

 

[Mr_Ragweed2]

Flashbond, sorry i have been away from this project but i am back now. Are you still willing to help? if so, i can post a refresher to bring us both back up to speed.
Thanks.

 

[Legacy 143009]

Yes, please tell me what you want to achieve from the beginnig

 

[Mr_Ragweed2]

ok, simple version - I am trying to overwrite data if it exists based on a value (vendor name) in column c. I have code that copies data (a dynamic number of lines) from one ws and pastes it to the finalrow of another ws.
Here is the code i have so far:

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
    
    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
  'starts here
  Dim ColumnCValueFound As Boolean
  Dim rng As Range
  Dim lRow As Long
Dim J As Integer

  lRow = OSumWS.Cells(Rows.Count, 3).End(xlUp).Row
  For J = 1 To lRow
    If OSumWS.Cells(J, 3).Value = "Dekalb" Then
      ColumnCValueFound = True
      
      OSumWS.Cells(lRow, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            lRow = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row
      
      Exit For
    End If
  Next

  ElseIf Not ColumnCValueFound Then
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
 'ends here
    
            OSumWS.Cells(ThisFinal + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            ThisFinal = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row
            
        End If
    Next I

The above code mostly works. Mostly.
Here is a snip of what it looks like before i try and overwrite:

so in theory, if i change the units to 55 it should copy right over these same two lines. (numerics will obviously change as well.) This is not happening entirely. The second line of data is not overwriting and i get this:

The line that starts in column N with $193.23 is supposed to be overwritten with the line below it. Beyond that, i think we (you) have helped me through most everything else.

 

[Mr_Ragweed2]

Major update here. The code i posted before is doing exactly what it should be. The second line of data that pastes is in a different part of code (shown below):

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

I know it's clunky and very novice looking - sorry. So now i guess the issue is that all of this needs to be conditional based on the overwrite being true. If the ovewrite code is false then this needs to run as written.
Said another way: Let's call the "ColumnCValueFound" bit of code the "overwrite code".
If ColumnCValueFound code = false then basically nothing happens and my original code runs as written and the overwrite code is not used. But....
If ColumnCValueFound code = true then it executes the overwrite code we worked on for Part A (1st line of overwrite) and then amends the code in this reply Part B (2nd line of overwrite). -Shifting the paste destination up 1 line.
How do i do that?

Thanks

 

[Mr_Ragweed2]

For clarity, the code i just posted follows immediately after the code we were working on, with these two lines in between for formatting the ws:

OSumWS.UsedRange.Columns.AutoFit
    Sheets("Dekalb Seed Order Form").Activate