Getting Type Mismatch

[Excellearner]

Hi All,
I am still New Bee and trying to learn VBA concepts.Quick help is really appreciated.
I am trying to select a row based on the searchstring and search for strings "X", "F" and "P" and replace them with either "P" or "F" based on User form entries.

Following is the code and i also tried .Find. Please help ray:

Private Sub cmdStatus_Click()

Dim rng1 As Range
Dim NewAddress As String
Dim Exstring As String

Dim Rownumber As Integer
Dim StringLength As Integer
Dim TestStatus As Boolean


Dim LastCellAddress

SearchString = txtTestCase.Value

With ActiveSheet.Cells
Set c = .Find(SearchString, LookIn:=xlValues)

If Not c Is Nothing Then
NewAddress = c.Address(RowAbsolute:=False, ColumnAbsolute:=False)
Rownumber = Right(NewAddress, 1) 'Finding the Row Number of the Test Case
StringLength = Len(NewAddress)
If IsNumeric(Mid(NewAddress, 2, 1)) Then
Rownumber = Right(NewAddress, StringLength - 1)
Else
Rownumber = Right(NewAddress, StringLength - 2)
End If

Set rng1 = ActiveSheet.Rows(Rownumber) 'Selecting the row of the entered Test Case

'Seeting Test Status to Pass or Fail
If optPass.Value = True Then
TestStatus = "P"
ElseIf optPass.Value = False Then
TestStatus = "F"
End If

Dim Lookc As Range
'Rows(rng1.Address).Select
'Range(rng1.Address).Select


Selection.Replace What:="X", Replacement:=TestStatus, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Selection.Replace What:="P", Replacement:=TestStatus, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Selection.Replace What:="F", Replacement:=TestStatus, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

txtTestCase.SetFocus
txtTestCase.Value = ""
txtTestCase.EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection
Else
MsgBox "Test Case :" & SearchString & " could not be found "
txtTestCase.SetFocus
txtTestCase.Value = ""
txtTestCase.EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection
End If
End With
End Sub

 

[Greg Truby]

Where you're getting into trouble is that you have Dim'ed TestStatus as boolean and you are trying to treat it as a string. The code below is not tested, but should give you some ideas on how to better use range objects. You don't need to extract the row from an address to do what you want to do.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cmdStatus_Click()

    <SPAN style="color:#00007F">Dim</SPAN> TestStatus <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

    <SPAN style="color:#00007F">Set</SPAN> c = ActiveSheet.Cells.Find(txtTestCase.Value, LookIn:=xlValues)
    
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        TestStatus = IIf(optPass, "P", "F")
        <SPAN style="color:#00007F">With</SPAN> c.EntireRow
            .Replace What:="X", Replacement:=TestStatus, LookAt:=xlPart, _
                     SearchOrder:=xlByRows, MatchCase:=<SPAN style="color:#00007F">False</SPAN>
        
            .Replace What:="P", Replacement:=TestStatus, LookAt:=xlPart, _
                     SearchOrder:=xlByRows, MatchCase:=<SPAN style="color:#00007F">False</SPAN>
        
            .Replace What:="F", Replacement:=TestStatus, LookAt:=xlPart, _
                     SearchOrder:=xlByRows, MatchCase:=<SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        
        txtTestCase.SetFocus
        txtTestCase.Value = ""
        txtTestCase.EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection
    <SPAN style="color:#00007F">Else</SPAN>
        MsgBox "Test Case :" & txtTestCase.Value & " could not be found "
        txtTestCase.SetFocus
        txtTestCase.Value = ""
        txtTestCase.EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

I should comment that both your code and the code above make the assumption that this TestCase that you are searching for as the key to finding the right row would not contain an "F", "P" nor "X". Otherwise I think this code would inadvertantly alter the value of the cell containing TestCase.

HTH

 

[Excellearner]

Hi Greg,
Thanks for the reply. I realized my mistake.
But i could not quite understand the last statement.
To give more clarity. My data sheet may contain "P" , "F" or "X" for a selected string

What i am intending to do is to replace all cells in the row with either "P" or "F" based on option button selected in the user form.

Unfortunately the code is not replacing the values. Any idea where it is going wrong.

TIA

 

[Greg Truby]

Not sure what to tell you. I went ahead and set up the following test bed:

book1
	A	B	C	D	E	F	G
1	Original
2	one	P	F	X	P	F	X
3	two	P	F	X	P	F	X
4	three	P	F	X	P	F	X
Sheet3

Set up a userform using the controls you describe (nice of you to use Hungarian notation, it made things much quicker and easier to follow). I ran the code above three (3) times picking Pass/Fail/Pass and got:

book1
	A	B	C	D	E	F	G
6	AfterRunning
7	one	P	P	P	P	P	P
8	two	F	F	F	F	F	F
9	three	P	P	P	P	P	P
Sheet3

The point of the last statement in my previous post was that this alters all the cells in the row. So if I used a test row with the key value "Primero" and selected fail then the test value gets changed to "Frimero".

HTH

 

[Excellearner]

Greg ,
I understood what you said , is there any way i can take care of it.

Otherwise Code is workign perfect.

Thanks a lot..

 

[Greg Truby]

Glad to hear that it's working. If all of the cells that need to be edited lie to the right of the column with the key being sought, you can simple change the line that reads:

With c.EntireRow

to read

With c.EntireRow.Resize(, 256 - c.Column).Offset(, c.Column)

which shrinks and then offsets the cells upon which the .Replace function acts.

 

[Excellearner]

Thanks Greg. It worked.

Did you see my other post
http://www.mrexcel.com/board2/viewtopic.php?t=125012&highlight=

I want to take input like this from user T1, R1,R2, R3, R4 and build a matrix like mapping intersections with "X" .

Is there any easy way to do it. Currently i am taking the row number and attaching with respecting Column and placing X mark.

Thanks again..