Excel VLOOKUP Macro Solved/Closed

Question

Hello, 

I am working on a project to create a simple VLOOKUP.  I have created the macro and it works well, but now I am looking for some advise on how to modify the code to add Input Boxes to capture various items.  This way the end user doesn't have to modify the actual macro code for use on different spreadsheets. 

Currently this is the code I came up with... 

Sub Blank_Lookup() 

Dim sLastRow As String 
Dim vRange As Variant 

'Calculate last row 
    sLastRow = Trim(Str(Application.WorksheetFunction.CountA(Range("A:A")))) 
    
'Input box to capture starting cell 
    vRange = InputBox("Enter Starting Cell") 
      
'Find matching item numbers on the WCSSCustItemNumLookupTable Tab 
    Range(vRange).Select 
'''Enter # of Columns Back"-" or Forward"+" that lookup will be compaired to.         '''R5100 Value Will Equal Last Available Row 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(TRIM(RC[-1]),Lookup_Table!R1C1:R5000C2,2,0)" 
'''Change Column Name, This Will Be Your Starting Cells Column 
    Selection.AutoFill Destination:=Cells("A2:A" + sLastRow) 
'''Change Column Name, This Will Be Your Starting Cells Column 
    Range("A2:A" + sLastRow).Select 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
    Cells.EntireColumn.AutoFit 
    Cells.EntireRow.AutoFit 
End Sub 

I would like to do the following... (1) Replace ("A2:A" + sLastRow) part, with vRange + sLastRow variables.  (2) Create a variable to change the +/- value in the VLOOKUP formula (RC[[-1]).  (3) Create a variable to change the last row value in the Lookup_Table tab :R5100. 

Hopefully this makes sense, if not I will try to explain it in more detail. 

Matt


Configuration: Windows XP / Firefox 3.5.6

froggy6703 · Accepted Answer

I tried change the code to this and now I am getting the following error...
AutoFill method of Range class failed
Any ideas how to fix this?

Also for the other part, I had look into that this morning.  The problem I am seeing with that is if the user enters "AB2" as there start range.  This would then result in the variables containing A(sCol) & B2(lRow).  That is why I thought if I could trim the alfa or numeric values I would prevent this from happening.

rizvisa1 · Answer

Please do explain more. I don't get what you mean by replace by "vRange + sLastRow variables." I am also not getting you where this +/- value in loop up would be coming from. I am not so clear about third part too

From what all I could understand

you can change this 
ActiveCell.FormulaR1C1 = "=VLOOKUP(TRIM(RC[-1]),Lookup_Table!R1C1:R5000C2,2,0)" 

to 
    Dim colOffset
    colOffset = -1
    ActiveCell.FormulaR1C1 = "=VLOOKUP(TRIM(RC[" & colOffset & "]),Lookup_Table!R1C1:R" & sLastRow & "C2,2,0)"



and  this
 Selection.AutoFill Destination:=Cells("A2:A" + sLastRow)

to 
 Selection.AutoFill Destination:=Cells("A2:A" & sLastRow)

froggy6703 · Answer

Rizvisa1 thank you for responding so fast.  You actually helped a lot with what you said above that solves two of the three questions already.  The only thing I have a question on is in the statement   Selection.AutoFill Destination:=Cells("A2:A" + sLastRow) I would like to replace the "A2:A" part somehow with a variable that can be captured through an input box.  Do you know how that would be done.

Thanks again,

Matt

rizvisa1 · Answer

Matt, not knowing how A2A: may change, I am presuming that only part that will change will be the "A" part, You can have an input box as you had earlier like 

vRange2 = InputBox("Enter Lookup Column") 

Selection.AutoFill Destination:=Cells(vRange2  & 2 & ":" & vRange2  & sLastRow)

froggy6703 · Answer

I am getting an error message when I try that.  But it does work for the next line that selects the range.  Also I will need to change both the "A" part and the "2" part.  I was thinking instead of having two input boxes to capture each of these values.  Is there a way to trim off the numeric value from a valiable, or the alfebetic value of a variable.  What I am thinking is taking the variable vRange (which equals B1). Now trimming it both ways creating two new variables call vRangeA (which equals B)) and vRangeN (which equals 1).

Here is the code I am using...

'Find matching item numbers on the Lookup_Table Tab
    Range(vRange).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(TRIM(RC[" & colOffset & "]),Lookup_Table!R1C1:R" & sLTLastRow & "C2,2,0)"
    
    'Getting Error Message On This Line
    Selection.AutoFill Destination:=Cells(vRange2 & 2 & ":" & vRange2 + sLastRow)
    
    'This Line Works OK Though
    Range(vRange2 & 2 & ":" & vRange2 + sLastRow).Select
    
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Cells.EntireColumn.AutoFit

rizvisa1 · Answer

Try this

Selection.AutoFill Destination:=Range(vRange2 & 2 & ":" & vRange2 & sLastRow)

You cannot have like CELL("A2:A10"). CELLS address is always one cell and not more. You have to use range  in this case as above

About separating number and text. Yes it can be done

Dim sCol as String 
Dim lRow as long

Dim vRange3 as string

vRange3 = inputbox("Enter Start Range (example A4)")
sCol =Left(Vrange3, 1) 'this gets the 1 character from left 
lRow = mid(vRange3, 2) 'this gets characters starting from position number 2 till end

rizvisa1 · Answer

I dont get any error. Could you post your workbook at some share site like https://authentification.site and post link here, 

For inputbox, then you can do this

Dim sCol As String
Dim sRow As String

sCol = InputBox("Enter Start Range (example A4)")
sCol = Trim(sCol)

Do While (IsNumeric(Right(sCol, 1)))
  sRow = Right(sCol, 1) & sRow
  sCol = Trim(Mid(sCol, 1, Len(sCol) - 1))
Loop

froggy6703 · Answer

Hey, thank you this worked.  I changed the code to accept the new code you just listed to split the variable and now everything is working great.

Thanks again for all your help.

Matt

Excel VLOOKUP Macro

8 responses

Similar discussions

Subscribe To Our Newsletter!