#REF! Error I can't seem to understand

[krispykreme]

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:

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.

 

[sheetspread]

Be sure you're not exceeding the column count of WSOLDR in your vlookup

 

[MUKESHY12390]

double count column number,I would use Loop instead of vlookup and its more dynamic.

 

[krispykreme]

Thanks for that!! I had been counting the columns before but didn't notice that the last column being counted was blank!

For future reference, how would I go about using Loop rather than vlookup?

 

[Peter_SSs]

For future reference, how would I go about using Loop rather than vlookup?

Firstly, I would not automatically accept that looping would be better than VLOOKUP. The best method would depend on particular circumstances including how big (row count) the lookup table is, whether the looping is to be done through worksheet cells, or through values held in an array in memory etc.

In any case, I'm not sure what the following means in relation to looping

.. its more dynamic.

Another option instead of using the application/worksheet function vlookup in your code, would be to use vba's native Find method. My earlier comment about the best option depending on particular circumstances holds for this alternative too.

 

[Macropod]

Cross-posted at: #REF! Error that I cannot seem to understand

Please read our policy on Cross-Posting in rule #13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

Note: According to post #5 at msofficeforums, your problem has been solved...