Help with intense Macro

[twon]

I am trying to write a macro in VBA that will compare two worksheet based on columns that i tell it to. So when i run the macro i want it to prompt me and ask me for which column it should find discrepences with. Than print out the whole row that is a descrepency in another sheet. So i can compare two different years of data. The reason i need it to prompt me is becasue each year the worksheet slightly changes because new data is taken into account. Oh and i cant use an addon becasue the network is locked down and i am the only one who can load and addin. the administrator for the network will not allow add-in to be loaded so that is why it needs to be a macro

 

[iliace]

Is the number of rows always the same? Or, is there some column that uniquely identifies rows?

For example, let's say you have account and balance. Year one:

40100 $1234
40200 $1000
40300 $159

Next year, you added an account:

40100 $1234
40150 $777
40200 $1000
40300 $159

So you'd need to compare the columns by the account number (first column). If the number of rows doesn't change, then there is no need for a unique identifier.

Also, what kind of data are you comparing in these columns - text, numbers, etc? If numbers or dates, are you looking for any difference, or within some material amount/length of time?

 

[twon]

there can be a different number of rows between the worksheets. Each year the number of rows change because we aquire more periodicals that we track. The number of columns can also change because for example in 2007 we didnt take into account the impact factor of the periodicals but in 2008 we did so the number of columns increased we also added 350 more rows between the two years because we gained more periodicals that we are tracking. In most cases we are comparing text with the titles of the periodicals however sometimes the issn change so i also need to check against numbers for example the number look like "1303-2945". and titles are just text. let me know if i need to be more specific. Thanks for helping

 

[twon]

Please can anyone help with this. I am pretty stuck

 

[al_b_cnu]

Hi,

Try:

Sub CompareColumns()
Dim iCol As Integer
Dim lRow As Long
Dim rCur As Range
Dim sCur1 As String, sCur2 As String
Dim ws1 As Worksheet, ws2 As Worksheet, wsResults As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set wsResults = Sheets("Sheet3")
wsResults.UsedRange.ClearContents

lRow = 0
iCol = Selection.Column
For Each rCur In ws1.Range(Cells(1, iCol).Address, ws1.Cells(Rows.Count, iCol).End(xlUp).Address)
    sCur1 = ""
    sCur2 = ""
    On Error Resume Next
    sCur1 = CStr(rCur.Value)
    sCur2 = CStr(ws2.Cells(rCur.Row, iCol).Value)
    On Error GoTo 0
    If sCur1 <> sCur2 Then
        lRow = lRow + 1
        wsResults.Rows(lRow).Value = ws1.Rows(rCur.Row).Value
    End If
Next rCur
End Sub

This macro assumes the following:
1) the currently selected column is the column to be compared
2) Input sheets are Sheet1 and sheet2
3) Results shreet is Sheet3

 

[al_b_cnu]

Perhaps better:

Sub CompareColumns()
Dim iCol As Integer
Dim lRow As Long, lRowEnd1 As Long, lRowEnd2 As Long
Dim rCur As Range
Dim sCur1 As String, sCur2 As String
Dim ws1 As Worksheet, ws2 As Worksheet, wsResults As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set wsResults = Sheets("Sheet3")
wsResults.UsedRange.ClearContents

iCol = Selection.Column

lRow = 0
lRowEnd1 = ws1.Cells(Rows.Count, iCol).End(xlUp).Row
For Each rCur In ws1.Range(Cells(1, iCol).Address, Cells(lRowEnd1, iCol).Address)
    sCur1 = ""
    sCur2 = ""
    On Error Resume Next
    sCur1 = CStr(rCur.Value)
    sCur2 = CStr(ws2.Cells(rCur.Row, iCol).Value)
    On Error GoTo 0
    If sCur1 <> sCur2 Then
        lRow = lRow + 1
        wsResults.Rows(lRow).Value = ws1.Rows(rCur.Row).Value
    End If
Next rCur

lRowEnd2 = ws2.Cells(Rows.Count, iCol).End(xlUp).Row

If lRowEnd2 > lRowEnd1 Then
    lRow = lRow + 1
    wsResults.Rows(lRow & ":" & lRow + lRowEnd2 - lRowEnd1 - 1).Value = ws2.Rows(lRowEnd1 + 1 & ":" & lRowEnd2).Value
End If
End Sub