I have 3 columns in a spreadsheet; data (which can be either numeric or
alpha) in Column A always starts in Row 11 and can go for 5, 10, or 10000
rows (i.e., it varies each time). Data (which can be either numeric or
alpha) in Column B always starts in row 2 and the last used cell can also
vary each time. Column C is blank. I need a macro that will do the
following:

1) Go to C2 and determine if Column A contains any instances of what is in
cell B2; if it does, then skip down to C3. If Column A does not contain any
instances of what is in cell B2, then enter 'N/A' in C2, and go on to C3.
2) Repeat this action through to the last used cell in Column B, at which
time, go on to the rest of the sub that follows.

Can someone please help me with this? Looping (and integrating last cell
code) is beyond my expertise at this time, but I'm trying to learn.
Thanks....Paige

Give this a whirl...

Sub FindStuff()
Dim rngToSearch As Range
Dim rngToFind As Range
Dim rngFound As Range
Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
Set rngToSearch = .Columns("A")
Set rngToFind = .Range(.Range("B2"), .Cells(Rows.Count, "B").End(xlUp))
End With

For Each rng In rngToFind
Set rngFound = rngToSearch.Find(What:=rng.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then rng.Offset(0, 1).Value = "NA"
Next rng

End Sub

--
HTH...

Jim Thomlinson


"Paige" wrote:

I have 3 columns in a spreadsheet; data (which can be either numeric or
alpha) in Column A always starts in Row 11 and can go for 5, 10, or 10000
rows (i.e., it varies each time). Data (which can be either numeric or
alpha) in Column B always starts in row 2 and the last used cell can also
vary each time. Column C is blank. I need a macro that will do the
following:

1) Go to C2 and determine if Column A contains any instances of what is in
cell B2; if it does, then skip down to C3. If Column A does not contain any
instances of what is in cell B2, then enter 'N/A' in C2, and go on to C3.
2) Repeat this action through to the last used cell in Column B, at which
time, go on to the rest of the sub that follows.

Can someone please help me with this? Looping (and integrating last cell
code) is beyond my expertise at this time, but I'm trying to learn.
Thanks....Paige

Wow - you are AWSOME! Works like a charm, and I even understand what you
did...so little by little am learning. Thanks so much!!!

"Jim Thomlinson" wrote:

Give this a whirl...

Sub FindStuff()
Dim rngToSearch As Range
Dim rngToFind As Range
Dim rngFound As Range
Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
Set rngToSearch = .Columns("A")
Set rngToFind = .Range(.Range("B2"), .Cells(Rows.Count, "B").End(xlUp))
End With

For Each rng In rngToFind
Set rngFound = rngToSearch.Find(What:=rng.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then rng.Offset(0, 1).Value = "NA"
Next rng

End Sub

--
HTH...

Jim Thomlinson


"Paige" wrote:

I have 3 columns in a spreadsheet; data (which can be either numeric or
alpha) in Column A always starts in Row 11 and can go for 5, 10, or 10000
rows (i.e., it varies each time). Data (which can be either numeric or
alpha) in Column B always starts in row 2 and the last used cell can also
vary each time. Column C is blank. I need a macro that will do the
following:

1) Go to C2 and determine if Column A contains any instances of what is in
cell B2; if it does, then skip down to C3. If Column A does not contain any
instances of what is in cell B2, then enter 'N/A' in C2, and go on to C3.
2) Repeat this action through to the last used cell in Column B, at which
time, go on to the rest of the sub that follows.

Can someone please help me with this? Looping (and integrating last cell
code) is beyond my expertise at this time, but I'm trying to learn.
Thanks....Paige

Glad to help. I have seen the code you write in some of your other posts. You
should be trying this kind of stuff on your own. I know you can do it... any
time you want help though, just ask.
--
HTH...

Jim Thomlinson


"Paige" wrote:

Wow - you are AWSOME! Works like a charm, and I even understand what you
did...so little by little am learning. Thanks so much!!!

"Jim Thomlinson" wrote:

Give this a whirl...

