VBA help #3

Hi,

I'm an excel VBA total novice.  I dabble in access vba, but am unsure where 
to start in excel.

What I need is some code I can tie to a macro (& keyboard shortcut) which 
will find all values under 1000 in the selected range and change them to 
1001.

Can anyone point me in the right direction?

Any help greatly appreciated.....thanks, Jason 


0
dummy8613 (43)
11/21/2007 2:06:06 PM
excel 39879 articles. 2 followers. Follow

4 Replies
596 Views

Similar Articles

[PageSpeed] 3

Look in the vba help index for FINDNEXT. There is a good example.
c.value=1001

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Jay" <dummy@dummy.dummy> wrote in message 
news:eYGxqeELIHA.3356@TK2MSFTNGP02.phx.gbl...
> Hi,
>
> I'm an excel VBA total novice.  I dabble in access vba, but am unsure 
> where to start in excel.
>
> What I need is some code I can tie to a macro (& keyboard shortcut) which 
> will find all values under 1000 in the selected range and change them to 
> 1001.
>
> Can anyone point me in the right direction?
>
> Any help greatly appreciated.....thanks, Jason
> 

0
dguillett1 (2487)
11/21/2007 2:15:31 PM
Hi Jay........
I got some similar help from "Ed from Az" and "Hemant_india" in another 
group.  Perhaps this is what you're looking for.....

Sub ChgValuesInSelectedRange()
 Dim Range1 As Range
 Dim EaCell As Range
 Set Range1 = Selection
 For Each EaCell In Range1
   If IsNumeric(EaCell.Value) Then
     If EaCell.Value < Range("a14").Value And EaCell.Value <> "" Then
       EaCell.Value = Range("a15").Value
     End If
   End If
Next EaCell
End Sub

Vaya con Dios,
Chuck, CABGx3


"Jay" wrote:

> Hi,
> 
> I'm an excel VBA total novice.  I dabble in access vba, but am unsure where 
> to start in excel.
> 
> What I need is some code I can tie to a macro (& keyboard shortcut) which 
> will find all values under 1000 in the selected range and change them to 
> 1001.
> 
> Can anyone point me in the right direction?
> 
> Any help greatly appreciated.....thanks, Jason 
> 
> 
> 
0
CLR (807)
11/21/2007 7:41:00 PM
Thanks Chuck...that's great.  I've managed to do it thanks to your code.

I've only written VBA for Access (because you *have* to if you want any 
proper functionality.

I guess I just have to start thinking in terms of ranges etc., rather 
than the access object model.

Thanks again........Regards, Jason, UK



CLR wrote:
> Hi Jay........
> I got some similar help from "Ed from Az" and "Hemant_india" in another 
> group.  Perhaps this is what you're looking for.....
> 
> Sub ChgValuesInSelectedRange()
>  Dim Range1 As Range
>  Dim EaCell As Range
>  Set Range1 = Selection
>  For Each EaCell In Range1
>    If IsNumeric(EaCell.Value) Then
>      If EaCell.Value < Range("a14").Value And EaCell.Value <> "" Then
>        EaCell.Value = Range("a15").Value
>      End If
>    End If
> Next EaCell
> End Sub
> 
> Vaya con Dios,
> Chuck, CABGx3
> 
> 
> "Jay" wrote:
> 
>> Hi,
>>
>> I'm an excel VBA total novice.  I dabble in access vba, but am unsure where 
>> to start in excel.
>>
>> What I need is some code I can tie to a macro (& keyboard shortcut) which 
>> will find all values under 1000 in the selected range and change them to 
>> 1001.
>>
>> Can anyone point me in the right direction?
>>
>> Any help greatly appreciated.....thanks, Jason 
>>
>>
>>
0
spam6370 (39)
11/22/2007 7:40:46 PM
 You're welcome Jay, glad you got it working.  Thanks for the feedback.

Vaya con Dios,
Chuck, CABGx3



"Jay" wrote:

