Solved: Autofill

[PianoMan5]

[vba]Sub Create_Autofill()
Dim uiSheet As Worksheet
Dim LR As Long

On Error Resume Next
Set uiSheet = Application.InputBox("Please select a cell with the necessary worksheet to vlookup.", _
"VLookup Reference", Type:=8).Parent
If uiSheet Is Nothing Then
Answer = MsgBox("No selection has been made; please rerun the macro.", "Selection Error!", vbExclamation)
Exit Sub
End If

LR = Range("F65536").End(xlUp).Row
With Application.WorksheetFunction.VLookup(Range("F2"), uiSheet.Range("A:E"), 2, 0)
.AutoFill Destination:=Range("A2:A" & LR)
End With
End Sub[/vba]

I want for every nonblank cell in column A to do the vlookup function and retrieve the value. What am I doing wrong? Thanks in advance!

[Bob Phillips]

MsgBox arguments

[VBA]Sub Create_Autofill()
Dim uiSheet As Worksheet
Dim LR As Long
Dim Answer As VbMsgBoxResult

On Error Resume Next
Set uiSheet = Application.InputBox("Please select a cell with the necessary worksheet to vlookup.", _
"VLookup Reference", Type:=8).Parent
If uiSheet Is Nothing Then

Answer = MsgBox("No selection has been made; please rerun the macro.", vbExclamation, "Selection Error!")
Else

LR = Range("F" & Rows.Count).End(xlUp).Row
With Application.WorksheetFunction.VLookup(Range("F2"), uiSheet.Range("A:E"), 2, 0)
.AutoFill Destination:=Range("A2:A" & LR)
End With
End If
End Sub
[/VBA]

[PianoMan5]

XLD, I'm not getting issues with the messagebox but rather blanks...I can't figure out to how to autofill the cells.

Here's my quandry:

• Can't enter the vlookup as an excel formula and autofill
  • Although this entry is easier to autofil (being it's a formula and not an exact value), the macro is for general terms so the sheet the formula is looking in will change (thus causing referencing issues)
• Can't autofil the vlookup as a VBA / excel formula
  
  • Although the vlookup formula is easier this way, it provides an exact value, I can't simply autofill down as the formula should autofill, not the value

[PianoMan5]

I figured it out! I do wish I didn't have to run a for/next loop but it gets the job done...it may seem easy to some but I'm ecstatic!

[vba]

Sub Create_Autofill()

Dim UserSheet As Worksheet
Dim LastRow As Range
Dim NumRows As Integer

On Error Resume Next
Set UserSheet= Application.InputBox("Please select a cell with the necessary worksheet to vlookup.", _
"VLookup Reference", Type:=8).Parent
If UserSheet Is Nothing Then
Answer = MsgBox("No selection has been made; please rerun the macro.", "Selection Error!", vbExclamation)
Exit Sub
End If

Set LastRow = Range("F2").End(xlDown)
For NumRows = 2 To LastRow.Row
On Error Resume Next 'If VLookup returns a blank, tells macro to skip and go to next cell#
Cells(NumRows, 1) = WorksheetFunction.VLookup(Cells(NumRows, 6), UserSheet.Range("A:F"), 2, 0)
Next
End Sub

[/vba]

Thanks all!

[Bob Phillips]

[VBA]Sub Create_Autofill()
Const FORMULA_LOOKUP As String = "=IFERROR(VLOOKUP($F2,'<sheet>'!A:F,2,FALSE),"""")"
Dim UserSheet As Worksheet
Dim LastRow As Long
Dim SheetName As String

On Error Resume Next
Set UserSheet = Application.InputBox("Please select a cell with the necessary worksheet to vlookup.", _
"VLookup Reference", Type:=8).Parent
If UserSheet Is Nothing Then

Answer = MsgBox("No selection has been made; please rerun the macro.", vbExclamation, "Selection Error!")
Else

SheetName = UserSheet.Name
LastRow = Range("F2").End(xlDown).Row
With Range("A2").Resize(LastRow - 1)

.Formula = Replace(FORMULA_LOOKUP, "<Sheet>", SheetName)
.Value = .Value
End With
End If
End Sub[/VBA]