Macro Question #5

Hi guys, 

I have got the following code which looks for a specific value in column A 
(1), then i want it to change the value in column G (7) to another value. For 
some reason this doesn't seem to be happening with the following code. Any 
thoughts?

For i = 1 To Rows.Count
    If Cells(i, 1).Value = "Value1" Then
        Cells(i, 7).Value = "Value 2"
    End If

Cheers, 
Rod

0
Rodney1 (32)
1/14/2008 10:42:07 AM
excel 39879 articles. 2 followers. Follow

2 Replies
495 Views

Similar Articles

[PageSpeed] 34

Sub sdf()
For i = 1 To Rows.Count
    If Cells(i, 1).Value = "Value1" Then
        Cells(i, 7).Value = "Value 2"
    End If
Next
End Sub


works for me

But this works for an exact text match.  the values in column A must be:

Value1

with no leading or trailing spaces.
-- 
Gary''s Student - gsnu200765


"Rodney" wrote:

> Hi guys, 
> 
> I have got the following code which looks for a specific value in column A 
> (1), then i want it to change the value in column G (7) to another value. For 
> some reason this doesn't seem to be happening with the following code. Any 
> thoughts?
> 
> For i = 1 To Rows.Count
>     If Cells(i, 1).Value = "Value1" Then
>         Cells(i, 7).Value = "Value 2"
>     End If
> 
> Cheers, 
> Rod
> 
0
GarysStudent (1572)
1/14/2008 10:55:00 AM
You were missing a "Next" to go with your "For".  Also, the following fixes 
when you select a group of rows and the first row is not row 1.

 For i = 1 To Selection.Rows.Count
    MsgBox Selection.Rows(i).Row
    If Cells(i, 1).Value = "Value1" Then
        Cells(i, 7).Value = "Value 2"
    End If
 Next

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Rodney" <Rodney@discussions.microsoft.com> wrote in message 
news:99118CF1-4059-4E27-A79B-2562127CBEC0@microsoft.com...
> Hi guys,
>
> I have got the following code which looks for a specific value in column A
> (1), then i want it to change the value in column G (7) to another value. 
> For
> some reason this doesn't seem to be happening with the following code. Any
> thoughts?
>
> For i = 1 To Rows.Count
>    If Cells(i, 1).Value = "Value1" Then
>        Cells(i, 7).Value = "Value 2"
>    End If
>
> Cheers,
> Rod
> 


0
nospam2791 (369)
1/14/2008 8:37:46 PM
Reply:

Similar Artilces:

Excel Automation
I would like to create an Excel workbook with command buttons (AKA control macros). I have code to create a workbook. Now I need to know how to create Excel command buttons, then set properties and assign macros to the command buttons in VBA from Access. The code on the button would generally be simple like this: Private Sub CommandButton1_Click() Sheets("Tab One").Select End Sub This is not an Excel newsgroup. Please post your question in the appropriate forum. "tcb" wrote: > I would like to create an Excel workbook with command buttons (AKA > control m...

Upgrade Question #3
I currently use Microsoft Money 2005 Premium. Can I upgrade to 2007 deluxe or do I still have to use premium? You can upgrade to Deluxe. <billybish@gmail.com> wrote in message news:1166276989.686433.196350@73g2000cwn.googlegroups.com... >I currently use Microsoft Money 2005 Premium. Can I upgrade to 2007 > deluxe or do I still have to use premium? > ...

Permissions Question #2
How can I make sure that an NT user has permissions to open other users folders? sd ...

attaching a macro to a control button in the Quick Access Toolbar
Excel 12, WXP I have a macro that I used a lot in Excel 11 and I would like to use it in Excel 12. I have put a button (form control) on the Quick Access Toolbar and I want to attach the macro to it. Help says to right click on the control and choose "assign macro" but when I right click on the control I don't get that choice. Can't find any help for this question in VB editor either. It describes how to put a command button on the worksheet itself, but no way to assign a macro that is in the Quick Access Toolbar. Any suggestions? See http://www.rondebruin.nl/ima...

Word excel questions
Hi, I have 3 windows Xp home edition sp2 pc's. Unfortunetly, none of them came with Microsoft Word. The pc's did come with Word perfect 10 by Corel. My teen has to be away from school for several days. In her computer class, they use word and will be doing some Excel things. She will do some make ups at home. Is it possible to get Word w/excell for free? Or maybe a trial version for free until she is able to return to school? I told the teacher all this and mentioned that there may be a trial version online. Any tips would be great. thanks in advance -- Thanks so very much fo...

Migration question
I'd like to move my contact list, calandar information, inbox and outlook file folders to a new computer. I assume these are each stored in separate files and they can simply be copied to the new computer? If so, where can I find out the names of the individual files? Nope. All are in the same file: a Personal Folders (PST) file. Just copy that file and configure the new installation to use it as its default. Take a look at these pages for info on Outlook data transfer: http://www.slipstick.com/config/backup.htm -- Russ Valentine [MVP-Outlook] "MH" <mehla@earthlink.net>...

Exchange SP2 Install Question
We are going to install SP2 this weekend. Does anyone know if the front-end and back-end servers need to be restarted after the install? I assume so, but I can't find anything telling me this. Thanks! In most cases the installer will restart services without requiring you to restart the server. Have seen instances where a server reboot was required. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "KThomas00" <thomask@centratechnology.com> wrote in message news:OcX0dR5QGHA.1096@TK2MSFTNGP11.phx.gbl....

Year/Month/Date Question
Is there a formula that can calculate years, months and days into a decimal? For example: 23 years, 6 months, 0 days would be 23.5 years. Depends on how your years, months and days are stored. Assuming they're in cells A1 (years), B1 (months) and C1 (days), then this formula would get you close: =A1+((B1*30)+C1)/365.25 That will actually return 23.49281, which if you round to 1 decimal place is 23.5 If you want to gty to get closer use B1*30.4375 instead of B1*30, since 365.25/12 = 30.4375 which you can "assume" is the average number of days in any single mont...

problem with macro
i have a worksheet that shows worktimes for employees, the cells ar grey, when you enter the employees time or "shift" and press a butto that executes my macro, it changes the colour of the "shift" to whit to show the time scale that the employee is working. the macro run fine, the problem is that to run the macro i have to click a button. i there a way of running the macro, or executing the code when i ente the employees "shift" of time. or is this not possible? this is the code i am using at the minute for it to run when i click o a button: Private Function Sh...

Date formatting problem #5
Hi there, I've tried everything I can think of with this one - help!! I'm reading dates from 2 cells, storing them in variables, comparing them and then storing the resulting date in a different cell. Code currently is: If stStoredDate<> stDate then stStoredDate=stDate Activecell.Offset(0,1).Value = stStoredDate End If My problem is stStoredDate = 04/09/05 (4 Sep 05 as I'm in the UK), stDate is the same, but the value in the cell is 09/04/05 (9 Apr 05). Both cells have identical formatting. If I change the formatting to 'general', stStoredDate= 38599, but t...

Fixed asset question
The controller of my company would like to know if there is a mass change type operation available for handling impairments in Fixed Assets in GP. He says he has read the manual and there doesn't appear to be a way. Is there a way to do impairments without modifying each individual asset? Thanks, M. ...

Microsoft
Support plan states that "Managed User" will receive an answer from Microsoft within 48 hours if a community user does not answer, but that does not appear to be happening. Please comment. When did you post your query to this newsgroup / and what is the title? Regards James "Lisa Rivers" <LisaRivers@discussions.microsoft.com> wrote in message news:E13EE281-E188-4721-8CC9-D51AD25F27C0@microsoft.com... > Support plan states that "Managed User" will receive an answer from > Microsoft > within 48 hours if a community user does not answer, bu...

2 questions one on list/combo boxes and the other on "atomically" hiding columns or rows.
I followed most of the post recently on the subject of list and combo boxes. From what I can tell I need to create a list but I need to have the list find the item and than a price associated with that item and than it's cost. Is there some where that I can find away to do that? The other question on the opening page of my quote sheet I have by columns the floors (4) of the house and under those a list of rooms (these are the headings of the rows) that are on each floor. In the following columns I have the my wiring combination. I have all this linked to other worksheets that have simil...

Question about removing a KB##### item
I suspect a recent update 6/11 KB982381 to IE 8 causing an issue. I'll skip the long reason. I have installed other items after this update. So when I go to remove the KB item via Control Panel, I get a message that a list of programs might not work after I do, about 10-12 items. Is this Microsoft's disclaimer or a real concern. I thought it was an easy thing to remove an update? I mean, it is easy, but I thought it was kinda idiot proof. TIA Big_Al wrote: > I suspect a recent update 6/11 KB982381 to IE 8 causing an issue. > I'll skip the long re...

ALT+F8 macro list without showing "Personal.xlsm!" for every macro?
Is there a setting somewhere that will allow me to call up my list of macros with ALT+F8 and _not_ have "Personal.xlsm!" show in fornt of every one of them? It would make things just a bit easier if I could call up the list and begin typing the macro name and have the list find it for me. Ed ...

Exchange 5.5 (VPN/External Access)
Hi All, We currently use exchange 5.5 (win2k server) for our postoffice and im currently looking into giving a small amount of users access to their email from home. How secure or what are the best practices in this situation....? OWA pop3 tunneling VPN Custom Recipient (just forward email to their home account) Lets just leave this one out for the moment as i know all about this.... Each of these scare the hell out of me as the client machines will largly be out of my control. I think the VPN option scares me the most as this will give most access, whereas with the other two i'm...

A simple question!!
HI all, Given a time period A formatted as hh:mm, how do you divide a 24 hr period (formatted as hh:mm) by A? Would appreciate answers. I am getting dubious answers! Don- Multiply with 24 first, one day is 1 in Excel and one hour is 1/24 so if you have an hourly rate in B2 and the time worked in A2 use =B2*A2*24 and format as currency -- Regards, Peo Sjoblom "Don" <Don@discussions.microsoft.com> wrote in message news:070D5314-028B-46B0-B037-ACA269E662DB@microsoft.com... > HI all, > > Given a time period A formatted as hh:mm, how do you divide a 24 hr per...

SUMIF Question
Can you do a range such as M4:O10? My spreadsheet let me put this in but it is not giving me the correct value. I have a 1 in cell O4 and it is giving me a value of "0" for this formula. =SUMIF('June08 Summary'!$E$4:$E$10,"DM",'June08 Summary'!M4:O10) -- Deb No, sumif and countif will only sum/count M4:M10 in your case. If you want to sum all entries in M4:O10 where E4:E10 regardless if there are multiple entries on the same row you can use =SUMPRODUCT(('June08 Summary'!$E$4:$E$10="DM")*('June08 Summary'!M4:O10)) -- Reg...

multi port USB hubs question
I recently received a multi port USB hub and plugged it into my laptop. It seemed like a good idea as I only had 2 USB ports on my computer. This gave me 4 where before I only had 1 which made a total of 5. I plugged a new external DVD burning drive into one of the multi ports ports. The drive used 2 ports, 1 for drive and 1 for drives power. I also plugged 2 external hard drives into the other 2 ports. The burning drive would not work and 1 of the external hard drives was not detected in computer. I worked on this problem all day surfing web for answers and finally got online wi...

eMail Attachments over 5 MB
Hello, is there in MS CRM 3.0 a limit for eMail attachments? I had problems with attachments over 5 MB. Can I change it? greetings, Stefan Stefan, 5MB is the limitation. Regards, Kamala Hello, thanks for the early response. can I change this somehow??? greetings, Stefan "kamalabr@gmail.com" wrote: > Stefan, > > 5MB is the limitation. > > Regards, > Kamala > > There's a 5 MB attachment limitation which can be changed from the registry. On the Server HKEY_LOCAL_MACHINE\Software\Microsoft\MSCRM\maxuploadfilesize On the Client HKEY_CURRENT...

triggering a macro
I need to trigger a macro when there is a change in a cell.. Hi use the worksheet_change event. See: http://www.cpearson.com/excel/events.htm -- Regards Frank Kabel Frankfurt, Germany "Sam" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:F26F8DDC-59EC-47DB-BBD0-0C079298691E@microsoft.com... > I need to trigger a macro when there is a change in a cell.. ...

Migrate out of Groupwise 5
I need to migrate only a handful of email accounts out of groupwise in to an exchange 2003 server? any ideas on how to pull this off? Yup, I'll mail you one we used. If you don't receive it, let me know. -- Cheers DV adv@arivia.co.za (Do not use the @eskom.co.za) "Philip" <pwalley@consultrix.net> wrote in message news:8eb401c432dc$a5c23a60$a101280a@phx.gbl... > I need to migrate only a handful of email accounts out of > groupwise in to an exchange 2003 server? any ideas on how > to pull this off? ...

licensing question 11-16-04
Hi. Can someone briefly explain the licensing for MSCRM? Is it just like a CAL, one for every potential user? Is it per-connection or named user? Thanks, S "Scaryberry" <nope@noway.com> wrote in message news:OI6juFDzEHA.3548@TK2MSFTNGP09.phx.gbl... > Hi. Can someone briefly explain the licensing for MSCRM? Is it just like > a CAL, one for every potential user? Is it per-connection or named user? > > Thanks, > > S Hi, amazing what the search button on the web groups can do. Would like to know the difference between a Sales Professional and Sa...

VBA Code for displaying time taken by a macro
hi, can you pls give me the VBA Code for diplaying the time taken by a macro to give the output. Regards, Karthik One way: Dim time1 As Double, time2 As Double time1 = Timer ' <your macro here> time2 = Timer MsgBox Format(time2 - time1, "0.00 \s\ec") In article <1123328550.673153.200890@g49g2000cwa.googlegroups.com>, "Macro man" <karthikr22@gmail.com> wrote: > hi, > > can you pls give me the VBA Code for diplaying the time taken by a > macro to give the output. > > Regards, > > Karthik Depends up...

Auto Accept Agent #5
I've been testing this. There is one thing I don't like. If you invite a conference room as a resource and the resource does not accept, the invitations still go out to the recipients. Is there a way to configure the agent to stop this so if the resource is not available the invitations don't go out. No. If you send out a regular meeting request and one person rejects it, the entire meeting doesn't get cancelled automatically, does it? Why should auto accept behave any differently? Remember that all it is doing is automatically accepting/rejecting a meeting reques...