Add new vaue to a different sheet
 

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

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

--
helmekk

-----------------------------------------------------------------------
helmekki's Profile: http://www.excelforum.com/member.php...nfo&userid=693
View this thread: http://www.excelforum.com/showthread.php?threadid=38409

Add new vaue to a different sheet
 

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


---
Regards,
Norman



"helmekki" wrote in
message ...

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 sheet2
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

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


--
helmekki


------------------------------------------------------------------------
helmekki's Profile:
http://www.excelforum.com/member.php...fo&userid=6939
View this thread: http://www.excelforum.com/showthread...hreadid=384097

Add new vaue to a different sheet
 

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.Valu e) 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

"helmekki" wrote in
message ...

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 sheet2
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

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


--
helmekki


------------------------------------------------------------------------
helmekki's Profile:
http://www.excelforum.com/member.php...fo&userid=6939
View this thread: http://www.excelforum.com/showthread...hreadid=384097

Add new vaue to a different sheet
 

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)


"helmekki" wrote in
message ...

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 sheet2
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

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


--
helmekki


------------------------------------------------------------------------
helmekki's Profile:
http://www.excelforum.com/member.php...fo&userid=6939
View this thread: http://www.excelforum.com/showthread...hreadid=384097

Add new vaue to a different sheet
 

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!

---
Regards,
Norman



"Norman Jones" wrote in message
...
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


---
Regards,
Norman

Add new vaue to a different sheet
 

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.

"Norman Jones" wrote in message
...
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


---
Regards,
Norman



"helmekki" wrote
in message ...

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 sheet2
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

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


--
helmekki


------------------------------------------------------------------------
helmekki's Profile:
http://www.excelforum.com/member.php...fo&userid=6939
View this thread:
http://www.excelforum.com/showthread...hreadid=384097

Add new vaue to a different sheet
 

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

"Bob Phillips" wrote in message
...
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)


"helmekki" wrote
in
message ...

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 sheet2
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

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


--
helmekki


------------------------------------------------------------------------
helmekki's Profile:
http://www.excelforum.com/member.php...fo&userid=6939
View this thread:
http://www.excelforum.com/showthread...hreadid=384097

Add new vaue to a different sheet
 

Hi Bill,

Look at VBA's help on WorkSheetFunction.


---
Regards,
Norman



"William Benson" wrote in message
...
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.

"Norman Jones" wrote in message
...
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


---
Regards,
Norman



"helmekki" wrote
in message ...

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 sheet2
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

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


--
helmekki


------------------------------------------------------------------------
helmekki's Profile:
http://www.excelforum.com/member.php...fo&userid=6939
View this thread:
http://www.excelforum.com/showthread...hreadid=384097

Add new vaue to a different sheet
 

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.

---
Regards,
Norman



"William Benson" wrote in message
...
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.

"Norman Jones" wrote in message
...
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


---
Regards,
Norman



"helmekki" wrote
in message ...

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 sheet2
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

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


--
helmekki


------------------------------------------------------------------------
helmekki's Profile:
http://www.excelforum.com/member.php...fo&userid=6939
View this thread:
http://www.excelforum.com/showthread...hreadid=384097

Add new vaue to a different sheet
 

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" wrote in message
...
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.

---
Regards,
Norman



"William Benson" wrote in message
...
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.

"Norman Jones" wrote in message
...
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


---
Regards,
Norman



"helmekki" wrote
in message ...

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 sheet2
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

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


--
helmekki


------------------------------------------------------------------------
helmekki's Profile:
http://www.excelforum.com/member.php...fo&userid=6939
View this thread:
http://www.excelforum.com/showthread...hreadid=384097

Add new vaue to a different sheet
 

Hi Bill,

You may find the following thread informative:

http://tinyurl.com/am3er



---
Regards,
Norman



"William Benson" wrote in message
...
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" wrote in message
...
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.

---
Regards,
Norman



"William Benson" wrote in message
...
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.

Add new vaue to a different sheet
 

"Doug Glancy" wrote in message
...
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.

Add new vaue to a different sheet
 

Great thread, thanks!

"Norman Jones" wrote in message
...
Hi Bill,

You may find the following thread informative:

http://tinyurl.com/am3er



---
Regards,
Norman



"William Benson" wrote in message
...
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" wrote in message
...
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.

---
Regards,
Norman



"William Benson" wrote in message
...
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.

Add new vaue to a different sheet
 

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



"Doug Glancy" wrote in message
...
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

"Bob Phillips" wrote in message
...
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)


"helmekki" wrote
in
message ...

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 sheet2
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

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


--
helmekki


------------------------------------------------------------------------
helmekki's Profile:
http://www.excelforum.com/member.php...fo&userid=6939
View this thread:
http://www.excelforum.com/showthread...hreadid=384097

Add new vaue to a different sheet
 

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........ :)

:)


--
helmekki


------------------------------------------------------------------------
helmekki's Profile: http://www.excelforum.com/member.php...fo&userid=6939
View this thread: http://www.excelforum.com/showthread...hreadid=384097

Add new vaue to a different sheet
 

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


--
steveB

Remove "AYN" from email to respond
"helmekki" wrote in
message ...

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 sheet2
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

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


--
helmekki


------------------------------------------------------------------------
helmekki's Profile:
http://www.excelforum.com/member.php...fo&userid=6939
View this thread: http://www.excelforum.com/showthread...hreadid=384097

Add new vaue to a different sheet
 

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" wrote in message
...
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



"Doug Glancy" wrote in message
...
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

"Bob Phillips" wrote in message
...
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)


"helmekki" wrote
in
message ...

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 sheet2
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

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


--
helmekki


------------------------------------------------------------------------
helmekki's Profile:
http://www.excelforum.com/member.php...fo&userid=6939
View this thread:
http://www.excelforum.com/showthread...hreadid=384097

Add new vaue to a different sheet
 

Thanks, that was nice to say.

"Doug Glancy" wrote in message
...
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" wrote in message
...
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



"Doug Glancy" wrote in message
...
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

"Bob Phillips" wrote in message
...
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)


"helmekki"
wrote in
message ...

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 sheet2
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

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


--
helmekki


------------------------------------------------------------------------
helmekki's Profile:
http://www.excelforum.com/member.php...fo&userid=6939
View this thread:
http://www.excelforum.com/showthread...hreadid=384097