Add new vaue to a different sheet

[helmekki]

Hi there

'* The code confirms that each value in sheet1 exist in sheet2.
'* I need if the code finds a new value in sheet1 to put it in sheet
last value..............
This code is my try, but cannot perform the job needed


Code
-------------------

Sub AdNwExp()
Dim counter As Integer
Dim myRng, cel, exp As Range

counter = 1
Do
Set exp = Sheet1.Range("B2:B50")
Set myRng = Sheet2.Range("B2:B50").Cells(counter, 1)

For Each cel In exp
If cel = myRng Then GoTo 1
Next cel
1:
counter = counter + 1
Loop Until IsEmpty(exp)

End Sub

-------------------

 

[Norman Jones]

Hi Helmekki,

Try:

Sub AdNwExp()

Dim myRng, cel, exp As Range
Dim destCell As Range

Set exp = Sheet1.Range("B2:B52")
Set myRng = Sheet2.Range("B2:B52")

On Error GoTo XIT

For Each cel In exp.Cells
If IsEmpty(cel) Then Exit For
With Application
.ScreenUpdating = False
If IsError(.Match(cel.Value, myRng, 0)) Then
If .CountA(myRng) = 0 Then
Set destCell = myRng(1)
ElseIf .CountA(myRng) = 1 Then
Set destCell = myRng(2)
Else
Set destCell = myRng(1).End(xlDown). _
Offset(1)
End If
cel.Copy destCell
End If
End With
Next
XIT:
Application.ScreenUpdating = True

End Sub

 

[Doug Glancy]

helmekki,

I started from scratch, but hopefully kept all your requirements. Like
yours, this only looks in B2:B50 and quits when it encounters an empty cell.
I think there are mo:

Sub AdNwExp()
Dim sheet1_cell As Range
Dim sheet2_last_row As Long

Application.ScreenUpdating = False
For Each sheet1_cell In Sheet1.Range("B2:B50")
If IsEmpty(sheet1_cell) Then
Exit For
End If
If Sheet2.Range("B2:B50").Find(what:=sheet1_cell.Value) Is Nothing Then
sheet2_last_row = Sheet2.Range("B" & Rows.Count).End(xlUp).Row
Sheet2.Range("B" & sheet2_last_row + 1).Value = sheet1_cell.Value
End If
Next sheet1_cell
Application.ScreenUpdating = True

End Sub

By the way, when you declared the variables:
Dim myRng, cel, exp As Range
only exp is declared as a range, the rest are declared as variants. To
declare them all ranges you'd do this:
Dim myRng as Range, cel as Range, exp As Range

hth,

Doug

 

[Bob Phillips]

Sub AdNwExp()
Dim counter As Integer
Dim myRng, cel, exp As Range

Set myRng = Sheet2.Range("B2:B" &
Sheet2.Cells(Rows.Count,"B").End(xlUp).Row)

For Each cell In Sheet1.Range("B2:B" &
Sheet1.Cells(Rows.Count,"B").End(xlUp).Row)
On Error Resume Next
iRow = Application.Match(cell.Value, myRng,0)
On Error Goto 0
If iRow = 0 Then
Sheet2.Cells(myRng(myRng.Count).Row + 1, "B").Value = cell.Value
Set myRng = myRng.Resize(myRng.Rows.Count + 1)
End If
Next cell

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Norman Jones]

Hi Helmekki,

Just a typo warning:

Set exp = Sheet1.Range("B2:B52")
Set myRng = Sheet2.Range("B2:B52")

In the above expressions, B52 should read B50, of course!

 

[William Benson]

Norman,

Any idea why I cannot find .Match as a documented method of the Application
object in Microsoft VBA Help? It seems to work perfectly, and I wanted to
learn about its arguments.

Thanks.

 

[Doug Glancy]

Bob,

I ran this with Option Explicit and had two compile errors. I changed your
declarations to:

Dim counter As Integer
Dim myRng, cell, exp As Range
Dim irow As Long

Can you briefly opine on the advantages (or not) of Match over Find for
this. I notice that both you and Norman used Match.

Thanks,

Doug Glancy

 

[Norman Jones]

Hi Bill,

Look at VBA's help on WorkSheetFunction.

 

[Norman Jones]

Hi Bill,

To add, Match is an Excel function; it is not a VBA function and you should
therefore look at the Excel help files.

Only a subset of Excel functions are available to VBA. VBA help provides a
list of these functions.

 

[William Benson]

Norman, thanks for the reply. I looked up the VBA reference to
WorksheetFunction object and it says "Used as a container for Microsoft
Excel worksheet functions that can be called from Visual Basic." Obviously,
not all functions fit this category. Specifically typing WorksheetFunction
has always given me the valid listing of functions, without the need to type
Application first ... but I had never thought of cutting out the "middleman"
(i.e., "WorksheetFunction") and was frankly surprised to see it work. Is it
just because Microsoft knows typing WorksheetFunction is a pain in the butt
and cuts us VBA-ers some slack, or is there a more fundamentally OOP reason
for it that, once I know this, I can leverage it in other situations?

