Variable not set

[LuisE]

I have this macro running from a commandbutton in sheet2, it gets a
concatenated string and look it up in sheet24 once found retrieves datum from
sheet24 and shows it in an userform
I'm getting the variable/block not set error for rFound, cFound is working

Thanks in advance

Sub DirectLabor()

Dim Category
Dim Period
Dim Row
Dim Column
Dim Comment
Dim rFound As Range
Dim cFound As Range



Category = Sheet2.Range("A1") & Sheet2.Range("A77")

Period = Sheet2.Range("B3")



With Sheet24

Set rFound = Sheet24.Cells.Find(What:=Category, After:=Sheet24.Cells(1, 1),
LookIn:=xlValues

Row = rFound.Row



Set cFound = Sheet24.Cells.Find(What:=Period, After:=Sheet24.Cells(1, 1),
LookIn:=xlValues

Column = cFound.Column



Comment = Sheet24.Cells(Row, Column).Text


Frm1.Txt.Text = Comment

End With

Frm1.Show

End Sub

 

[Gary Keramidas]

you have a few problems:

1. don't use row and column as variables. they ar keywords in excel.
2. you need to test if the value you're finding is actually found.

this isn't perfect because i can't test against what you have. if it doesn't
find a row or column, you will have a problem. use this as a guide


Sub DirectLabor()
Dim Category
Dim Period
Dim iRow
Dim iColumn
Dim Comment
Dim rFound As Range
Dim cFound As Range
Dim ws As Worksheet


Category = Sheet2.Range("A1") & Sheet2.Range("A77")

Period = Sheet2.Range("B3")
Set ws = Worksheets("Sheet24")

With ws
Set rFound = ws.Cells.Find(What:=Category, After:=ws.Cells(1, 1),
LookIn:=xlValues)
If Not rFound Is Nothing Then
iRow = rFound.Row
End If
Set cFound = ws.Cells.Find(What:=Period, After:=ws.Cells(1, 1),
LookIn:=xlValues)
If Not cFound Is Nothing Then
iColumn = cFound.Column
End If
End With
On Error Resume Next
Comment = ws.Cells(iRow, iColumn).Text
On Error GoTo 0
If Comment <> "" Then
Frm1.Txt.Text = Comment
Frm1.Show
Else
MsgBox "data not found"
End If
End Sub

 

[Barb Reinhardt]

I'd add something like this

if not rfound is nothing
'Do whatever you'd do if you'd do if rFound exists
end if

It appears to me that your line to define rFound with find isn't really
finding what you want.

 

[Rick Rothstein \(MVP - VB\)]

Just to add to the other comments you have received...

These may be nothing more than typos on your part (which is why it is always
better to copy/paste real code rather than re-type it), but both of the
following lines (taken exactly as you posted them) appear to be missing
closing parentheses.

Set rFound = Sheet24.Cells.Find(What:=Category, After:=Sheet24.Cells(1, 1),
LookIn:=xlValues

Set cFound = Sheet24.Cells.Find(What:=Period, After:=Sheet24.Cells(1, 1),
LookIn:=xlValues


Rick

 

[Jean-Yves]

Hi,

Tobe sure as well, declare thevairalble type

Dim Category as string
Dim Period as string ...
Category = Sheet2.Range("A1").Value & Sheet2.Range("A77").Value