Microsoft Excel - Macro for compare 2 range in one Excel sheet

Asked By John on 08-Nov-10 04:59 AM

I have an Excel file given as below. I want to compare Two Range in one excel sheet.

Range1 is Total Panel (Blue Highlighted) Rage2 is Yellow Highlighted. I want which panel nos. missing in range 1. Missing panels should be highlighted with any other colour.

Can you any one give a solution for this. I got a macro for this but not working properly. Can anyone to help to make it work properly.



Sub MarkDifferences()
Dim active_sheet As Worksheet
Dim name1 As String
Dim name2 As String
Dim range1 As Range
Dim range2 As Range
Dim cells1 As Collection
Dim cells2 As Collection
Dim cell1 As Range
Dim cell2 As Range
Dim key As String
Dim no_match As Boolean

    Set active_sheet = ActiveSheet
'    name1 = InputBox$("First Range Name:", "First Range",
' "")
name1 = "Range1"
    If Len(name1) = 0 Then Exit Sub
    Set range1 = active_sheet.Range(name1)

'    name2 = InputBox$("Second Range Name:", "Second
' Range", "")
name2 = "Range2"
    If Len(name2) = 0 Then Exit Sub
    Set range2 = active_sheet.Range(name2)

    ' Make normal collections holding the cells.
    Set cells1 = New Collection
    For Each cell1 In range1.Cells
      key = cell1.Row - range1.Row & "," & cell1.Column - _
        range1.Column
      cells1.Add cell1, key
    Next cell1

    Set cells2 = New Collection
    For Each cell2 In range2.Cells
      key = cell2.Row - range2.Row & "," & cell2.Column - _
        range2.Column
      cells2.Add cell2, key
    Next cell2

    ' Examine the cells in the first collection.
    For Each cell1 In cells1
      On Error Resume Next
      Err.Clear
      key = cell1.Row - range1.Row & "," & cell1.Column - _
        range1.Column
      Set cell2 = cells2(key)
      If Err.Number <> 0 Then
        ' The second cell is missing.
        no_match = True
      ElseIf cell1.Text <> cell2.Text Then
        ' The cells don't match.
        no_match = True
      Else
        no_match = False
      End If

      ' If the cells don't match, color cell1.
      If no_match Then
        With cell1.Interior
          .ColorIndex = 35
          .Pattern = xlSolid
        End With
      Else
        With cell1.Interior
          .ColorIndex = xlNone
        End With
      End If
    Next cell1

    ' Examine the cells in the second collection.
    For Each cell2 In cells2
      On Error Resume Next
      Err.Clear
      key = cell2.Row - range2.Row & "," & cell2.Column - _
        range2.Column
      Set cell1 = cells1(key)
      If Err.Number <> 0 Then
        ' The second cell is missing.
        no_match = True
      ElseIf cell2.Text <> cell1.Text Then
        ' The cells don't match.
        no_match = True
      Else
        no_match = False
      End If

      ' If the cells don't match, color cell2.
      If no_match Then
        With cell2.Interior
          .ColorIndex = 35
          .Pattern = xlSolid
        End With
      Else
        With cell2.Interior
          .ColorIndex = xlNone
        End With
      End If
    Next cell2
End Sub


 

ZONE -1 BLOCK -1

ZONE -1 BLOCK - 2

ZONE -1 BLOCK - 13

ZONE -1 BLOCK - 14

ZONE -1 BLOCK - 15

ZONE -1 BLOCK - 16

ZONE -1 BLOCK - 17

ZONE -1 BLOCK - 18

 

 

 

TOTAL

SL.NO

PANEL NO.

REQD

SL.NO

PANEL NO.

REQD

SL.NO

PANEL NO.

REQD

SL.NO

PANEL NO.

REQD

SL.NO

PANEL NO.

REQD

SL.NO

PANEL NO.

REQD

SL.NO

PANEL NO.

REQD

SL.NO

PANEL NO.

REQD

 

 

 

