Find and replace entry with new value and remove any not found

[wal_verin]

Hi all
I have gone through the past post find it difficult to get some help from previous post.
The idea is i want to create a command button that is able to update values in sheet 1 from sheet 2.
When the command button is pressed. The macro will check all the Id and update with new figures if it matches with that in sheet 2.
If it notices and old account number that is not found in sheet 2 it will delete this. The tricky thing is the row in which it has to update this figure is not on the same row as the account no. If it also finds that there is a new account no in sheet 2 that is not in sheet 1, it should insert it following the format below. It the value is the same then it should do nothing.
It should add them in ascending order of the account no.

Thanks a million
Below is an example of the idea

Sheet 1
A B C
Account no Text Amount
1- 10012 Tequeue
2- Checkbook 2000
3-
4- 10013 Bernato dia
Checkbook 3000


Sheet 2
A B C
Account no Text Amount
1-10011 Setin 500
2-10012 Tequeue 4500
3-10013 Bernato dia 3000
4-10014 Newman 1000

please help

 

[Tim_Excel_]

Sub replaceandcheckdata()
LRow =  Sheets(1).Cells(Sheets(1).Rows.Count, "A").End(xlUp).Row+1
For each Cell in Sheets(2).Range("A2:A" & LRow)
Set searchAccountNo = Sheets(1).Range("A2:A5").Find(Cell.Value)
If not searchAccountNo is Nothing then
Sheets(1).Range("B" & searchAccountNo & ":C" & searchAccountNo).Value = Sheets(2).Range("B" & Cell.Row & ":C" & Cell.Row).Value 
Else
LRow =  Sheets(1).Cells(Sheets(1).Rows.Count, "A").End(xlUp).Row+1
Sheets(1).Range("A" & LRow & ":C" & LRow).Value = Sheets(2).Range("A" & Cell.Row & ":C" & Cell.Row).Value
End If
Next Cell
End Sub

Does this do the trick for you? Make sure to back up your data before executing the code.

 

[wal_verin]

Thanks a million Tim Excel. I tried the code but it seems to go wild and instead of doing the changes necessary in sheet 1 (Avstämning) it does the opposite in sheet 2(Fortnoxbalans). At the same time it seems to be doing something other than the initial thought. Please i appreciate if you could help again. Below is my adjusted codes, probably i might have messed up something. Note the F column i chose is right because those are the values i want to be updated

Sub Button_replace_and_check()Dim v As Worksheet, w As Worksheet
Dim LRow As Long, Cell As Range
Dim Kontonr As Range


Application.ScreenUpdating = False
Application.EnableEvents = False


'Change the link to where you saved your files
Set v = Worksheets("Fortnoxbalans")
Set w = Worksheets("Avstämning")


LRow = v.Cells(v.Rows.Count, "A").End(xlUp).Row + 1
For Each Cell In w.Range("A1:A" & LRow)
Set Kontonr = v.Range("A7:A300").Find(Cell.Value)
If Not Kontonr Is Nothing Then
v.Range("B" & Kontonr & ":C" & Kontonr).Value = w.Range("B" & Cell.Row & ":F" & Cell.Row).Value
Else
LRow = v.Cells(w.Rows.Count, "A").End(xlUp).Row + 1
v.Range("A" & LRow & ":C" & LRow).Value = w.Range("A" & Cell.Row & ":F" & Cell.Row).Value
End If
Next Cell


Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[Tim_Excel_]

Noticed some lazy mistakes in my initial code, see below for the tested & working version. It loops through the col holding the Account no.'s in sheet 2, looks for these no.'s in sheet 1, and if the no. is found in sheet 1, the data from sheet 2 is copied to the respective no. in sheet 1. If the account no. from sheet 2 does not appear in sheet 1, all values from sheet 2's no. are copied to the last row in sheet 1.

Please adjust the references to your data.

Sub replaceandcheckdata()

LRow = Sheets(2).Cells(Sheets(2).Rows.Count, "A").End(xlUp).Row


For Each Cell In Sheets(2).Range("A2:A" & LRow)
    Set searchAccountNo = Sheets(1).Range("A2:A5").Find(Cell.Value)
    If Not searchAccountNo Is Nothing Then
        Sheets(1).Range("B" & searchAccountNo.Row & ":C" & searchAccountNo.Row).Value = Sheets(2).Range("B" & Cell.Row & ":C" & Cell.Row).Value
    Else
        LRow = Sheets(1).Cells(Sheets(1).Rows.Count, "A").End(xlUp).Row + 1
        Sheets(1).Range("A" & LRow & ":C" & LRow).Value = Sheets(2).Range("A" & Cell.Row & ":C" & Cell.Row).Value
    End If
Next Cell
End Sub

 

[wal_verin]

Hi Tim_Excel, I really appreciate your time in helping me out with this. It has saved alot of hours of try by error but i am still having challenges to get it working like you explained above in your reply.

It actually does not replace the value in Sheet 1 from Sheet 2, if it happens to be a different value and it would have been perfect if it could add the unfound account no in sheet 2 in ascending order directly after (But this can wait) If i can get the others working that is a major breakthrough . See below the output results

Could you also help in explaining your codes may be i can manage to adjust the little needed?. Thanks again

 

[wal_verin]

Sorry the image didn't show up
A Column B Column C Column row 9

2731	Avräkning lagstadgade sociala avgifter	-7 855,00
	Enligt huvudbok	-7 855,00
	Summa
2820	Kortfristiga skulder till anställda	-2 149,80
	Enligt huvudbok	-35 346,13

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Hi Tim_Excel, I really appreciate your time in helping me out with this. It has saved alot of hours of try by error but i am still having challenges to get it working like you explained above in your reply.

