Microsoft Office Forums - View Single Post - [Solved] #REF! Error that I cannot seem to understand

krispykreme · 09-11-2016, 04:54 PM

Hi,

The code below worked successfully on the initial trial. On the second trial, where I used 2 different workbooks (in data range identical to initial workbooks except for data values), the output keeps coming out as "#REF!". I've been staring at it for ages and can't seem to work out what the issue is, it worked on the first trial, and the only thing I altered in the code in second trial is workbook names. Code as below:

Code:

Sub Comments()

Dim WBNEW As Workbook 'current week's workbook
Dim WBOLD As Workbook 'previous week's workbook
Dim WSNEW As Worksheet 'current week's worksheet
Dim WSOLD As Worksheet 'previous week's worksheet
Dim WSOLDR As Range


'Open all relevant workbooks also make them macro enabled?
Set WBNEW = Workbooks("HPD10.xlsm") '<--------change name of current week's workbook
Set WBOLD = Workbooks("HPD9.xlsm") '<--------change name of previous week's workbook
Set WSNEW = WBNEW.Worksheets("Open cases") 'change if relevant worksheet has different name
Set WSOLD = WBOLD.Worksheets("Open cases") 'change if relevant worksheet has different name
Set StartCell = WSOLD.Range("A1")

LastRow = WSOLD.Cells(WSOLD.Rows.Count, 1).End(xlUp).Row 'count last row
LastColumn = WSOLD.Cells(1, WSOLD.Columns.Count).End(xlToLeft).Column 'count last column
Set WSOLDR = WSOLD.Range(StartCell, WSOLD.Cells(LastRow, LastColumn))

Dim i As Long

For i = 1 To WSNEW.Rows.Count 'loops i from 1 to the last row in WSNEW which is counted
    If WSNEW.Cells(i, 1).Value <> "" Then 'if value of cells is NOT BLANK
    WSNEW.Cells(i, 37).Value = Application.VLookup(WSNEW.Cells(i, 1).Value, WSOLDR, 37, False) 'Assign each vlookup to a cell in WSNEW
    WSNEW.Cells(i, 38).Value = Application.VLookup(WSNEW.Cells(i, 1).Value, WSOLDR, 38, False)
    WSNEW.Cells(i, 39).Value = Application.VLookup(WSNEW.Cells(i, 1).Value, WSOLDR, 39, False)
    WSNEW.Cells(i, 40).Value = Application.VLookup(WSNEW.Cells(i, 1).Value, WSOLDR, 40, False)
    End If
Next i

MsgBox ("Transfer Complete! =)") 'popup textbox indicating code finished running

End Sub

Any help would be appreciated. I have also posted this here:

http://www.mrexcel.com/forum/excel-q...nderstand.html