PANEL NO.

REQD

QTY

QTY

QTY

QTY

QTY

QTY

QTY

QTY

 

 

 

QTY

1

IP-01

44

1

IP-01

42

1

IP-01

57

1

IP-01

25

1

IP-01

74

1

IP-01

71

1

IP-01

54

1

IP-01

71

 

 

 

IP-01

438

2

IP-03

4

2

IP-D-160

1

2

IP-05

1

2

IP-02

50

2

IP-03

11

2

IP-01A

1

2

IP-03

29

2

IP-02

2

 

 

 

IP-01A

1

3

IP-D-160

2

3

IP-D-180

4

3

IP-D-160

3

3

IP-03

16

3

IP-0-52

1

3

IP-03

11

3

IP-D-165

2

3

IP-03

20

 

 

 

IP-03

91

4

IP-D-180

3

4

IP-D-180S

6

4

IP-D-180

4

4

IP-0-58

4

4

IP-0-58

1

4

IP-0-58

1

4

IP-D-180

11

4

IP-05

2

 

 

 

IP-D-160

7

5

IP-D-180S

5

5

IP-0-59

4

5

IP-D-180S

4

5

IP-0-60

1

5

IP-0-62

1

5

IP-0-60

2

5

IP-D-180S

2

5

IP-D-160

1

 

 

 

IP-D-162

8

6

IP-0-66

2

6

IP-0-64

1

6

IP-0-60

5

6

IP-0-62

1

6

IP-0-70

4

6

IP-0-68

3

6

IP-0-58

2

6

IP-D-180

9

 

 

 

IP-D-165

2

7

IP-0-72

1

7

IP-0-68

1

7

IP-0-66

1

7

IP-0-66

2

7

IP-0-78

4

7

IP-0-72

1

7

IP-0-64

2

7

IP-D-180S

7

 

 

 

IP-D-180

49

8

IP-0-84

1

8

IP-0-82

1

8

IP-0-82

1

8

IP-0-68

2

8

IP-0-82

3

8

IP-0-76

1

8

IP-0-70

1

8

IP-0-58

2

 

 

 

IP-D-180S

50

9

IP-0-86

1

9

IP-0-95

1

9

IP-0-86

1

9

IP-0-72

1

9

IP-0-86

1

9

IP-0-80

3

9

IP-0-74

1

9

IP-0-60

1

 

 

 

IP-0-58

10

10

IP-0-90

2

10

IP-1-02

1

10

IP-1-00

1

10

IP-0-74

1

10

IP-0-90

1

10

IP-0-84

2

10

IP-0-78

2

10

IP-0-66

1

 

 

 

IP-0-59

4

11

IP-1-08

1

11

IP-1-23

2

11

IP-1-02

1

11

IP-0-76

1

11

IP-0-96

2

11

IP-0-98

3

11

IP-0-80

1

11

IP-0-70

1

 

 

 

IP-0-60

9

12

IP-1-24

1

12

IP-2-80

1

12

IP-1-28

1

12

IP-0-86

1

12

IP-2-30

1

12

IP-2-94

2

12

IP-0-86

2

12

IP-0-72

1

 

 

 

IP-0-64

3

13

IP-1-40

1

13

IP-2-96

2

13

IP-2-82

2

13

IP-0-88

1

13

IP-2-82

2

13

IP-3-24

2

13

IP-0-96

1

13

IP-0-74

1

 

 

 

IP-0-66

6

14

IP-2-76

1

14

IP-3-01

2

14

IP-2-92

2

14

IP-0-94

1

14

IP-3-20

1

14

IP-3-40

2

14

IP-0-98

3

14

IP-0-76

1

 

 

 

IP-0-68

6

15

IP-2-90

2

15

IP-3-23

2

15

IP-2-96

3

15

IP-1-42

1

15

IP-3-42

1

15

IP-3-48

2

15

IP-2-82

1

15

IP-0-78

1

 

 

 

IP-0-70

6

16

IP-2-92

