Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable not set
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable not set
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable not set
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. -- HTH, Barb Reinhardt "LuisE" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable not set
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 "LuisE" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable not set
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 -- Regards Jean-Yves Tfelt Europe "LuisE" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-Time error '91': Object variable of With block variable not set | Excel Programming | |||
variable height variable width stacked bar charts | Charts and Charting in Excel | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |