VBA forms - HELP!!!

Hello,

I'm using the following in a macro but it doesn't work:

    Range("A2:A65536").Select
    If Cells.Select = "0" Then Selection.ClearContents

What should I change???
0
OneLeg (20)
12/15/2004 6:17:03 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
688 Views

Similar Articles

[PageSpeed] 41

Hi
what are you trying to achieve ?

-- 
Regards
Frank Kabel
Frankfurt, Germany
"One-Leg" <OneLeg@discussions.microsoft.com> schrieb im Newsbeitrag 
news:09BDF4EF-22F3-4223-B2FD-B45D8EB37D7F@microsoft.com...
> Hello,
>
> I'm using the following in a macro but it doesn't work:
>
>    Range("A2:A65536").Select
>    If Cells.Select = "0" Then Selection.ClearContents
>
> What should I change??? 


0
frank.kabel (11126)
12/15/2004 6:24:58 PM
I just recorded the macro6 below but it could be simply

Columns(1).Replace "0", ""

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 12/15/2004 by Don Guillett
'

'
    Columns("A:A").Select
    Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"One-Leg" <OneLeg@discussions.microsoft.com> wrote in message
news:09BDF4EF-22F3-4223-B2FD-B45D8EB37D7F@microsoft.com...
> Hello,
>
> I'm using the following in a macro but it doesn't work:
>
>     Range("A2:A65536").Select
>     If Cells.Select = "0" Then Selection.ClearContents
>
> What should I change???


0
Don
12/15/2004 6:35:01 PM
Hello,

I have many cells with numbers in them and many come up with only a "0" in 
them.  I want to use a macro that will see the value of the cell and if it is 
"0", then I would like to clear the cell.  If it's anything else than "0", 
nothing should be done!!!

Thanks

"Frank Kabel" wrote:

> Hi
> what are you trying to achieve ?
> 
> -- 
> Regards
> Frank Kabel
> Frankfurt, Germany
> "One-Leg" <OneLeg@discussions.microsoft.com> schrieb im Newsbeitrag 
> news:09BDF4EF-22F3-4223-B2FD-B45D8EB37D7F@microsoft.com...
> > Hello,
> >
> > I'm using the following in a macro but it doesn't work:
> >
> >    Range("A2:A65536").Select
> >    If Cells.Select = "0" Then Selection.ClearContents
> >
> > What should I change??? 
> 
> 
> 
0
OneLeg (20)
12/15/2004 6:59:02 PM
Hello,