Sub FindStuff()
Dim rngToSearch As Range
Dim rngToFind As Range
Dim rngFound As Range
Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
Set rngToSearch = .Columns("A")
Set rngToFind = .Range(.Range("B2"), .Cells(Rows.Count, "B").End(xlUp))
End With

For Each rng In rngToFind
Set rngFound = rngToSearch.Find(What:=rng.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then rng.Offset(0, 1).Value = "NA"
Next rng

End Sub

--
HTH...

Jim Thomlinson


"Paige" wrote:

I have 3 columns in a spreadsheet; data (which can be either numeric or
alpha) in Column A always starts in Row 11 and can go for 5, 10, or 10000
rows (i.e., it varies each time). Data (which can be either numeric or
alpha) in Column B always starts in row 2 and the last used cell can also
vary each time. Column C is blank. I need a macro that will do the
following:

1) Go to C2 and determine if Column A contains any instances of what is in
cell B2; if it does, then skip down to C3. If Column A does not contain any
instances of what is in cell B2, then enter 'N/A' in C2, and go on to C3.
2) Repeat this action through to the last used cell in Column B, at which
time, go on to the rest of the sub that follows.

Can someone please help me with this? Looping (and integrating last cell
code) is beyond my expertise at this time, but I'm trying to learn.
Thanks....Paige

Thanks, Jim. I'm a little (well, more than a little) fuzzy on loops and many
finer points of coding. I try to look stuff up both here and in my numerous
books and do myself, but after awhile, when the headache hits, I know it's
time to ask for assistance. One of these near weekends I'll just have to sit
down with my books and really learn some of the important things missed the
first time around. Your assistance, and that of everyone else's on this
website is just invaluable, and I thank you again for that! Have a great
evening...Paige

"Jim Thomlinson" wrote:

Glad to help. I have seen the code you write in some of your other posts. You
should be trying this kind of stuff on your own. I know you can do it... any
time you want help though, just ask.
--
HTH...

Jim Thomlinson


"Paige" wrote:

Wow - you are AWSOME! Works like a charm, and I even understand what you
did...so little by little am learning. Thanks so much!!!

"Jim Thomlinson" wrote:

Give this a whirl...

Sub FindStuff()
Dim rngToSearch As Range
Dim rngToFind As Range
Dim rngFound As Range
Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
Set rngToSearch = .Columns("A")
Set rngToFind = .Range(.Range("B2"), .Cells(Rows.Count, "B").End(xlUp))
End With

For Each rng In rngToFind
Set rngFound = rngToSearch.Find(What:=rng.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then rng.Offset(0, 1).Value = "NA"
Next rng

End Sub

--
HTH...

Jim Thomlinson


"Paige" wrote:

I have 3 columns in a spreadsheet; data (which can be either numeric or
alpha) in Column A always starts in Row 11 and can go for 5, 10, or 10000
rows (i.e., it varies each time). Data (which can be either numeric or
alpha) in Column B always starts in row 2 and the last used cell can also
vary each time. Column C is blank. I need a macro that will do the
following:

1) Go to C2 and determine if Column A contains any instances of what is in
cell B2; if it does, then skip down to C3. If Column A does not contain any
instances of what is in cell B2, then enter 'N/A' in C2, and go on to C3.
2) Repeat this action through to the last used cell in Column B, at which
time, go on to the rest of the sub that follows.

Can someone please help me with this? Looping (and integrating last cell
code) is beyond my expertise at this time, but I'm trying to learn.
Thanks....Paige

You don't give yourself enough credit. If you understand that code you are
probably further along than you think. You understand worksheet objects,
range objects and get the idea of using a for next loop to traverse a
collection of objects... That is lots and more than most.
--
HTH...

Jim Thomlinson


"Paige" wrote:

Thanks, Jim. I'm a little (well, more than a little) fuzzy on loops and many
finer points of coding. I try to look stuff up both here and in my numerous
books and do myself, but after awhile, when the headache hits, I know it's
time to ask for assistance. One of these near weekends I'll just have to sit
down with my books and really learn some of the important things missed the
first time around. Your assistance, and that of everyone else's on this
website is just invaluable, and I thank you again for that! Have a great
evening...Paige

