Linking and 255 Characters

What about this??
the following code opens the workbook containing the explanation, looks for cells(i,"C") in A1:A100. If the value is found, say cell A53, returns in cell(i,15) the value of B53.

Sub ...
...
Dim c As Range
Dim StrToFind As String
...
ThisWorkbook.Activate
Sheets(1).Activate 'Activate proper sheet
StrToFind = Cells(i, "C")
'If not, open "C:\My Documents\Survey\" & Cells(i, 1) & ".xls]Explanations

Workbooks.Open Filename:= _
"C:\My Documents\Survey\" & Cells(i, 1) & ".xls"

'If open, activate it!!!

Sheets("Explanations").Select
Set c = Range("A1:A100").Find(What:=StrToFind, After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
ThisWorkbook.ActiveSheet.Cells(i, 15) = c.Offset(0, 1)
Else
ThisWorkbook.ActiveSheet.Cells(i, 15) = "Not found"
End If
...
End Sub

Ciao

Chiello,

For the most part your code works nicely, but I ran into some minor problems. Can you let me know what the correct syntax would be to close each workbook in the directory within the For loop

I tried inserting

'Workbooks.Close Filename:= _
'"K:\MVSSAS\DataColl\datcol04\Finished\" & Cells(i, 1) & ".xls"

after your "end if" statement...but i got an error.

Also for on of my "i" observations i ran into an error stating:

"Run-time error '1004': Application-defined or object-defined error"

when i hit the debug button it hilghted the syntax "ThisWorkbook.Activesheet.Cells(i,15)=c.offset(0,1)"

Would you happen to know why this occured? Or what code can be inserted so at least it would flag that there's something wrong for that specific observation, but continue running for the remaining observations?

Thanks so much!

Try this:

'Workbooks.Close Filename:= _
'"K:\MVSSAS\DataColl\datcol04\Finished\" & Cells(i, 1) & ".xls"

Click to expand...

dim StrFileName as String
StrFileName = Cells(i, 1) & ".xls"
Workbooks(StrFileName).Close SaveChanges:=False

Also for on of my "i" observations i ran into an error stating:

"Run-time error '1004': Application-defined or object-defined error"

when i hit the debug button it hilghted the syntax "ThisWorkbook.Activesheet.Cells(i,15)=c.offset(0,1)"
Would you happen to know why this occured?

Click to expand...

Check 0<i<65537 and c.Column < 256

Or what code can be inserted so at least it would flag that there's something wrong for that specific observation, but continue running for the remaining observations?

Click to expand...

Insert at the top of routine:
On Error GoTo ErrorOccurred

and at bottom:
...
Exit Sub
ErrorOccurred:
MsgBox "Error Occurred"
Resume Next
End Sub

Hope it's clear

Ciao

I've refined my code, but l found that using the "workbook open" technique - it takes extremely long to go through all of my 424 observations. I ended the run after 15 minutes because I'm assuming there must be another alternative. I hope!

Below i've included the sub procedures that I initially posted and the revised code based on the suggestions posted by ciello

Option 1 uses a vlookup formula and runs relatively fast. But it still truncates explanations that contain more than 255 characters.

Option 2 uses the workbook open method.

Sub Option1()
Dim MyCount
MyCount = Range(Range("A5"), Range("A5").End(xlDown)).count + 4
For i = 5 To MyCount
Cells(i, 15).Formula = "=VLOOKUP(C" & i & ",'C:\My Documents\Survey\[" & Cells(i, 1) & _
".xls]Explanations'!$A$1:$B$100,2,FALSE)"
Next i

End Sub



Sub Option2()

Application.ScreenUpdating = False

Dim c As Range
Dim StrToFind As String
Dim MyCount
MyCount = Range(Range("A5"), Range("A5").End(xlDown)).count + 4
'On Error GoTo ErrorOccurred

For i = 5 To MyCount
ThisWorkbook.Activate
Sheets(1).Activate
StrToFind = Cells(i, "C")

Workbooks.Open Filename:= _
"C:\My Documents\Survey\" & Cells(i, 1) & ".xls"
Sheets("Explanations").Select

Set c = Range("A1:A100").Find(What:=StrToFind, After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If Not c Is Nothing Then
ThisWorkbook.ActiveSheet.Cells(i, 15) = c.Offset(0, 1)
Else
ThisWorkbook.ActiveSheet.Cells(i, 15) = "Not found"
End If
ActiveWorkbook.Close (False)
Next i

Exit Sub

ErrorOccurred:
ThisWorkbook.ActiveSheet.Cells(i, 15) = "ERROR"
Resume Next

Application.ScreenUpdating = False

End Sub


Does anyone have any other suggestions? I attempted to search for help on MS's website:
http://support.microsoft.com/?scid=.../servicedesks/bin/kbsearch.asp?article=213841
it has sample code - but it's foreign to me



Also, I noticed that when I ran the "option 2" macro - an error occured for 1 observation - but I'm not sure why it happend exactly. There were 1,104 characters in that observation. But when I reduced the characters in that worksheet (to 25) and reran the macro - no error occured. Is there a certain range above 255 characters where an error arises?