As always, thanks for your wisdom!

Based on this, I am wondering how you managed to bypass the
WorksheetFunction object and link Application object to .Match?

 

[Norman Jones]

Hi Bill,

You may find the following thread informative:

http://tinyurl.com/am3er

 

[Bob Phillips]

I ran this with Option Explicit and had two compile errors. I changed your
declarations to:

Dim counter As Integer
Dim myRng, cell, exp As Range
Dim irow As Long

Bad me. I usually evangelise on Option Explicit and then I get caught not
using it <vbg>

Actually, the only Dims needed are

Dim myRng As Range
Dim cell As Range
Dim iRow As Long

the rest were throwbacks to the OPs code.

Can you briefly opine on the advantages (or not) of Match over Find for
this. I notice that both you and Norman used Match.

For me, there are 3 benefits
- it's faster, even with the call to a WorksheetFunction
- the code is more compact
- error trapping is more straight-forward, especially if you do it the way
Norman did, that is

If IsError(.Match(cell.Value, myRng, 0)) Then

Actually four, I just don't like Find, I always have problems with it.

 

[William Benson]

Great thread, thanks!

Hi Bill,

You may find the following thread informative:

http://tinyurl.com/am3er

 

[William Benson]

Doug,

You asked for the differences between FIND and MATCH. Basically, Match is
not case sensitive, and Match works with one-dimensional arrays. Therefore,
you cannot use Match with ranges which exceed 1 x N or M x 1. I
include the below as a demonstration.

My suggestion is that since the OP's need was VERY narrow, MATCH worked, but
in general, Find is the way to go. I have no idea which is quicker, but Find
is more versatile.

'DEMONSTRATION OF INEFFECTIVENESS OF MATCH TO TEST A RANGE FOR A VALUE

Sub TestMatch()
Dim LookUpRange As Range
Const MyVal = "XXX"
Range("A1").Value = "XXX"

'Results:
'Range $1:$1 Range is 1 x 256 Value 'Matched' = True
'Range $A$1:$E$2 Range is 2 x 5 Value 'Matched' = False
'Range $A:$A Range is 65536 x 1 Value 'Matched' = True

Set LookUpRange = Range("A1:IV1")
Debug.Print "Range " & LookUpRange.Address & " " & _
" Range is " & LookUpRange.Rows.Count & " x " & _
LookUpRange.Columns.Count & " Value 'Matched' = " & _
Not (IsError(Application.Match(MyVal, LookUpRange, 0)))

Set LookUpRange = Range("A1:E2")
Debug.Print "Range " & LookUpRange.Address & " " & _
" Range is " & LookUpRange.Rows.Count & " x " & _
LookUpRange.Columns.Count & " Value 'Matched' = " & _
Not (IsError(Application.Match(MyVal, LookUpRange, 0)))

Set LookUpRange = Range("A1:A65536")
Debug.Print "Range " & LookUpRange.Address & " " & _
" Range is " & LookUpRange.Rows.Count & " x " & _
LookUpRange.Columns.Count & " Value 'Matched' = " & _
Not (IsError(Application.Match(MyVal, LookUpRange, 0)))

End Sub

 

[helmekki]

Thank u all very much...all your codes worked well,

apart from Bob Phillips's code did not work...............hope you
look at it , ia interested in knowing the answer........

 

[STEVE BELL]

You got some great ideas - but I like to use CountIf(rng, value)
(less possibility for errors)

Dim x as long
x = Worksheetfunction.Countif(rng,value)
If x = 0 then
msgbox "Not Found"
Else
msgbox x & " number of matches found"
End If

 

[Doug Glancy]

William,

Thanks for the info. It's great that in less than two hours you went from
asking about Match to learned exposition on the topic. I ran your code and
it was very clear.

I demonstrated the limitation even more simply to myself by using Match in
a spreadsheet. It finds a match if it's 1D but if it's 2D it returns #NA.
This makes senses, since the "position" of a match in a 2D array is
ambiguous, but it's interesting that the Excel help on Match doesn't say
anything about the one-dimensional limit.

Thanks again,

Doug

 

[William Benson]

Thanks, that was nice to say.

William,

Thanks for the info. It's great that in less than two hours you went from
asking about Match to learned exposition on the topic. I ran your code
and it was very clear.

I demonstrated the limitation even more simply to myself by using Match in
a spreadsheet. It finds a match if it's 1D but if it's 2D it returns #NA.
This makes senses, since the "position" of a match in a 2D array is
ambiguous, but it's interesting that the Excel help on Match doesn't say
anything about the one-dimensional limit.

Thanks again,

Doug