I did that allready but the other cells in colum A that have numbers 
(105482102542) in them, the "0" are removed and that is not a good thing...  
:-(

Thanks!!!

"Don Guillett" wrote:

> I just recorded the macro6 below but it could be simply
> 
> Columns(1).Replace "0", ""
> 
> Sub Macro6()
> '
> ' Macro6 Macro
> ' Macro recorded 12/15/2004 by Don Guillett
> '
> 
> '
>     Columns("A:A").Select
>     Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
>         ReplaceFormat:=False
> End Sub
> 
> -- 
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "One-Leg" <OneLeg@discussions.microsoft.com> wrote in message
> news:09BDF4EF-22F3-4223-B2FD-B45D8EB37D7F@microsoft.com...
> > Hello,
> >
> > I'm using the following in a macro but it doesn't work:
> >
> >     Range("A2:A65536").Select
> >     If Cells.Select = "0" Then Selection.ClearContents
> >
> > What should I change???
> 
> 
> 
0
OneLeg (20)
12/15/2004 7:01:06 PM
Hi
then use 'Edit - Replace' for this

-- 
Regards
Frank Kabel
Frankfurt, Germany
"One-Leg" <OneLeg@discussions.microsoft.com> schrieb im Newsbeitrag 
news:BBCBE224-4BA0-4A92-B2F0-41B2840C41DD@microsoft.com...
> Hello,
>
> I have many cells with numbers in them and many come up with only a "0" in
> them.  I want to use a macro that will see the value of the cell and if it 
> is
> "0", then I would like to clear the cell.  If it's anything else than "0",
> nothing should be done!!!
>
> Thanks
>
> "Frank Kabel" wrote:
>
>> Hi
>> what are you trying to achieve ?
>>
>> -- 
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>> "One-Leg" <OneLeg@discussions.microsoft.com> schrieb im Newsbeitrag
>> news:09BDF4EF-22F3-4223-B2FD-B45D8EB37D7F@microsoft.com...
>> > Hello,
>> >
>> > I'm using the following in a macro but it doesn't work:
>> >
>> >    Range("A2:A65536").Select
>> >    If Cells.Select = "0" Then Selection.ClearContents
>> >
>> > What should I change???
>>
>>
>> 


0
frank.kabel (11126)
12/15/2004 7:05:05 PM
Hello,

Using Edit/Replace does the same thing...

"Frank Kabel" wrote:

> Hi
> then use 'Edit - Replace' for this
> 
> -- 
> Regards
> Frank Kabel
> Frankfurt, Germany
> "One-Leg" <OneLeg@discussions.microsoft.com> schrieb im Newsbeitrag 
> news:BBCBE224-4BA0-4A92-B2F0-41B2840C41DD@microsoft.com...
> > Hello,
> >
> > I have many cells with numbers in them and many come up with only a "0" in
> > them.  I want to use a macro that will see the value of the cell and if it 
> > is
> > "0", then I would like to clear the cell.  If it's anything else than "0",
> > nothing should be done!!!
> >
> > Thanks
> >
> > "Frank Kabel" wrote:
> >
> >> Hi
> >> what are you trying to achieve ?
> >>
> >> -- 
> >> Regards
> >> Frank Kabel
> >> Frankfurt, Germany
> >> "One-Leg" <OneLeg@discussions.microsoft.com> schrieb im Newsbeitrag
> >> news:09BDF4EF-22F3-4223-B2FD-B45D8EB37D7F@microsoft.com...
> >> > Hello,
> >> >
> >> > I'm using the following in a macro but it doesn't work:
> >> >
> >> >    Range("A2:A65536").Select
> >> >    If Cells.Select = "0" Then Selection.ClearContents
> >> >
> >> > What should I change???
> >>
> >>
> >> 
> 
> 
> 
0
OneLeg (20)
12/15/2004 7:15:01 PM
then use
Columns(1).Replace "0", "", LookAt:=xlWhole
or, more simply
Columns(1).Replace "0", "", xlWhole
-- 
Don Guillett
SalesAid Software
donaldb@281.com
"One-Leg" <OneLeg@discussions.microsoft.com> wrote in message
news:484215B8-BB04-49F3-91D5-32199B2A3B7D@microsoft.com...
> Hello,
>
> I did that allready but the other cells in colum A that have numbers
> (105482102542) in them, the "0" are removed and that is not a good
thing...
> :-(
>
> Thanks!!!
>
> "Don Guillett" wrote:
>
> > I just recorded the macro6 below but it could be simply
> >
> > Columns(1).Replace "0", ""
> >
> > Sub Macro6()
> > '
> > ' Macro6 Macro
> > ' Macro recorded 12/15/2004 by Don Guillett
> > '
> >
> > '
> >     Columns("A:A").Select
> >     Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
> >         SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> >         ReplaceFormat:=False
> > End Sub
> >
> > -- 
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
> > "One-Leg" <OneLeg@discussions.microsoft.com> wrote in message
> > news:09BDF4EF-22F3-4223-B2FD-B45D8EB37D7F@microsoft.com...
> > > Hello,
> > >
> > > I'm using the following in a macro but it doesn't work:
> > >
> > >     Range("A2:A65536").Select
> > >     If Cells.Select = "0" Then Selection.ClearContents
> > >
> > > What should I change???
> >
> >
> >


0
Don
12/15/2004 7:53:54 PM
Hi
just check that you want to match the whole cell

-- 
Regards
Frank Kabel
Frankfurt, Germany
"One-Leg" <OneLeg@discussions.microsoft.com> schrieb im Newsbeitrag 
news:0B00B4D6-505E-4B7E-84AA-3C7CC0D1FFD3@microsoft.com...
> Hello,
>
> Using Edit/Replace does the same thing...
>
> "Frank Kabel" wrote:
>
>> Hi
>> then use 'Edit - Replace' for this
>>
>> -- 
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>> "One-Leg" <OneLeg@discussions.microsoft.com> schrieb im Newsbeitrag
>> news:BBCBE224-4BA0-4A92-B2F0-41B2840C41DD@microsoft.com...
>> > Hello,
>> >
>> > I have many cells with numbers in them and many come up with only a "0" 
>> > in
>> > them.  I want to use a macro that will see the value of the cell and if 
>> > it
>> > is
>> > "0", then I would like to clear the cell.  If it's anything else than 
>> > "0",
>> > nothing should be done!!!
>> >
>> > Thanks
>> >
>> > "Frank Kabel" wrote:
>> >
>> >> Hi
>> >> what are you trying to achieve ?
>> >>
>> >> -- 
>> >> Regards
>> >> Frank Kabel
>> >> Frankfurt, Germany
>> >> "One-Leg" <OneLeg@discussions.microsoft.com> schrieb im Newsbeitrag
>> >> news:09BDF4EF-22F3-4223-B2FD-B45D8EB37D7F@microsoft.com...
>> >> > Hello,
>> >> >
>> >> > I'm using the following in a macro but it doesn't work:
>> >> >
>> >> >    Range("A2:A65536").Select
>> >> >    If Cells.Select = "0" Then Selection.ClearContents
>> >> >
>> >> > What should I change???
>> >>
>> >>
>> >>
>>
>>
>> 


0
frank.kabel (11126)
12/15/2004 8:15:58 PM
Reply:

Similar Artilces:

Trace form privilege error
One of our user classes receives a privilege error launching smartlist - altho it does load after they click OK. Is there anyway to trace this to see which form is causing this problem? Thanks Theo :) Hi Theo, Could you post the error message? And probably what window he/she is trying to open? -Kevin "Theo" wrote: > One of our user classes receives a privilege error launching smartlist - > altho it does load after they click OK. > Is there anyway to trace this to see which form is causing this problem? > Thanks > Theo :) Kevin Sorry - i know how to do a dex tr...

