macro

[RE: VLOOKUP fORMULA]

dEAR aLL,

Can anybody help me please....

Here is a simple macro which copy formulas from B9 till B33
Is there any way I can name the cell B40 as "END" and copy the formulas
until END
if so what I should add more in this code?

Sub Macro1()
' Macro1 Macro

Range("B9").Select
Selection.AutoFill Destination:=Range("B9:B33"), Type:=xlFillDefault
Range("B9:B33").Select
End Sub

Thanks

 

[OssieMac]

By END I am assuming that you want to autofill down as far as the last used
row in another column. Is this correct? If so, then you can find the last
used row in an adjacent column and concatenate that in the range as per the
following. The code finds the last used row in column A. Edit that to another
column if necessary.

Dim lastRow As Long

'Edit the "A" in the following line to match
'the column to test for last row
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("B9").Select
Selection.AutoFill Destination:=Range("B9:B" & lastRow), Type:=xlFillDefault
Range("B9:B" & lastRow).Select

 

[RE: VLOOKUP fORMULA]

Dear Sir,

Regarding this answer.
This is working fine.
I pasted this code in Macro code. Always giving a pop up window stating that

"The flwg code cannot be saved in macro free work book, *VB project to save
a file with htese features, click No, and then choose a macro-enabled file
type in the file type list.

Can you tell me exactly where to paste this code,
Can you show me how.

 

[OssieMac]

To get that message then I am assuming that you are using xl2007 so the
instructions are for xl2007. If this is not correct then please let me know
what version of xl you are using.

You need to select Save as. The following instructions tell you how to do
this:-

Select the Microsoft button (Big button top left of screen)
Click Save as
In the Save dialog box in the Save as type field select Excel Macro-Enabled
Workbook (*.xlsm)
In the File Name field either leave the name that is there or insert another
one. (If you insert a new name then there is no need to insert the extension
but if you do then it must be .xlsm)

Click Save and you are done. However, you could now have 2 files with
similar names but one with xlsx extension and one with xlsm extension. You
use the xlsm one (or macro enabled one)

You also need to set the options to allow macros to run as follows.

Select the Microsoft button (Big button top left of screen)
Select Excel options (towards bottom right of dialog box)
Select Trust Center (Left column of dialog box)
Select Trust Center settings (Towards middle right of dialog box)
Select Macro Settings (Left column)
Select Disable all macros with notification
Click OK and then OK again.

Now when you attempt to open an xl file with a macro you will be notified
that a macro exists and ask if you want to allow macros to run (or something
like that) so select Yes or allow or whatever. (I am not sure of the actual
message because I have my computer set up to allow all to run from a trusted
location and I never see the message).

I am happy for you that my previous answer was what you were looking for and
I hope this helps you to complete it. Feel free to get back to me if you are
still having problems.

 

[RE: VLOOKUP fORMULA]

I works perfect and thanks OssieMac for spending your valuable time for me.