!REF# Error in the excel after running the VBA script

[beloshi]

Hello excel experts and gurus,

Its delightful to be part of such an informative forum where I learnt alot, I was a VBA newbie but thanks to this forum I got an hold on to the things in my daily work life somehow with the help of this forum and its posts.

I am encountering a problem a with regard to the excel Vlookup formula which tries to verify the results. I will explain a brief background so that an expert might get my POV that what I am trying to achieve here.

Background:

I have an excel macro enabled file in which I have scripts to take care of data coming from different files and also importing few tabs deleting the older ones with the same name. So the macro enabled file is the one we update every month to update the data / figures inside.

Problem:

There is one sheet 'DELTA' which contains VLOOKUP formulas in which we try to see if the import was error free and the figures are good, in this sheet but every month the formula gets messed up with the error '!REF#'. Although there is no change in the formula so I need to put all the references again and it takes time as I have 25 files which contain 12 worksheets each.


below are some of the formulas I use ;

[B]Formula 1:[/B]
C6-VLOOKUP($B$6,'DQ # Stores'!$B:$M,2,FALSE)

[B]Formula 2:[/B]
$C$6-SUM(VLOOKUP($B$6,'DQ # Stores per Branch'!$B:$AI,2,FALSE),VLOOKUP($B$6,'DQ # Stores per Branch'!$B:$AI,13,FALSE),VLOOKUP($B$6,'DQ # Stores per Branch'!$B:$AI,24,FALSE))

[B]Formula 3:[/B]
$F$6-VLOOKUP($B$6,'DQ # Stores'!$B:$M,8,FALSE)

[B]Formula 4:[/B]
$F$6-SUM(VLOOKUP($B$6,'DQ # Stores per Branch'!$B:$AI,8,FALSE),VLOOKUP($B$6,'DQ # Stores per Branch'!$B:$AI,19,FALSE),VLOOKUP($B$6,'DQ # Stores per Branch'!$B:$AI,30,FALSE))

So is there a way that It should remain same everytime using some other excel function but achieving the same result above . So in everyfile the formula doesn't change nor the cell references. But every month after updating the sheet with VBA script I need to do over the above formula in each individual sheet again.

Hope I am clear with my explanation.

Awaiting reply

with best regards

Beloshi

 

[jmacleary]

Hi there. The problem will be being caused by the bit of your process where you are deleting the older ones with the same name. As soon as you delete the sheet, the references will all go to #n/a. Instead of deleting each sheet, could you copy the new data over the old? Alternatively, you could record a macro where yo reset the formulas, then run that macro after you import the data. Hope this helps.
John

 

[Special-K99]

If the macro causes the VLOOKUPs to produce #REFs then it would be a good idea to post the macro.

 

[beloshi]

Hi there. The problem will be being caused by the bit of your process where you are deleting the older ones with the same name. As soon as you delete the sheet, the references will all go to #n/a. Instead of deleting each sheet, could you copy the new data over the old? Alternatively, you could record a macro where yo reset the formulas, then run that macro after you import the data. Hope this helps.
John

For that I need write a new macro for the populating the data in each sheet, and I have only one file which contains there worksheets which are same in all 25 files and I am already populating data from the macro individually to the required tabs just not the ones which are already updated and are being imported from the updated file.


Your idea of recording a macro after the import is somehow less time consuming so it will help alot.
Can you please help me using code to put the above formulas in a specified sheet as an example I will try to follow it on all the files at once.

If the macro causes the VLOOKUPs to produce #REFs then it would be a good idea to post the macro.

here is the code

Sub ImportSheets()
Dim sImportFile As String, sFile As String
Dim sThisBk As Workbook
Dim vfilename As Variant
Dim sht As Worksheet
Dim wbbk As Workbook
Dim importedsht As Worksheet


'Previous Country Sheets Delete to import new data
Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Sheets
    Select Case sh.Name
        Case "DQ Detail # Countries - Detail", "1", "2", "3", "4", "5"
            sh.Delete
    End Select
Next


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set sThisBk = ActiveWorkbook
sImportFile = Application.GetOpenFilename( _
FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx; *.xlsm", Title:="Open Workbook")
If sImportFile = "False" Then
    MsgBox "No File Selected!"
    Exit Sub


Else
vfilename = Split(sImportFile, "\")
sFile = vfilename(UBound(vfilename))
Application.Workbooks.Open Filename:=sImportFile


Set wbbk = Workbooks(sFile)
For Each sht In wbbk.Sheets
    If sht.Name <> "Legend" Then
        sht.Copy before:=ThisWorkbook.Sheets("Lgnd")
    End If
Next
wbbk.Close SaveChanges:=False
Application.CutCopyMode = False
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True

 

[Special-K99]

I'd go with jmacleary's answer as your macro doesn't appear to be manipulating formulas directly so it could well be the deletions causing the #REF errors.

 

[beloshi]

I'd go with jmacleary's answer as your macro doesn't appear to be manipulating formulas directly so it could well be the deletions causing the #REF errors.

Yes its the deletion causing the can you please help how can I reset the formulas using VBA code, a help would be highly appreciated.

 

[jmacleary]

OK. You just need to record a macro. Click the Developer tab and click Record Macro. Give it a name like Resetter, and then just type in the formulas that you need. Click stop recording at the end. This will give you the vba code you need.