Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Object variable or with Block variable not set

This was working for me, and I must have change something to get the variable not set error.

Thanks.
Howard


Option Explicit

Sub FindNewPN()

Dim rngFndPrd As Range
Dim c As Range
Dim ws1Part_Num As Range
Dim ws2From_Item As Range

Set ws1Part_Num = Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
Set ws2From_Item = Sheets("Sheet2").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

Application.ScreenUpdating = False
For Each c In ws1Part_Num

Set rngFndPrd = ws2From_Item.Find(c, LookIn:=xlValues, lookat:=xlWhole)
rngFndPrd.Offset(0, 5).Copy

Sheets("Sheet1").Range("F100").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteAll, Transpose:=False

Application.ScreenUpdating = True

Next
End Sub
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Object variable or with Block variable not set

Hi Howard,

Am Wed, 2 Oct 2013 23:28:45 -0700 (PDT) schrieb Howard:

This was working for me, and I must have change something to get the variable not set error.


this error comes when no item is found. Change the code:

If Not rngFndPrd Is Nothing Then
rngFndPrd.Offset(0, 5).Copy
Sheets("Sheet1").Range("F100").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteAll, Transpose:=False
End If


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Object variable or with Block variable not set

Hi Howard,

Am Thu, 3 Oct 2013 10:05:04 +0200 schrieb Claus Busch:

If Not rngFndPrd Is Nothing Then
rngFndPrd.Offset(0, 5).Copy
Sheets("Sheet1").Range("F100").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteAll, Transpose:=False
End If


better without copy and paste:

Application.ScreenUpdating = False
For Each c In ws1Part_Num

Set rngFndPrd = ws2From_Item.Find(c, LookIn:=xlValues, _
lookat:=xlWhole)
If Not rngFndPrd Is Nothing Then
Sheets("Sheet1").Range("F100").End(xlUp)(2) _
= rngFndPrd.Offset(0, 5)
End If

Next
Application.ScreenUpdating = True


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Object variable or with Block variable not set

On Thursday, October 3, 2013 1:36:24 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Thu, 3 Oct 2013 10:05:04 +0200 schrieb Claus Busch:



If Not rngFndPrd Is Nothing Then


rngFndPrd.Offset(0, 5).Copy


Sheets("Sheet1").Range("F100").End(xlUp).Offset(1, 0).PasteSpecial _


Paste:=xlPasteAll, Transpose:=False


End If




better without copy and paste:



Application.ScreenUpdating = False

For Each c In ws1Part_Num



Set rngFndPrd = ws2From_Item.Find(c, LookIn:=xlValues, _

lookat:=xlWhole)

If Not rngFndPrd Is Nothing Then

Sheets("Sheet1").Range("F100").End(xlUp)(2) _

= rngFndPrd.Offset(0, 5)

End If



Next

Application.ScreenUpdating = True





Regards

Claus B.



Aha, I did indeed change something like I said, it was the data I was using to test the code while I wrote it. Once it worked for me I changed the data a much larger data set and it had no match.

I like the non-copy paste you suggest. I'm sure its possible to include a Transpose or Values.

What would that look like?

Thanks Claus.
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Object variable or with Block variable not set

Hi Howard,

Am Thu, 3 Oct 2013 02:01:14 -0700 (PDT) schrieb Howard:

I like the non-copy paste you suggest. I'm sure its possible to include a Transpose or Values.


to get only the values try:

Application.ScreenUpdating = False
For Each c In ws1Part_Num
Set rngFndPrd = ws2From_Item.Find(c, LookIn:=xlValues, _
lookat:=xlWhole)
If Not rngFndPrd Is Nothing Then
With Sheets("Sheet1")
.Range("F100").End(xlUp).Offset(1, 0).Value _
= rngFndPrd.Offset(0, 5).Value
End With
End If
Next
Application.ScreenUpdating = True

to get the values and transpose the range try:

Application.ScreenUpdating = False
For Each c In ws1Part_Num
Set rngFndPrd = ws2From_Item.Find(c, LookIn:=xlValues, _
lookat:=xlWhole)
If Not rngFndPrd Is Nothing Then
If rngBig Is Nothing Then
Set rngBig = rngFndPrd.Offset(0, 5)
Else
Set rngBig = Union(rngBig, rngFndPrd.Offset(0, 5))
End If
End If
Next
rngBig.Copy
Sheets("Sheet1").Range("F100").End(xlUp).Offset(1, 0) _
.PasteSpecial xlPasteValues, Transpose:=True
Application.ScreenUpdating = True


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Object variable or with Block variable not set

On Thursday, October 3, 2013 2:21:27 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Thu, 3 Oct 2013 02:01:14 -0700 (PDT) schrieb Howard:



I like the non-copy paste you suggest. I'm sure its possible to include a Transpose or Values.




to get only the values try:



Application.ScreenUpdating = False

For Each c In ws1Part_Num

Set rngFndPrd = ws2From_Item.Find(c, LookIn:=xlValues, _

lookat:=xlWhole)

