Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup
Hi everyone:
1. I have two sheets, "Sheet1" and "Sheet2" containing data. 2. "Sheet2" have some data from "Sheet1" but also includes different data as well. 3. I can find new data (different) data in "Sheet2" by using vlookup. however I have macro running on sheet1 to create pivot table report. I am just missing those new data from Sheet2. 4. Both "Sheet1" and "Sheet2" has data from column A to X. Common lookup column being D. Row numbers change each month. I know if I run vlookup in sheet2, all the new data will return #N/A. I know I can make it return either than #N/A using iserror and if function but. How do I include those new data from "Sheet2" to "Sheet1" using VBA? so I can run the pivot macro on "Sheet1"? Thank you so much for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup
Here is one way:
Option Explicit Option Base 1 Sub UpdateSheet1() Dim DataArray(65000, 24) As Variant Dim Fnd As Double Dim X As Double Dim Y As Double Dim Z As Double Dim LookupRng As Range Dim Res As Variant Sheets("Sheet1").Select Set LookupRng = Workbooks("wb containing sheet1.xls").Names("NamedRange-All Data").RefersToRange Do While True If Cells(X, 1).Value = Empty Then Exit Do Res = Application.VLookup(Cells(X, 4), LookupRng, 4, False) 'lookup up col d in sheet1. If not found there is an error so pick up all data from this row to add If Not (IsError(Res)) Then Else Fnd = Fnd + 1 For Y = 1 To 26 DataArray(Fnd, Y) = Cells(X, Y).Value Next End If X = X + 1 Loop Windows("wb containing sheet1.xls").Activate Sheets("sheet1").Select Range("A65000").End(xlUp).Select 'this is a row with data, this row +1 is empty! X = ActiveCell.Row + 1 For Y = 1 To Fnd 'this will populate all your "new" data to sheet1... For Z = 1 To 24 Cells(X, Z).Value = DataArray(Y, Z) Next X = X + 1 Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup
On May 30, 9:21*pm, Mike H. wrote:
Here is one way: Option Explicit Option Base 1 Sub UpdateSheet1() Dim DataArray(65000, 24) As Variant Dim Fnd As Double Dim X As Double Dim Y As Double Dim Z As Double Dim LookupRng As Range Dim Res As Variant Sheets("Sheet1").Select Set LookupRng = Workbooks("wb containing sheet1.xls").Names("NamedRange-All Data").RefersToRange Do While True * * If Cells(X, 1).Value = Empty Then Exit Do * * Res = Application.VLookup(Cells(X, 4), LookupRng, 4, False) * * 'lookup up col d in sheet1. *If not found there is an error so pick up all data from this row to add * * If Not (IsError(Res)) Then * * Else * * * * Fnd = Fnd + 1 * * * * For Y = 1 To 26 * * * * * * DataArray(Fnd, Y) = Cells(X, Y).Value * * * * Next * * End If * * X = X + 1 Loop Windows("wb containing sheet1.xls").Activate Sheets("sheet1").Select Range("A65000").End(xlUp).Select *'this is a row with data, this row +1 is empty! X = ActiveCell.Row + 1 For Y = 1 To Fnd *'this will populate all your "new" data to sheet1... * * For Z = 1 To 24 * * * * Cells(X, Z).Value = DataArray(Y, Z) * * Next * * X = X + 1 Next End Sub "If Cells(X, 1).Value = Empty Then Exit Do" This line makes an error of the followings ' Run-time error '1004', application defined or object defined error' |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup
Before the Do while true line, place a line:
x=1 Sorry about that. You have to give x a value before starting to evaluate a cell because a cell address of (0,1) is not valid. "James8309" wrote: On May 30, 9:21 pm, Mike H. wrote: Here is one way: Option Explicit Option Base 1 Sub UpdateSheet1() Dim DataArray(65000, 24) As Variant Dim Fnd As Double Dim X As Double Dim Y As Double Dim Z As Double Dim LookupRng As Range Dim Res As Variant Sheets("Sheet1").Select Set LookupRng = Workbooks("wb containing sheet1.xls").Names("NamedRange-All Data").RefersToRange Do While True If Cells(X, 1).Value = Empty Then Exit Do Res = Application.VLookup(Cells(X, 4), LookupRng, 4, False) 'lookup up col d in sheet1. If not found there is an error so pick up all data from this row to add If Not (IsError(Res)) Then Else Fnd = Fnd + 1 For Y = 1 To 26 DataArray(Fnd, Y) = Cells(X, Y).Value Next End If X = X + 1 Loop Windows("wb containing sheet1.xls").Activate Sheets("sheet1").Select Range("A65000").End(xlUp).Select 'this is a row with data, this row +1 is empty! X = ActiveCell.Row + 1 For Y = 1 To Fnd 'this will populate all your "new" data to sheet1... For Z = 1 To 24 Cells(X, Z).Value = DataArray(Y, Z) Next X = X + 1 Next End Sub "If Cells(X, 1).Value = Empty Then Exit Do" This line makes an error of the followings ' Run-time error '1004', application defined or object defined error' |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup
On Jun 3, 9:06*pm, Mike H. wrote:
Before the Do while true line, place a line: x=1 Sorry about that. *You have to give x a value before starting to evaluate a cell because a cell address of (0,1) is not valid. * "James8309" wrote: On May 30, 9:21 pm, Mike H. wrote: Here is one way: Option Explicit Option Base 1 Sub UpdateSheet1() Dim DataArray(65000, 24) As Variant Dim Fnd As Double Dim X As Double Dim Y As Double Dim Z As Double Dim LookupRng As Range Dim Res As Variant Sheets("Sheet1").Select Set LookupRng = Workbooks("wb containing sheet1.xls").Names("NamedRange-All Data").RefersToRange Do While True * * If Cells(X, 1).Value = Empty Then Exit Do * * Res = Application.VLookup(Cells(X, 4), LookupRng, 4, False) * * 'lookup up col d in sheet1. *If not found there is an error so pick up all data from this row to add * * If Not (IsError(Res)) Then * * Else * * * * Fnd = Fnd + 1 * * * * For Y = 1 To 26 * * * * * * DataArray(Fnd, Y) = Cells(X, Y).Value * * * * Next * * End If * * X = X + 1 Loop Windows("wb containing sheet1.xls").Activate Sheets("sheet1").Select Range("A65000").End(xlUp).Select *'this is a row with data, this row +1 is empty! X = ActiveCell.Row + 1 For Y = 1 To Fnd *'this will populate all your "new" data to sheet1... * * For Z = 1 To 24 * * * * Cells(X, Z).Value = DataArray(Y, Z) * * Next * * X = X + 1 Next End Sub "If Cells(X, 1).Value = Empty Then Exit Do" This line makes an error of the followings ' Run-time error '1004', application defined or object defined error'- Hide quoted text - - Show quoted text - Thanks mate. You are a champ! Have a nice day! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup
On Jun 4, 9:43*am, James8309 wrote:
On Jun 3, 9:06*pm, Mike H. wrote: Before the Do while true line, place a line: x=1 Sorry about that. *You have to give x a value before starting to evaluate a cell because a cell address of (0,1) is not valid. * "James8309" wrote: On May 30, 9:21 pm, Mike H. wrote: Here is one way: Option Explicit Option Base 1 Sub UpdateSheet1() Dim DataArray(65000, 24) As Variant Dim Fnd As Double Dim X As Double Dim Y As Double Dim Z As Double Dim LookupRng As Range Dim Res As Variant Sheets("Sheet1").Select Set LookupRng = Workbooks("wb containing sheet1.xls").Names("NamedRange-All Data").RefersToRange Do While True * * If Cells(X, 1).Value = Empty Then Exit Do * * Res = Application.VLookup(Cells(X, 4), LookupRng, 4, False) * * 'lookup up col d in sheet1. *If not found there is an error so pick up all data from this row to add * * If Not (IsError(Res)) Then * * Else * * * * Fnd = Fnd + 1 * * * * For Y = 1 To 26 * * * * * * DataArray(Fnd, Y) = Cells(X, Y).Value * * * * Next * * End If * * X = X + 1 Loop Windows("wb containing sheet1.xls").Activate Sheets("sheet1").Select Range("A65000").End(xlUp).Select *'this is a row with data, this row +1 is empty! X = ActiveCell.Row + 1 For Y = 1 To Fnd *'this will populate all your "new" data to sheet1... * * For Z = 1 To 24 * * * * Cells(X, Z).Value = DataArray(Y, Z) * * Next * * X = X + 1 Next End Sub "If Cells(X, 1).Value = Empty Then Exit Do" This line makes an error of the followings ' Run-time error '1004', application defined or object defined error'- Hide quoted text - - Show quoted text - Thanks mate. You are a champ! Have a nice day!- Hide quoted text - - Show quoted text - Option Explicit Option Base 1 Sub UpdateSheet1() Dim DataArray(65000, 24) As Variant Dim Fnd As Double Dim X As Double Dim Y As Double Dim Z As Double Dim LookupRng As Range Dim Res As Variant Sheets("Sheet1").Select Set LookupRng = Workbooks("Testlookup.xls").Names("Lookup").Refers ToRange X = 1 Do While True If Cells(X, 1).Value = Empty Then Exit Do Res = Application.VLookup(Cells(X, 4), LookupRng, 4, False) 'looking up column d in sheet1, if not found there is an error so pick up all data from row to add' If Not (IsError(Res)) Then Else Fnd = Fnd + 1 For Y = 1 To 25 DataArray(Fnd, Y) = Cells(X, Y).Value Next End If X = X + 1 Loop Windows("Testlookup.xls").Activate Sheets("Sheet1").Select Range("A65000").End(xlUp).Select 'This is a row with data, this row + 1 is empty' X = ActiveCell.Row + 1 For Y = 1 To Fnd 'This will populate new data from sheet2 to sheet1 For Z = 1 To 24 Cells(X, Z).Value = DataArray(Y, Z) Next X = X + 1 Next End Sub "DataArray(Fnd, Y) = Cells(X, Y).Value" creates error "script out of range" Could you confirm this line? "Set LookupRng = Workbooks("wb containing sheet1.xls").Names("NamedRange-All Data").RefersToRange " Because I am searching for values in columnD in sheet1 to sheet2 as sheet 2 has new datas. wb containing sheet1.xls = testlookup.xls NamedRange-All Data = I named the whole sheet2 as "lookup" Did I do it wrong? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup
The line
For Y=1 to 26 Should be For Y=1 to 24 That will take care of the out-of-range error. The lookup range would need to start with Col D as you want to look up the value of col D to see if there is a match. It could actually be just col D. Then change this line to the line below Res = Application.VLookup(Cells(X, 4), LookupRng, 4, False) change to this: Res = Application.VLookup(Cells(X, 4), LookupRng, 1, False) That should work. "James8309" wrote: On Jun 4, 9:43 am, James8309 wrote: On Jun 3, 9:06 pm, Mike H. wrote: Before the Do while true line, place a line: x=1 Sorry about that. You have to give x a value before starting to evaluate a cell because a cell address of (0,1) is not valid. "James8309" wrote: On May 30, 9:21 pm, Mike H. wrote: Here is one way: Option Explicit Option Base 1 Sub UpdateSheet1() Dim DataArray(65000, 24) As Variant Dim Fnd As Double Dim X As Double Dim Y As Double Dim Z As Double Dim LookupRng As Range Dim Res As Variant Sheets("Sheet1").Select Set LookupRng = Workbooks("wb containing sheet1.xls").Names("NamedRange-All Data").RefersToRange Do While True If Cells(X, 1).Value = Empty Then Exit Do Res = Application.VLookup(Cells(X, 4), LookupRng, 4, False) 'lookup up col d in sheet1. If not found there is an error so pick up all data from this row to add If Not (IsError(Res)) Then Else Fnd = Fnd + 1 For Y = 1 To 26 DataArray(Fnd, Y) = Cells(X, Y).Value Next End If X = X + 1 Loop Windows("wb containing sheet1.xls").Activate Sheets("sheet1").Select Range("A65000").End(xlUp).Select 'this is a row with data, this row +1 is empty! X = ActiveCell.Row + 1 For Y = 1 To Fnd 'this will populate all your "new" data to sheet1... For Z = 1 To 24 Cells(X, Z).Value = DataArray(Y, Z) Next X = X + 1 Next End Sub "If Cells(X, 1).Value = Empty Then Exit Do" This line makes an error of the followings ' Run-time error '1004', application defined or object defined error'- Hide quoted text - - Show quoted text - Thanks mate. You are a champ! Have a nice day!- Hide quoted text - - Show quoted text - Option Explicit Option Base 1 Sub UpdateSheet1() Dim DataArray(65000, 24) As Variant Dim Fnd As Double Dim X As Double Dim Y As Double Dim Z As Double Dim LookupRng As Range Dim Res As Variant Sheets("Sheet1").Select Set LookupRng = Workbooks("Testlookup.xls").Names("Lookup").Refers ToRange X = 1 Do While True If Cells(X, 1).Value = Empty Then Exit Do Res = Application.VLookup(Cells(X, 4), LookupRng, 4, False) 'looking up column d in sheet1, if not found there is an error so pick up all data from row to add' If Not (IsError(Res)) Then Else Fnd = Fnd + 1 For Y = 1 To 25 DataArray(Fnd, Y) = Cells(X, Y).Value Next End If X = X + 1 Loop Windows("Testlookup.xls").Activate Sheets("Sheet1").Select Range("A65000").End(xlUp).Select 'This is a row with data, this row + 1 is empty' X = ActiveCell.Row + 1 For Y = 1 To Fnd 'This will populate new data from sheet2 to sheet1 For Z = 1 To 24 Cells(X, Z).Value = DataArray(Y, Z) Next X = X + 1 Next End Sub "DataArray(Fnd, Y) = Cells(X, Y).Value" creates error "script out of range" Could you confirm this line? "Set LookupRng = Workbooks("wb containing sheet1.xls").Names("NamedRange-All Data").RefersToRange " Because I am searching for values in columnD in sheet1 to sheet2 as sheet 2 has new datas. wb containing sheet1.xls = testlookup.xls NamedRange-All Data = I named the whole sheet2 as "lookup" Did I do it wrong? |
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 | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
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 |