Vlookup Formula to Macro

[Ian]

Hello
We use Excel 2000 to book work in and out, in the "BOOK IN" worksheet we
enter the Part Numbers for each job down Column A, (one in each Row), I then
have the following Formula pasted into each Cell down Column C that look up the
Part Number that was entered into the Cell in Column A, and returns the correct
Part Description in the Cell of that Row in Column C.

=IF($A2="","",VLOOKUP($A2,PARTNUMBERS!$A$2:$B$1000,2,FALSE))

The list of Part Numbers and Part Descriptions are in the worksheet named "PARTNUMBERS"
where all the Part Numbers are down Column A, and the corresponding Part Descriptions
are down Column B.

This Formula works well but I would really like to convert it to a Macro so that the
Formulas don't get deleted when we cut and paste Cells from the "BOOK IN" worksheet
to the "BOOK OUT" worksheet.

Can anyone help.

TIA
Ian

 

[Biff]

Hi Ian!

Can't help with a macro, but it would be much easier to
just protect the cells with the formulas.

Biff

-----Original Message-----
Hello
We use Excel 2000 to book work in and out, in the "BOOK IN" worksheet we
enter the Part Numbers for each job down Column A, (one in each Row), I then
have the following Formula pasted into each Cell down Column C that look up the
Part Number that was entered into the Cell in Column A, and returns the correct
Part Description in the Cell of that Row in Column C.

=IF($A2="","",VLOOKUP($A2,PARTNUMBERS! $A$2:$B$1000,2,FALSE))

The list of Part Numbers and Part Descriptions are in the worksheet named "PARTNUMBERS"
where all the Part Numbers are down Column A, and the

corresponding Part Descriptions

are down Column B.

This Formula works well but I would really like to

convert it to a Macro so that the

 

[Max]

... when we cut and paste Cells from the "BOOK IN" worksheet

to the "BOOK OUT" worksheet.

Perhaps just *copy* (instead of Cut) from "BOOK IN"
& then, in "BOOK OUT", paste as values
(Edit > paste special > check "values" > OK)

Then back to "BOOK IN"
and clear the part numbers entered in col A ?

 

[Mark]

Hi Ian
I would suggest looking at DGET "Extracts from a database a single record
that matches the specified criteria" (from Help). I use it regularly. It
works well for the kind of application you describe.
Search this forum for more info.

Mark

 

[Ian]

Hi Biff
Thanks for the suggestion, unfortunately it doesn't work as you can't cut/paste Cells that are protected.

Cheers
Ian

 

[Ian]

Hi Max
Thanks for the suggestion, haven't tried it yet but I don't really like the idea of having to go
backwards and forwards between worksheets, I would like to just cut from one workheet
and paste into another in one go.

Cheers
Ian

 

[Ian]

Hi Mark
I'm unfamiliar with DGET, so will look into it.

Cheers
Ian

 

[Max]

Maybe try the sub MakeStaticCopy() below
(but always try in a *back-up* copy) ..

Press Alt + F11 to go to VBE

Click Insert > Module

Copy > Paste
everything within the dotted lines below
[from "begin vba" till "end vba"]
into the empty white space on the right side in VBE

---------begin vba----------
Sub MakeStaticCopy()
Application.ScreenUpdating = False
Sheets("Book In").Select
Cells.Copy

Sheets.Add
Selection.PasteSpecial Paste:=xlValues
Selection.PasteSpecial Paste:=xlFormats
Range("A1").Select

Sheets("Book In").Select
Range("A2:A100").ClearContents
'Adjust range A2:A100 to suit
Range("A1").Select

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
---------end vba----------

Press Alt + Q to exit and return to Excel

In Sheet: Book In
------------------------
Draw a button on the sheet from the Forms Toolbar
(If necessary, activate the toolbar via View > Toolbars > Forms)

The Assign Macro dialog will pop-up automatically
[You can also right-click on an existing button > Assign Macro]

Select "MakeStaticCopy" > OK

Rename the button to taste, e.g.: "Book Out"

Each click on the button will run the macro,
with a static copy of "Book In" produced in a new sheet

The range A2:A100 in "Book In" will then be cleared/reset
(Adjust the range to suit)

 

[Max]

=IF($A2="","",VLOOKUP($A2,PARTNUMBERS!$A$2:$B$1000,2,FALSE))

This Formula works well but I would really like to convert it to a Macro

...

Re-above, maybe try the sub below which will
insert the formulas into C2:C100 in "Book In"
(You could assign the sub to a button in "Book In",
as mentioned in earlier post)

Sub InsertFormulas()
With Sheets("Book In").Range("C2:C100")
' Adjust the range in col C to suit
' the max likely number of rows
.FormulaR1C1 = _
"=IF(RC1="""","""",VLOOKUP(RC1,PartNumbers!R2C1:R1000C2,2,FALSE))"
End With
End Sub