PDA

View Full Version : Input Box: Ranges...



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

mdmackillop
04-24-2013, 03:09 PM
1. I don't see how the code following GoTo LookupComments will ever run. You jump right over it.
2. If UserRange was B:E, LastCol would be 5 and the VLookup would fail.

BravesPiano5
04-24-2013, 08:59 PM
mdmackillop: I see what you're saying and I fixed both issues (long day!)....

Unfortunately, this brings me back to secondary question I have. Is there a way via the input box to select a seperate user defined workbook? It seems I have all of the code to support it but the input box only allows the user to select a worksheet within the same workbook. :mkay

I can paste the updated code I have if need be.

Teeroy
04-25-2013, 05:08 AM
I don't understand, you define the workbook as the double parent of the range therefore it has to be the same workbook.

When going about selecting another workbook I tend to use a listbox on a userform which lists all open workbooks (from the workbooks collection) and choose from that list (there is less chance of errors). If you really want to select a different file look at This (http://www.mrexcel.com/forum/excel-questions/526431-visual-basic-applications-pop-up-window-choose-file.html) but be sure to include error handling.

BravesPiano5
04-25-2013, 06:40 AM
Teeroy -- I don't completely follow your logic...what do you mean it has to be the same workbook? :think: I assumed that I was retrieving the worksheet (parent) and workbook (double parent) of the range selected regardless of where it's at.

Even so..I'd prefer for the user to simply click on the (separate) open workbook and define the range desired to run the code. It works perfect with a separate worksheet within the same workbook but not a different workbook.

mdmackillop
04-25-2013, 11:32 AM
Please post the full code

Teeroy
04-26-2013, 04:03 AM
Sorry I misunderstood what you were doing. Assuming you'd corrected the Goto problem that mdmackillop has already mentioned the InputBox locked you to the ActiveWorkbook so I asssumed that's where you were collecting the Ranges. You'll need to select the WorkBook prior to running the InputBox to collect the Range; while the InputBox is open you can't change WorkBooks.

I've got a couple of questions.
DeductionWB is dimensioned as a variant but nothing is assigned to it. What is it supposed to be?
Is DeductionWB supposed to be the ActiveWorkBook? DeductionWB.Sheets("Table").Select can only work if it is.Most of your ranges are not explicitly referenced to a WorkBook.Worksheet and will therefore default to the ActiveWorkBook and ActiveSheet.

If you provide some more detail about the end goal, and your revised code, we might be able to help a bit more.

snb
04-26-2013, 06:17 AM
You can apply this inputbox to any range in a loaded workbook as long as you specify the name of the workbook & it's sheet.
You can only select a range (e.g. $H$1:$H$30); after having done that you can enter (after Clicking F2) e.g. '[example.xlsx]sheet1'!

the result being '[example.xlsx]sheet1'!$H$1:$H$30


Sub M_snb()
Application.InputBox("Please highlight the range", "snb", Selection.Address, Type:=8).Interior.ColorIndex = 3
End Sub