Inserting 0 in selected cells in excel

[Byrner]

Hello , A very warm Hello or Hi to everybody from Ireland

I have just joined my name is Maureen, Not great on computers but using excel for a small craft business I am trying to get going.
Can anybody help? I need to insert the number 0 in selected cells , is there a quicker way to do this other then by typing in 0 opposite every selected name?
I have a long list of customer names, if one name on the list e has order from me in the past I need to insert 0 in the column beside their name.
Its an old list, so I have to read from the list and and when a name matches insert the 0
Any help would be really great
Thanks again
Maureen

 

[philwojo]

It might help to provide some sample data in the format (layout) as you have it. You can also use places like dropbox to upload a sample copy of your file there and then link it here, some people can look at those links to help as well.

I think you might be able to use vlookup or maybe and index/match formula, but without seeing the data and layout it is hard to speculate.

 

[Byrner]

Hi Thank you for the reply, I cannot really post real Names on my list but this is a sample of what it looks like

They are all single columns and if the name matches my list I insert a (0) in the cell beside the name.
Thanks again
M.[TABLE="width: 506"]
<colgroup><col><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]First Name[/TD]
[TD]Surname [/TD]
[TD]Address[/TD]
[TD]Tel1[/TD]
[TD]Tel 2 [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD="align: right"]21/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]22/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]23/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]24/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]25/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]26/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]27/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]28/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]29/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]30/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]31/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Byrner]

hnks for the reply, Cannot really show real names etc, but this is an example of what i am doing.Hope it helps
Maureen


[TABLE="width: 506"]
<colgroup><col width="75" style="width: 56pt;"><col width="97" style="width: 73pt;"><col width="64" span="3" style="width: 48pt;"><col width="78" style="width: 59pt;"><col width="64" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="width: 75"]Date[/TD]
[TD="width: 97"]First Name[/TD]
[TD="width: 64"]Surname [/TD]
[TD="width: 64"]Address[/TD]
[TD="width: 64"]Tel1[/TD]
[TD="width: 78"]Tel 2 [/TD]
[TD="class: m_-952652500266650936gmail-xl64, width: 64"]Yes[/TD]
[/TR]
[TR]
[TD="class: m_-952652500266650936gmail-xl63, align: right"]21/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-952652500266650936gmail-xl64, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: m_-952652500266650936gmail-xl63, align: right"]22/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-952652500266650936gmail-xl64"] [/TD]
[/TR]
[TR]
[TD="class: m_-952652500266650936gmail-xl63, align: right"]23/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-952652500266650936gmail-xl64"] [/TD]
[/TR]
[TR]
[TD="class: m_-952652500266650936gmail-xl63, align: right"]24/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-952652500266650936gmail-xl64, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: m_-952652500266650936gmail-xl63, align: right"]25/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-952652500266650936gmail-xl64"] [/TD]
[/TR]
[TR]
[TD="class: m_-952652500266650936gmail-xl63, align: right"]26/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-952652500266650936gmail-xl64"] [/TD]
[/TR]
[TR]
[TD="class: m_-952652500266650936gmail-xl63, align: right"]27/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-952652500266650936gmail-xl64"] [/TD]
[/TR]
[TR]
[TD="class: m_-952652500266650936gmail-xl63, align: right"]28/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-952652500266650936gmail-xl64"] [/TD]
[/TR]
[TR]
[TD="class: m_-952652500266650936gmail-xl63, align: right"]29/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-952652500266650936gmail-xl64"] [/TD]
[/TR]
[TR]
[TD="class: m_-952652500266650936gmail-xl63, align: right"]30/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-952652500266650936gmail-xl64"] [/TD]
[/TR]
[TR]
[TD="class: m_-952652500266650936gmail-xl63, align: right"]31/03/2017[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]zyz[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-952652500266650936gmail-xl64, align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: m_-952652500266650936gmail-xl64"]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Byrner]

Hi again
Is it possible to double click/ right click on the cell and automatically insert 0, I found it online but only works with x cannot seem to change the x to 0
Thanks

 

[philwojo]

All of your sample data are the same values, so not sure why you have a zero in some areas and not in others. Can you change the sample data to be unique, but to kind of mirror your real data so we can help better.

Does your data ever duplicates of names?

 

[MickG]

Try this:-

Private [COLOR=navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=navy]As[/COLOR] Range)
    [COLOR=navy]If[/COLOR] Target.Column = 7 And Not Target.Row = 1 [COLOR=navy]Then[/COLOR]
        Target = IIf(Target = "", 0, "")
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

To insert code:-
In data sheet Right click sheet "Tab", select "View Code", Vbwindow appears, Paste code in VbWindow, close Vbwindow

To run code click any cell in column "G"
Click once for a "0" click again to clear cell
Regards Mick

 

[Byrner]

Hi the
The (0) is to indicate that the name appears on my written list. The written list is thousands of names that span the last 20 years.
I am trying to match the old list with what I have on file. Some of the names on five go back about 5 years.,
The reason I use a zero number is because its a program I borrowed and works really well but needs the number (0) to trigger it
So as of now I am typing in the 0, but trying to find a quicker way. The names do not duplicate very much either as the sales are mostly to overseas visitors who purchased items while on holiday here.
But Thanks again for your help
Maureen

 

[Fluff]

A variation on MickG's code. This works on double click

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column <> 7 Then Exit Sub
   Cancel = True
   Target.Value = IIf(Target.Value = "", 0, "")
End Sub

 

[philwojo]

OK, I can't think of a way to make excel, or any computer program for that matter, enter in a value based on a paper list. Maybe I am missing what you are trying to accomplish, and if so I apologize for that, but how would you like Excel to know to put in a zero from something outside the program?