***Help on writing a vba code

[arush.saxena1]

Hi Can any one please help me writing one code for a specific project?

Requirement -

I have few values in sheet 1 - eg - from column A1 to A3 (1,2,3)
In sheet 2 i have values , from Column A1 to A6 (1,2,3,4,5,6) and column C1 to C6 (A,b,c,d,e,f).

Now i want code to look A1,A2,A3 values from sheet 1 and if the values are in Sheet 2 Code should add C1,c2,c3 data from Sheet 2 to sheet 1 in a new row. if any data is not found in sheet 2 it should Say NA.

Thanks in advance!!

 

[Claus Busch]

Hi,

Am Sun, 24 Aug 2014 02:35:11 -0700 (PDT) schrieb
(e-mail address removed):

I have few values in sheet 1 - eg - from column A1 to A3 (1,2,3)
In sheet 2 i have values , from Column A1 to A6 (1,2,3,4,5,6) and column C1 to C6 (A,b,c,d,e,f).

Now i want code to look A1,A2,A3 values from sheet 1 and if the values are in Sheet 2 Code should add C1,c2,c3 data from Sheet 2 to sheet 1 in a new row. if any data is not found in sheet 2 it should Say NA.

you do not need VBA.
Try instead a formula:

=VLOOKUP(A1,Sheet2!$A$1:$C$6,3,0)
and copy down


Regards
Claus B.

 

[arush]

Thank you so much Claus.
But the problem is that data is for more then 30,0000 rows and when i use V,look up it does not allow me to filter NA columns (error says count is greater is than 10,000).
Kind Regards,
Arush

 

[Claus Busch]

Hi,

Am Sun, 24 Aug 2014 02:50:17 -0700 (PDT) schrieb arush:

But the problem is that data is for more then 30,0000 rows and when i use V,look up it does not allow me to filter NA columns (error says count is greater is than 10,000).

that is a restriction of the filter.
You can use a macro but Filter will give you the same error:

Sub Test()
Dim LRow1 As Long, LRow2 As Long
Dim i As Long, j As Long, n As Long, counter As Long
Dim arrCheck As Variant, arrData As Variant
Dim arrOut() As Variant

LRow1 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
LRow2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
arrCheck = Sheets("Sheet1").Range("A1:A" & LRow1)
arrData = Sheets("Sheet2").Range("A1:C" & LRow2)

For i = LBound(arrCheck) To UBound(arrCheck)
counter = 0
For j = LBound(arrData) To UBound(arrData)
If arrData(j, 1) = arrCheck(i, 1) Then
ReDim Preserve arrOut(n)
arrOut(n) = arrData(j, 3)
n = n + 1
Exit For
Else
counter = counter + 1
End If
Next
If counter = LRow2 Then
ReDim Preserve arrOut(n)
arrOut(n) = "NA"
n = n + 1
End If
Next
Sheets("Sheet1").Range("B1").Resize(rowsize:=n) = _
Application.Transpose(arrOut)
End Sub


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Sun, 24 Aug 2014 02:50:17 -0700 (PDT) schrieb arush:

But the problem is that data is for more then 30,0000 rows and when i use V,look up it does not allow me to filter NA columns (error says count is greater is than 10,000).

or do you need a macro to create a filtered list for "NA"?
Then try (the macro writes you the missed items with NA to columns D:E):

Sub Test()
Dim LRow1 As Long, LRow2 As Long
Dim i As Long, j As Long, n As Long, counter As Long
Dim arrCheck As Variant, arrData As Variant
Dim arrOut() As Variant

LRow1 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
LRow2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
arrCheck = Sheets("Sheet1").Range("A1:A" & LRow1)
arrData = Sheets("Sheet2").Range("A1:C" & LRow2)

For i = LBound(arrCheck) To UBound(arrCheck)
counter = 0
For j = LBound(arrData) To UBound(arrData)
If arrData(j, 1) = arrCheck(i, 1) Then
Exit For
Else
counter = counter + 1
End If
Next
If counter = LRow2 Then
ReDim Preserve arrOut(1, n)
arrOut(0, n) = arrCheck(i, 1)
arrOut(1, n) = "NA"
n = n + 1
End If
Next
Sheets("Sheet1").Range("D1:E1").Resize(rowsize:=n) = _
Application.Transpose(arrOut)
End Sub


Regards
Claus B.

 

[arush]

Thanks again Claus!!
first one is working absolutely fine however on second one i am getting error.
Regards,
Arush

 

[Claus Busch]

Hi,

Am Sun, 24 Aug 2014 06:13:23 -0700 (PDT) schrieb arush:

first one is working absolutely fine however on second one i am getting error.

both are tested. What line errors out?


Regards
Claus B.

 

[arush]

Sheets("Sheet1").Range("D1:E1").Resize(rowsize:=n) = _

 

[Claus Busch]

Hi,

Am Sun, 24 Aug 2014 06:27:33 -0700 (PDT) schrieb arush:

Sheets("Sheet1").Range("D1:E1").Resize(rowsize:=n) = _

is the sheet name not "Sheet1"?


Regards
Claus B.

 

[arush]

it says run time error 5.

 

[Claus Busch]

Hi,

Am Sun, 24 Aug 2014 06:34:58 -0700 (PDT) schrieb arush:

please have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "Vlookup"

Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Sun, 24 Aug 2014 15:43:11 +0200 schrieb Claus Busch:

https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "Vlookup"

you have to download the file because macros are disabled in OneDrive


Regards
Claus B.

 

[arush]

It worked...Thanks a ton Claus!!