2

16

IP-3-37

2

16

IP-3-04

2

16

IP-2-12

1

16

IP-3-44

1

16

IP-3-62

1

16

IP-2-98

2

16

IP-0-80

3

 

 

 

IP-0-72

4

17

IP-2-96

4

17

IP-3-66

1

17

IP-3-30

3

17

IP-2-40

1

17

IP-3-66

1

17

IP-3-76

1

17

IP-3-10

1

17

IP-0-82

2

 

 

 

IP-0-74

3

18

IP-2-98

2

18

IP-3-72

3

18

IP-3-38

2

18

IP-2-98

2

18

IP-3-70

1

18

IP-3-78

6

18

IP-3-26

1

18

IP-0-84

1

 

 

 

IP-0-76

3

19

IP-3-48

1

19

IP-3-78

2

19

IP-3-52

2

19

IP-3-96

3

19

IP-3-78

1

19

IP-3-98

2

19

IP-3-30

1

19

IP-0-94

1

 

 

 

IP-0-78

7

20

IP-3-50

1

20

IP-3-85

2

20

IP-3-62

1

20

IP-4-08

1

20

IP-3-80

6

20

IP-4-04

2

20

IP-3-34

2

20

IP-0-98

3

 

 

 

IP-0-80

7

21

IP-3-52

1

21

IP-3-89

2

21

IP-3-78

3

21

IP-4-12

1

21

IP-3-86

1

21

IP-4-06

1

21

IP-3-36

1

21

IP-1-08

1

 

 

 

IP-0-82

7

22

IP-3-64

1

22

IP-4-07

2

22

IP-3-84

1

22

IP-4-52

1

22

IP-3-90

2

22

IP-4-08

1

22

IP-3-38

2

22

IP-2-76

1

 

 

 

IP-0-84

4

23

IP-3-78

1

23

IP-4-45

1

23

IP-4-02

2

23

IP-4-54

1

23

IP-3-92

1

23

IP-4-10

1

23

IP-3-52

1

23

IP-2-88

1

 

 

 

IP-0-86

6

24

IP-3-80

2

24

IP-4-74

1

24

IP-4-26

1

24

IP-4-64

1

24

IP-4-00

2

24

IP-4-12

1

24

IP-3-54

1

24

IP-2-94

2

 

 

 

IP-0-90

3

25

IP-3-96

1

25

IP-4-87

1

25

IP-5-00

1

25

IP-4-66

1

25

IP-4-08

1

25

IP-4-14

1

25

IP-3-56

1

25

IP-2-96

1

 

 

 

IP-0-95

1

26

IP-4-04

1

26

IP-5-24

1

26

IP-5-06

1

26

IP-4-68

1

26

IP-4-30

3

26

IP-4-18

4

26

IP-3-58

1

26

IP-3-00

1

 

 

 

IP-0-96

3

27

IP-4-18

2

27

IP-5-47

2

27

IP-5-18

1

27

IP-4-72

2

27

IP-4-32

3

27

IP-4-20

1

27

IP-3-82

1

27

IP-3-26

2

 

 

 

IP-0-98

9

28

IP-4-22

1

28

IP-5-38

1

28

IP-5-38

1

28

IP-4-76

2

28

IP-4-40

1

28

IP-4-22

1

28

IP-3-88

1

28

IP-3-28

1

 

 

 

IP-1-02

2

29

IP-4-36

1

29

IP-5-66

2

29

IP-5-44

1

29

IP-4-90

1

29

IP-4-50

2

29

IP-4-26

3

29

IP-3-94

1

29

IP-3-36

1

 

 

 

IP-1-08

2

30

IP-4-52

1

30

IP-5-69

10

30

IP-5-58

2

30

IP-4-92

2

30

IP-4-54

1

30

IP-4-28

1

30

IP-4-08

1

30

IP-3-44

1

 

 

 

IP-1-23

2

31

IP-4-70

3

31

IP-5-86

1

31

IP-5-54