It actually does not replace the value in Sheet 1 from Sheet 2, if it happens to be a different value and it would have been perfect if it could add the unfound account no in sheet 2 in ascending order directly after (But this can wait) If i can get the others working that is a major breakthrough . See below the output results

Could you also help in explaining your codes may be i can manage to adjust the little needed?. Thanks again

 

[Tim_Excel_]

I am surprised it doesn't work for you, as it clearly does on my end. I have added comments in the code to explain its workings, hope this helps you.

Sub replaceandcheckdata()

LRow = Sheets(2).Cells(Sheets(2).Rows.Count, "A").End(xlUp).Row 'determines the last row in sheet(2)


For Each Cell In Sheets(2).Range("A2:A" & LRow) [COLOR=#ff0000]'starts a loop through all Account numbers in sheet(2) col A[/COLOR]
    Set searchAccountNo = Sheets(1).Range("A2:A5").Find(Cell.Value) [COLOR=#ff0000]'searches col A (change the range to your preferences) for the current cell value / account number in sheet(2) col A[/COLOR]
    If Not searchAccountNo Is Nothing Then[COLOR=#ff0000] 'If the search is not empty / if the account number was found in sheet(1)[/COLOR]
        Sheets(1).Range("B" & searchAccountNo.Row & ":C" & searchAccountNo.Row).Value = Sheets(2).Range("B" & Cell.Row & ":C" & Cell.Row).Value[COLOR=#ff0000] 'the two cells (B:C) next to the account number in sheet(1) col A are filled with the values from the respective cells in sheet(2)[/COLOR]
    Else [COLOR=#ff0000]'if [B]nothing[/B] was found[/COLOR]
        LRow = Sheets(1).Cells(Sheets(1).Rows.Count, "A").End(xlUp).Row + 1[COLOR=#ff0000] 'determine the last row in sheet(1), but since we'll add data below this row, set it to +1[/COLOR]
        Sheets(1).Range("A" & LRow & ":C" & LRow).Value = Sheets(2).Range("A" & Cell.Row & ":C" & Cell.Row).Value [COLOR=#ff0000]'paste the values in A:C in sheet(2) to the last row in sheet(1)[/COLOR]
    End If
Next Cell [COLOR=#ff0000]'continue to the next cell in the loop[/COLOR]
End Sub

 

[wal_verin]

Hi, I am quite surprise as well, Instead of it to update the Amount once the account no is found, It place it in the same row as the account no and the text in. It also does not delete those that are in sheet 1 and not in sheet 2, quite stranse as well.

I am surprised it doesn't work for you, as it clearly does on my end. I have added comments in the code to explain its workings, hope this helps you.

Sub replaceandcheckdata()

LRow = Sheets(2).Cells(Sheets(2).Rows.Count, "A").End(xlUp).Row 'determines the last row in sheet(2)


For Each Cell In Sheets(2).Range("A2:A" & LRow) [COLOR=#ff0000]'starts a loop through all Account numbers in sheet(2) col A[/COLOR]
    Set searchAccountNo = Sheets(1).Range("A2:A5").Find(Cell.Value) [COLOR=#ff0000]'searches col A (change the range to your preferences) for the current cell value / account number in sheet(2) col A[/COLOR]
    If Not searchAccountNo Is Nothing Then[COLOR=#ff0000] 'If the search is not empty / if the account number was found in sheet(1)[/COLOR]
        Sheets(1).Range("B" & searchAccountNo.Row & ":C" & searchAccountNo.Row).Value = Sheets(2).Range("B" & Cell.Row & ":C" & Cell.Row).Value[COLOR=#ff0000] 'the two cells (B:C) next to the account number in sheet(1) col A are filled with the values from the respective cells in sheet(2)[/COLOR]
    Else [COLOR=#ff0000]'if [B]nothing[/B] was found[/COLOR]
        LRow = Sheets(1).Cells(Sheets(1).Rows.Count, "A").End(xlUp).Row + 1[COLOR=#ff0000] 'determine the last row in sheet(1), but since we'll add data below this row, set it to +1[/COLOR]
        Sheets(1).Range("A" & LRow & ":C" & LRow).Value = Sheets(2).Range("A" & Cell.Row & ":C" & Cell.Row).Value [COLOR=#ff0000]'paste the values in A:C in sheet(2) to the last row in sheet(1)[/COLOR]
    End If
Next Cell [COLOR=#ff0000]'continue to the next cell in the loop[/COLOR]
End Sub

 

[Tim_Excel_]

I'm afraid what you're saying is just a little vague, for example I wouldn't know what you mean with "It place it in the same row as the account no and the text in". I suggest you take a good look at my code and tailor it to your own needs as I am out of time to help you any further

good luck

 

[wal_verin]

Sorry if i didn't explain clearly. If you would notice in my first post i described that the Amount and the Account no as well as text are not on the same row. The Account no and the text are on the same and then comes an additional text on another called Checkbook that has the amount to it on Column C. Now what your codes does is it places the updated value on the row above where lies the Account no and first text . Note these text are different from the headings. That is the output looks like this

Account no---------------- Text---------------- Amount
1- 10012----------------- Tequeue--------------25000 (The updated value )
2- '''----------------------Checkbook------------ 2000 (Instead of updating this value)

The information on the first two rows refer to the same customer. The text includes the name of the customer on row 1 and row 2 is the type of transaction.

Hope someone who is a Pro in VBA help out with this. I am still on the novice level