***Help on writing a vba code

  • Thread starter Thread starter arush.saxena1
  • Start date Start date
A

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!!
 
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.
Click to expand...

you do not need VBA.
Try instead a formula:

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


Regards
Claus B.
 
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
 
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).
Click to expand...

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.
 
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).
Click to expand...

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.
 
Thanks again Claus!!
first one is working absolutely fine however on second one i am getting error.
Regards,
Arush
 
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.
Click to expand...

both are tested. What line errors out?


Regards
Claus B.
 
Hi,

Am Sun, 24 Aug 2014 06:27:33 -0700 (PDT) schrieb arush:
Sheets("Sheet1").Range("D1:E1").Resize(rowsize:=n) = _
Click to expand...

is the sheet name not "Sheet1"?


Regards
Claus B.
 
Back
Top