help to correct error code duplicate data

abdelfattah

Board Regular
hi, gays
i have data in sheet1 i would transfer data just non duplicated to sheet 2 i try with this code but show me error " object required"
and this is my code

Code:
Public Sub SumDuplicateData()


Dim Srch As Range
Sheet2.Range("A2:G100").Value = Empty

For I = 2 To Sheet1.Range("F100").End(xlUp).Row
Set Srch = Sheet2.Range("F2:F100").Find(Sheet1.Range("F" & I).Value)
If Srch Is Nothing Then
For j = 1 To 6
Sheet2.Range(Cells(Sheet2.Range("F100").End(xlUp).Offset(1, 0).Row, j).Address).Value = Sheet1.Range(Cells(I, j).Address).Value
Next j
Sheet2.Range("G" & Sheet2.Range("F100").End(xlUp).Row).Value = Sheet2.Range("F100").End(xlUp).Row - 1
Else
For j = 1 To 3
Sheet2.Range(Cells(Srch.Row, j).Address).Value = Sheet1.Range(Cells(I, j).Address).Value + Sheet2.Range(Cells(Srch.Row, j).Address).Value
Next j
End If
Next I


End Sub
 
Last edited by a moderator:

MoshiM

Active Member
i have data in sheet1 i would transfer data just non duplicated to sheet 2 i try with this code but show me error " object required"
and this is my code
The following macro is untested. Please read the comments I'm not entirely sure that I understood what your code is supposed to do
Code:
Public Sub SumDuplicateData()

    Dim Srch As Range, T As Long

    Sheet2.Range("A2:G100").clear 'clear this range

    T = Range("F100").End(xlUp).row 'last used row in Column F starting at row 100 going upwards

    For I = 2 To T

Set Srch = Sheet2.Range("F2:F100").Find(Sheet1.Range("F" & I).Value2)

        If Srch Is Nothing Then 'If the sought range is not found

            For j = 1 To 6

                Sheet2.Cells(T + 1, j).Value2 = Sheet1.Cells(I, j).Value2
                'T + 1 Is last used row in Column F starting at row 100 going upwards then down 1
            Next j

            Sheet2.Range("G" & T).Value2 = Sheet2.Range("F" & T - 1).value2

            'T - 1 Is last used row in Column F starting at row 100 going upwards then up 1
            'Did you want a row number here or a value[currently set to value]?
            'If a row number is desired then change Sheet2.Range("F" & T - 1).value2 to Sheet2.Range("F" & T - 1).row

        Else

            For j = 1 To 3

                Sheet2.Cells(Srch.Row, j).Value2 = Sheet1.Cells(I, j).Value2 + Sheet2.Cells(Srch.Row, j).Value2

            Next j

        End If

    Next I

End Sub
 
Last edited:

abdelfattah

Board Regular
i would find the mistake about error message what i mentioned
i have sheet1 contains data some of them duplicated i wanna transfer total values duplicated data to sheet2
when i run the macro ocurres error this message object requires
i hope this clear
 

MoshiM

Active Member
i would find the mistake about error message what i mentioned
i have sheet1 contains data some of them duplicated i wanna transfer total values duplicated data to sheet2
when i run the macro ocurres error this message object requires
i hope this clear
Where are you getting the error?

Also change
Code:
T = Range("F100").End(xlUp).row
to
Code:
T = Sheet2.Range("F100").End(xlUp).row
 

DanteAmor

Well-known Member
i would find the mistake about error message what i mentioned
i have sheet1 contains data some of them duplicated i wanna transfer total values duplicated data to sheet2
when i run the macro ocurres error this message object requires
i hope this clear
Forget the code a bit, explain how your data is and what you need to do.
I understand that you want to copy lake from one part to another part.
But I do not know how your data is, nor what do you mean by "some of them duplicated and wanna transfer total values ​​duplicated"

Just write some examples.
If there are duplicates, in which column or which columns.
If there are 5 duplicates, do you want to copy or move the 5 records or only 4, which 4? the first, the last?
 

abdelfattah

Board Regular
Forget the code a bit, explain how your data is and what you need to do.
I understand that you want to copy lake from one part to another part.
But I do not know how your data is, nor what do you mean by "some of them duplicated and wanna transfer total values ​​duplicated"

Just write some examples.
If there are duplicates, in which column or which columns.
If there are 5 duplicates, do you want to copy or move the 5 records or only 4, which 4? the first, the last?
let me more explanation
i have sheet1 containes data in cells from a2: g100 ok when i fill data i have duplicated data i would transfer the data to sheet2
SHEET1:
a b c d e f g
ITEM BRAND TYPE ORIGIN IMPORT EXPORT BALANCE
1 1200R20 G580 THI 200 50 150
2 1200R20 G580 THI 300 150 150

AND WHAT IWOUL SHOW THE RESULT IN SHEET2 LIKE THIS
a b c d e f g
ITEM BRAND TYPE ORIGIN IMPORT EXPORT BALANCE
1 1200R20 G580 THI 500 200 300
i hope this clear
 

abdelfattah

Board Regular
thank's MoshiM but, it doesn't work you're change when i run the macro show this error "object required"
 
Last edited:

DanteAmor

Well-known Member
let me more explanation
i have sheet1 containes data in cells from a2: g100 ok when i fill data i have duplicated data i would transfer the data to sheet2
SHEET1:
a b c d e f g
ITEM BRAND TYPE ORIGIN IMPORT EXPORT BALANCE
1 1200R20 G580 THI 200 50 150
2 1200R20 G580 THI 300 150 150

AND WHAT IWOUL SHOW THE RESULT IN SHEET2 LIKE THIS
a b c d e f g
ITEM BRAND TYPE ORIGIN IMPORT EXPORT BALANCE
1 1200R20 G580 THI 500 200 300
i hope this clear
You could get that result with a pivot table:



But if the macro is necessary...
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top