error 91 and inputbox string
 

The following macro needs tweaking and I am not able in any way...could
someone please have a look at it and help me!
1) When I run the macro the following error appears:
runtime error 91... Object variable or With block variable not set.
If I substitute rngNd with "C1:C20" no error message comes up.
obviously I would like to use rngNd.

2) I have tried entering "*" & strPO (which works for finding text earlier
on in the macro) but when applied as shown below it does not work.

Dim strCtr As String
Dim Cell As Range
Dim LastR As Long
Dim rngNd As Range

LastR = Cells(Rows.Count, "C").End(xlDown).Row
rngNd = Range("C1:C" & LastR)

strCtr = Application.InputBox(prompt:= _
"Inserisci Nome Contratto")

For Each Cell In wksTo.Range(rngNd)
If Cell.Value = "*" & strPO And Cell.Offset(0, -2).Value = 0 Then
Cell.Offset(0, -2).Value = strCtr
End If
Next Cell
End Sub

Thanks in advance.

error 91 and inputbox string
 

Try this

Option Explicit

Sub Test()
Dim strCtr As String
Dim myCell As Excel.Range
Dim LastR As Long
Dim rngNd As Excel.Range
Dim wksTo As Excel.Worksheet 'Added this.
Dim strPO
LastR = wksTo.Cells(Rows.Count, "C").End(xlDown).Row
'You have rngNd defined as a range. As a result, you need to use "Set rngND
= "

Set rngNd = wksTo.Range("C1:C" & LastR)

strCtr = Application.InputBox(prompt:= _
"Inserisci Nome Contratto")

'Reserve Cell for other functionality

For Each myCell In rngNd
If myCell.Value = "*" & strPO And myCell.Offset(0, -2).Value = 0 Then
myCell.Offset(0, -2).Value = strCtr
End If
Next myCell
End Sub

'Untested


"goodfish" wrote:

The following macro needs tweaking and I am not able in any way...could
someone please have a look at it and help me!
1) When I run the macro the following error appears:
runtime error 91... Object variable or With block variable not set.
If I substitute rngNd with "C1:C20" no error message comes up.
obviously I would like to use rngNd.

2) I have tried entering "*" & strPO (which works for finding text earlier
on in the macro) but when applied as shown below it does not work.

Dim strCtr As String
Dim Cell As Range
Dim LastR As Long
Dim rngNd As Range

LastR = Cells(Rows.Count, "C").End(xlDown).Row
rngNd = Range("C1:C" & LastR)

strCtr = Application.InputBox(prompt:= _
"Inserisci Nome Contratto")

For Each Cell In wksTo.Range(rngNd)
If Cell.Value = "*" & strPO And Cell.Offset(0, -2).Value = 0 Then
Cell.Offset(0, -2).Value = strCtr
End If
Next Cell
End Sub

Thanks in advance.

error 91 and inputbox string
 

In addition to Barb's comment

LastR = .Cells(Rows.Count, "C").End(xlDown).Row

will always evaluate to 65536 - or whatever the last row is in your version
of Excel. So rngNd will always be the entire column C. I think you mean
to use xlUp.

LastR = wksTo.Cells(Rows.Count, "C").End(xlUp).Row

hth,

Doug

"goodfish" wrote in message
...
The following macro needs tweaking and I am not able in any way...could
someone please have a look at it and help me!
1) When I run the macro the following error appears:
runtime error 91... Object variable or With block variable not set.
If I substitute rngNd with "C1:C20" no error message comes up.
obviously I would like to use rngNd.

2) I have tried entering "*" & strPO (which works for finding text earlier
on in the macro) but when applied as shown below it does not work.

Dim strCtr As String
Dim Cell As Range
Dim LastR As Long
Dim rngNd As Range

LastR = Cells(Rows.Count, "C").End(xlDown).Row
rngNd = Range("C1:C" & LastR)

strCtr = Application.InputBox(prompt:= _
"Inserisci Nome Contratto")

For Each Cell In wksTo.Range(rngNd)
If Cell.Value = "*" & strPO And Cell.Offset(0, -2).Value = 0 Then
Cell.Offset(0, -2).Value = strCtr
End If
Next Cell
End Sub

Thanks in advance.

__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4380 (20090829) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




__________ Information from ESET NOD32 Antivirus, version of virus signature database 4380 (20090829) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

error 91 and inputbox string
 

Thanks very much to both of you!! Now it works!
There was no way of getting the piece
If myCell.Value = "*" & strPO.Value
to recognise "*" & strPO
(I tried entering it many different ways and the closest I got was
substituting it with
"OL-" & strPO
which should be valid for all cases....
However now I have changed the
"*" & strPO to
"rngFound":
where rngFound was predefined earlier
Set rngFound = rngToSearch.Find(What:="*" & strPO, _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=False)
Thanks again for your prompt replies.

"Doug Glancy" wrote:

In addition to Barb's comment

LastR = .Cells(Rows.Count, "C").End(xlDown).Row

will always evaluate to 65536 - or whatever the last row is in your version
of Excel. So rngNd will always be the entire column C. I think you mean
to use xlUp.

LastR = wksTo.Cells(Rows.Count, "C").End(xlUp).Row

hth,

Doug

"goodfish" wrote in message
...
The following macro needs tweaking and I am not able in any way...could
someone please have a look at it and help me!
1) When I run the macro the following error appears:
runtime error 91... Object variable or With block variable not set.
If I substitute rngNd with "C1:C20" no error message comes up.
obviously I would like to use rngNd.

2) I have tried entering "*" & strPO (which works for finding text earlier
on in the macro) but when applied as shown below it does not work.

Dim strCtr As String
Dim Cell As Range
Dim LastR As Long
Dim rngNd As Range

LastR = Cells(Rows.Count, "C").End(xlDown).Row
rngNd = Range("C1:C" & LastR)

strCtr = Application.InputBox(prompt:= _
"Inserisci Nome Contratto")

For Each Cell In wksTo.Range(rngNd)
If Cell.Value = "*" & strPO And Cell.Offset(0, -2).Value = 0 Then
Cell.Offset(0, -2).Value = strCtr
End If
Next Cell
End Sub

Thanks in advance.

__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4380 (20090829) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




__________ Information from ESET NOD32 Antivirus, version of virus signature database 4380 (20090829) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com