vlookup formula not working

[Laura Harris]

Hello, It's my first post, so be patient with me.

I'am currently capturing production data in an excel "operator form" and using named ranges and a macro to export this data and append it to a "shift report database". Within this database on a separate tab, I'm summarizing the data using multiple SUMPRODUCTs. My issue lies with capturing the downtime comments with the "DAILY COMM" tab. I use a vlookup and concantenate to retrieve the comments for each hour of the shift, for a specific date, specific line and specific work order. I'll set the vlookup, test it, but the next day it wont work. If I manually re-enter the vlookup formula it works. I don;t get it.? Do I need to change the export macro? I tried using a IF MATCH, VLOOKUP, and had the same issues.
HELP

Sub EXPORT_1ST()
'
' EXPORT_1ST Macro
'

'
Sheets("EXPORT DATA").Select
Application.Goto Reference:="FIRST_SHIFT"
Selection.Copy
Workbooks.Open Filename:="P:\SHIFT REPORT DATABASE.xlsm"
Sheets("1ST SHIFT").Select
Range("B1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Worksheets("1ST SHIFT").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1ST SHIFT").Sort.SortFields.Add Key:=Range( _
"A3:A31"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("1ST SHIFT").Sort
.SetRange Range("A3:EG31")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin

And how can I create a macro to sort a range of cells when the range is always growing?

 

[Anant Chirmade]

to get range till the last cell used you can use the following code

.SetRange Range(Range("A3",ActiveCell.SpecialCells(xlCellTypeLastCell).Address)

This will set the range from cell a3 till the data is used.
if you want last cell from "EG" column. then you can use the following code :

.SetRange Range(Range("A3"),Range("EG1048576").end(xlup).offset(1,0).address)

 

[rumshar]

Hi Anant,
I use excel 2003 style of coding for sorting my data. I find it very easy to understand and code.

Option Explicit

Sub SortData()
'Since you have already selected your data,hence using selection as range here
Selection.Sort Range("A3"), xlAscending, Header:=xlGuess

End Sub

With Regards
Rudra