I am using vlookup to interrogate a column of cells and if the value I input
is not found in the column I would like to move the input value to the 1st
open cell at the end of the column. I don't know how to determine what the
1st open cell is without having to list each cell and check if it is blank.
Is there a way to look for the first cell that is blank? Thanks.

A formula cannot move or copy a cell, so you would need a macro to do
what you want to achieve.

Pete

On May 21, 4:34*pm, robeck wrote:
I am using vlookup to interrogate a column of cells and if the value I input
is not found in the column I would like to move the input value to the 1st
open cell at the end of the column. *I don't know how to determine what the
1st open cell is without having to list each cell and check if it is blank.. *
Is there a way to look for the first cell that is blank? Thanks.

Select the top cell of the column, then use End-Down , Down and you will arrive at the first
blank cell.

HTH,
Bernie
MS Excel MVP


"robeck" wrote in message
...
I am using vlookup to interrogate a column of cells and if the value I input
is not found in the column I would like to move the input value to the 1st
open cell at the end of the column. I don't know how to determine what the
1st open cell is without having to list each cell and check if it is blank.
Is there a way to look for the first cell that is blank? Thanks.

Do I need to write something in Visual Basic? I have done some macros but
haven't done anything conditional with a macro, is there a way to do that?

"Pete_UK" wrote:

A formula cannot move or copy a cell, so you would need a macro to do
what you want to achieve.

Pete

On May 21, 4:34 pm, robeck wrote:
I am using vlookup to interrogate a column of cells and if the value I input
is not found in the column I would like to move the input value to the 1st
open cell at the end of the column. I don't know how to determine what the
1st open cell is without having to list each cell and check if it is blank..
Is there a way to look for the first cell that is blank? Thanks.

Perhaps you could explain exactly what you want to do, and give
details like the sheet names involved, the cells you want to work with
and the formula you are using now.

Pete

On May 21, 7:55*pm, robeck wrote:
Do I need to write something in Visual Basic? *I have done some macros but
haven't done anything conditional with a macro, is there a way to do that?



"Pete_UK" wrote:
A formula cannot move or copy a cell, so you would need a macro to do
what you want to achieve.

Pete

On May 21, 4:34 pm, robeck wrote:
I am using vlookup to interrogate a column of cells and if the value I input
is not found in the column I would like to move the input value to the 1st
open cell at the end of the column. *I don't know how to determine what the
1st open cell is without having to list each cell and check if it is blank.. *
Is there a way to look for the first cell that is blank? Thanks.- Hide quoted text -

- Show quoted text -

Column A contains numerous cells with a project number (for example
200746498). The number of cells valued can change, right now we have values
in cells A3-A88. I have named the column projects. Right now I have a
vlookup statement in cell D2 which reads "VLOOKUP(C2,projects,1,FALSE)" where
I am inputting a project number in cell C2 and checking if that number is
already entered in the projects column (checking for duplicates). If it is
already there I don't want to do anything with it but if it has not been
entered in the projects column (the formula above returns an NA) I need to
find the first open cell in the column (this could change as projects are
added or deleted) and add the project number to the end of the list.
Thanks for your help.

"Pete_UK" wrote:

Perhaps you could explain exactly what you want to do, and give
details like the sheet names involved, the cells you want to work with
and the formula you are using now.

Pete

On May 21, 7:55 pm, robeck wrote:
Do I need to write something in Visual Basic? I have done some macros but
haven't done anything conditional with a macro, is there a way to do that?



"Pete_UK" wrote:
A formula cannot move or copy a cell, so you would need a macro to do
what you want to achieve.

Pete

On May 21, 4:34 pm, robeck wrote:
I am using vlookup to interrogate a column of cells and if the value I input
is not found in the column I would like to move the input value to the 1st
open cell at the end of the column. I don't know how to determine what the
1st open cell is without having to list each cell and check if it is blank..
Is there a way to look for the first cell that is blank? Thanks.- Hide quoted text -

- Show quoted text -

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

You can remove or comment out the line

