VBA If statement............

Posted by Daniel on November 19, 2001 2:20 PM

In my spreadsheet I hold stock names, prices, etc and 2 level counts. Store level & Critical level.

I have another column with the formula:

If Stock level &LT;= Critical level Then
Write "Yes"
Else
Write "No"

Now I would like to create a module that searches through this column and finds all the "Yes" values and prints the row data of that product to another sheet.

I've been using For Each Next loops but im rusty with my VBA.

Hope you can help, if you need to know any more details just mail me.

Posted by Ben O. on November 19, 2001 10:18 PM

Daniel,

This solution may not be pretty, but it works. First of all, here's the code:

Sub ConditionalRowCopy()
Application.ScreenUpdating = False
sSheet = ActiveSheet.Name
' Change 2 to the number of the sheet you will be pasting to.
dSheet = 2
' Change 6 to the number of the column that has the stock level / critical level formula in it
myCol = 6
For x = 2 To LastCell(Cells).Row
If Cells(x, myCol).Value = "Yes" Then
Rows(x).Copy
Sheets(dSheet).Select
Rows(LastCell(Cells).Row + 1).Select
ActiveSheet.Paste
Sheets(sSheet).Select
End If
Next x
Sheets(sSheet).Select
End Sub

In addition to the instructions I wrote into the code, you must make sure your destination sheet (dsheet) has some information in it (I assumed it had the same header row as the source sheet when I tested it) in order for the "LastCell" function to work. Speaking of the LastCell function, it is a custom function (I can't take credit for it, but it's very useful) that my code uses. Here it is, you must paste it into your VBA editor so the macro can use it:

Function LastCell(ws As Range) As Range
Dim LastRow&, LastCol%

' Error-handling is here in case there is not any
' data in the worksheet

On Error Resume Next

With ws

' Find the last real row

LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

' Find the last real column

LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

End With

' Finally, initialize a Range object variable for
' the last populated row.

Set LastCell = ws.Cells(LastRow&, LastCol%)

End Function

That's it. The macro works for me when I test it on some sample data. Grabs all of the "Yes" records and none of the "No" records.

-Ben

Posted by Daniel on November 21, 2001 7:01 AM

Thanks for the reply, just one more question:

Where do i put these scripts?

I have out them both in sheet1. As a separate sub and function.

But this has not worked for me.

Also in replacing column number to the "Yes/No" column number should i not put the column lettter?

I am still a beginner with VBA, sorry

Thanks again.