My Msgbox Code isnt working

Microsoft Excel 2003 (full product)
July 14, 2010 at 07:45:27
Specs: Windows XP
I have a code that is supposed to detect the string "GRM" on an excel spreadsheet and reportt back the number of the cell it is in, here is the code, please let me know why its not working. (GRM can be anywhere on the sheet:

Function FindGRM() As Integer
Dim firstAddress As Range

With Worksheets(3).Range("a1:N1")
Set c = .Find("GRM", LookIn:=xlValues)
If Not c Is Nothing Then
Set firstAddress = c
FindGRM = firstAddress.Column

End If
End With

End Function


See More: My Msgbox Code isnt working

Report •

#1
July 14, 2010 at 09:38:10
Well, I see a few things:

1 - Your subject line says Msgbox but there is no MsgBox code shown. I assume you realize that this Function will return a value in a cell, not in a MsgBox.

2 - You said: report back the number of the cell it is in.

Actually, it will return the number of the column it is in because of FindGRM = firstAddress.Column

3 - You said: GRM can be anywhere on the sheet but the code only searches Range("A1:N1")

4 - What do you mean by not working?

When used as written, it does indeed return the column number of the cell within the range Sheet3!A1:N1 if any of those cells contain GRM. It returns 0 if GRM is not found.


Report •

#2
July 14, 2010 at 10:20:33
yes sorry, GRM can be found anywhere within that range, that is just a range im working with right now. Also, yes, i want the column. By not working, i mean it is turning up a msgbox that says 0 (even though i have a "GRM" within my range on the sheet)

Report •

#3
July 14, 2010 at 10:58:55
re: i mean it is turning up a msgbox that says 0

Please see item # 1 in my previous response. There is no way that that Function is turning up a msgbox

This sub-routine will turn up a msgbox, your function will not.

Sub TurnUpAMsgBox()
 MsgBox "This is a MsgBox"
End Sub

I will assume that you mean that is putting a 0 in the cell in which you enter the function.

Since I can't see your spreadsheet from where I am sitting, I can't tell you why it is putting a 0 in that cell.

Your exact code works just fine for me, when the conditions are set up with GRM in any cell within the range Sheet3!A1:N1.

Are you testing the function with GRM already in the cell or are you adding GRM in the cell after the function is already in a cell?

As written, you have to recalculate the function each time to have it update. Change it to this and it should update whenever the workbook recalculates:

Function FindGRM() As Integer
Dim firstAddress As Range
 Application.Volatile
  With Worksheets(3).Range("a1:N1")
   Set c = .Find("GRM", LookIn:=xlValues)
    If Not c Is Nothing Then
     Set firstAddress = c
      FindGRM = firstAddress.Column
    End If
  End With
End Function


Report •

Related Solutions

#4
July 14, 2010 at 15:18:16
Hi,

Your function FindGRM() is hard coded for the range, as mentioned by DerbyDad03, but it is also hardcoded for Worksheet(3), i.e., your third worksheet (not necessarily "Sheet3")

I suspect that your function is searching for "GRM" on a different worksheet - that does not contain GRM.

Also as DerbyDad03 points out, your function will not respond if GRM is added or moved, unless you click on the cell containing =FindGRM, or you make the function volatile.

If you changed your function so that the range to be searched was included in the function, then it would work without being made volatile. Excel will keep track of the range it refers to and updates it if a cell in that range changes.

Also for a single 'Find' you have redundant code, such as first address. Making the function an integer function is not necessary, as this may limit error reporting from the function.

Try this:

Public Function FindGRM(searchIn As Range) As Variant
Dim c As Range
'search for GRM in the specified range
Set c = searchIn.Find("GRM", LookIn:=xlValues)
If Not c Is Nothing Then
'found - return column number
FindGRM = c.Column
Else
'not found - return #NA error
FindGRM = CVErr(xlErrNA)
End If
End Function

Use this function with the appropriate range:
=findGrm(H15:I18)

Regards


Report •

#5
July 14, 2010 at 15:29:50
Hi,

Talking of redundant code, the following appears to work - returning the column number if found or #VALUE if not:

Public Function FindGRM(searchIn As Range) As Variant
    FindGRM = searchIn.Find("GRM", LookIn:=xlValues).Column
End Function

Regards


Report •

Ask Question