1

31

IP-4-96

1

31

IP-4-62

1

31

IP-4-38

1

31

IP-4-38

1

31

IP-3-46

1

 

 

 

IP-1-24

1

32

IP-4-74

4

32

IP-5-89

2

32

IP-5-60

1

32

IP-5-14

2

32

IP-4-64

1

32

IP-4-58

1

32

IP-4-60

1

32

IP-3-52

1

 

 

 

IP-1-40

1

33

IP-5-44

1

 

 

107

33

IP-5-64

2

33

IP-5-16

1

33

IP-4-88

1

33

IP-4-84

1

33

IP-4-66

2

33

IP-3-58

1

 

 

 

IP-1-42

1

34

IP-5-50

2

 

 

 

34

IP-5-68

3

34

IP-5-24

1

34

IP-5-40

1

34

IP-5-00

1

34

IP-4-74

1

34

IP-3-70

1

 

 

 

IP-2-76

2

35

IP-5-60

1

 

 

 

35

IP-5-70

8

35

IP-5-26

2

35

IP-5-58

2

35

IP-5-02

1

35

IP-5-14

1

35

IP-3-72

1

 

 

 

IP-2-80

1

36

IP-5-66

1

 

 

 

36

IP-5-76

1

36

IP-5-28

2

36

IP-5-84

1

36

IP-5-22

1

36

IP-5-56

3

36

IP-4-04

1

 

 

 

IP-2-82

5

37

IP-5-68

1

 

 

 

 

TOTAL

127

37

IP-5-32

1

37

IP-5-62

2

37

IP-5-24

1

37

IP-5-60

1

37

IP-4-14

1

 

 

 

IP-2-90

2

38

IP-5-68A

1

 

 

 

 

 

 

38

IP-5-34

2

38

IP-5-60

1

38

IP-5-46

1

38

IP-5-62

1

38

IP-4-22

1

 

 

 

IP-2-92

4

39

IP-5-70

1

 

 

 

 

 

 

39

IP-5-48

1

39

IP-5-68

3

39

IP-5-52

1

39

IP-5-64

4

39

IP-4-34

3

 

 

 

IP-2-94

4

40

IP-5-72

1

 

 

 

 

 

 

40

IP-5-50

1

40

IP-5-78

2

40

IP-5-54

2

40

IP-5-66

2

40

IP-4-38

4

 

 

 

IP-2-96

10

41

IP-5-74

2

 

 

 

 

 

 

41

IP-5-56

1

41

IP-5-82

1

41

IP-5-58

1

41

IP-5-68

1

41

IP-4-44

1

 

 

 

IP-2-98

6

42

IP-5-82

1

 

 

 

 

 

 

42

IP-5-60

5

42

IP-5-48

1

42

IP-5-62

2

42

IP-5-70

6

42

IP-4-82

1

 

 

 

IP-3-00

1

43

IP-5-90

1

 

 

 

 

 

 

43

IP-5-64

2

43

IP-5-56

1

43

IP-5-64

2

43

IP-5-72

1

43

IP-5-18

1

 

 

 

IP-3-01

2

44

IP-5-98

1

 

 

 

 

 

 

44

IP-5-66

3

44

IP-5-66

7

44

IP-5-66

2

44

IP-5-74

1

44

IP-5-26

1

 

 

 

IP-3-10

1

 

 

114

 

 

 

 

 

 

45

IP-5-68

1

45

IP-5-30

1

45

IP-5-68

2

45

IP-5-78

4

45

IP-5-34

2

 

 

 

IP-3-23

2

 

 

 

 

 

 

 

 

 

46

IP-5-70

9

46

IP-5-14

1

46

IP-5-70

5

46

IP-5-86

1

46

IP-5-38

1

 

 

 

IP-3-24

2

114

 

 

 

 

 

 

 

 

47

IP-5-74

3

47

IP-5-92

1

47

IP-5-72

3

47

IP-5-92

3

47

IP-5-44

1

 

 

 

