none
replacing record with updated one RRS feed

  • Question

  • i am trying to put a updated data back to the same spot with the new updates  

    updated data is in sheet4 (wksCP)

    and the data that i am updating is on sheet1 (wksCM)

    Sub UpdatedataCM()
        Dim j As Integer
        Dim maymame As Integer
        'Dim t As Variant
        'Dim wks As Worksheet
        Dim i As Range
        Dim lastrow As Range
            Dim wksCP As Worksheet
        Dim wksCM As Worksheet
        Set wksCP = Sheets("ControlP") 'assigning sheet4 to wksCP
        Set wksCM = Sheets("CompanyM") 'assigning sheet1 to wksCM

        wksCP.Select
        If Application.CountA(Range("C2:C17")) = 0 Then 'checkif the date field is empty
        MsgBox "Nothing to Save"
        GoTo endsub:
        Else
        maymame = wksCP.[c2] 'copying the search criteria

        wksCM.Select 'selecting sheet1
        Set lastrow = wksCM.Range("A" & wksCM.Rows.Count).End(xlUp).Offset(1, 0) 'Finding the next empty row

        For j = 2 To lastrow 'starting loop to find the row that has the data
        If Cells(j, "A").Value = maymame Then 'if data is a match the search criteria then
        Set i = wksCM.Range(Cells(j, "A")).Offset(0, 0) '"i" = the row that the data the is going to replaced

       GoTo startdataentry:
       End If
        Next j 'loop incriment


    startdataentry:
            i.Value = i.Offset(-1, 0).Value + 1 'Entering the next ID #
            'i.Offset(0, 0).Value = TextCompanyNumber.Text
            'i.Offset(0, 20).Value = Cells(2, 3).Value 'Company #
            i.Offset(0, 3).Value = Cells(3, 3).Value 'Company Name
            i.Offset(0, 4).Value = Cells(4, 3).Value 'Address 1
            i.Offset(0, 20).Value = Cells(5, 3).Value 'Address 2
            i.Offset(0, 5).Value = Cells(6, 3).Value 'City
            i.Offset(0, 6).Value = Cells(7, 3).Value 'State
            i.Offset(0, 7).Value = Cells(8, 3).Value 'Zip
            'i.Offset(0, 21).Value = Cells(9, 3).Value 'Zip 4
            i.Offset(0, 14).Value = Cells(10, 3).Value 'Telephone
            i.Offset(0, 16).Value = Cells(11, 3).Value 'Fax
            i.Offset(0, 17).Value = Cells(12, 3).Value 'Email Address
            i.Offset(0, 30).Value = Cells(13, 3).Value 'Company Code
            i.Offset(0, 26).Value = Cells(14, 3).Value 'Tax ID.
            i.Offset(0, 27).Value = Cells(15, 3).Value 'Creditcard #
            i.Offset(0, 28).Value = Cells(16, 3).Value 'Creditcard Date
            i.Offset(0, 29).Value = Cells(17, 3).Value 'Creditcard Code
            wksCP.Select 'selecting sheet4
            Range("C2:C17").Clear

        End If

    endsub:
    End Sub

                                            

    Vforum

    Wednesday, October 16, 2019 4:20 PM

Answers

  • I assume that you were getting a code error but you have not indicated on which line errors.

    Untested but edit the following line of code

     Set i = wksCM.Range(Cells(j, "A")).Offset(0, 0) '"i" = the row that the data the is going to replaced

    To the following

    Set i = wksCM.Cells(j, "A").Offset(0, 0) '"i" = the row that the data the is going to replace

    If above does not answer your question then feel free to get back to me and tell me exactly what is occurring including any error messages and which line of code errors.


    Regards, OssieMac

    • Marked as answer by Kosta si Friday, October 18, 2019 10:09 PM
    Thursday, October 17, 2019 9:18 PM
  • thank you,

    Kosta


    Vforum

    • Marked as answer by Kosta si Friday, October 18, 2019 12:31 PM
    Friday, October 18, 2019 12:31 PM
  • Hi Kosta si,

    You marked your "Thank you" post to me as the answer instead of the answer that I posted.


    Regards, OssieMac

    • Marked as answer by Kosta si Friday, October 18, 2019 10:09 PM
    Friday, October 18, 2019 8:08 PM

All replies

  • I assume that you were getting a code error but you have not indicated on which line errors.

    Untested but edit the following line of code

     Set i = wksCM.Range(Cells(j, "A")).Offset(0, 0) '"i" = the row that the data the is going to replaced

    To the following

    Set i = wksCM.Cells(j, "A").Offset(0, 0) '"i" = the row that the data the is going to replace

    If above does not answer your question then feel free to get back to me and tell me exactly what is occurring including any error messages and which line of code errors.


    Regards, OssieMac

    • Marked as answer by Kosta si Friday, October 18, 2019 10:09 PM
    Thursday, October 17, 2019 9:18 PM
  • thank you,

    Kosta


    Vforum

    • Marked as answer by Kosta si Friday, October 18, 2019 12:31 PM
    Friday, October 18, 2019 12:31 PM
  • Hi Kosta si,

    You marked your "Thank you" post to me as the answer instead of the answer that I posted.


    Regards, OssieMac

    • Marked as answer by Kosta si Friday, October 18, 2019 10:09 PM
    Friday, October 18, 2019 8:08 PM