Find specific value in .CSV file and replace string if found

[somers]

Hi,

I have a macro on a button that writes various different cells from 1 workbook (main) into a csv string in another workbook (csv file).
For every new button click a new row is created in the csv file.
Ex. value1, value2, value3, ...
Now i would like to add this code:
* When button is clicked then
* IF value1 in csv file already exist replace whole csv string with exisiting data from workbook(main)
* IF value1 does nog exist in the csv file then continue the normal code

Here is the original code but i'm completely lost to replace the existing data from the csv.

Sub Verkocht()

    Dim inputmsg As Integer
    
    inputmsg = MsgBox("Project verkocht?", vbYesNo, "Double check")
    
        If inputmsg = vbYes Then
            Call Append2CSV
        Else
        End If

End Sub

Sub Append2CSV()
    Dim tmpCSV1, tmpCSV2, tmpCSV3, tmpCSV4 As String
    Dim f As Integer
    Dim savestring, dealnr, plannr, klant, beurs, plaats, jaar, calculator As String
    Const CSVFile As String = "Z:\Voorcalculatie.csv"

    f = FreeFile
    Open CSVFile For Append As #f

    tmpCSV1 = Range2CSV(Sheets(1).Range("C5"))
    tmpCSV2 = Range2CSV(Sheets(2).Range("C21:D23"))
    tmpCSV3 = Range2CSV(Sheets(2).Range("C26:D30"))
    tmpCSV4 = Range2CSV(Sheets(2).Range("C33:D38"))

    Print #f, tmpCSV1; ","; tmpCSV2; ","; tmpCSV3; ","; tmpCSV4
    Close #f
    dealnr = Sheets(1).Range("C5")
    plannr = Sheets(1).Range("C6")
    klant = Sheets(1).Range("C7")
    beurs = Sheets(1).Range("F5")
    plaats = Sheets(1).Range("F7")
    jaar = Sheets(1).Range("I5")
    calculator = Sheets(1).Range("C10")
    savestring = ThisWorkbook.Path & "\" & dealnr & "_" & plannr & "_" & klant & "_" & beurs & "_" & plaats & "_" & jaar & "_" & calculator & "_Calcul_VERKOCHT"
    ActiveWorkbook.SaveAs Filename:=savestring
End Sub
Private Function Range2CSV(ByVal list As Range) As String
    Dim tmp As String
    Dim r As Range
    Dim rowNum As Long

    rowNum = list.Cells(0, 1).Row
    For Each r In list.Cells
        If r.Row <> rowNum Then
            rowNum = r.Row
            'tmp = Left(tmp, Len(tmp) - 1) & vbCrLf  'remove last comma and start new line
        End If
        tmp = tmp & r.Value & ","
    Next
         tmp = Left(tmp, Len(tmp) - 1) 'remove final comma

    Range2CSV = tmp
End Function

 

[somers]

Any sugestions please, or is there some kind of guru (payed?) who can look at this?

 

[somers]

I'm trying this code but every new run of the macro just keeps adding data the last free row in the csv file.
i want to overwrite if the "dealnr" already exists.

Sub Verkocht()

    Dim inputmsg As Integer
    
    inputmsg = MsgBox("Project verkocht?", vbYesNo, "Double check")
    
        If inputmsg = vbYes Then
        
        
        Dim LUrng As Range
        Dim CSVwb As Workbook
        Dim dealnr, plannr, klant, beurs, plaats, jaar, calculator As String
        Dim tmpCSV1, tmpCSV2, tmpCSV3, tmpCSV4, tmpCSV11, tmpCSV12, tmpCSV13, tmpCSV14 As String
        Dim f As Integer
        Dim savestring As String
        Const CSVFile As String = "Z:\Voorcalculatie.csv"
        
             dealnr = Sheets(1).Range("C5")
             plannr = Sheets(1).Range("C6")
             klant = Sheets(1).Range("C7")
             beurs = Sheets(1).Range("F5")
             plaats = Sheets(1).Range("F7")
             jaar = Sheets(1).Range("I5")
             calculator = Sheets(1).Range("C10")

            CSVpath = "Z:\Voorcalculatie.csv"
            Set CSVwb = Workbooks.Open(CSVFile)
 
        Set LUrng = CSVwb.Sheets(1).UsedRange.Find(dealnr)


        If LUrng Is Nothing Then
              
              
                    CSVwb.Close savechanges:=False
           f = FreeFile
            Open CSVFile For Append As #f

    tmpCSV1 = Range2CSV(Sheets(1).Range("C5"))
    tmpCSV2 = Range2CSV(Sheets(2).Range("C21:D23"))
    tmpCSV3 = Range2CSV(Sheets(2).Range("C26:D30"))
    tmpCSV4 = Range2CSV(Sheets(2).Range("C33:D38"))
    Print #f, tmpCSV1; ","; tmpCSV2; ","; tmpCSV3; ","; tmpCSV4
    Close #f

    savestring = ThisWorkbook.Path & "\" & dealnr & "_" & plannr & "_" & klant & "_" & beurs & "_" & plaats & "_" & jaar & "_" & calculator & "_Calcul_VERKOCHT"
    
    ActiveWorkbook.SaveAs Filename:=savestring
            
            'new
            Else
                CSVwb.Close savechanges:=False
                 
               f = FreeFile
               Open CSVFile For Append As #f

   
    tmpCSV11 = RangeOW2CSV(Sheets(1).Range("C5"))
    tmpCSV12 = RangeOW2CSV(Sheets(2).Range("C21:D23"))
    tmpCSV13 = RangeOW2CSV(Sheets(2).Range("C26:D30"))
    tmpCSV14 = RangeOW2CSV(Sheets(2).Range("C33:D38"))

    Print #f, tmpCSV11; ","; tmpCSV12; ","; tmpCSV13; ","; tmpCSV14
    Close #f
        
    savestring = ThisWorkbook.Path & "\" & dealnr & "_" & plannr & "_" & klant & "_" & beurs & "_" & plaats & "_" & jaar & "_" & calculator & "_Calcul_VERKOCHT"
    
    ActiveWorkbook.SaveAs Filename:=savestring
            
             'CSVwb.Sheets(1).UsedRange.Replace what:=dealnr, replacement:=Append2CSV
             
             End If
            'end new
            
        Else
        End If

End Sub


Private Function Range2CSV(ByVal list As Range) As String
    Dim tmp As String
    Dim R As Range
    Dim rowNum As Long

    rowNum = list.Cells(0, 1).Row
    For Each R In list.Cells
        If R.Row <> rowNum Then
            rowNum = R.Row
            'tmp = Left(tmp, Len(tmp) - 1) & vbCrLf  'remove last comma and start new line
        End If
        tmp = tmp & R.Value & ","
    Next
         tmp = Left(tmp, Len(tmp) - 1) 'remove final comma

    Range2CSV = tmp
End Function



 Private Function RangeOW2CSV(ByVal list As Range) As String
    Dim tmp As String
    Dim R As Range
    Dim rowNum As Long
 
        Dim LUrng As Range
        Dim dealnr As String
                     
             dealnr = Sheets(1).Range("C5")
          
        Set LUrng = Sheets(1).UsedRange.Find(dealnr)

    rowNum = list.Cells(0, 1).Row
    For Each LUrng In list.Cells
        
         rowNum = LUrng.Row
            'tmp = Left(tmp, Len(tmp) - 1) & vbCrLf  'remove last comma and start new line
         tmp = tmp & LUrng.Value & ","
    Next
         tmp = Left(tmp, Len(tmp) - 1) 'remove final comma

    RangeOW2CSV = tmp
End Function

 

[somers]

Are there experts here that i can consult?