IP-3-26

3

107

 

 

 

 

 

 

 

 

48

IP-5-78

1

48

IP-5-76

2

48

IP-5-74

3

 

TOTAL

166

48

IP-5-48

1

 

 

 

IP-3-30

4

127

 

 

 

 

 

 

 

 

49

IP-5-84

1

49

IP-5-86

1

49

IP-5-78

1

 

 

 

49

IP-5-52

1

 

 

 

IP-3-34

2

188

 

 

 

 

 

 

 

 

50

IP-5-86

2

50

IP-5-70

4

50

IP-5-84

1

 

 

 

50

IP-5-54

1

 

 

 

IP-3-36

2

194

 

 

 

 

 

 

 

 

51

IP-D-162

3

51

IP-5-88

2

51

IP-5-92

2

 

 

 

51

IP-5-56

1

 

 

 

IP-3-37

2

187

 

 

 

 

 

 

 

 

52

IP-D-180

6

52

IP-5-34

1

52

IP-5-96

1

 

 

 

52

IP-5-60

1

 

 

 

IP-3-38

4

166

 

 

 

 

 

 

 

 

53

IP-D-180S

8

53

IP-5-16

1

53

IP-D-162

2

 

 

 

53

IP-5-64

3

 

 

 

IP-3-40

2

194

 

 

 

 

 

 

 

 

 

TOTAL

188

54

IP-5-74

1

54

IP-D-180

5

 

 

 

54

IP-5-66

2

 

 

 

IP-3-48

3

 

 

 

 

 

 

 

 

 

 

 

 

55

IP-6-14

1

55

IP-D-180S

10

 

 

 

55

IP-5-68

6

 

 

 

IP-3-50

1

###

 

 

 

 

 

 

 

 

 

 

 

56

IP-D-162

3

 

TOTAL

187

 

 

 

56

IP-5-70

3

 

 

 

IP-3-52

5

 

 

 

 

 

 

 

 

 

 

 

 

57

IP-D-180

7

 

 

 

 

 

 

57

IP-5-72

1

 

 

 

IP-3-54

1

 

 

 

 

 

 

 

 

 

 

 

 

58

IP-D-180S

8

 

 

 

 

 

 

58

IP-5-76

3

 

 

 

IP-3-56

1

 

 

 

 

 

 

 

 

 

 

 

 

 

TOTAL

194

 

 

 

 

 

 

59

IP-5-78

2

 

 

 

IP-3-58

2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

60

IP-5-82

1

 

 

 

IP-3-62

2

 

1277

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

61

IP-5-92

2

 

 

 

IP-3-64

1

 

1130

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

TOTAL

194

 

 

 

IP-3-66

2

 

147

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-3-72

4

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-3-76

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-3-78

13

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-3-80

8

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-3-82

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-3-85

2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-3-88

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-3-89

2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-3-94

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-3-96

4

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-04

4

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-06

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-07

2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-08

4

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-10

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-12

2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-14

2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-18

6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-20

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-22

3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-26

4

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-28

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-36

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-38

6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-45

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-52

2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-58

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-60

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-66

3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-70

3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-74

6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-84

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-4-87

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-00

2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-02

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-14

4

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-22

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-24

3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-38

3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-44

3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-46

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-47

2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-50

3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-52

2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-54

4

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-56

6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-58

5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-60

10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-62

5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-64

13

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-66

19

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-68

17

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-68A

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-69

10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-70

36

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-72

6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-74

10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-78

10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-82

3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-84

3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-86

5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-89

2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-90

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-92

8

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-96

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

IP-5-98

1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

TOTAL

1130

Rolf Jaeger replied to John on 08-Nov-10 12:24 PM
Hi John:

your post seems to have been clipped somewhere in the process (the Total panel did not show at all). Please repost with just a subset of your worksheet (or alternatively upload the workbook).

Also, please explain how you would like the results of the comparison be presented to you.

