Excel VBA - Action if a cell holds a certain value?
 

I am just running an excercise to see if I can expand a loopin
function.

I have a column that is basically a toggle that holds either an X o
nothing. I would like to go through each row of this column and d
something if there is an X there. For now I am just going to count th
X's essentially writing a countif function. Later it will not coun
but copy things to other areas based on the X.

I am a novice at VB, but I do have a little bit of C++ knowledge.
have come up with something that doesn't work but am not sure where t
go from here.

Here is the code that I have:

Sub Copy()
Dim Store As Integer

Store = 0

For Each c In Worksheets("SPARE9").Range("O11:O92").Cells
If c.WorksheetFunction.Value Eqv Val("x") Then Store = Store
1
Next

Range("A1").Select
ActiveCell.FormulaR1C1 = Store


End Sub

Any Ideas?

Thanks,
Andre

--
Message posted from http://www.ExcelForum.com

Excel VBA - Action if a cell holds a certain value?
 

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim destCell As Range

With Worksheets("SPARE9")
Set myRng = .Range("O11:O92")
End With

For Each myCell In myRng.Cells
If LCase(myCell.Value) = "x" Then
With Worksheets("other")
Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
myCell.EntireRow.Copy _
Destination:=destCell
End If
Next myCell
End Sub

if you really wanted to just count the x's, you could let excel do the work for
you:

msgbox application.countif(worksheets("spare9").range("o1 1:o92"),"x")



"hibatt <" wrote:

I am just running an excercise to see if I can expand a looping
function.

I have a column that is basically a toggle that holds either an X or
nothing. I would like to go through each row of this column and do
something if there is an X there. For now I am just going to count the
X's essentially writing a countif function. Later it will not count
but copy things to other areas based on the X.

I am a novice at VB, but I do have a little bit of C++ knowledge. I
have come up with something that doesn't work but am not sure where to
go from here.

Here is the code that I have:

Sub Copy()
Dim Store As Integer

Store = 0

For Each c In Worksheets("SPARE9").Range("O11:O92").Cells
If c.WorksheetFunction.Value Eqv Val("x") Then Store = Store +
1
Next

Range("A1").Select
ActiveCell.FormulaR1C1 = Store

End Sub

Any Ideas?

Thanks,
Andrew

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Excel VBA - Action if a cell holds a certain value?
 

Hi hibatt,

A few things (which doesn't necessarily make an error)
- you haven't declared c, Dim c as Range
- working with row numbers, it is better to use Long variable instead of Integer because there are 64K+ row an Integer could come short.
- look at the Value or Text properties of a cell to see what's in there.
- use the InStr() function to search a substring with a string.
- no need to select a cell to write into it.

Sub Copy()
Dim c as Range
Dim Store As Integer

Store = 0

For Each c In Worksheets("SPARE9").Range("O11:O92").Cells
If InStr(1,c.Text,"x", vbTextCompare) 0 Then Store = Store +1
Next

Range("A1").Value= Store

End Sub


Regards,
Sebastien