Turn off Vlookup auto calculate on worksheet

[andyd92]

Hi all

I have a worksheet that uses a macro to perform a vlookup which basically returns stock values on a daily basis. I need the worksheet to record stock levels on a daily basis so that if a product is out of stock for more than 1 week then another formula will warn me a particular product needs action.

The problem is the vlookup auto updates. So the stock levels for each day change to the current stock level. What I want is the vlookup to be performed once on that day then the values do not change. So over a period of time I have stock records for each day.

Thinking the easiest way is to turn off auto calculate. Is this possible at a formula, worksheet or workbook level? If I use the workbook on another machine will auto calculate be turned back on?

Ideally I only want to turn off auto calculate for that worksheet. As other worksheets need auto calculate running.

Any thoughts / ideas greatly appreciated.

Kind regards

Andy
Excel 2007 / Windows 2007

 

[MARK858]

Seeing as you are using VBA can you not use a Application.Vlookup for the vlookup then it will go in the cell as values?

For instance with the data as below try running the macro

Sub test()
    Dim c As Range
    For Each c In Range("B9:B11")
        c = Application.VLookup(c.Offset(, -1), Range("a1:e5"), 3, 0)
    Next
End Sub

Excel Workbook
	A	B	C	D	E
1	2	13	25	23	26
2	3	30	12	22	17
3	4	39	25	16	49
4	5	38	43	34	22
5	6	17	40	47	34
6
7
8
9	5
10	6
11	3
Sheet1

 

[MARK858]

Actually no need to loop it...

Sub test()
Range("B9:B11") = Application.VLookup(Range("B9:B11").Offset(, -1), Range("a1:e5"), 4, 0)
End Sub

 

[andyd92]

Hi thanks for the reply. I tried adapting my code to using application vlookup however the Vlookup just returned #NAME? for each product. Here is my code (version which works prior to adding application.vlookup. How would I change the code?

Sub mrexcel()
Dim NextCol As Long
Workbooks.Open Filename:="C:\Users\H36231\Desktop\M\Current stock.xlsx"
Windows("Stock Alert.xlsm").Activate
NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Cells(1, NextCol).Value = Date
Cells(2, NextCol).Resize(16, 1).FormulaR1C1 = _
"=VLOOKUP(RC1,'[Current stock.xlsx]Sheet1'!C1:C2,2,FALSE)"
End Sub

 

[MARK858]

Sorry but I am at work now but I will have a look tonight if no one converts the formula for you

 

[MARK858]

Try the code below, change the bit in red to your worksheet name (not the sheets codename)

[color=darkblue]Sub[/color] mrexcel()

    [color=darkblue]Dim[/color] NextCol [color=darkblue]As[/color] [color=darkblue]Long[/color], i [color=darkblue]As[/color] Range

    Workbooks.Open Filename:="C:\Users\H36231\Desktop\M\Current stock.xlsx"
    Windows("Stock Alert.xlsm").Activate
    
    NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
    
    Cells(1, NextCol).Value = [color=darkblue]Date[/color]
    
    [color=darkblue]Set[/color] i = Cells(2, NextCol).Resize(16, 1)
    i = Application.VLookup(i.Offset(, -1), Workbooks("Current stock.xlsx").Sheets("[COLOR="#FF0000"]Sheet1[/COLOR]").Columns("A:B"), 2, 0)

[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[MARK858]

Poor reading yesterday. Instead try...

[color=darkblue]Sub[/color] mrexcel()

    [color=darkblue]Dim[/color] NextCol [color=darkblue]As[/color] [color=darkblue]Long[/color], i [color=darkblue]As[/color] Range, c [color=darkblue]As[/color] Range
    Application.ScreenUpdating = 0
    
    Workbooks.Open Filename:="C:\Users\H36231\Desktop\M\Current stock.xlsx"
    Windows("Stock Alert.xlsm").Activate

    NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1

    Cells(1, NextCol).Value = [color=darkblue]Date[/color]
    
    [color=darkblue]Set[/color] i = Cells(2, NextCol).Resize(16, 1)
    [color=darkblue]For[/color] [color=darkblue]Each[/color] c [color=darkblue]In[/color] i
        c = Application.VLookup(Cells(c.Row, 1), Workbooks("Current stock.xlsx").Sheets("Sheet1").Columns("A:B"), 2, 0)
        Columns(c.Column).AutoFit
    [color=darkblue]Next[/color]
    
    Application.ScreenUpdating = 1
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

I don't like having to loop it but seeing as you are only dealing with 16 rows then it won't make any noticeable difference

 

[andyd92]

Sorry for the late reply, I have amended my code slightly to work with my spreadsheets and also tried to implement the changes you made, failing miserably. Here is my amended and working code without the application.vlookup.

Sub DailyStockupdate()
    Dim NextCol As Long
    Workbooks.Open Filename:="C:\Users\H36231\Desktop\M\Live Data\1on1stock.csv"
    Windows("Stock status sheet.xlsm").Activate
    NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
    Cells(1, NextCol).Value = Date
    Cells(2, NextCol).Resize(268, 1).FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC1,'[1on1stock.csv]1on1stock'!C1:C5,5,FALSE),0)"
End Sub

Also note that my spreadsheet will have approx 1000 rows when finished. Will that be an issue with the loop?

 

[MARK858]

The loop will be a bit of an issue time wise but a bigger issue with the time taken will be the use of VLookup if you are using formula rather than application.
If you are going to use formula look at Index Match and turn calculation off when running the code.
I doubt if I will get a chance to look at it today but will try and look in later this week to see where you are.

 

[MARK858]

See if the 2 files in the links are of help...

http://app.box.com/s/1rzsm7ratkan2gn494y5

http://app.box.com/s/4tzpfmq93o18cum8f5tr