VBA code to say Yes or No if any VBA code is present in ActiveWork
Using XL 2003 & 97 Would like hit Ctrl+Shift+V and have an answer either yes or no as to the presence of Any VBA code in the Active Workbook. I do know that about the VBA editor/Explorer or to press the Run Macro button and get a list from various sources . I would like to stay at the keyboard and process a series of macros including one to let me know if I even need to evoke the VBA explorer. TIA Dennis There is no bootstrap macro to tell you whether you have macros or not. If there were there would be no limit to virus writers. --- HTH, David McRitchie, Microsoft MVP - Excel ...

Print, Merged Cells, and VBA
Hello I'm looking for a code that will help me with a print job for a worksheet. There are a lot of merged cells and a lot of "IF"s to be used in the VBA for range selection. Thanks in advance, Sarr ...

Survey Form
I've been asked to do a survey form for work. Users would be using a touch screen laptop to input data with at least one field, postcode/ zipcode requiring data entry from an onscreen keypad. Is this something that could be created using excel and a VBA form? Any advice and suggestions greatly appreciated. DonH Don, You can shape command buttons into letters & numbers and have them feed into a textbox/cell. Regards, Eddie http://HelpExcel.com On 8 Feb, 15:51, e...@HelpExcel.com wrote: > Don, > > You can shape command buttons into letters & numbers and have them...

delete fields in subform combobox after main form combobox is chan
I have a main form with a continuous sub form. There is a combobox on the main form called "FuelTypeChoice" The sub form is a continuous form "fPerfEmissionGuaDetails" There is a combobox on the subform called"FuelChoice" How can I have all of the records in the subform combobox be deleted if the main form combobox selection is changed? so... as soon as the user changes the FuelTypeChoice all of the FuelChoice are deleted so the user can make new Fuel Choices. The ID field between the two tables is PerfEmissionGuaID Background... 3 tables and a main form w...

Use VBA to update Access table or Query from Excel
Can I use VBA to update Access table or Query from Excel? Thanks in advance Hi Leungkong, > Can I use VBA to update Access table or Query from Excel? Of course, using ADO or DAO. See: http://www.erlandsendata.no/english/index.php?d=envbadacexportado Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Hi Jan, Thanks. I think ADO is what I want. But I am not only want to export from excel to access. I want to edit some data in access table. For example, Access has a table "ProductList" I want to use Excel to call the product by "Pro...

Outlook Forms in Outlook 2003
I have created a message form for new Hires. I have it set to route. When the form is filled out and routed, the next person on the list can open the form and fill out the form and as soon as they press the route button they get an error saying This form could not be displayed. I have cleared the forms cache added a registry change. Cleared all the areas for forms. I have even deleted the form and recreated from scratch...same error every time. Any help would be appreciated. Thanks You didn't say where you published the form.=20 FYI, there is a newsgroup specifically for O...