Best wishes,
Rolf
John replied to Rolf Jaeger on 14-Nov-10 05:57 AM

Thank you for your reply. I added here with my Excel file zip format .

Book2.zip


This is my worksheet.  My range 2 is Column A to columns P. (A1,A2 to P1,P2 contains the columns header. Range1 starting from Column T1 and Column U1.
(T1,T2, U1, U2 are column Header). Is it possible to upload my excel sheet anyway here. I don't know how to upload excel file here.

I want to check range2 for which panels are not added in range1. Some panels No. are missing in range1. eg. IP-5-18 not in range1 but it is on range2. I want to add these panels to range1. It is very difficult to find out which panels are not added yet in Range1. can you give a  macro can that can  findout which panel need to add  range1 it will be very easy for me.

Actually I need to add which panels are missing in range1 from range2 as a sorting order. I mean serial wise on the same sheet or another sheet one.


John replied to Rolf Jaeger on 14-Nov-10 05:58 AM

I added my Excel file here and waiting for your favourable reply

Rolf Jaeger replied to John on 15-Nov-10 08:34 PM
Hi John:

Please give the code listed below a try. It will list all panels appearing in your range2, but missing in your range1 in column W of your worksheet.

Hope this helped,
Rolf

Const COLUMN_OF_MISSING_PANELS As String = "W"
 
Sub ListMissingPanels()
  Columns(COLUMN_OF_MISSING_PANELS).Clear
  Dim pnlRange1 As Range, pnlRange2 As Range
  Set pnlRange2 = Range(Range("A1"), Range("P64"))
  Set pnlRange1 = Range(Range("T1"), Range("T" & Rows.Count).End(xlUp))
  Dim r As Range
  Dim p As Range
  Dim pnlFound As Range
  Range(COLUMN_OF_MISSING_PANELS & 1).Value = "Panels missing in Range 1"
  For Each p In pnlRange2.Columns
    If (-1) ^ p.Column < 0 Then 'Only look in odd columns
      Dim c As Range
      For Each c In p.Cells.SpecialCells(xlCellTypeConstants)
        If c.Row > 1 Then
          On Error Resume Next
          Set pnlFound = pnlRange1.Find(c.Value)
          If pnlFound Is Nothing Then
            If Not IsInrange(Columns(COLUMN_OF_MISSING_PANELS), c.Value) Then Range(COLUMN_OF_MISSING_PANELS & Rows.Count).End(xlUp).Offset(1).Value = c.Value
          End If
          Set pnlFound = Nothing
        End If
      Next c
    End If
  Next p
End Sub
 
Function IsInrange(r As Range, s As String)
  Dim c As Range
  For Each c In r.Cells.SpecialCells(xlCellTypeConstants)
    If c.Value = s Then
      IsInrange = True
      Exit Function
    End If
  Next c
  IsInrange = False
End Function

John replied to Rolf Jaeger on 20-Nov-10 03:07 AM
Thanks alot. Its working.
John replied to John on 20-Nov-10 07:13 AM
JOHNS FILE.zip If possible can you please update the macro suitable for this attached excel file.
Rolf Jaeger replied to John on 20-Nov-10 01:59 PM
Hi John:

sorry can't do, for a couple reasons:

  1. your latest Excel workbook doesn't have much in common with the workbook you described in your original post (quite frankly I am not clear at all what you are trying to accomplish with the macro I originally proposed in this latest workbook)
  2. I am trying to help people develop their own VBA coding capabilities. When I provide extensive code I am doing so in the hope that the people I am helping begin to pick up their end and try to modify my code to their specific requirements (at least within reason), and
  3. your final request is bordering a request for contract work; if that's what you had in mind send me e-mail at office@soarentcomputing.com.
I trust you understand my position.

Best wishes,
Rolf
John replied to Rolf Jaeger on 21-Nov-10 07:47 AM
Sir

I need to check only the PANEL NO. not only look in odd columns. That correction only I just ask you. I don't have enough knowledge of macro or VBA. But I would like to learn.