Help with VLOOKUP

[rwilfing]

I have a very simple vlookup formula I am trying to run but everytime I use it it adds a column and puts it in column A then shifts all other columns to the right one.

I am very new to vlookup and wonder if it has anything to do with the fact I am running a Macro to import data?

Thanks in advance

Here is the formula I am using

vlookup($A$2,$O$2:$P$5000,2,0)

 

[Smitty]

Welcome to the Board!

A formula can't effect physical changes on a worksheet (other than Conditional Formatting), so I'd imagine it has something to do with your code. Care to post it?

 

[rwilfing]

This was actually my first macro so it was done with the recorder. haven't been able to go back and tweak it much. So thank you for taking the time to muddle thru this nightmare of code

 Sub Beginning()
'
' Beginning Macro
'

'
ChDrive "C"
ChDir "C:\\UPSDATA"
myfilename = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
    Sheets("Template").Cells.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
[a1].Select
    Application.CutCopyMode = False     'Clear Selection Marque
    ActiveSheet.Name = "NEW"
    
        
      With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & myfilename _
        , Destination:=Range("$A$2"))
        .Name = "myfilename"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 9, 1, 1, 1, 9, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        ' Kill myfilename
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("A:L").Select
    With Selection
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A2").Select
    Columns("A:A").ColumnWidth = 20
    ActiveSheet.Select
    Columns("A:L").Select
    ActiveWorkbook.Worksheets("NEW").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("NEW").Sort.SortFields.Add Key:=Range("D2:D5500") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("NEW").Sort
        .SetRange Range("A1:L5500")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A2").Select
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    Range("A2").Select

ActiveSheet.Name = Range("d1").End(xlDown).Text



Columns("A:L").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "J2:J5500"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "I2:I5500"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "L2:L5500"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A1:L5500")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    
    




End Sub

 

[rwilfing]

And I would agree with you BUT it doesn't happen when I take the formula out????

 

[Smitty]

That's really weird, it's certainly not something I can pin down offhand. Do you have any event code in there?

 

[rwilfing]

No event code.
Would it help to include the vlookup within the macro at the end somewhere, after it imports and sorts the file?

 

[Smitty]

I suppose you could, but the underlying cause would still bother me.

 

[rwilfing]

Well I just tried it using the index/match function and the same thing happened.

I am totally baffled now.

 

[Smitty]

Did you happen to invoke Data-->Text to Columns in this session? Once you've done that any text pasting you do will follow that original behavior until you restart Excel.

 

[rwilfing]

No I haven't and I have restarted Excel numerous times