VBA and Scheduled task in Outlook
Hi, how can I: a) pull the query from somewhere and send it by e-mail automatically. E.g. I want to take a temperature table from http://www.wunderground.com/history/airport/EHAM/2009/7/10/DailyHistory.html?req_city=NA&req_state=NA&req_statename=NA where 2009/7/10 is the date of yesterday put it into an e-mail and send it automatically each day at 8 o'clock? b) if this is not possible, how can I send an e-mail with the attachment c:\Documents\query20090710.xls each day at 8 o'clock? my boss wants this automatized, I know how to write the VBA code for Excel, but don'...

How to display unique choice on continuous forms
Hello, We have a contact database and I'm having a hard time displaying a person's preferred address. Here's our setup: tblPeople *PersonID PersonName tblCompanies *CompanyID CompanyName CompanyAddress tblAffiliations *AffiliationID CompanyID PersonID AffiliationAddress UseAffiliationAddress This structure allows a person to work at a company yet maintain a separate work address. When UseAffiliationAddress is checked, then their affiliation address, not the company's address, is their preferred mailing address. I have a form for People (frmPeople), with a subform sho...

Display a PDF in a Windows Form.
I've got a kiosk program that displays information that runs unattended. I need to have the ability to put PDF up on the screen. I have other stuff on the screen that needs to still be displayed. Adobe opens as a new window with menus and whatnot so this is not acceptable. Does anybody know of a way to display a PDF in a winform? I don't need any of the menus or stuff like that - just display the pdf. It would also be nice if I could programmatically control what page it's displaying so I could display multiple pages in succession. TIA - Jeff. "Mr. ...

vba select field
Hi, having a button on a form, is it possible to select only some fields (all from the same table) of the current record instead of all fields? I've tried this code: RunCommand acCmdSelectRecord RunCommand acCmdCopy Forse dipende dei dati nel record. but it selects ALL fields in the form. Thank you. Remigio hi, On 10.04.2010 18:01, remigio wrote: > having a button on a form, is it possible to select only some fields > (all from the same table) of the current record instead of all fields? > I've tried this code: > > RunCommand acCmdSelectRecord &...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

Form field in a report
I have a report and i want to put a date in it that has previously been entered in a form. It works for the first page but i just get #Name? on all other pages. my field on the rport is =[Forms]![PrintInvoiceStats1]![Invoice Start Date] Any help would be appretiated Smiffy wrote: >I have a report and i want to put a date in it that has previously been >entered in a form. It works for the first page but i just get #Name? on all >other pages. > >my field on the rport is =[Forms]![PrintInvoiceStats1]![Invoice Start Date] > Sounds like the form is being closed. It must...

Automated form filling from a list
Hi all, I have a report that needs to be filled and printed. 500 of those reports. Data for them is in a sheet in form of a table. What I need to do is to take a row from one sheet, put the data in another sheet in correct places, print it, take next row, print it etc. Any idea how can I do it using Excel only ? Plan B is to make an application that using automation will do it using those 2 sheets. But I would prefer to do it simple as possible. I would appreciate any concepts. Best Regards Pawel Why not just use Mail Merge in Word? Pees wrote: > Hi al...

Excel VBA/Macro programming for beginners
Hi all, I know a bit VB 6.0 programming. Will it help me to write exce vba/macro(same thing?).can anybody help me by providing some fre websites where i can have informations/codes for VBA/macro. Please tell me how could i make a *command Button * in a cell in th excel sheet -- sdebu_200 ----------------------------------------------------------------------- sdebu_2000's Profile: http://www.officehelp.in/member.php?userid=430 View this thread: http://www.officehelp.in/showthread.php?t=118812 Posted from - http://www.officehelp.i Try David McRitchie's "getting started with mac...

Turn pc speaker on/off with vba code
Is this possible? Thanks I found something here on how to do it withVBScript. You can probably modify it to work in VBA http://www.pcreview.co.uk/forums/thread-1468591.php -- HTH, Barb Reinhardt "David" wrote: > Is this possible? > Thanks Thanks Barb I'll have a go (I need to improve my web searching skills) "Barb Reinhardt" wrote: > I found something here on how to do it withVBScript. You can probably modify > it to work in VBA > > http://www.pcreview.co.uk/forums/thread-1468591.php > -- > HTH, > ...

