Error Object Variable Not Set when not using Set

Question

I keep running into the error message "Object Variable or With Block Variable Not Set", however, when I use SET to set the variable then I get the same error despite having set it. Does anyone know why this may be happening? This is my code I have and the line that keep throwing the two errors is

name = sht.Range("A1:Z2").Find(What:=colname, Lookat:=xlWhole, LookIn:=xlFormulas, MatchCase:=True)

The error gets thrown so early that the code cannot even try running getdata so I have not yet been able to debug that to see if it works entirely. Before calling getdata, the function getcolumnindex runs fine. Here is my entire code

Public Sub Main() 

Dim wb As Workbook, ws As Worksheet, i As Range, dict As Object, wbSrc As Workbook 

Dim value As Long 

Set wb = ThisWorkbook 
Set ws = wb.Worksheets("Sheet1") 

Set dict = CreateObject("scripting.dictionary") 

For Each i In ws.Range("E2:E15").Cells 
 sysnum = i.value 
 sysrow = i.Row 
 syscol = i.Column 

getcolumnindex ws, "Range (nm)" 
value = getdata(sysrow, "Range (nm)") 

Next i 

End Sub

Function getcolumnindex(sht As Worksheet, colname As String) As Integer 

Dim name As Range, colind As Integer 
## Error gets thrown at this line below 
Set name = sht.Range("A1:Z2").Find(What:=colname,Lookat:=xlWhole, LookIn:=xlFormulas, MatchCase:=True) 
   If Not name Is Nothing Then 
        colind = name.Column 
        MsgBox name & " column index is " & colind 
   End If
End Function

Function getdata(WDrow As Integer, parametercol As String) 

Dim cell As Variant, ws As Worksheet 

cell = getcolumnindex(ws, "Tuning Range (nm)") 
Data = Cells(WDrow, parametercol) 
MsgBox (Data) 

End Function

@BigBen It does find the colname as it works when it is on its own. Its when combined with getdata does it throw the error. I know that it does because the messagebox appears printing the column index — user20114520
– user20114520, Commented Oct 7, 2022 at 16:57
@BigBen So I can write `Set name = sht.Range("A1:Z2").Find(What:=colname, LookAt:=xlWhole, LookIn:=xlFormulas, MatchCase:=True) — user20114520
– user20114520, Commented Oct 7, 2022 at 17:00
@BigBen Unfortunately, even with adding the two lines it did not get rid of the error — user20114520
– user20114520, Commented Oct 7, 2022 at 17:04

BigBen · Accepted Answer · 2022-10-07 19:31:59Z

0

There are many issues. Among them, in getdata,

ws is not Set.
Cells(WDrow, parametercol) implicitly refers to the ActiveSheet, but really needs to have its parent worksheet specified.
MsgBox (Data) should not have any parentheses.
Data is an undeclared variable.

Function getdata(WDrow As Integer, parametercol As String) 

   Dim cell As Variant, ws As Worksheet 

   cell = getcolumnindex(ws, "Tuning Range (nm)")  'ws is never Set
   Data = Cells(WDrow, parametercol)
   MsgBox Data 

End Function

It would be good to read up on the difference between Subs and Functions, and how to return a value from a Function.

getcolumnindex returns 0, always. If you want getcolumnindex to return something, you need the line

getcolumnindex = ...

within the function.

The following line does nothing with the return value of getcolumnIndex.

getcolumnindex ws, "Range (nm)"

This does nothing with the return value of getcolumnIndex.

edited Oct 7, 2022 at 19:31

answered Oct 7, 2022 at 17:14

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

BigBen Over a year ago

I would highly suggest reading up on the difference between Subs and Functions, and how to return a value from a Function. None of your functions are returning what you think they are.

user20114520 Over a year ago

So I should format it to be similar to value = getdata(sysrow, "Range (nm)")

BigBen Over a year ago

First read this.

user20114520 Over a year ago

Using the readings I have made this change

Function getcolumnindex(sht As Worksheet, colname As String) As Integer     Dim paramname As Range     Set paramname = sht.Range("A1:Z2").Find(What:=colname, Lookat:=xlWhole, LookIn:=xlFormulas, MatchCase:=True)         If Not paramname Is Nothing Then             getcolumnindex = paramname.Column             MsgBox paramname & " column index is " & getcolumnindex         End If End Function

Collectives™ on Stack Overflow

Error Object Variable Not Set when not using Set

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related