Excel VLOOKUP Macro [Solved/Closed]

froggy6703 16 Posts Tuesday March 23, 2010Registration date March 29, 2013 Last seen - Apr 8, 2010 at 06:23 PM - Latest reply: froggy6703 16 Posts Tuesday March 23, 2010Registration date March 29, 2013 Last seen
- Apr 9, 2010 at 04:24 PM
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


See more 

8 replies

Best answer
froggy6703 16 Posts Tuesday March 23, 2010Registration date March 29, 2013 Last seen - Apr 9, 2010 at 02:37 PM
2
Thank you
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.

Thank you, froggy6703 2

Something to say? Add comment

CCM has helped 1671 users this month

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Apr 8, 2010 at 06:48 PM
0
Thank you
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 16 Posts Tuesday March 23, 2010Registration date March 29, 2013 Last seen - Apr 8, 2010 at 06:55 PM
0
Thank you
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Apr 8, 2010 at 09:22 PM
0
Thank you
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 16 Posts Tuesday March 23, 2010Registration date March 29, 2013 Last seen - Apr 9, 2010 at 12:05 PM
0
Thank you
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Apr 9, 2010 at 02:23 PM
0
Thank you
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Apr 9, 2010 at 03:10 PM
0
Thank you
I dont get any error. Could you post your workbook at some share site like http://www.speedyshare.com/ 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 16 Posts Tuesday March 23, 2010Registration date March 29, 2013 Last seen - Apr 9, 2010 at 04:24 PM
0
Thank you
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