#1
|
|||
|
|||
#REF! Error that I cannot seem to understand
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 http://www.mrexcel.com/forum/excel-q...nderstand.html |
#2
|
|||
|
|||
Hi,
Which line in the code shows the error. Also did you go back to the 2 original workbooks to see if they worked? |
#3
|
|||
|
|||
HI,
If you posted the same question on a different forum site let us know. http://www.mrexcel.com/forum/excel-q...nderstand.html Some members may not help if they see a cross post that you did not refer too. |
#4
|
|||
|
|||
Oh sorry was not aware of the cross forum thing.
There doesn't seem to be a specific line in the code that returns an error. It all seems to work, and in the initial trial, there wasn't a problem. When I used the same code on 2 different workbooks (only the name was changed lines 7&8 of code), then the output columns in the new workbook displayed "#REF!" in every cell. I suspect it may be a range defining issue as I was having trouble with this before but I cannot find where. |
#5
|
|||
|
|||
The issue was that the last column that was being counted had blank cells and this was throwing off the whole macro. Thanks all!
|
#6
|
||||
|
||||
Please read http://www.excelguru.ca/content.php?184 and add links to ALL your cross posts - Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Trying to understand formulas and error | Jack-P-Winner | Excel | 1 | 04-22-2014 03:50 PM |
Automation error Unknown error" message once they open the Excel file | hlina | Excel | 1 | 10-08-2013 09:14 PM |
At wit's end trying understand styles for numbering/bullet levels in MS Word | gopher_everett | Word | 3 | 02-22-2013 05:19 PM |
Symbols I don't understand appearing in word | suzantn | Word | 2 | 11-22-2012 08:36 AM |
Runtime error 5487 - Word cannot complete the save to to file permission error | franferns | Word | 0 | 11-25-2009 05:35 AM |