| Home |
| Search |
| Today's Posts |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup in VBA
Better
Sub copyandcleanup() 'make temp sheet and clean up Application.ScreenUpdating = False 'delete destination sheet rows With Sheets("PivotTableData") lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row 'MsgBox lr If lr 0 Then .Rows(2).Resize(lr).Delete End With Sheets("LabourReport").Copy after:=ActiveSheet Columns("B").SpecialCells(xlCellTypeBlanks).Entire Row.Delete 'copy data lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row lc = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByColumns, xlPrevious).Column Sheets("pivottabledata").Range("a2").Resize(lr, lc).Value = _ Range("a2").Resize(lr, lc).Value 'delete tempsheet Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True 'restore updating Application.ScreenUpdating = True End Su -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Sub copyandcleanup()'SalesAidSoftware 'make temp sheet and clean up Application.ScreenUpdating = False Sheets("LabourReport").Copy after:=ActiveSheet With Cells .Copy .PasteSpecial Paste:=xlPasteValues End With Columns("B").SpecialCells(xlCellTypeBlanks).Entire Row.Delete 'delete destination sheet rows With Sheets("PivotTableData") lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row 'MsgBox lr If lr 0 Then .Rows(2).Resize(lr).Delete End With 'copy data lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row lc = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByColumns, xlPrevious).Column Range("a2").Resize(lr, lc).Copy Sheets("pivottabledata").Range("a2") 'delete tempsheet Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True 'restore updating Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message news:... What does the AFTER look like. If desired, send you file to my address below along with a clear explanation and before/after examples. I will take a look tomorrow. -- Don Guillett Microsoft MVP Excel SalesAid Software "Joyce" wrote in message ... Hi, I am working with a report that is downloaded into Excel. The format cannot be changed, so I have to reformat in order to generate a pivot table. Here is an example of the layout: John Smith 54 Field 1 Field 2 Field 3 Field 4 Field 1 Field 2 Field 3 Field 4 Field 1 Field 2 Field 3 Field 4 Totals for John Smith Mary Jones 88 Field 1 Field 2 Field 3 Field 4 Field 1 Field 2 Field 3 Field 4 Field 1 Field 2 Field 3 Field 4 Field 1 Field 2 Field 3 Field 4 Field 1 Field 2 Field 3 Field 4 Field 1 Field 2 Field 3 Field 4 Totals for Mary Jones Etc. The lines of data vary per employee and per report. I am creating a macro that will automate the process, but am encountering a problem with my method. I insert two headings to the right of Field 4 called EmployeeName and EmployeeNo. I then create a Vlookup that looks the combined information in the field at the top of each section. It works fine, except that the references aren't relative, so if the number of rows change, the vlookup doesn't work. Here is part of my code: ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,Employees,2,0)" Range("Q3").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,Employees,3,0)" Range("P3:Q3").Select Selection.AutoFill Destination:=Range("P3:Q13") Range("P3:Q13").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Rows("2:2").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Range("A2").Select Selection.End(xlDown).Select Selection.End(xlDown).Select Rows("14:14").Select Selection.Delete Shift:=xlUp Range("A16").Select Selection.End(xlToRight).Select Range("P16").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R15C1,Employees,2,0)" Range("Q16").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R15C1,3,0)" Range("Q16").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R15C1,Employees,3,0)" Range("P16:Q16").Select Selection.AutoFill Destination:=Range("P16:Q29") I have to resolve the number of times this repeats as well, but would like to resolve this first! Thanks for any assistance. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Vlookup in VBA
This fixes all problems.
Sub GetData() Application.ScreenUpdating = False Sheets("LabourReport").Select lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row lc = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByColumns, xlPrevious).Column 'eliminates formulas Cells(1, 1).Resize(lr, lc).Value = _ Cells(1, 1).Resize(lr, lc).Value 'Gets employee num and name With Columns("a") Set c = .Find(What:="Employee", LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) If Not c Is Nothing Then firstAddress = c.Address Do With Range(c.Address, Range(c.Address).End(xlDown)) .Offset(, 15) = Mid(c, InStr(c, ":") + 2, 3) .Offset(, 15).NumberFormat = "0" .Offset(, 16) = Right(c, Len(c) - InStrRev(c, ":")) End With Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With 'deletes blank rows Columns("B").SpecialCells(xlCellTypeBlanks).Entire Row.Delete 'copies data With Sheets("PivotTableData") dlr = .Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row If dlr 0 Then .Rows(2).Resize(dlr).Delete ..Range("a2").Resize(lr, lc).Value = _ Range("a2").Resize(lr, lc).Value End With Application.ScreenUpdating = True 'deletes labourreport Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Better Sub copyandcleanup() 'make temp sheet and clean up Application.ScreenUpdating = False 'delete destination sheet rows With Sheets("PivotTableData") lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row 'MsgBox lr If lr 0 Then .Rows(2).Resize(lr).Delete End With Sheets("LabourReport").Copy after:=ActiveSheet Columns("B").SpecialCells(xlCellTypeBlanks).Entire Row.Delete 'copy data lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row lc = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByColumns, xlPrevious).Column Sheets("pivottabledata").Range("a2").Resize(lr, lc).Value = _ Range("a2").Resize(lr, lc).Value 'delete tempsheet Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True 'restore updating Application.ScreenUpdating = True End Su -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Sub copyandcleanup()'SalesAidSoftware 'make temp sheet and clean up Application.ScreenUpdating = False Sheets("LabourReport").Copy after:=ActiveSheet With Cells .Copy .PasteSpecial Paste:=xlPasteValues End With Columns("B").SpecialCells(xlCellTypeBlanks).Entire Row.Delete 'delete destination sheet rows With Sheets("PivotTableData") lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row 'MsgBox lr If lr 0 Then .Rows(2).Resize(lr).Delete End With 'copy data lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row lc = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByColumns, xlPrevious).Column Range("a2").Resize(lr, lc).Copy Sheets("pivottabledata").Range("a2") 'delete tempsheet Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True 'restore updating Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message news:... What does the AFTER look like. If desired, send you file to my address below along with a clear explanation and before/after examples. I will take a look tomorrow. -- Don Guillett Microsoft MVP Excel SalesAid Software "Joyce" wrote in message ... Hi, I am working with a report that is downloaded into Excel. The format cannot be changed, so I have to reformat in order to generate a pivot table. Here is an example of the layout: John Smith 54 Field 1 Field 2 Field 3 Field 4 Field 1 Field 2 Field 3 Field 4 Field 1 Field 2 Field 3 Field 4 Totals for John Smith Mary Jones 88 Field 1 Field 2 Field 3 Field 4 Field 1 Field 2 Field 3 Field 4 Field 1 Field 2 Field 3 Field 4 Field 1 Field 2 Field 3 Field 4 Field 1 Field 2 Field 3 Field 4 Field 1 Field 2 Field 3 Field 4 Totals for Mary Jones Etc. The lines of data vary per employee and per report. I am creating a macro that will automate the process, but am encountering a problem with my method. I insert two headings to the right of Field 4 called EmployeeName and EmployeeNo. I then create a Vlookup that looks the combined information in the field at the top of each section. It works fine, except that the references aren't relative, so if the number of rows change, the vlookup doesn't work. Here is part of my code: ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,Employees,2,0)" Range("Q3").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,Employees,3,0)" Range("P3:Q3").Select Selection.AutoFill Destination:=Range("P3:Q13") Range("P3:Q13").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Rows("2:2").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Range("A2").Select Selection.End(xlDown).Select Selection.End(xlDown).Select Rows("14:14").Select Selection.Delete Shift:=xlUp Range("A16").Select Selection.End(xlToRight).Select Range("P16").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R15C1,Employees,2,0)" Range("Q16").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R15C1,3,0)" Range("Q16").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R15C1,Employees,3,0)" Range("P16:Q16").Select Selection.AutoFill Destination:=Range("P16:Q29") I have to resolve the number of times this repeats as well, but would like to resolve this first! Thanks for any assistance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
| using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
Linear Mode
