Update if exist,otherwise insert

**Medex** · 01-14-2019, 02:33 AM

Hello dear members.

I got one situation and I will try to explain it.

I got excel file with two sheets (Sheet 1-Template, Sheet 2-DB). Template is used by manager who appoints yearly targets on monthly base. After state and approve the targets the data will be copy and pasted into DB. But sometimes manager can change targets after approve step.

In this point I need that if the data stated on Template exist on DB, Update, otherwise insert new lines. Actually I wrote code to insert lines but I couldn't write the proper code for check. Please help me on this issue.

Sub MonthlyTargetDB()

Application.ScreenUpdating = False

Dim Target As Workbook:         Set Target = ThisWorkbook
Dim Tmpl As Worksheet:          Set Tmpl = Target.Worksheets("Template")
Dim DB As Worksheet:            Set DB = Target.Worksheets("DB")
Dim i As Integer:               i = DB.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Dim j As Integer:               j = DB.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row

Tmpl.Range("A5").Copy
DB.Range(DB.Range("A" & i), DB.Range("A" & i + 11)).PasteSpecial (xlPasteValues)

Tmpl.Range("B5").Copy
DB.Range(DB.Range("B" & j), DB.Range("B" & j + 11)).PasteSpecial (xlPasteValues)


Tmpl.Range(Tmpl.Range("A8"), Tmpl.Range("B8").End(xlDown)).Copy
DB.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

**mohan.r1980** · 01-15-2019, 05:16 AM

See what i read your question

Sub test()
Dim shtTemp As Worksheet, shtDB As Worksheet
Dim r As Range, s As String, i As Long, MyVal As Long
Dim myRange As Range, MyYear As Range, myMonth As Range
Dim sCompany As String, sYear As String, sMonth As String

Set shtTemp = Worksheets("Template")
Set shtDB = Worksheets("DB")

Set myRange = shtDB.Range("A1:A" & shtDB.Range("A" & Rows.Count).End(xlUp).Row)
Set myMonth = shtDB.Range("C1:C" & shtDB.Range("A" & Rows.Count).End(xlUp).Row)
Set MyYear = shtDB.Range("B1:B" & shtDB.Range("A" & Rows.Count).End(xlUp).Row)

MyVal = shtTemp.Range("B21").Value
sCompany = shtTemp.Range("A5").Value
sYear = shtTemp.Range("B5").Value



If WorksheetFunction.SumIfs(shtDB.Range("D1:D" & shtDB.Range("A" & Rows.Count).End(xlUp).Row), myRange, sCompany, MyYear, sYear) = MyVal And _
   WorksheetFunction.CountIf(myRange, sCompany) > 0 Then
    
    MsgBox "No Changes done", vbInformation
    GoTo EndProcess
    
    
ElseIf WorksheetFunction.SumIfs(shtDB.Range("D1:D" & shtDB.Range("A" & Rows.Count).End(xlUp).Row), myRange, sCompany, MyYear, sYear) <> 0 And _
   WorksheetFunction.CountIf(myRange, sCompany) <> 0 Then
   
    
          For i = 1 To 12
              sMonth = shtTemp.Range("A" & i + 7).Value
              
              Set r = myRange.Find(sCompany)
                  
                  If Not r Is Nothing Then
                      
                          Do While Not (r.Offset(, 1).Value = sYear And r.Offset(, 2).Value = sMonth)
                           
                             Set r = r.Offset(1, 0)
                             
                              If Not r Is Nothing Then
                                  r.Select
                              End If
                           Loop
                     r.Offset(, 3).Value = shtTemp.Range("B" & i + 7).Value
                  
                  End If
        
          Next i
    
 Else
          
          For i = 1 To 12
                sMonth = shtTemp.Range("A" & i + 7).Value
                
                shtDB.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = sCompany
                shtDB.Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = sYear
                shtDB.Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = sMonth
                shtDB.Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = shtTemp.Range("B" & i + 7).Value
          Next i
    
End If
        MsgBox "Add/Updates Successfully", vbInformation
EndProcess:

