krispykreme
New Member
- Joined
- Sep 5, 2016
- Messages
- 6
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:
Any help would be appreciated.
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.