BravesPiano5
04-24-2013, 12:22 PM
I have no idea why the below code is not working...I want the user to identify the range for the vlookup but the macro is not understanding the range. What am I doing wrong?!
Option Explicit
Sub Worklist_Comments()
Dim LastRow, NumRows, LastCol, CommentsColNum, DCLookupNum As Long
Dim MsgAnswer As VbMsgBoxResult
Dim UserRange As Range
Dim UserWS As Worksheet
Dim DeductionWB, UserWB As Workbook
GoTo LookupComments
UserWB = UserRange.Parent.Parent
UserWS = UserRange.Parent
'Asks user to identify the range then defines for VBA the worksheet and workbook the user defined
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With
Application.StatusBar = "Copying comments over..."
DeductionWB.Sheets("Table").Select
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'Identify the last row to be used to create the comments column
If Range("AA14").Value = "Comments" Then
CommentsColNum = 27
DCLookupNum = 12
Range("AA15", Cells((LastRow), CommentsColNum)).ClearContents
Else
CommentsColNum = 23
DCLookupNum = 9
Columns("V:V").Copy
With Columns("W:W")
.Insert Shift:=xlToRight
.AutoFit
End With
Range("W14").Value = "Comments"
Range("W15", Cells((LastRow), CommentsColNum)).ClearContents
End If
'Checks which column the comments are in and clears the column out; also indentifies what column
'the DC# is
For NumRows = 15 To (LastRow - 1)
On Error Resume Next
Cells(NumRows, CommentsColNum) = Application.WorksheetFunction.VLookup(Cells(NumRows, DCLookupNum), _
UserWB(UserWS).UserRange, LastCol, 0)
If Cells(NumRows, CommentsColNum) = "0" Then Cells(NumRows, CommentsColNum) = ""
On Error GoTo 0
Next
'Perform VLookup to get the comments
GoTo Finisher
LookupComments:
On Error Resume Next
Application.DisplayAlerts = False
Set UserRange = Application.InputBox("Please highlight the range of the comments you wish to copy over. ", _
"Deduction Worklist -- Comments", Selection.Address, Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If UserRange Is Nothing Then
MsgAnswer = MsgBox("A valid range has not been selected; do you wish to continue without comments?", vbYesNo, _
"Deduction Worklist -- Comments")
If MsgAnswer = vbYes Then GoTo Finisher
If MsgAnswer = vbNo Then Exit Sub
End If
LastCol = UserRange.Cells(UserRange.Count).Column
'Identify the # of columns in the user defined range to vlookup
Finisher:
Call DeductionMonitorStabilization
'Run the worklist macro
End Sub
Option Explicit
Sub Worklist_Comments()
Dim LastRow, NumRows, LastCol, CommentsColNum, DCLookupNum As Long
Dim MsgAnswer As VbMsgBoxResult
Dim UserRange As Range
Dim UserWS As Worksheet
Dim DeductionWB, UserWB As Workbook
GoTo LookupComments
UserWB = UserRange.Parent.Parent
UserWS = UserRange.Parent
'Asks user to identify the range then defines for VBA the worksheet and workbook the user defined
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With
Application.StatusBar = "Copying comments over..."
DeductionWB.Sheets("Table").Select
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'Identify the last row to be used to create the comments column
If Range("AA14").Value = "Comments" Then
CommentsColNum = 27
DCLookupNum = 12
Range("AA15", Cells((LastRow), CommentsColNum)).ClearContents
Else
CommentsColNum = 23
DCLookupNum = 9
Columns("V:V").Copy
With Columns("W:W")
.Insert Shift:=xlToRight
.AutoFit
End With
Range("W14").Value = "Comments"
Range("W15", Cells((LastRow), CommentsColNum)).ClearContents
End If
'Checks which column the comments are in and clears the column out; also indentifies what column
'the DC# is
For NumRows = 15 To (LastRow - 1)
On Error Resume Next
Cells(NumRows, CommentsColNum) = Application.WorksheetFunction.VLookup(Cells(NumRows, DCLookupNum), _
UserWB(UserWS).UserRange, LastCol, 0)
If Cells(NumRows, CommentsColNum) = "0" Then Cells(NumRows, CommentsColNum) = ""
On Error GoTo 0
Next
'Perform VLookup to get the comments
GoTo Finisher
LookupComments:
On Error Resume Next
Application.DisplayAlerts = False
Set UserRange = Application.InputBox("Please highlight the range of the comments you wish to copy over. ", _
"Deduction Worklist -- Comments", Selection.Address, Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If UserRange Is Nothing Then
MsgAnswer = MsgBox("A valid range has not been selected; do you wish to continue without comments?", vbYesNo, _
"Deduction Worklist -- Comments")
If MsgAnswer = vbYes Then GoTo Finisher
If MsgAnswer = vbNo Then Exit Sub
End If
LastCol = UserRange.Cells(UserRange.Count).Column
'Identify the # of columns in the user defined range to vlookup
Finisher:
Call DeductionMonitorStabilization
'Run the worklist macro
End Sub