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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.