help to correct error code duplicate data

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,429
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I do not understand what you have in your code, you can explain what you need.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
thank's MoshiM but, it doesn't work you're change when i run the macro show this error "object required"
 
Last edited:
Upvote 0
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:

95c1c393bcc70018004bb5b7c6553076.jpg


But if the macro is necessary...
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top