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

 

[andyd92]

Hi thanks for the files.

They seem to do exactly what I need. Will have a play and see if I can adapt to my worksheets this afternoon and let you know how I get on.

Really appreciate your help with this.

Kind regards

Andy

 

[MARK858]

Happy you are getting somewhere, once you have got the code working with your sheet post the code as it should be tinkered with I.e turning screenupdating off and doing something where it refers to the whole columns in the lookup.

 

[andyd92]

Hi, finally got it working, but one issue I found so far. When I close the 1on1stock.csv workbook, the macro returns an error. Which is a runtime error '9' subscript out of range. This seems strange as I changed over this workbook in the code?

Code is as follows:

Sub NEWSTOCKUPDATER()
Dim NextCol As Long, i As Range


'     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
    
    Set i = Cells(2, NextCol).Resize(268, 1)
    Debug.Print NextCol
    i = Application.VLookup(i.Offset(, -(NextCol - 1)), Workbooks("1on1stock.csv").Sheets("1on1stock").Columns("A:E"), 5, 0)


Columns(NextCol).AutoFit
End Sub

Also after the Macro has run, can I make it close the 1on1stock.csv file? Save the Stock Status Workbook and also return to the Stock Alerts Worksheet? (Which is on Stock Status Workbook)

 

[andyd92]

One more thing, the Application.Vlookup returns #N/A for products which aren't on 1on1stock worksheet.

Normally I would wrap a vlookup with a IFERROR return 0 formula. How can I do that with application.vlookup.

Apologies for the questions, and thank you in advance.

Kind regards

Andy

 

[MARK858]

When I close the 1on1stock.csv workbook, the macro returns an error

Both worksheets have got to be open. For the rest see code

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

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

    [color=green]'     Workbooks.Open Filename:="C:\Users\H36231\Desktop\M\Live Data\1on1stock.csv"[/color]
    [color=green]'   Windows("Stock status sheet.xlsm").Activate[/color]

    Application.ScreenUpdating = [color=darkblue]False[/color]

    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(268, 1)

    i = Application.VLookup(i.Offset(, -(NextCol - 1)), Workbooks("1on1stock.csv").Sheets("1on1stock").Range("A1:E" & Range("A" & Rows.Count).End(xlUp).Row), 5, 0)
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    i.Cells.Replace "#N/A", "", xlWhole
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
    
    Columns(NextCol).AutoFit
    
    Application.Goto Reference:=Worksheets("Stock Alerts").Range("A1")
    
    Workbooks("1on1stockA.csv").Close [color=darkblue]False[/color]
    ThisWorkbook.Close [color=darkblue]True[/color]
    
    Application.ScreenUpdating = [color=darkblue]True[/color]

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

 

[andyd92]

Hi

Thanks for the reply and apologies for the late reply. I missed the email saying you responded.

Something strange seems to be happening. I ran the updated code and all seemed to be working. However when the lookup returned #N/A the cell was blank rather than 0. So I added a 0 into the code as below. Problem is that I'm getting 0's for items which have a stock value.

Sub STOCKUPDATER281013()
'
    Dim NextCol As Long, i As Range


    '   Workbooks.Open Filename:="C:\Users\Administrator\Desktop\T4NB\1on1stock.csv"
    '   Windows("Stock status sheet.xlsm").Activate


    Application.ScreenUpdating = False


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


    Set i = Cells(2, NextCol).Resize(268, 1)


    i = Application.VLookup(i.Offset(, -(NextCol - 1)), Workbooks("1on1stock.csv").Sheets("1on1stock").Range("A1:E" & Range("A" & Rows.Count).End(xlUp).Row), 5, 0)
    
    On Error Resume Next
    i.Cells.Replace "#N/A", "0", xlWhole
    On Error GoTo 0
    
    Columns(NextCol).AutoFit
    
    Application.Goto Reference:=Worksheets("Stock Alerts").Range("A1")
    
       
    Application.ScreenUpdating = True


End Sub

Basically every product below row 52 comes back as a 0??

(Note i removed the close workbook switch worksheet code for testing, but it works great thanks)

 

[MARK858]

Are you sure that you don't have any leading spaces or line breaks etc. in the cells you are looking up on either sheet? as I can run it for a 1000 lines with no trouble with the results being a mixture of text,numbers, numbers as text and formula results.

 

[andyd92]

Ok seem to have found the problem.

Previously when the code was working, the code for the application vlookup was

Debug.Print NextCol
    i = Application.VLookup(i.Offset(, -(NextCol - 1)), Workbooks("1on1stock.csv").Sheets("1on1stock").Columns("A:E"), 5, 0)

As opposed to the code which was given me a problem

i = Application.VLookup(i.Offset(, -(NextCol - 1)), Workbooks("1on1stock.csv").Sheets("1on1stock").Range("A1:E" & Range("A" & Rows.Count).End(xlUp).Row), 5, 0)

Not really sure what the difference is, but currently using the first code with the added if error and all appears to be working correctly...

 

[MARK858]

Oh I can see what it is (a bit of stupidity on my part which would have showed up if I had just shortened the first lookup on my test sheet).
The last range is referencing the activesheet rather than Sheets("1on1stock").
Try....

Sub mrexcel()

    Dim NextCol As Long, i As Range

    '     Workbooks.Open Filename:="C:\Users\H36231\Desktop\M\Live Data\1on1stock.csv"
    '   Windows("Stock status sheet.xlsm").Activate

    Application.ScreenUpdating = False

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

    Cells(1, NextCol).Value = Date

    Set i = Cells(2, NextCol).Resize(Range("A" & Rows.Count).End(xlUp).Row - 1, 1)
    
    With Workbooks("1on1stock.csv").Sheets("1on1stock")
    i = Application.VLookup(i.Offset(, -(NextCol - 1)), .Range("A1:E" & .Range("A" & Rows.Count).End(xlUp).Row), 5, 0)
    End With
    
    i.Cells.Replace "#N/A", "0", xlWhole
    Columns(NextCol).AutoFit
    Application.Goto Reference:=Worksheets("Stock Alerts").Range("A1")

    Workbooks("1on1stockA.csv").Close False
    ThisWorkbook.Close True

    Application.ScreenUpdating = True

End Sub

 

[andyd92]

Ok thanks I will test later and let you know.