End Sub

**Medex** · 01-15-2019, 02:03 PM

Mohan.r1980 I am so grateful to you for your code, really appreciate it. Actually code works when I add targets for the first time. But if I want to update the previous data I got "run-time error 1004" and code shows the problem for

r.select

. Please help me to solve that problem.

**mohan.r1980** · 01-16-2019, 02:28 AM

Hi Medex,

find below updated code

Sub test()
Dim shtTemp As Worksheet, shtDB As Worksheet
Dim r As Range, s As String, i As Long, MyVal As Long
Dim myRange As Range, MyYear As Range, myMonth As Range
Dim sCompany As String, sYear As String, sMonth As String

Application.ScreenUpdating = False

Set shtTemp = Worksheets("Template")
Set shtDB = Worksheets("DB")

Set myRange = shtDB.Range("A1:A" & shtDB.Range("A" & Rows.Count).End(xlUp).Row)
Set myMonth = shtDB.Range("C1:C" & shtDB.Range("A" & Rows.Count).End(xlUp).Row)
Set MyYear = shtDB.Range("B1:B" & shtDB.Range("A" & Rows.Count).End(xlUp).Row)

MyVal = shtTemp.Range("B21").Value
sCompany = shtTemp.Range("A5").Value
sYear = shtTemp.Range("B5").Value

shtDB.Select

If WorksheetFunction.SumIfs(shtDB.Range("D1:D" & shtDB.Range("A" & Rows.Count).End(xlUp).Row), myRange, sCompany, MyYear, sYear) = MyVal And _
   WorksheetFunction.CountIf(myRange, sCompany) > 0 Then
    
    MsgBox "No Changes done", vbInformation
    shtTemp.Select
    GoTo EndProcess
    
    
ElseIf WorksheetFunction.SumIfs(shtDB.Range("D1:D" & shtDB.Range("A" & Rows.Count).End(xlUp).Row), myRange, sCompany, MyYear, sYear) <> 0 And _
   WorksheetFunction.CountIf(myRange, sCompany) <> 0 Then
   
    
          For i = 1 To 12
              sMonth = shtTemp.Range("A" & i + 7).Value
              
              Set r = myRange.Find(sCompany)
                  
                  If Not r Is Nothing Then
                      
                          Do While Not (r.Offset(, 1).Value = sYear And r.Offset(, 2).Value = sMonth)
                           
                             Set r = r.Offset(1, 0)
                             
                              If Not r Is Nothing Then
                                  r.Select
                              End If
                           Loop
                     r.Offset(, 3).Value = shtTemp.Range("B" & i + 7).Value
                  
                  End If
        
          Next i
    
 Else
          
          For i = 1 To 12
                sMonth = shtTemp.Range("A" & i + 7).Value
                
                shtDB.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = sCompany
                shtDB.Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = sYear
                shtDB.Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = sMonth
                shtDB.Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = shtTemp.Range("B" & i + 7).Value
          Next i
    
End If
        MsgBox "Add/Updates Successfully", vbInformation
     shtTemp.Select
     shtTemp.Range("A1").Select
    Application.ScreenUpdating = True
EndProcess:

End Sub

**Medex** · 01-16-2019, 02:51 AM

Mohan.r1980 your code works perfectly. you'r are really so talented. Thanks a lot for your support.

**mohan.r1980** · 01-16-2019, 02:57 AM

Your welcome dear and thanks for rep

Update if exist,otherwise insert

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Update if exist,otherwise insert

Re: Update if exist,otherwise insert

Re: Update if exist,otherwise insert

Re: Update if exist,otherwise insert

Re: Update if exist,otherwise insert

Re: Update if exist,otherwise insert

Thread Information

Users Browsing this Thread

Similar Threads

Insert rows if value doesn't exist

Code to update or insert if doesnt exist

How can I insert continuous serial number when only in Name exist in col B?

Insert row if text doesn't exist

search for worksheet, insert new if doesn't exist

[SOLVED] Insert if not exist

How do i insert lines into a worksheet depending on if data exist.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions