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]

Take Dim ThisFinal As Long out of sub. Since its default value is zero, you can make the following check:

If ThisFinal =0 Then
  ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
End If

No one can touch him again after a value is assigned greater than 0.

Also you can add another check-valve:

If ThisFinal =0 Then
  ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
ElseIf Not ColumnCValueFound Then
      ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
End If

 

[Mr_Ragweed2]

Thank you for helping me!
Ok, i'm following you so far i think. Default is 0 so run a check to make sure its actually zero. got it. Where i'm losing you is the next line. If the column c value is found are you still running the same finalrow statement? How do i find where in column c the value is so i can paste to that location?
Sorry, just trying to understand it better so i don't have to ask as often.

 

[Legacy 143009]

No, if it's Not found then proceed with the lastrow statement also.
In other words, if it is already assigned to a value >0 (which means code was run before) or no value fouund on column C, then don't touch it.

You should loop through column c in order to find the value. Declare a boolean inside Sub:

Dim ThisFinal As Long
Sub test()
  Dim ColumnCValueFound As Boolean
  Dim rng As Range
  Dim lRow As Long

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

  If ThisFinal =0 Or Not ColumnCValueFound Then 'Or just If Not ColumnCValueFound Then should be enough in your case
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
  End If
End Sub

 

[Mr_Ragweed2]

I like this, very instructional/informative. Thank you.
So then if it does find my value in column c, then that becomes the new final row and it will paste it there to overwrite what exists?
(Sorry i have not had a chance to test any of this yet. I have been travelling.)

 

[Legacy 143009]

So then if it does find my value in column c, then that becomes the new final row and it will paste it there to overwrite what exists?

if it does find the value in column c, then the last used row becomes the new final row. Else, does nothing.

 

[Mr_Ragweed2]

Awesome. Thank you. I should get to try this today and will post results either way.

 

[Mr_Ragweed2]

So i tried inserting your code and get an error "For variable already in use". Here's what i did:

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
   
'your code starts here: 
Dim ColumnCValueFound As Boolean
  Dim rng As Range
  Dim lRow As Long

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

  If ThisFinal =0 Or Not ColumnCValueFound Then 'Or just If Not ColumnCValueFound Then should be enough in your case
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
  End If
'your code 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 'new line
           
        End If
    Next I

I tried putting an "Exit For" after my For but that did nothing. I also tried commenting out your For and that did nothing. I'm sure i am just overlooking something maybe?

 

[Legacy 143009]

Ok, change this:

For i = 1 To lRow

to this

For j = 1 To lRow

 

[Mr_Ragweed2]

If it helps, we are looking for the ColumnCValue on the OSumWS. We are copying data from DekalbWS. (and just to be clear, i did change "your_value" to Dekalb since that is the value i am looking for.)