Access 2003 Form Question
I have an access database that I am using for site surveys. In a nutshell, there is a header table with a date and location as the keys and a detail section with the survey results. The detail section shows a series of 30 or so questions(from a questions table) on how the site was rated. I am trying to design a form that populates a yes/no/not available type response in the detail section. The user would either click on a yes/no/NA type box or have a drop down and select the value(whichever is easiest) next to the box. After the user answers the 30 or so questions, an answers table...

Send form windows to the back of -global- Z-order
I know there is a function to send child form windows to the back of an app's 'local' Z-order. And there's an easy way to send them to the -front- of the global Z-order. But is there any way to send them to the back of the global Z-order? In other words, place the form windows behind all other currently active windows? Bob wrote: > I know there is a function to send child form windows to the back of > an app's 'local' Z-order. And there's an easy way to send them to the > -front- of the global Z-order. > > But is there any way to se...

Loading a Form from VBA
Hi all, I'd like a module to open and load a form in my Access 2003 database... How can I do this? Use the OpenForm method. Look in the Help file for details. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Mike" <michael.leon.schwartz@gmail.com> wrote in message news:1184770076.109526.207900@o11g2000prd.googlegroups.com... > Hi all, I'd like a module to open and load a form in my Access 2003 > database... How can I do this? > On Jul 18, 10:58 am, "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com&g...

Delete rows in table from Module or Form
Hi fols I've been trying to delete som rows from a table: in a form - wont work in a module - wont work either in a query that's called from a module - and ofcoursre , wont work --- tried this in the module Set db = CurrentDb() SQLstr = "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And [magasineId] = '" & myMagsineId & "' & "" ;" CurrentDb.Execute SQLstr and this: DoCmd.RunSQL "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And [magasineId] = '" & myMagsineId & &quo...

Excel and VBA
I am very interested in learning more about using VBA in office, especially Excel. Can you post some resources (online, books, etc) that can be used to help learn about using Visual Basic in office. Thanks! Here's a good place to start: http://www.contextures.com/xlbooks.html Does that help? *********** Regards, Ron XL2002, WinXP "Bob Sinclair" wrote: > I am very interested in learning more about using VBA in office, especially > Excel. Can you post some resources (online, books, etc) that can be used to > help learn about using Visual Basic in office. >...

creating appointments from form assistant
Appointments created using the follow up button and the form asisstant need to integrate to outlook. Currently it does not integrate (CRM3.0) since the user that created it is not added to the appointment. Having to reopen and edit the appointment defeats the purpose of the follow up button and the form assistant. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the sugges...

Edit contact form
I edited the fields in the contact form fields, however when I look up the contact in contacts page it does not display the changes that i made to the form, Can anyone help? Try this: http://www.greatplains.com/techknowledge/techknowledge.asp ?id=30501&target=CS >-----Original Message----- >I edited the fields in the contact form fields, however >when I look up the contact in contacts page it does not >display the changes that i made to the form, Can anyone >help? >. > Did you publish and reset iis? Bill >-----Original Message----- > >Try this: &g...

dlookup formula in Form
I need a dlookup formula for a field in a form that will provide an org number for a given Org_Name. I need to know where in the form to put the dlookup, e.g., macro, code, what event, and syntax. They are all text fields. Tbl_Control has Control_OrgName and Control_OrgNumber. The Master table has Emp_OrgName and Emp_OrgNumber. I think it translates to something like ... dlookup Emp_OrgName in TBL_Control where Emp_OrgName = Control_OrgName A simple combo box can provide that data in a form. What are you trying to do? -- Arvin Meyer, MCP, MVP http://www.datast...

VBA Customization
Is there a way to get the name of the current company through VBA in Great Plains 8.0? Thanks for all your help. open GreatPlains. Add the current window, it will add the Toolbar then add fields choose the company name on the top left after the user id. >-----Original Message----- >Is there a way to get the name of the current company through VBA in Great >Plains 8.0? > >Thanks for all your help. > > > >. > Thanks. I tried, but I couldn't add company name to the field list. It opens up the login screen instead. Any thoughts? Thanks again. "ger...