If Not rngFndPrd Is Nothing Then

With Sheets("Sheet1")

.Range("F100").End(xlUp).Offset(1, 0).Value _

= rngFndPrd.Offset(0, 5).Value

End With

End If

Next

Application.ScreenUpdating = True



to get the values and transpose the range try:



Application.ScreenUpdating = False

For Each c In ws1Part_Num

Set rngFndPrd = ws2From_Item.Find(c, LookIn:=xlValues, _

lookat:=xlWhole)

If Not rngFndPrd Is Nothing Then

If rngBig Is Nothing Then

Set rngBig = rngFndPrd.Offset(0, 5)

Else

Set rngBig = Union(rngBig, rngFndPrd.Offset(0, 5))

End If

End If

Next

rngBig.Copy

Sheets("Sheet1").Range("F100").End(xlUp).Offset(1, 0) _

.PasteSpecial xlPasteValues, Transpose:=True

Application.ScreenUpdating = True





Regards

Claus B.


Okay, got it.
Thanks a ton, Claus.

Regards,
Howard
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Object variable or with Block variable not set



Am Thu, 3 Oct 2013 02:01:14 -0700 (PDT) schrieb Howard:








I like the non-copy paste you suggest. I'm sure its possible to include a Transpose or Values.








to get only the values try:








Application.ScreenUpdating = False




For Each c In ws1Part_Num




Set rngFndPrd = ws2From_Item.Find(c, LookIn:=xlValues, _




lookat:=xlWhole)




If Not rngFndPrd Is Nothing Then




With Sheets("Sheet1")




.Range("F100").End(xlUp).Offset(1, 0).Value _




= rngFndPrd.Offset(0, 5).Value




End With




End If




Next




Application.ScreenUpdating = True








to get the values and transpose the range try:








Application.ScreenUpdating = False




For Each c In ws1Part_Num




Set rngFndPrd = ws2From_Item.Find(c, LookIn:=xlValues, _




lookat:=xlWhole)




If Not rngFndPrd Is Nothing Then




If rngBig Is Nothing Then




Set rngBig = rngFndPrd.Offset(0, 5)




Else




Set rngBig = Union(rngBig, rngFndPrd.Offset(0, 5))




End If




End If




Next




rngBig.Copy




Sheets("Sheet1").Range("F100").End(xlUp).Offset(1, 0) _




.PasteSpecial xlPasteValues, Transpose:=True




Application.ScreenUpdating = True












Regards




Claus B.



Okay, got it.

Thanks a ton, Claus.



Regards,

Howard


Just noticed as I was pasting in my worksheet, with the transpose code you have introduce a new variable:

If rngBig Is Nothing Then ... etc.

Puzzling, but I do trust your code. Just wondering.

Howard


  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Object variable or with Block variable not set

Hi again,

Am Thu, 3 Oct 2013 02:27:23 -0700 (PDT) schrieb Howard:

Okay, got it.


to transpose the range you also can dump the found items in an array:

Application.ScreenUpdating = False
For Each c In ws1Part_Num
Set rngFndPrd = ws2From_Item.Find(c, LookIn:=xlValues, _
lookat:=xlWhole)
If Not rngFndPrd Is Nothing Then
ReDim Preserve varOut(i)
varOut(i) = rngFndPrd.Offset(0, 5).Value
i = i + 1
End If
Next
Sheets("Sheet1").Range("F100").End(xlUp).Offset(1, 0) _
.Resize(columnsize:=UBound(varOut) + 1) = varOut
Application.ScreenUpdating = True


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Object variable or with Block variable not set

Hi Howard,

Am Thu, 3 Oct 2013 02:48:21 -0700 (PDT) schrieb Howard:

If rngBig Is Nothing Then ... etc.


dim rngBig as Range
This will create a range with all found cells

The new answer:
Dim i as integer
Dim varOut() as Variant


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Object variable or with Block variable not set

On Thursday, October 3, 2013 2:51:16 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Thu, 3 Oct 2013 02:48:21 -0700 (PDT) schrieb Howard:



If rngBig Is Nothing Then ... etc.




dim rngBig as Range

This will create a range with all found cells



The new answer:

Dim i as integer

Dim varOut() as Variant





Regards

Claus B.


Sweet, thanks a lot.

Regards
Howard
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Runtime Error '91' Object variable or With block variable not set Alec Coliver Excel Discussion (Misc queries) 2 October 24th 09 02:29 PM
Error Number: 91 object variable or With block Variable not set Hifni Excel Programming 1 January 9th 08 12:56 PM
Getting inconsistent Error 91-Object variable or With block variable not set mfq Excel Programming 0 December 14th 05 07:08 PM
Run-time error '91': "Object variable or With block variable not set Mike[_92_] Excel Programming 2 December 30th 04 11:59 AM
Cells.Find error Object variable or With block variable not set Peter[_21_] Excel Programming 2 May 8th 04 02:15 PM


All times are GMT +1. The time now is 08:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"