How to make a Microsoft Office Excel 2007 Function?

Question

I need to create a function for if column d equals certain name the whole row will be copied to sheet 2.  When doing this I want to keep original in sheet one and be able to edit copy in sheet two without change to original.

Amit · Accepted Answer

Hi..Kel,

if you want to make your own function then you have to write a macro (but it a sub not function. that meance it will persorm some task and not return any value)


copy below code in VBA editor

Sub Check_and_copy()
    For i = 1 To Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row
        If Sheet1.Cells(i, 1) = "Some Name" Then
            ThisWorkbook.Activate
            Rows(i & ":" & i).Select
            Selection.Copy
            Windows("Workbook1").Activate
            ActiveCell.SpecialCells(xlCellTypeLastCell).Select
            ActiveCell.Offset(1, ActiveCell.Column * -1 + 1).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
        End If
    Next
    MsgBox "Done"
    Range("A1").Select
End Sub

and run

but I suggest to use VLOOKUP Function to achieve your goal. To learn more about Vlookup visit:

http://www.contextures.com/xlfunctions02.html

lizzie · Answer

you need to save file as and then choose word 97-2003 to save in .doc format.

Trending News

How to make a Microsoft Office Excel 2007 Function?

2 Answers