PDA

View Full Version : Solved: Autofill



PianoMan5
07-09-2012, 07:58 AM
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

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! :thumb

Bob Phillips
07-09-2012, 09:25 AM
MsgBox arguments

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

PianoMan5
07-09-2012, 10:00 AM
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
07-09-2012, 06:22 PM
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! :rotlaugh:



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


Thanks all!

Bob Phillips
07-10-2012, 12:16 AM
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