Excel macro help

[allllec]

Right, basicly im trying to search for a cell in another xls document, and copy the name of the sheet on which it is located idealy, although at the moment it just copies the contents of A1, which will do. im getting error 91 around the search bit of the code. any ideas? i know its messy btw im not very good at this kinda thing.


FYI the first loops runs perfectly - then its errors.

pelase help asap!

Sub Macro1()
    Dim n As Integer
    Dim a As String
    Dim b As String
    Dim c As String
    Dim d As String
    Dim e As String
 
    For n = 3 To 811 Step 1
    Windows("11.xls").Activate
    Sheets("Sheet4").Select
    a = "D" & n
    Range(a).Select
    b = ActiveCell.FormulaR1C1
    Windows("ESPRESSO SPARES 2006.xls").Activate
    Cells.Find(What:=b, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Range("A1,A2").Select
    c = ActiveCell.FormulaR1C1
 
    Windows("11.xls").Activate
    d = "E" & n
    Range(d).Select
    ActiveCell.FormulaR1C1 = c
    Next n
 
End Sub

 

[Munkeh]

If you run through it in debug what value have you got for b when your searching? Check that the search isn't returning a null it might not like that, and hence why its returning an error.

 

[allllec]

If you run through it in debug what value have you got for b when your searching? Check that the search isn't returning a null it might not like that, and hence why its returning an error.

You where right - i was under the impression that all the numbers and such needed where all here. appearently not. Anyway - i fixed it up - but then i made the mistake of displaying a message every time soemthing wasnt found. anyway it went on for ages - figured something went wrong. killed the process. lost everything although it had been saved. just wipped the work sheet!


back to square one thanks a lot thou

 

[Munkeh]

No probs, a few tips -

Comment it so you know whats going on if you come back later
Indent loops make it easier to read
Instead of using :
a = "D" & n
Range(a).Select
you can use : range("D" & n).select
etc.

Just looks a bit neater. Also some error handling wouldn't go amiss... Just stuff to look into...

 

[allllec]

Yeah, kinda had to get it done ASAP and stuff.

however it all crashing was kinda good - took a half hour but ive moved everything around and just using vlookup's to do the hard work now

thanks for the help anyhow !