I'm in the process of writing/editing a macro, but after I run the macro once, the Expressions in the Locals Watch window disappear, and if I try to run it again it produces an "Excel has encountered an error and needs to close".
The macro probably doesn't make much sense out-of-context, but perhaps there's a glaring error that's causing this ...
A guess - possibly related to the Error Handling in the vba? (1st time I've used this in a macro).
Any ideas/advice anyone?
Option Explicit Sub Compare() Dim strNaspK As String Dim strCtryK As String Dim intCtryRev As Integer Do Until ActiveCell.Offset(1, 0) = "" ActiveCell.Offset(1, 0).Select strNaspK = ActiveCell strCtryK = ActiveCell.Offset(0, 2) On Error GoTo ErrJump: Sheets("Comparison").Select Columns("G:G").Select Selection.Find(What:="" & strNaspK & "", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Select intCtryRev = ActiveCell.Offset(0, 18) ' no of rows of data for same NASP id ActiveCell.Range("A1:C" & intCtryRev).Select Selection.Find(What:="" & strCtryK & "", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Select ActiveCell.Offset(0, 15).Range("A1").Select Selection.Copy Sheets("Comparison2").Select ActiveCell.Offset(0, 18).Select ActiveSheet.Paste ActiveCell.Offset(0, -18).Select Jumpout: Sheets("Comparison2").Select Loop Exit Sub ErrJump: Resume Jumpout: End Sub
Basically, I've got two sets of data on different sheets, with NASP ID and country being variables. Just trying to import the data from the 'Comparison' sheet, if both the NASP id and country matches that on the first sheet, established by the variables.
Thanks for any advice.