Macro to compare columns and output the results
Bernie,
Thanks, this works.
Beth
"Bernie Deitrick" wrote:
Beth,
Great.
Immediately after your .Columns("H").Insert, use code like
Range("H1").Value = "Column Title for H"
HTH,
Bernie
MS Excel MVP
"Beth" wrote in message
...
Hi Bernie,
Thanks! I figured out the wrapping and it worked like a charm :-)
I modified the macro to let it auto insert a new column for H
(.Columns("H").Insert). But how do I make it add the column title in H1 ?
TIA
Beth
"Bernie Deitrick" wrote:
Sorry. The lines
If IsError(Range("D" & myR).Value) Or IsError(Range("E" & myR).Value)
Then
and
If IsError(Range("F" & myR).Value) Or IsError(Range("G" & myR).Value)
Then
both wrapped incorrectly - the Then should go on the same line as the first part...
HTH,
Bernie
MS Excel MVP
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Beth,
Sub HiLiteMatches()
Dim myR As Long
For myR = 2 To Cells(Rows.Count, 4).End(xlUp).Row
If IsError(Range("D" & myR).Value) Or IsError(Range("E" & myR).Value) Then
Range("H" & myR).Value = "ID contains #N/A, "
Else
If Range("D" & myR).Value = Range("E" & myR).Value Then _
Range("H" & myR).Value = "ID matches, "
If Range("D" & myR).Value < Range("E" & myR).Value Then _
Range("H" & myR).Value = "ID does NOT match, "
End If
If IsError(Range("F" & myR).Value) Or IsError(Range("G" & myR).Value) Then
Range("H" & myR).Value = Range("H" & myR).Value & _
"number of files contains #N/A, "
Else
If Range("F" & myR).Value = Range("G" & myR).Value Then _
Range("H" & myR).Value = Range("H" & myR).Value & _
"number of files matches, "
If Range("F" & myR).Value < Range("G" & myR).Value Then _
Range("H" & myR).Value = Range("H" & myR).Value & _
"number of files does NOT match, "
End If
Next myR
End Sub
HTH,
Bernie
MS Excel MVP
"Beth" wrote in message
...
Hi,
I have another question.
I'm comparing two pairs of columns (Column D with E, Column F with G. The
data in these are vlookup-ed from other sheets) and putting down the
comparison results in Column H.
If Column D & E matches, Column F & G matches = Column H results: "ID
matches, number of files matches"
If Column D & E matches, Column F & G does NOT match = Column H results:
"ID matches, number of files does NOT match"
If Column D & E does NOT match, Column F & G match = Column H results: "ID
does NOT match, number of files matches"
If Column D & E does NOT match, Column F & G does NOT match = Column H
results: "ID does NOT match, number of files does NOT match"
If EITHER Column D & E is #N/A.... instead of "ID does not match" substitute
with "ID contains #N/A" in the results in Column H. Same for Column F & G, if
#N/A is present, substitute "Number of files does not match" with "Number of
files contains #N/A". So in Column H there will also be combinations like "ID
matches, number of files contains #N/A", "ID contains #N/A, number of files
does NOT match", and so on.
Using EXACT with much filtering/sorting can do the job, but it would be
great if this can be done with a macro :-)
TIA
Beth
|