MsgBox "I just added """ & Target.Value & """ to the list."

if you don't want to be notified when the project list is changed.

You can remove the formula in cell D2 since all it will do in the future is to return the value
entered in C2.

If your named range "projects" is dynamic, then you can remove the lines from On Error Resume Next
to the end.
If it is not dynamic, then those lines will update that named range to include the newest additions.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$C$2" Then Exit Sub
If IsError(Application.Match(Target.Value, Range("A:A"), False)) Then
Application.EnableEvents = False
Range("A2").End(xlDown)(2).Value = Target.Value
MsgBox "I just added """ & Target.Value & """ to the list."
Application.EnableEvents = True
End If

On Error Resume Next
ActiveWorkbook.Names("projects").Delete
ActiveWorkbook.Names.Add Name:="projects", RefersTo:= _
"='" & Target.Parent.Name & "'!" & Range("A3", Cells(Rows.Count, 1).End(xlUp)).Address

End Sub


"robeck" wrote in message
...
Column A contains numerous cells with a project number (for example
200746498). The number of cells valued can change, right now we have values
in cells A3-A88. I have named the column projects. Right now I have a
vlookup statement in cell D2 which reads "VLOOKUP(C2,projects,1,FALSE)" where
I am inputting a project number in cell C2 and checking if that number is
already entered in the projects column (checking for duplicates). If it is
already there I don't want to do anything with it but if it has not been
entered in the projects column (the formula above returns an NA) I need to
find the first open cell in the column (this could change as projects are
added or deleted) and add the project number to the end of the list.
Thanks for your help.

"Pete_UK" wrote:

Perhaps you could explain exactly what you want to do, and give
details like the sheet names involved, the cells you want to work with
and the formula you are using now.

Pete

On May 21, 7:55 pm, robeck wrote:
Do I need to write something in Visual Basic? I have done some macros but
haven't done anything conditional with a macro, is there a way to do that?



"Pete_UK" wrote:
A formula cannot move or copy a cell, so you would need a macro to do
what you want to achieve.

Pete

On May 21, 4:34 pm, robeck wrote:
I am using vlookup to interrogate a column of cells and if the value I input
is not found in the column I would like to move the input value to the 1st
open cell at the end of the column. I don't know how to determine what the
1st open cell is without having to list each cell and check if it is blank..
Is there a way to look for the first cell that is blank? Thanks.- Hide quoted text -

- Show quoted text -

That works (almost), now when I input a project id in cell C2 (one that is
not already on the list) it adds it in cell A8 which already had a value in
it and continues to add any subsequent ids in cell A8. Thanks so much for
your help so far, it is really appreciated.

"Bernie Deitrick" wrote:

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

You can remove or comment out the line

MsgBox "I just added """ & Target.Value & """ to the list."

if you don't want to be notified when the project list is changed.

You can remove the formula in cell D2 since all it will do in the future is to return the value
entered in C2.

If your named range "projects" is dynamic, then you can remove the lines from On Error Resume Next
to the end.
If it is not dynamic, then those lines will update that named range to include the newest additions.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$C$2" Then Exit Sub
If IsError(Application.Match(Target.Value, Range("A:A"), False)) Then
Application.EnableEvents = False
Range("A2").End(xlDown)(2).Value = Target.Value
MsgBox "I just added """ & Target.Value & """ to the list."
Application.EnableEvents = True
End If

On Error Resume Next
ActiveWorkbook.Names("projects").Delete
ActiveWorkbook.Names.Add Name:="projects", RefersTo:= _
"='" & Target.Parent.Name & "'!" & Range("A3", Cells(Rows.Count, 1).End(xlUp)).Address

End Sub


"robeck" wrote in message
...
Column A contains numerous cells with a project number (for example
200746498). The number of cells valued can change, right now we have values
in cells A3-A88. I have named the column projects. Right now I have a
vlookup statement in cell D2 which reads "VLOOKUP(C2,projects,1,FALSE)" where
I am inputting a project number in cell C2 and checking if that number is
already entered in the projects column (checking for duplicates). If it is
already there I don't want to do anything with it but if it has not been
entered in the projects column (the formula above returns an NA) I need to
find the first open cell in the column (this could change as projects are
added or deleted) and add the project number to the end of the list.
Thanks for your help.

"Pete_UK" wrote:

Perhaps you could explain exactly what you want to do, and give
details like the sheet names involved, the cells you want to work with
and the formula you are using now.

Pete

On May 21, 7:55 pm, robeck wrote:
Do I need to write something in Visual Basic? I have done some macros but
haven't done anything conditional with a macro, is there a way to do that?



"Pete_UK" wrote:
A formula cannot move or copy a cell, so you would need a macro to do
what you want to achieve.

Pete

On May 21, 4:34 pm, robeck wrote:
I am using vlookup to interrogate a column of cells and if the value I input
is not found in the column I would like to move the input value to the 1st
open cell at the end of the column. I don't know how to determine what the
1st open cell is without having to list each cell and check if it is blank..
Is there a way to look for the first cell that is blank? Thanks.- Hide quoted text -

- Show quoted text -

Based on your description, I assumed that your list started in cell A2 with a header value, and then
the actual values started in C3. Apparently, your first entry is in Cell A8. You can either
replace the A2 with A8, and A3 with A9, or use

Cells(Rows.Coount,1).End(xlUp)(2).Value = Target.Value

in place of

Range("A2").End(xlDown)(2).Value = Target.Value

HTH,
Bernie
MS Excel MVP


"robeck" wrote in message
...
That works (almost), now when I input a project id in cell C2 (one that is
not already on the list) it adds it in cell A8 which already had a value in
it and continues to add any subsequent ids in cell A8. Thanks so much for
your help so far, it is really appreciated.

"Bernie Deitrick" wrote:

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

You can remove or comment out the line

MsgBox "I just added """ & Target.Value & """ to the list."

if you don't want to be notified when the project list is changed.

You can remove the formula in cell D2 since all it will do in the future is to return the value
entered in C2.

If your named range "projects" is dynamic, then you can remove the lines from On Error Resume
Next
to the end.
If it is not dynamic, then those lines will update that named range to include the newest
additions.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$C$2" Then Exit Sub
If IsError(Application.Match(Target.Value, Range("A:A"), False)) Then
Application.EnableEvents = False
Range("A2").End(xlDown)(2).Value = Target.Value
MsgBox "I just added """ & Target.Value & """ to the list."
Application.EnableEvents = True
End If

On Error Resume Next
ActiveWorkbook.Names("projects").Delete
ActiveWorkbook.Names.Add Name:="projects", RefersTo:= _
"='" & Target.Parent.Name & "'!" & Range("A3", Cells(Rows.Count, 1).End(xlUp)).Address

End Sub


"robeck" wrote in message
...
Column A contains numerous cells with a project number (for example
200746498). The number of cells valued can change, right now we have values
in cells A3-A88. I have named the column projects. Right now I have a
vlookup statement in cell D2 which reads "VLOOKUP(C2,projects,1,FALSE)" where
I am inputting a project number in cell C2 and checking if that number is
already entered in the projects column (checking for duplicates). If it is
already there I don't want to do anything with it but if it has not been
entered in the projects column (the formula above returns an NA) I need to
find the first open cell in the column (this could change as projects are
added or deleted) and add the project number to the end of the list.
Thanks for your help.

"Pete_UK" wrote:

Perhaps you could explain exactly what you want to do, and give
details like the sheet names involved, the cells you want to work with
and the formula you are using now.

Pete

On May 21, 7:55 pm, robeck wrote:
Do I need to write something in Visual Basic? I have done some macros but
haven't done anything conditional with a macro, is there a way to do that?



"Pete_UK" wrote:
A formula cannot move or copy a cell, so you would need a macro to do
what you want to achieve.

Pete

On May 21, 4:34 pm, robeck wrote:
I am using vlookup to interrogate a column of cells and if the value I input
is not found in the column I would like to move the input value to the 1st
open cell at the end of the column. I don't know how to determine what the
1st open cell is without having to list each cell and check if it is blank..
Is there a way to look for the first cell that is blank? Thanks.- Hide quoted text -

- Show quoted text -

That works great, thank you so much for your help

"Bernie Deitrick" wrote:

Based on your description, I assumed that your list started in cell A2 with a header value, and then
the actual values started in C3. Apparently, your first entry is in Cell A8. You can either
replace the A2 with A8, and A3 with A9, or use

Cells(Rows.Coount,1).End(xlUp)(2).Value = Target.Value

in place of

Range("A2").End(xlDown)(2).Value = Target.Value

HTH,
Bernie
MS Excel MVP


"robeck" wrote in message
...
That works (almost), now when I input a project id in cell C2 (one that is
not already on the list) it adds it in cell A8 which already had a value in
it and continues to add any subsequent ids in cell A8. Thanks so much for
your help so far, it is really appreciated.

"Bernie Deitrick" wrote:

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

You can remove or comment out the line

MsgBox "I just added """ & Target.Value & """ to the list."

if you don't want to be notified when the project list is changed.

You can remove the formula in cell D2 since all it will do in the future is to return the value
entered in C2.

If your named range "projects" is dynamic, then you can remove the lines from On Error Resume
Next
to the end.
If it is not dynamic, then those lines will update that named range to include the newest
additions.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$C$2" Then Exit Sub
If IsError(Application.Match(Target.Value, Range("A:A"), False)) Then
Application.EnableEvents = False
Range("A2").End(xlDown)(2).Value = Target.Value
MsgBox "I just added """ & Target.Value & """ to the list."
Application.EnableEvents = True
End If

On Error Resume Next
ActiveWorkbook.Names("projects").Delete
ActiveWorkbook.Names.Add Name:="projects", RefersTo:= _
"='" & Target.Parent.Name & "'!" & Range("A3", Cells(Rows.Count, 1).End(xlUp)).Address

End Sub


"robeck" wrote in message
...
Column A contains numerous cells with a project number (for example
200746498). The number of cells valued can change, right now we have values
in cells A3-A88. I have named the column projects. Right now I have a
vlookup statement in cell D2 which reads "VLOOKUP(C2,projects,1,FALSE)" where
I am inputting a project number in cell C2 and checking if that number is
already entered in the projects column (checking for duplicates). If it is
already there I don't want to do anything with it but if it has not been
entered in the projects column (the formula above returns an NA) I need to
find the first open cell in the column (this could change as projects are
added or deleted) and add the project number to the end of the list.
Thanks for your help.

"Pete_UK" wrote:

Perhaps you could explain exactly what you want to do, and give
details like the sheet names involved, the cells you want to work with
and the formula you are using now.

Pete

On May 21, 7:55 pm, robeck wrote:
Do I need to write something in Visual Basic? I have done some macros but
haven't done anything conditional with a macro, is there a way to do that?



"Pete_UK" wrote:
A formula cannot move or copy a cell, so you would need a macro to do
what you want to achieve.

Pete

On May 21, 4:34 pm, robeck wrote:
I am using vlookup to interrogate a column of cells and if the value I input
is not found in the column I would like to move the input value to the 1st
open cell at the end of the column. I don't know how to determine what the
1st open cell is without having to list each cell and check if it is blank..
Is there a way to look for the first cell that is blank? Thanks.- Hide quoted text -

- Show quoted text -