> Thanks Chuck...that's great.  I've managed to do it thanks to your code.
> 
> I've only written VBA for Access (because you *have* to if you want any 
> proper functionality.
> 
> I guess I just have to start thinking in terms of ranges etc., rather 
> than the access object model.
> 
> Thanks again........Regards, Jason, UK
> 
> 
> 
> CLR wrote:
> > Hi Jay........
> > I got some similar help from "Ed from Az" and "Hemant_india" in another 
> > group.  Perhaps this is what you're looking for.....
> > 
> > Sub ChgValuesInSelectedRange()
> >  Dim Range1 As Range
> >  Dim EaCell As Range
> >  Set Range1 = Selection
> >  For Each EaCell In Range1
> >    If IsNumeric(EaCell.Value) Then
> >      If EaCell.Value < Range("a14").Value And EaCell.Value <> "" Then
> >        EaCell.Value = Range("a15").Value
> >      End If
> >    End If
> > Next EaCell
> > End Sub
> > 
> > Vaya con Dios,
> > Chuck, CABGx3
> > 
> > 
> > "Jay" wrote:
> > 
> >> Hi,
> >>
> >> I'm an excel VBA total novice.  I dabble in access vba, but am unsure where 
> >> to start in excel.
> >>
> >> What I need is some code I can tie to a macro (& keyboard shortcut) which 
> >> will find all values under 1000 in the selected range and change them to 
> >> 1001.
> >>
> >> Can anyone point me in the right direction?
> >>
> >> Any help greatly appreciated.....thanks, Jason 
> >>
> >>
> >>
> 
0
CLR (807)
11/23/2007 7:33:01 PM
Reply:

Similar Artilces:

CRM 3.0 Implementation
I am interested in the experiences of others with implementing Microsoft CRM 3.0. I am a one man development team who has been tasked with implementing CRM 3.0 with 30 users initially. Our organization has been running on Lotus Notes for quite a while. We moved to echange for e-mail over a year ago but still use Lotus for custom databases. The first step will be pulling the data from Lotus Notes to CRM. I have looked into the Microsoft CRM 3.0 Certification. There is a company that offers a 10 day CRM 3.0 boot camp. Is this a good idea, and at what point should I take it? We would lik...

Recipient Policy help #2
Our default recipient policy is companyname.local. We have 10 other recipient policy’s for each site within our company – they all have separate smtp addresses. The postmaster uses the default recipient policy smtp address companyname.local to send out ndr’s etc. but the address companyname.local is not allowed out of our firewall and it wont be allowed. We can create a new address to add as the default recipient policy which is unique and is allowed through the firewall. Please let me know if this is ok and what I should look out for??? So I am a little confused about what you are as...

