VBA equaivalent of worksheet functions

A

andyandy12345

New Member
Joined
Feb 26, 2011
Messages
11
Hi, new to using excel VBA and struggling...

Is there a VBA equivalent to this formula?
=IF(ISNUMBER(FIND("mytext",A3, 1)),"good","bad")

I have defined a range in VBA and need to create a VB module to do check the range for certain text and based on the result output the true or false information to an offsett column on the same row.

I have, now, worked out that the boolean ISNUMERIC() function will report false if there is not a number in the range being checked and am struggling to work out what i need to do.

I have been using the FIND() to look for "mytext" and xlpart, matchcase:=0 etc but cannot combine the boolean logic test with the text lookup.

Please help and show how simple it is if you understand what you are doing...

Thank you in advance
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Hi. Try InStr

Code: 
Sub atest()
With Range("A3")
    .Offset(, 1) = IIf(InStr(.Value, "mytext") > 0, "good", "bad")
End With
End Sub
 
Upvote 0
Thanks - that did what it was supposed to but it is not case sensitive and I need tro used a defined range. I did try using the instr but cannot gwt it to work either

This is my attempt at it: which always results in bad as I am using the isnumeric() - can you help debug? Any help is greatly apprecieated.

Sub EnvironmentCheck()
Dim r As Range, c As Range
' Format SpreadSheet
Worksheets("Sheet1").Range("Y1") = "results"
' Define result range

Set r = Worksheets("Sheet1").Range("A2", Range("A2").End(xlDown))

For Each c In r

If ISNUMERIC(c.Find(what:="mytest", LookIn:=xlValues, lookat:=xlPart, MatchCase:=0)) _
Then c.Offset(0, 24) = "good" Else c.Offset(0, 24) = "bad"


Next c

'r.Select
End Sub
 
Upvote 0
Try

Code: 
Sub atest()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        With .Range("A" & i)
            .Offset(, 24) = IIf(InStr(.Value, "mytext") > 0, "good", "bad")
        End With
    Next i
End With
End Sub
 
Upvote 0
Try

Code: 
Sub atest()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        With .Range("A" & i)
            .Offset(, 24) = IIf(InStr(1, .Value, "mytext", vbBinaryCompare) > 0, "good", "bad")
        End With
    Next i
End With
End Sub
 
Upvote 0
Genius, thank you very much. Will have to work out how it does what it does now...

I had to swap vbBinaryCompare with vbTextCompare and it is perfect.

Thanks :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
search all worksheets based upon textbox value and add to listbox
Replies
7
Views
392
Jaye7
J
pcc
  • Solved
Conversion of special characters
Replies
6
Views
768
JEC
JEC
G
  • Solved
Adapt VBA code where "Find" is equal to a MID function (etc)
Replies
6
Views
248
GTS
G
R
  • Solved
VBA - Find Replace on selected cells
Replies
3
Views
324
rsulliva
R
N
  • Solved
how to automatically clear text if text in another cell is removed
Replies
1
Views
90
Joe4
Joe4

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top