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

[erosenzweig]

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

[karthikeyan113]

Can you share a sample sheet.

[MarvinP]

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.

Option Explicit
'Use Cells(RowCtr, "A").Value = "Test" for exact match
'Use StrConv(Cells(RowCtr, "A").Value) = StrConv("Test") for no case test
'Use the VBA InStr() function if only a part of cell matches
Sub DeleteRowIfStuffInIt()
Dim RowCtr As Double 'Row Counter to go down rows
Dim LastRow As Double '

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Change the A to Column Letter you want checked (above and below)

For RowCtr = 2 To LastRow
    If Cells(RowCtr, "A").Value = "Test" Then 'Exact Match
        Rows(RowCtr).EntireRow.Delete
        RowCtr = RowCtr - 1
    End If
Next RowCtr

End Sub

[MarvinP]

For problem #2
Instead of using the above

Rows(RowCtr).EntireRow.Delete

Look up the definition of VBA "Offset"
So something like

Cells(RowCtr,"A").Offset(0,1) = Range("D30").Value

[erosenzweig]

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

For RowCtr = 2 To LastRow
    If InStr(RowCtr, "B").Value = "target2"  Then 'Exact Match
        Rows(RowCtr).EntireRow.Delete
        RowCtr = RowCtr - 1
    End If
    End If
Next RowCtr

For RowCtr = 2 To LastRow
    If InStr(RowCtr, "B").Value = "target2"  Then 
        Rows(RowCtr).EntireRow.Delete
        RowCtr = RowCtr - 1
    End If
    End If
Next RowCtr

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

[protonLeah]

Option Explicit
Sub DeleteRowIfStuffInIt()
    Dim RowCtr  As Long 'Row Counter to go down rows
    Dim LastRow     As Long, _
        FoundPos    As Long, _
        DelCount    As Long
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For RowCtr = LastRow To 2 Step -1
    
        FoundPos = InStr(Range("B" & RowCtr).Value, "target2")
        If FoundPos > 0 Then
            Rows(RowCtr).EntireRow.Delete
            DelCount = DelCount + 1
        End If
    Next RowCtr
    MsgBox DelCount & " rows deleted"
End Sub

[MarvinP]

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.

[erosenzweig]

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

[erosenzweig]

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

[MarvinP]

Hi,
The Lastly part might look like this.

Sub OutOfRange()
Dim RowCtr As Double
Dim LastRow As Double
    LastRow = Cells(Rows.Count, "D").End(xlUp).Row
        For RowCtr = 2 To LastRow
            If (Cells(RowCtr, "D").Value < 350) Or (Cells(RowCtr, "D").Value > 1500) Then
                Cells(RowCtr, "D") = ""
            End If
        Next RowCtr
End Sub

[erosenzweig]

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)

Dim RowCtr  As Long 'Row Counter to go down rows
    Dim LastRow     As Long, _
        FoundPos    As Long, _
        DelCount    As Long
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For RowCtr = LastRow To 2 Step -1
    
        FoundPos = InStr(Range("C" & RowCtr).Value, "E")
        If FoundPos > 0 Then
        Cells(RowCtr, "A").Offset(0, 3) = Range("H20").Value
        End If
    Next RowCtr

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

[MarvinP]

Hi Emily,

Change

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

to

LastRow = 42