Macro Script help

[Joe Clueless]

Need some help please, somebody has provided me with the the following macro
and I need to make a change but i'm not exactly sure how to implement the
change.

At present I am unable to get in touch with the person , that provided it
and I need to try and get this all done this afternoon.
The instruction was as follows : "you might have some unwanted spaces in at
the begining or end of the data.Try altering the second line as shown below"

How do i put this

For Each Dn1 In Rng1
If Trim(Dn2) = Trim(Dn1) Then 'Alter this line

into this, do i append it to line 2 or what.

thanks if you can help

Sub compare
Dim Rng1 As Range, Dn1 As Range, Rng2 As Range, Dn2 As Range
With Sheets("Sheet1") '1
Set Rng1 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp))
End With
With Sheets("Sheet2") '2
Set Rng2 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp))
End With
For Each Dn2 In Rng2
For Each Dn1 In Rng1
If Dn2 = Dn1 Then
Dn1.Offset(, -4).Resize(, 31).Copy Dn2.Offset(, -4).Resize(,
31)
End If
Next Dn1
Next Dn2
End Sub

 

[Mike H]

Hi,

I would include the TRIm and this further mod to eliminate and case issues

Sub compare()
Dim Rng1 As Range, Dn1 As Range, Rng2 As Range, Dn2 As Range
With Sheets("Sheet1") '1
Set Rng1 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp))
End With

With Sheets("Sheet2") '2
Set Rng2 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp))
End With
For Each Dn2 In Rng2
For Each Dn1 In Rng1
If UCase(Trim(Dn2)) = UCase(Trim(Dn1)) Then
Dn1.Offset(, -4).Resize(, 31).Copy Dn2.Offset(, -4).Resize(,
31)
End If
Next Dn1
Next Dn2
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Joe Clueless]

hi
thanks for your help.

it still wont work properly on the original workbook but on a new workbook
with dummy data it was perfect.
So it looks like there is some "fault" in the original thats provoking the
problem. not sure what it is but will make a copy and remove all formatting
etc and see if i can sort it out

Many thanks

 

[Mike H]

Joe,

You may have non printable characters so copy to a new column using
=clean(e1) and copy down, you can then paste this back over the original
(paste values) and see if that does the trick

You could also try this line in the macro
If WorksheetFunction.Clean(UCase(Trim(Dn2))) =
WorksheetFunction.Clean(UCase(Trim(Dn1))) Then
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Joe Clueless]

Sort of got to the cause of the problem.
In column E I have a unique reference number that is being used. Any number
in the ranger 1343 to 1363 inclusive will cause the problem.
As long as I dont use those numbers then all is well.

Very strange (well to me it is !)

much appreciate your help though, thanks