Macro To compare column A on two worksheets

fishingsantafe

New Member
Joined
Sep 1, 2005
Messages
13
Here is a Macro I recorded.
Code:
Sub insertcolumn() 
' 
' insertcolumn Macro 
' Macro recorded 07/20/2007 by e337ljs 
' 
' Keyboard Shortcut: Ctrl+t 
' 
Columns("B:B").Select 
Selection.Insert Shift:=xlToRight 
Range("B1").Select 
ActiveCell.FormulaR1C1 = "New files" 
With ActiveCell.Characters(Start:=1, Length:=9).Font 
.Name = "Tahoma" 
.FontStyle = "Bold" 
.Size = 8 
.Strikethrough = False 
.Superscript = False 
.Subscript = False 
.OutlineFont = False 
.Shadow = False 
.Underline = xlUnderlineStyleNone 
.ColorIndex = 55 
End With 
Range("B2").Select 
ActiveCell.FormulaR1C1 = _ 
"=ISNA(MATCH(RC[-1],'July 07'!RC[-1]:R[43]C[-1],FALSE))" 
Range("B2").Select 
End Sub


The July 07 in the formula is the name of the worksheet before the active sheet. Is there a way to tell it to go to the previous worksheet Look at column A which will vary as to how many rows to see if the information in A2 of the active worksheet is found in column A of the previous sheet. If it is not found in column A then enter "true" in B of the active work sheet. If it is found on in column A on the previous sheet then enter "false" in B2 on the active worksheet. ( I want to compare numbers in Column A on active to Column A on the Worksheet before the active one to find new infomation that was added to the current months worksheet.

This works but I have always change the name of the worksheet every month I run it and I also have to change the range of the previous worksheet because this will vary every month. Is there and easy way to tell it to stop looking when it hits a blank row in column A on the previous worksheet? Any help is appreciated. I am a beginner at VB but am learning more each day. Hope this is not to confusing. I use Excel 2003
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Code:
Sub insertcolumn()
Dim mySheetIndex As Integer
Dim myLastMonthSheetName As String
Dim myFormula as String
Dim lRow as Long

'Insert new column
Columns("B:B").Insert Shift:=xlToRight

'Column Header
Range("B1").FormulaR1C1 = "New files"
With Range("B1").Font
    .Name = "Tahoma"
    .FontStyle = "Bold"
    .Size = 8
    .ColorIndex = 55
End With

'Make sure we are not on the first sheet in the workbook
'If not, get sheet name of last month's sheet
If ActiveSheet.Index = 1 Then
    MsgBox "An error occurred. This sub will not run on sheet 1."
    End
Else
myLastMonthSheetName = Worksheets(ActiveSheet.Index - 1).Name
End If

'Determine last row in last month's data
lRow = Worksheets(myLastMonthSheetName).Cells(Rows.Count, 1).End(xlUp).Row

'Build a string to use as a formula
myFormula = "=ISNA(MATCH(RC[-1],'" & _
    myLastMonthSheetName & _
    "'!RC[-1]:R[" & lRow & "]C[-1],FALSE))"

'The formula is put in Cell B2
Range("B2").FormulaR1C1 = myFormula

'End on Cell B2
Range("B2").Select

End Sub

I suppose we could have worked around getting the last row of last month's data by just using 1,000 rows or 10,000 rows for the lookup range...but this routine will check for the last row with data in the previous sheet in Column A and use that row number.

Some quick notes:
I cleaned up some of the defaults in the font properties (usually you can just erase some of the default values Excel records, especially with fonts, borders, and page setups).

I also removed the selection that Excel records:
Range("B2").Select
Selection.Delete

Can be:
Range("B2").Delete

And I took advantage of the index property to do what you needed here -- you can refer to a sheet by name or by index number. And of course if a sheet is Sheet(5), the sheet before it (last month) will be Sheet(4).

Regards.
 

fishingsantafe

New Member
Joined
Sep 1, 2005
Messages
13
Thanks for all your time. I will give it a try. I will need to study it all to see how you did it. So I can use part in the future. I am trying to learn all this.
 

fishingsantafe

New Member
Joined
Sep 1, 2005
Messages
13
I entered it in to excel and when I run the compiler to make sure everything is ok I get a Compile Error "invalid outside procedure" and it highlights :=xlToRight in the first line of code when I am trying to insert the column.

I even tried to put the original code that was recorded for inserting the column but then it gives me the same error for Columns("B:B").Select-- the ("B:B")part of the code. Any help is appreciated.
 

fishingsantafe

New Member
Joined
Sep 1, 2005
Messages
13
Never mind I figured it out on my own and the code now works. Thanks for your time it is appreciated.
 

Forum statistics

Threads
1,181,053
Messages
5,927,847
Members
436,572
Latest member
khalid hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top