Search : in
By :

Macro to compare two excel sheets

Last answer on Jan 15, 2009 7:20:22 am GMT annajins, on Dec 15, 2008 3:17:53 am GMT 
 Report this message to moderators

Hello all,


I need a help...!! I need help to write macro to compare two sheets, sheet1 and sheet2 on same excel book and display the result on the third sheet. The number of rows and columns may not be equal and it can vary each time.
So we need to find the range of columns and rows first..! I am struggling with this. So please extend your helping hands........

I tried the following code to compare the values,
Sub Macro7()
'
' Macro7 Macro
' Macro recorded 12/11/2008 by UBOC User
'
'
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=IF(SIT!RC=PPE!RC,"" "",CONCATENATE(SIT!RC,"" - "",PPE!RC))"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:EG2"), Type:=xlFillDefault
Range("A2:EG2").Select
Selection.AutoFill Destination:=Range("A2:EG1614"), Type:=xlFillDefault
Range("A2:EG1614").Select
End Sub

But here I don't know how to get the Range dynamically and implement it in this macro....

Some one Please help......

Best answers for « macro to compare two excel sheets » in :
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
How to freeze a row in an excel sheet ShowHow to freeze a row in an excel sheet To freeze a line in an excel sheet, for example line 1: Select line 2. Go to menu Window and select Freeze panes.
Create an Attendance Report with Excel sheet ShowCreate an Attendance Report with Excel sheet Issue Solution Issue In the case you want to create an attendance report with Excel , here below you will find a nice example: Consider that your report will have the following...
Ask your question Reply

1

ecksell, on Jan 15, 2009 7:16:47 am GMT
  • +1

To find the range of Rows and Columns as a number -

ActiveSheet.UsedRange.Rows.Count
or
ActiveSheet.UsedRange.Columns.Count

You can store these in different variables, then use a For loop that goes from 1 - to this variable to make comparisons.

I hope this is what you were looking for.

   
Reply to ecksell

2

 ecksell, on Jan 15, 2009 7:20:22 am GMT
  • +2

Also in addition to to finding the number of Rows and Columns in the above way, if you are using the code you provided in the first place the syntax for autofill is as follows -

Selection.AutoFill Destination:=Range("A2", "EG" & var), Type:=xlFillDefault

where var is the variable that stores the row number or column number. This way you dont need the For loop.

   
Reply to ecksell
Ask your question Reply
They need your help