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

[hibatt]

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

 

[Dave Peterson]

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("o1192"),"x")

 

[Sébastien]

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