Return to Answer

You have 2 problems:

1. Run-time error '91': Object variable or With block variable not set
2. not seeing that you have a Range object being returned

1 - Run-time error '91'

For the first error, the reason is because of the way you are calling your Function. As Fratyx points out, you need to use the keyword Set when calling searchAdress().

You can also see this in:

• What is the difference between dim and set in vba
• Returning a Range from a Function
• Can a VBA function in Excel return a range?

The reason for this is that you are trying to assign an object result to your variable. In VBA when setting objects, you use the Set keyword.

2 - not seeing a Range object

The code does return a Rangeobject as required. You can confirm this by some debug test code like:

Set nettowertadresse = searchAdress(Dateiname, CurrentSheet, "Nettowert", Range("A1:BA2")) nettowertadresse.Select 

or

MsgBox nettowertaddresse.Address 

This will select the cell which has your word in it, or display the address in a popup box.

You are probably using MsgBox() or hovering over the variable in the editor, which is returning the actual range value (nettowertadresse.value)

Both of these issues are related the your question from the other day, which addresses these exact same issues, but before you started to use your own function:

• Trouble with vba error 91 when using With, Range and .Find

See how the answer there relates directly to the explanations I give you here. The difference is that you are using your own function now, but the basic underlying issues are still the same.

You have 2 problems:

1. Run-time error '91': Object variable or With block variable not set
2. not seeing that you have a Range object being returned

1 - Run-time error '91'

For the first error, the reason is because of the way you are calling your Function. As Fratyx points out, you need to use the keyword Set when calling searchAdress().

You can also see this in:

• What is the difference between dim and set in vba
• Returning a Range from a Function
• Can a VBA function in Excel return a range?

The reason for this is that you are trying to assign an object result to your variable. In VBA when setting objects, you use the Set keyword.

2 - not seeing a Range object

The code does return a Rangeobject as required. You can confirm this by some debug test code like:

Set nettowertadresse = searchAdress(Dateiname, CurrentSheet, "Nettowert", Range("A1:BA2")) nettowertadresse.Select 

or

MsgBox nettowertaddresse.Address 

This will select the cell which has your word in it, or display the address in a popup box.

You are probably using MsgBox() or hovering over the variable in the editor, which is returning the actual range value (nettowertadresse.value)

Both of these issues are related the your question from the other day, which addresses these exact same issues, but before you started to use your own function:

• Trouble with vba error 91 when using With, Range and .Find

See how the answer there relates directly to the explanations I give you here. The difference is that you are using your own function now, but the basic underlying issues are still the same.

You have 2 problems:

1. Run-time error '91': Object variable or With block variable not set
2. not seeing that you have a Range object being returned

1 - Run-time error '91'

For the first error, the reason is because of the way you are calling your Function. As Fratyx points out, you need to use the keyword Set when calling searchAdress(). You can also see this in the question that Siddharth links to in his comment, and in Returning a Range from a Function.

The reason for this is that you are trying to assign an object result to your variable. In VBA when setting objects, you use the Set keyword.

2 - not seeing a Range object

The code does return a Rangeobject as required. You can confirm this by some debug test code like:

Set nettowertadresse = searchAdress(Dateiname, CurrentSheet, "Nettowert", Range("A1:BA2")) nettowertadresse.Select 

or

MsgBox nettowertaddresse.Address 

This will select the cell which has your word in it, or display the address in a popup box.

You are probably using MsgBox() or hovering over the variable in the editor, which is returning the actual range value (nettowertadresse.value)

Both of these issues are related the your question from the other day, which addresses these exact same issues, but before you started to use your own function:

• Trouble with vba error 91 when using With, Range and .Find

See how the answer there relates directly to the explanations I give you here. The difference is that you are using your own function now, but the basic underlying issues are still the same.

You have 2 problems:

1. Run-time error '91': Object variable or With block variable not set
2. not seeing that you have a Range object being returned

1 - Run-time error '91'

For the first error, the reason is because of the way you are calling your Function. As Fratyx points out, you need to use the keyword Set when calling searchAdress(). 

You can also see this in:

• What is the difference between dim and set in vba
• Returning a Range from a Function
• Can a VBA function in Excel return a range?

The reason for this is that you are trying to assign an object result to your variable. In VBA when setting objects, you use the Set keyword.

2 - not seeing a Range object

The code does return a Rangeobject as required. You can confirm this by some debug test code like:

Set nettowertadresse = searchAdress(Dateiname, CurrentSheet, "Nettowert", Range("A1:BA2")) nettowertadresse.Select 

or

MsgBox nettowertaddresse.Address 

This will select the cell which has your word in it, or display the address in a popup box.

You are probably using MsgBox() or hovering over the variable in the editor, which is returning the actual range value (nettowertadresse.value)

Both of these issues are related the your question from the other day, which addresses these exact same issues, but before you started to use your own function:

• Trouble with vba error 91 when using With, Range and .Find

See how the answer there relates directly to the explanations I give you here. The difference is that you are using your own function now, but the basic underlying issues are still the same.

You have 2 problems:

1. Run-time error '91': Object variable or With block variable not set
2. not seeing that you have a Range object being returned

1 - Run-time error '91'

For the first error, the reason is because of the way you are calling your Function. As Fratyx points out, you need to use the keyword Set when calling searchAdress(). You can also see this in the question that Siddharth links to in his comment, and in Returning a Range from a Function.

The reason for this is that you are trying to assign an object result to your variable. In VBA when setting objects, you use the Set keyword.

2 - not seeing a Range object

The code does return a Rangeobject as required. You can confirm this by some debug test code like:

Set nettowertadresse = searchAdress(Dateiname, CurrentSheet, "Nettowert", Range("A1:BA2")) nettowertadresse .Select 

This will select the cell which has your word in it.

You are probably using MsgBox() or hovering over the variable in the editor, which is returning the actual range value (nettowertadresse.value)

You have 2 problems:

1. Run-time error '91': Object variable or With block variable not set
2. not seeing that you have a Range object being returned

1 - Run-time error '91'

For the first error, the reason is because of the way you are calling your Function. As Fratyx points out, you need to use the keyword Set when calling searchAdress(). You can also see this in the question that Siddharth links to in his comment, and in Returning a Range from a Function.

The reason for this is that you are trying to assign an object result to your variable. In VBA when setting objects, you use the Set keyword.

2 - not seeing a Range object

The code does return a Rangeobject as required. You can confirm this by some debug test code like:

Set nettowertadresse = searchAdress(Dateiname, CurrentSheet, "Nettowert", Range("A1:BA2")) nettowertadresse.Select 

or

MsgBox nettowertaddresse.Address 

This will select the cell which has your word in it, or display the address in a popup box.

You are probably using MsgBox() or hovering over the variable in the editor, which is returning the actual range value (nettowertadresse.value)

Both of these issues are related the your question from the other day, which addresses these exact same issues, but before you started to use your own function:

• Trouble with vba error 91 when using With, Range and .Find

See how the answer there relates directly to the explanations I give you here. The difference is that you are using your own function now, but the basic underlying issues are still the same.