By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,568 Members | 1,050 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,568 IT Pros & Developers. It's quick & easy.

Macro wont play

P: 1
Hi Guys

I am a beginner in VBA but am having a problem. The code below steps through ok, yet when I hit play it errors on the object line and I dont understand why (run time 91). There is a worksheet function off this which returns now() as a time stamp, but I dont get why this doesnt step through

Expand|Select|Wrap|Line Numbers
  1.  
  2. Sub ReplaceEvareEscrow()
  3.  
  4. Dim lngrow As Long
  5.   Dim Account As String
  6.   Dim Update As Long
  7.   Dim Rngfound As Range
  8.   Dim Rngsearch As Range
  9.   Dim Evare As Range
  10.  
  11.   Set Rngsearch = Sheets("Data").Columns("B:C")
  12.  
  13.   With Sheets("Evare")
  14.  
  15.     lngrow = 2
  16.     Do Until .Cells(lngrow, 1) = Empty
  17.  
  18.       Account = .Cells(lngrow, 1)
  19.       Update = .Cells(lngrow, 5)
  20.  
  21.  
  22.       Set Rngfound = Rngsearch.Find(What:=Account, LookAt:=xlWhole, MatchByte:=False)
  23.  
  24.      With Rngfound
  25.         .Offset(0, 2).Value = Update
  26.      End With
  27.  
  28.     lngrow = lngrow + 1
  29.  
  30.     Loop
  31.  
  32.   End With
  33.  
  34. End Sub
  35.  
  36.  

Any help is appreciated

Mike
May 22 '07 #1
Share this Question
Share on Google+
1 Reply


danp129
Expert 100+
P: 321
Please be more clear when stating where you're getting the error. Anyhow, if your code doesn't find a match for the account then Rngfound will be nothing, and Nothing doesn't have any properties or methods, it really is nothing :) Try this:

Expand|Select|Wrap|Line Numbers
  1.  If Not Rngfound Is Nothing Then
  2.      With Rngfound
  3.         .Offset(0, 2).Value = Update
  4.      End With
  5.  End If
Also, if you only have one cell to update I wouldn't bother using With, just use Rngfound.Offset(0,2).Value=Update
May 23 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.