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.

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

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'

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'

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!

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?

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?