Excel 97 VBA Help File
In the MS Excel Visual Basic Reference help file contents page, I click on Functions and it only offers me functions beginning with the letter S. So, I have a list of Solver and SQL functions. But what about all the other functions in VBA, for example for doing arithmetic and manipulating dates and strings? Why don't they show up? Are they left out because those functions are all part of Visual Basic generally, and the Excel VBA help file is specific to the _extra_ functions in Excel VBA? It's the only explanation I can think of. Am I right, or have I got a corrupted help file (vbaxl...

help need with VC 6.0 IDE and mfc
Hello, First let me explain the scenario where i m using this requirement. We are Using CustomAppWizard and designing a wizard .One of the wizard pages will Insert Composite controls as many as the user wants . 1.So i should be able to dynamically insert ATL controls without using Insert Control Dailog. 2. can any one tell me how to dynamically create Template file in TEMPLATE folder of resource view . 3. I want to include many files created by templet files and add them to build by editing newproj.inf Is it possible to do this. 4.I would even like to know if i have 2 ifles in my C drive h...

Help, I cannot Save!
I created a document and locked the worksheet to protect the formulars before creating a template for the document. But now when I open th document and insert a new sheet using the template I created, th document will refuse to save. Once I click on save, office assistant will say "doc not saved". Wha could I have done wrong? PLease help. computerfinema -- computerfinema ----------------------------------------------------------------------- computerfineman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3716 View this thread: http://www.excelforum.c...

VBA training suggestions?
I'm looking for tips on software out there on learning VBA for Excel. I know how to create macros and have a good idea of what a script might look like for the macro I created. I just want to learn how to write them on my own for my personal use. Any suggestions? Thank you kindly for your input! I do not know of any software for learning VBA, but you can't go wrong looking at all the Excel VBA web sites as well as purchasing a copy of John Walkenbach's "Excel 2003 Power Programming with VBA" http://j-walk.com/ss/. Recording macros and then editing them is a pow...

VBA to check for latest version of front end?
I have a split database where the users have a local copy of the frontend. I would like to use VBA to check whether the users have the mostrecent version. I have set up the following tables:tVersion-Back-End (link to table in the BackEnd version)tVersion-FrontEnd (local table in the FrontEnd version)Each has a field Version where I am planning to put in the versionnumbers.Now, I am just starting to use VBA in my Access projects. Can someonepoint me to some coding to accomplish my task above? Can someone alsopoint me to a good resource (book, website, course) that would take mefrom a beginn...

Help Required
Hi, Whenever I open Outlook 2003, I am getting a dialog box which displays the following message: Microsoft Office Outlook has encountered a problem and needs to close. We are sorry for the inconvenience When I click Debug it displays a message box with the following error message "The instruction at "0x3007e993" referenced memory at "0x0000000:. The memory could not be read" When I click No it Visual Studio JIT debugger pops up. I uninstalled and installed several times but still the problem persists. Is there any regsitry entry that I've to modify/delete? ...

Need Help with Deleting Empty Paragraphs in Word 2003
I have written the code below to delete all empty paragraphs at the end of a document and then place the cursor at the end of the last paragraph. It works fine as a stand alone sub in a new doc, but fails inside the real document that contains other code that manipulates several documents. The failure is that it will delete the last empty para, but then gets stuck looping inside the While...Wend because subsequent .Delete are not happening. So, the question is why would this work in one document, but then fail in another? n = 0 ...

crm 3.0 error 03-01-06
Hello, I'me getting this error while installing crm3.0 for SBS: "error writing to file microsoft.mshtml.dll verify that you have access to that directory" That file is in the C:\Program Files\Microsoft.NET\Primary Interop Assemblies directory. I (and 'everyone') has full access to that dir. What can I do about this?? kind regards, Thomas ...

HELP Recovering addresses and email from Outlook 2003
I had some serious driver issues that required re-installing XP from disc. I did use the backup option and have a backup of all the old data. And of course had to reinstall Office 2003. Will third party software restore my old email and addresses or am I out of luck?? Thanks for the help texraid wrote: > I had some serious driver issues that required re-installing XP from > disc. I did use the backup option and have a backup of all the old > data. And of course had to reinstall Office 2003. > > Will third party software restore my old email and addresses or am I > out of lu...

Office 2003 Service Pack 3--subsequent problems opening Publisher
I run Publisher 2003 on Windows XP. On June 13, I updated my system with Office 2003 Service Pack 3 so that I could open Word documents with the file ext docx. Subsequent to the Service Pack 3 installation, whenever I open a Publisher file (which I created), I get the following message: "Publisher has detected a problem in the file you are trying to open. If you are certain that this file came from a trusted source and does not contain harmful information, click OK." What is causing this and is there a way to stop this pop-up message? All publications? Error message when you...

VBA to creating autotext entries or quickparts in different catego
I have a VBA application that basically allows people to easily create autotext entiries, move them between machines and use them making comments on assignments. Currently it operates in EXACTLY the same way in Word 2003 and 2007 (using userforms) and I want to keep that as long as possible. You can see the application at http://emarking-assistant.baker-evans.com and either the screen image or the video demos will give you an idea of what I am doing Currently I store all the comments in a long list of autotext entries that is displayed in a field with the value of the entr...

Non-VBA way of making custom menus.
Sorry for the new thread but even on Google, the thread isn't showing up. Well, it was SOOO easy, as I knew it would be. The webpage I quoted in my message this morning didn't mention the "New Menu" at all! So, here is the non-VBA way to create a custom menu: - TOOLS > CUSTOMIZE - under the categories available choose NEW MENU and then drag the NEW MENU option under the Commands window up to the menu bar (like D'UH!! <lol> Wish all those hours spent searching yielded webpages that gave this! <g>) - THEN one can go back to the MACROS category a few line...

Help me identify my missing permission (Cannot open public folder) -2147217843 (Maybe Authentication Fails?)
The following snippet of code throws an error number -2147217843. When I googled this error code, I see many references to authentication failed. I am assuming my problem is some kind of permission related problem on the "MyNewFolder" public folder. -- start code --- Dim objFolder As New CDO.Folder Dim f As ADODB.Field 'sURL is like: file://./backofficestorage/mydomain.com/Public Folders/MyNewFolder/ objFolder.DataSource.Open sURL, , adModeReadWrite, adFailIfNotExists --- end code -- I have code that runs before this that actually creates the "MyNewFolder" publ...

CRM 3.0 Login Problems
Some specific users are constantly getting prompted for CRM login in Outlook. We are using desktop client (online) online. This happens randomly. We have CRM 3.0 with rollup update 2 and IE7. We have also applied this fix http://support.microsoft.com/default.aspx/kb/934243/en-us. Also added the crm site to local intranet zone. Please help. Thanks. set the authentication in IE check rollup update 2 -- Regards, Imran MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "raj" wrote: >...

Showing 3:54PM instead of 3:54:03PM
How do you get rid of the seconds in the time area. I have changed the formatting in the time. I use the excel file as a data source. I include the time in the mail merge. It always shows up with the seconds in the time. Very frustrating. HELP PLEASE!! TJ it may be formatted as text, so won't respond to changing the time format. if it is text, the TIMEVALUE formula will convert it to a decimal-based time value which can then be formatted by using Excel's normal Number formatting-- to get rid of the seconds. Eddie O "TJ" wrote: > How do you get rid of the secon...

Hyperlink problem #3
I've got two workbooks on a shared drive with hyperlinks linking the two. When a user clicks on the hyperlink on the first workbook, it takes him to the second workbook. Fine. However, when the user clicks on the hyperlink in the second workbook to go back to the first, the error message says that that workbook is already open and it cannot open two files with the same name. Help is appreciated! I just tried a small test in xl2002 and it worked ok for me. I use Insert|Hyperlink to create the links. Are you sure that the hyperlinks point at the file you want--same folder and e...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

Outlook Client #3
Dear All, I have recently installed crm outlook client for one of my users and then also installed the 2 rollups for version 3.0 . Unfortunately outlook is still restarting even after the rollups. Kidnly advise the necessary solution that resolve the problem. Please clarify, outlook loads and then crash "restart"? Has Office applied with latest Office update? Frank Lee, Microsoft Dynamics CRM MVP http://microsoft-crm.spaces.live.com http://www.workopia.com/Links.htm "Faiz Amir" wrote: > Dear All, > I have recently installed crm outlook client for one of my ...

Rounding Numbers #3
I have a list of values as below: 476.14 361.99 345.69 463.08 515.29 403.44 330.68 347.64 375.36 I would like to create a formula that rounds the values to the nearest 0.05 eg. Round 476.14 to 476.15, 361.99 to 362.00, 375.36 to 375.35 etc… Is there anyway that I can do this? Thanks, Jane. JaneC wrote: > I have a list of values as below: > 476.14 > 361.99 > 345.69 > 463.08 > 515.29 > 403.44 > 330.68 > 347.64 > 375.36 > I would like to create a formula that rounds the values > to the nearest 0.05 eg. Round 476.14 to 476.15, > 361.99 to 362.00, 375.36...

compact database in VBA access 2007
Hello, I have recently upgrade to office 2007 from office 2003. To compact a database from within the database itself, I used the follwing code. Unfortunately it no longer works in access 2007. Is there some similar code that will work? Public Function FncCompactTheCurrentDB() CommandBars("Menu Bar"). _ Controls("Tools"). _ Controls("Database utilities"). _ Controls("Compact and repair database..."). _ accDoDefaultAction End Function Thank You, SL On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft....

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

SetWindowsHookEx #3
Hi there. Could someone explane to me what is the purpose of dwThreadId, the last member of SetWindowsHookEx function? I've expected that this is a thread id with which hooks is associated. That means that hook is getting only messages produced by this particular thread. But it looks like I'm getting system wide messages anyway. so could somone make it clear for me? In fact I need to process a mesages of only one window. I know the thread id of that window. But! this thread is not in my process! And I don't want a real system wide hook because it slows down the box. Ok, I think ...

Help with Registration
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, I've tried to register my copy of Office for Mac through the Mactopia page. I log in successfully, but then it just keeps on loading and doesn't refresh or change. Any advice? On 6/16/09 6:35 PM, in article 59b76b64.-1@webcrossing.caR9absDaxw, "theconfuzed1@officeformac.com" <theconfuzed1@officeformac.com> wrote: > I've tried to register my copy of Office for Mac through the Mactopia page. I > log in successfully, but then it just keeps on loading and doesn't refresh or > ...