"Jim Thomlinson" wrote:

Glad to help. I have seen the code you write in some of your other posts. You
should be trying this kind of stuff on your own. I know you can do it... any
time you want help though, just ask.
--
HTH...

Jim Thomlinson


"Paige" wrote:

Wow - you are AWSOME! Works like a charm, and I even understand what you
did...so little by little am learning. Thanks so much!!!

"Jim Thomlinson" wrote:

Give this a whirl...

Sub FindStuff()
Dim rngToSearch As Range
Dim rngToFind As Range
Dim rngFound As Range
Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
Set rngToSearch = .Columns("A")
Set rngToFind = .Range(.Range("B2"), .Cells(Rows.Count, "B").End(xlUp))
End With

For Each rng In rngToFind
Set rngFound = rngToSearch.Find(What:=rng.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then rng.Offset(0, 1).Value = "NA"
Next rng

End Sub

--
HTH...

Jim Thomlinson


"Paige" wrote:

I have 3 columns in a spreadsheet; data (which can be either numeric or
alpha) in Column A always starts in Row 11 and can go for 5, 10, or 10000
rows (i.e., it varies each time). Data (which can be either numeric or
alpha) in Column B always starts in row 2 and the last used cell can also
vary each time. Column C is blank. I need a macro that will do the
following:

1) Go to C2 and determine if Column A contains any instances of what is in
cell B2; if it does, then skip down to C3. If Column A does not contain any
instances of what is in cell B2, then enter 'N/A' in C2, and go on to C3.
2) Repeat this action through to the last used cell in Column B, at which
time, go on to the rest of the sub that follows.

Can someone please help me with this? Looping (and integrating last cell
code) is beyond my expertise at this time, but I'm trying to learn.
Thanks....Paige

I'm starting a similar project however this is a "running total" spreadsheet.
I will be comparing the data in "myrange" with several different columns
that will be added to each week.

This week the comparison would be with "D" and "P" next week there will be
another column. All columns will have the title "Route Number(s)".

Here is what I have so far:

With lastperiod

'Find the last used column
myrange = ActiveSheet.UsedRange.Columns.Count
ActiveSheet.Cells(1, myrange + 2).Select

'paste the info
ActiveSheet.Paste

'start checking for duplicate route numbers
Dim rngToSearch As Range
Dim rngToFind As Range
Dim rngFound As Range
Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
Set rngToSearch = .Cells(myrange + 2)
Set rngToFind =

"Jim Thomlinson" wrote:

Give this a whirl...

Sub FindStuff()
Dim rngToSearch As Range
Dim rngToFind As Range
Dim rngFound As Range
Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
Set rngToSearch = .Columns("A")
Set rngToFind = .Range(.Range("B2"), .Cells(Rows.Count, "B").End(xlUp))
End With

For Each rng In rngToFind
Set rngFound = rngToSearch.Find(What:=rng.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then rng.Offset(0, 1).Value = "NA"
Next rng

End Sub

--
HTH...

Jim Thomlinson


"Paige" wrote:

I have 3 columns in a spreadsheet; data (which can be either numeric or
alpha) in Column A always starts in Row 11 and can go for 5, 10, or 10000
rows (i.e., it varies each time). Data (which can be either numeric or
alpha) in Column B always starts in row 2 and the last used cell can also
vary each time. Column C is blank. I need a macro that will do the
following:

1) Go to C2 and determine if Column A contains any instances of what is in
cell B2; if it does, then skip down to C3. If Column A does not contain any
instances of what is in cell B2, then enter 'N/A' in C2, and go on to C3.
2) Repeat this action through to the last used cell in Column B, at which
time, go on to the rest of the sub that follows.

Can someone please help me with this? Looping (and integrating last cell
code) is beyond my expertise at this time, but I'm trying to learn.
Thanks....Paige