+ Reply to Thread
Results 1 to 12 of 12

If / then type of commands in VB -- help for a newbie!

  1. #1
    Registered User
    Join Date
    02-13-2011
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    6

    If / then type of commands in VB -- help for a newbie!

    Hi all --

    I'm a graduate student in psychology and need your help! I have data in Excel that I am manipulating using a 'recorded' macro -- mostly cutting cells, taking averages, sorting columns, etc. But I need to add three pieces to that VB macro that I can't do in the recording mode... I think these should be simple tasks, but after several hours of internet searching and trial-and-error, I can't seem to figure out how to do them. Here's what I need to do...

    1) I need to find a way to tell Excel to look through a column, and if it finds any cells with a specific word in it, to delete the entire row that cell is in.

    2) I need a way to tell Excel to look through a column, and if it finds a cell with a specific value, to change a the value of the cell next to it in the row. (So if Excel sees that cell C3 has an 'E' in it, to tell it that I want to replace cell C4 with the contents of a cell D30).

    3) Last thing is I need to find a way to tell Excel to take the standard deviation of a column of numbers (lets say column D) -- but to only include in that Std. deviation cells that have a specific value in the column next to them (so to only include value D3 in the standard deviation calculation if the value of C3 = 'yes').


    Many many thanks in advance for any help you can provide...

    Emily
    Last edited by erosenzweig; 02-13-2011 at 07:15 PM. Reason: change title

  2. #2
    Registered User
    Join Date
    02-13-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: If / then type of commands in VB -- help for a newbie!

    Can you share a sample sheet.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,111

    Re: VB help for a newbie? If/then type of stuff...

    Hi erosenzweig and welcome to the forum,

    I hate to do all you homework but this should get you through number 1 above. I didn't know if Test = TEST or not. Also I didn't know if you wanted "This is a test" to be counted as having the word Test in it.

    The following code will look through column A and find the word "Test" and delete that row if it is found. Note - this is an exact cell match and caps the same too. See comments for other possibilities.

    Here is a start.
    Please Login or Register  to view this content.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,111

    Re: If / then type of commands in VB -- help for a newbie!

    For problem #2
    Instead of using the above

    Please Login or Register  to view this content.
    Look up the definition of VBA "Offset"
    So something like
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-13-2011
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: If / then type of commands in VB -- help for a newbie!

    Hi Marvin --

    Thank you so much for your help! For part #1, I tried using your code with the InStr function (since the word I'm looking for is 'target2' embedded in a longer string), but I kept getting an error message saying Compile Error: Expected: Then or GoTo. I wasn't sure if I was supposed to leave the 'Exact Match' part in or not, but I tried it both ways and got the same error... Here's the two pieces of code I tried

    Please Login or Register  to view this content.
    I've uploaded the exact file I'm working with... You'll see the 'target2's in column B -- those are the rows I'm trying to delete...

    MANY MANY thanks. I wish this was homework, since then I could just skip it, but instead its likely part of a thesis project! In theory I could do this manually, but have so many files that I'd develop carpal tunnel in the process...

    Emily
    Attached Files Attached Files
    Last edited by pike; 02-13-2011 at 09:29 PM. Reason: add code tags for newbie PM rules

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,068

    Re: If / then type of commands in VB -- help for a newbie!

    Please Login or Register  to view this content.
    Ben Van Johnson

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,111

    Re: If / then type of commands in VB -- help for a newbie!

    Hi Emily,

    Back from a party. To get #3 I'd sort the data using Lists/Tables. I'd sort by the Column that had Yes or No. Then do a normal Standard Deviation formula on only the Yes values.

    If you need to do this same process for more than 20 workbooks then we should talk about more VBA. Let me know if this makes sense.

    If you have lots of Participants and huge amounts of data, I'd suggest Advanced Filters or better yet - Pivot Tables. See the attached with a Pivot Table Answer.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-13-2011
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: If / then type of commands in VB -- help for a newbie!

    ProtonLeah -- thank you so so much! That code worked like a charm, and once I had it, I was able to modify it with the help of Marvin's suggestion to do Part 2, and I think I can maybe figure out how to do the same (crudely) for Part 3.

    Marvin -- Thanks for your reply to my #3 just now. My problem is that I have a different number of yes/nos (errors) in each file, so I don't know how to do quite what you're suggesting. But I think I figured out a crude way around -- I basically told Excel to put only the values from the non-error trials into a new column, and then I'll just make a St.Dev out of that new column.

    One more question? I've been trying to modify that code to help me with one last task -- looking down Row D for all entries that are greater than 1500 or less than 350, and deleting that data (so the cell is now blank). I tried a very crude effort at doing this (basically using the code from Part 2, but telling it to fill the appropriate cells in with the value taken from a blank cell -- however Excel didn't seem to like this, and gave me an error telling me that the cell value I was asking it to place in there was a blank one. Any suggestions?

    Lastly -- Marvin, I don't have to do this for multiple worksheets per file, but I do have to do this whole routine for each file, of which there will be probably several hundred... Sigh. I'm sorry for being so hopelessly ill-informed about VBA -- I've done some programming, including macros, in statistical software packages, but never in Excel...

    Emily

  9. #9
    Registered User
    Join Date
    02-13-2011
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: If / then type of commands in VB -- help for a newbie!

    ProtonLeah -- thank you so so much! That code worked like a charm, and once I had it, I was able to modify it with the help of Marvin's suggestion to do Part 2, and I think I can maybe figure out how to do the same (crudely) for Part 3.

    Marvin -- Thanks for your reply to my #3 just now. My problem is that I have a different number of yes/nos (errors) in each file, so I don't know how to do quite what you're suggesting. But I think I figured out a crude way around -- I basically told Excel to put only the values from the non-error trials into a new column, and then I'll just make a St.Dev out of that new column.

    One more question? I've been trying to modify that code to help me with one last task -- looking down Row D for all entries that are greater than 1500 or less than 350, and deleting that data (so the cell is now blank). I tried a very crude effort at doing this (basically using the code from Part 2, but telling it to fill the appropriate cells in with the value taken from a blank cell -- however Excel didn't seem to like this, and gave me an error telling me that the cell value I was asking it to place in there was a blank one. Any suggestions?

    Lastly -- Marvin, I don't have to do this for multiple worksheets per file, but I do have to do this whole routine for each file, of which there will be probably several hundred... Sigh. I'm sorry for being so hopelessly ill-informed about VBA -- I've done some programming, including macros, in statistical software packages, but never in Excel...

    Emily

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,111

    Re: If / then type of commands in VB -- help for a newbie!

    Hi,
    The Lastly part might look like this.
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    02-13-2011
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: If / then type of commands in VB -- help for a newbie!

    Thanks Marvin -- that code worked great!

    Ok, last request -- I (mostly) promise!

    I'm trying to modify the following code to only perform its operations up until a specific row (so instead of going all the way to the end of the sheet, to stop at say, Row 42)


    Please Login or Register  to view this content.
    I know its probably something in modifying the LastRow command, but I mucked around and kept getting errors. Can someone help?

    Again, I am so hugely grateful to those of you who have replied...

    Emily

  12. #12
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,111

    Re: If / then type of commands in VB -- help for a newbie!

    Hi Emily,

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1