Add extra step to current macro

Hi Experts Using Excel 2013

I have the following macro which i would like to add an additional step too.
So based in the last cell in column B add the following formula to cell A2 =IFERROR(OFFSET(Data!DR2,MATCH(B2,Data!$DR$2:$DR$1303,0),-83),"")

into the VBA code...

Sub CopyandPasteValues()

    Application.ScreenUpdating = False

       Sheets("Unique List").Columns(2).ClearContents
        Sheets("Data").Range("DR:DR").SpecialCells(xlCellTypeConstants).Copy
         Sheets("Unique List").Range("B1").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False
    
         Sheets("Unique List").Range("B:B").RemoveDuplicates Columns:=2, Header:=xlYes

Application.ScreenUpdating = True

End Su

Open in new window

b
route217Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ProfessorJimJamConnect With a Mentor Commented:
Sub CopyandPasteValues()

    Application.ScreenUpdating = False

       Sheets("Unique List").Columns(2).ClearContents
        Sheets("Data").Range("DR:DR").SpecialCells(xlCellTypeConstants).Copy
         Sheets("Unique List").Range("B1").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False
   
         Sheets("Unique List").Range("B:B").RemoveDuplicates Columns:=Array(2), Header:=xlYes
         
        LastRow = Range("B" & Rows.Count).End(xlUp).Row
         
Range("A2:A" & LastRow).Formula = "=IFERROR(OFFSET(Data!DR2,MATCH(B2,Data!$DR$2:$DR$1303,0),-83),"""")"
Application.ScreenUpdating = True

End Sub
0
 
route217Author Commented:
Hi Profjimjam...

U are back...excellent...let me test and as always appreciate the feedback.
0
 
route217Author Commented:
hi Profjimjam

getting a compile error variable not defined....when testing macro..
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
route217Author Commented:
Fixed it..
0
 
ProfessorJimJamCommented:
you are welcome.

thanks for the feedback
0
 
route217Author Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.