| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
255
Hello,
This is my first posting on this forum. I first posted my question on the Mr Excel Message Board - http://www.mrexcel.com/board2/viewto...891&highlight= .... I got some help, but i'm still having some problems. Here's what I need to do: I have a worksheet that lists 424 records displaying "file names" (in column A) and "line numbers" (in column C). I'm using the vlookup funciton in my macro to retrieve explanations from files in another folder. '----------------------------------------------- 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 '----------------------------------------------- This works reasonably well (and fast), with the exception that if an explanation was longer than 255 characters it gets truncated. A person on Mr Excel responded and suggested that I use the following code instead: '----------------------------------------------- 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 '----------------------------------------- I ran this macro for the sample of 20 records that had more than 255 characters and only one resulted in having an error "Run-time error '1004': Application-defined or object-defined error" (I found this record had 1,104 characters in the explanation) What does this error mean? In addition, when i ran the macro for all 424 records, it took extremely long. After 15 minutes I hit escape and found that only 100 observations were processed. Does anyone have any ideas to solve my problem? Can code be added to either my option1 or option2 macros to have this run more efficiently... or should i start from scratch? Please help! THANKS... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
255
Dave, Thanks for the quick reply... I'll try running your code from
work on Monday. For the meantime... i'd like to learn more about the fundamentals of excel vba programming. I'm not much of a programmer. For instance, I'm not even sure what "Option Explicit" does. Do you have any suggestions on sites or books that I should read? I learn better from seeing examples. I have a hard time trying to code from scratch. Also, I saw your note on public.excel http://groups-beta.google.com/group/...270cd75fab6936 Let me explain. At first, I posted my question on the Mr Excel Message Board a few days ago, and someone helped me. But I came across other issues and didn't get a response on those. Then this morning I "disovered" the google forums.. so i figured i try it out, posting it here (excel.progamming) but I accidentally forgot to include a better descriptive subject line. I noticed my mistake right away, but I couldn't figure how to re-edit the subject line once it was posted. I was just concerned that most people would over look my post because it had a vague subject. So i thought it wouldn't hurt to post a reference on public.excel because (1) I wasn't sure which forum was "better" and (2) to include a better Subject Line other than "255". You had said it's unusual to post to multiple forums... did you mean by posting it at two forums within Google. Or Google and Mr. Excel? Dave Peterson wrote: I think you were hit with excel's limit of returning 255 characters from a closed workbook. If you have to keep the formulas, then as soon as these formulas recalculate (just recalculate--not rerunning the macro), then the results of the formula are gonna get truncated again. One option is to open the workbook in your sub (essentially the suggestion you got), retrieve the value in code and plop that returned value into the worksheet. But the bad news is that if you don't want truncation, you have to open up those workbooks. That could make the routine slow. Option Explicit Sub Option3() Dim myCount As Long Dim iRow As Long Dim myFileName As String Dim myWorksheetName As String Dim myFolder As String Dim testStr As String Dim res As Variant Dim myValue As Variant Dim tempWkbk As Workbook Dim testWks As Worksheet myFolder = "C:\My Documents\Survey\" myWorksheetName = "Explanations" With ActiveSheet myCount = .Range(.Range("A5"), ..Range("A5").End(xlDown)).Count + 4 For iRow = 5 To myCount myFileName = myFolder & .Cells(iRow, 1) & ".xls" testStr = "" On Error Resume Next testStr = Dir(myFileName) On Error GoTo 0 If testStr = "" Then 'file not found myValue = "File Not found" Else Set tempWkbk = Workbooks.Open(Filename:=myFileName, _ ReadOnly:=True) Set testWks = Nothing On Error Resume Next Set testWks = tempWkbk.Worksheets(myWorksheetName) On Error GoTo 0 If testWks Is Nothing Then myValue = "Worksheet not found" Else res = Application.VLookup(.Cells(iRow, 3).Value, _ testWks.Range("A1:b100"), 2, False) If IsError(res) Then myValue = "Missing from Table" Else myValue = res End If End If tempWkbk.Close savechanges:=False End If .Cells(iRow, 15).Value = myValue Next iRow End With End Sub (I wouldn't have a guess why you got the 1004 error without knowing what line caused the error.) wrote: Hello, This is my first posting on this forum. I first posted my question on the Mr Excel Message Board - http://www.mrexcel.com/board2/viewto...891&highlight= ... I got some help, but i'm still having some problems. Here's what I need to do: I have a worksheet that lists 424 records displaying "file names" (in column A) and "line numbers" (in column C). I'm using the vlookup funciton in my macro to retrieve explanations from files in another folder. '----------------------------------------------- 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 '----------------------------------------------- This works reasonably well (and fast), with the exception that if an explanation was longer than 255 characters it gets truncated. A person on Mr Excel responded and suggested that I use the following code instead: '----------------------------------------------- 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 '----------------------------------------- I ran this macro for the sample of 20 records that had more than 255 characters and only one resulted in having an error "Run-time error '1004': Application-defined or object-defined error" (I found this record had 1,104 characters in the explanation) What does this error mean? In addition, when i ran the macro for all 424 records, it took extremely long. After 15 minutes I hit escape and found that only 100 observations were processed. Does anyone have any ideas to solve my problem? Can code be added to either my option1 or option2 macros to have this run more efficiently... or should i start from scratch? Please help! THANKS... -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
255
Here's a link to a post that tried to explain how "option explicit" is your
friend: http://groups.google.co.uk/groups?th...apeXSPAM.c om ======== Debra Dalgleish has a nice list of books at: http://www.contextures.com/xlbooks.html For VBA: John Walkenbach's is a nice one to start with. I think that John Green (and others) is nice, too (for a second book??). See if you can find them in your local bookstore and you can choose what one you like best. ============ And if you use mrexcel or google, you're actually going through some web interface to get to the active newsgroups. Google is a very nice archive, but I find it much easier using a newsreader (like Outlook Express) to read the active messages (and to post). Saved from a previous post: If you have Outlook Express installed, try clicking on these links (or copy and paste into MSIE). news://msnews.microsoft.com/microsof...ic.excel.setup news://msnews.microsoft.com/microsoft.public.excel.misc news://msnews.microsoft.com/microsof...heet.functions news://msnews.microsoft.com/microsof...excel.newusers news://msnews.microsoft.com/microsof...el.programming (and a few more for MSWord) news://msnews.microsoft.com/microsof....docmanagement news://msnews.microsoft.com/microsof...word.word97vba news://msnews.microsoft.com/microsof....word.newusers news://msnews.microsoft.com/microsof...ord.pagelayout news://msnews.microsoft.com/microsof...ord.vba.addins news://msnews.microsoft.com/microsof....vba.beginners news://msnews.microsoft.com/microsof....customization news://msnews.microsoft.com/microsof...rd.vba.general news://msnews.microsoft.com/microsof....vba.userforms news://msnews.microsoft.com/microsof....word6-7macros (You can always connect to more later) Here are some links that explain it better: Chip Pearson has some notes written by Leonard Meads at: http://www.cpearson.com/excel/DirectConnect.htm David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/xlnews.htm http://www.mvps.org/dmcritchie/excel/oe6.htm http://www.mvps.org/dmcritchie/excel/oe6nws01.htm Tushar Mehta's notes at: http://www.tushar-mehta.com/misc_tut...e_ng/index.htm And if you're looking for old posts: Or you can use google (maybe a few hours behind) to search for stuff you've posted (and find the replies, too) http://groups.google.com/advanced_group_search http://groups.google.com/advanced_gr...Excel*&num=100 Ron de Bruin has an excel addin that you may like: http://www.rondebruin.nl/Google.htm wrote: Dave, Thanks for the quick reply... I'll try running your code from work on Monday. For the meantime... i'd like to learn more about the fundamentals of excel vba programming. I'm not much of a programmer. For instance, I'm not even sure what "Option Explicit" does. Do you have any suggestions on sites or books that I should read? I learn better from seeing examples. I have a hard time trying to code from scratch. Also, I saw your note on public.excel http://groups-beta.google.com/group/...270cd75fab6936 Let me explain. At first, I posted my question on the Mr Excel Message Board a few days ago, and someone helped me. But I came across other issues and didn't get a response on those. Then this morning I "disovered" the google forums.. so i figured i try it out, posting it here (excel.progamming) but I accidentally forgot to include a better descriptive subject line. I noticed my mistake right away, but I couldn't figure how to re-edit the subject line once it was posted. I was just concerned that most people would over look my post because it had a vague subject. So i thought it wouldn't hurt to post a reference on public.excel because (1) I wasn't sure which forum was "better" and (2) to include a better Subject Line other than "255". You had said it's unusual to post to multiple forums... did you mean by posting it at two forums within Google. Or Google and Mr. Excel? Dave Peterson wrote: I think you were hit with excel's limit of returning 255 characters from a closed workbook. If you have to keep the formulas, then as soon as these formulas recalculate (just recalculate--not rerunning the macro), then the results of the formula are gonna get truncated again. One option is to open the workbook in your sub (essentially the suggestion you got), retrieve the value in code and plop that returned value into the worksheet. But the bad news is that if you don't want truncation, you have to open up those workbooks. That could make the routine slow. Option Explicit Sub Option3() Dim myCount As Long Dim iRow As Long Dim myFileName As String Dim myWorksheetName As String Dim myFolder As String Dim testStr As String Dim res As Variant Dim myValue As Variant Dim tempWkbk As Workbook Dim testWks As Worksheet myFolder = "C:\My Documents\Survey\" myWorksheetName = "Explanations" With ActiveSheet myCount = .Range(.Range("A5"), .Range("A5").End(xlDown)).Count + 4 For iRow = 5 To myCount myFileName = myFolder & .Cells(iRow, 1) & ".xls" testStr = "" On Error Resume Next testStr = Dir(myFileName) On Error GoTo 0 If testStr = "" Then 'file not found myValue = "File Not found" Else Set tempWkbk = Workbooks.Open(Filename:=myFileName, _ ReadOnly:=True) Set testWks = Nothing On Error Resume Next Set testWks = tempWkbk.Worksheets(myWorksheetName) On Error GoTo 0 If testWks Is Nothing Then myValue = "Worksheet not found" Else res = Application.VLookup(.Cells(iRow, 3).Value, _ testWks.Range("A1:b100"), 2, False) If IsError(res) Then myValue = "Missing from Table" Else myValue = res End If End If tempWkbk.Close savechanges:=False End If .Cells(iRow, 15).Value = myValue Next iRow End With End Sub (I wouldn't have a guess why you got the 1004 error without knowing what line caused the error.) wrote: Hello, This is my first posting on this forum. I first posted my question on the Mr Excel Message Board - http://www.mrexcel.com/board2/viewto...891&highlight= ... I got some help, but i'm still having some problems. Here's what I need to do: I have a worksheet that lists 424 records displaying "file names" (in column A) and "line numbers" (in column C). I'm using the vlookup funciton in my macro to retrieve explanations from files in another folder. '----------------------------------------------- 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 '----------------------------------------------- This works reasonably well (and fast), with the exception that if an explanation was longer than 255 characters it gets truncated. A person on Mr Excel responded and suggested that I use the following code instead: '----------------------------------------------- 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 '----------------------------------------- I ran this macro for the sample of 20 records that had more than 255 characters and only one resulted in having an error "Run-time error '1004': Application-defined or object-defined error" (I found this record had 1,104 characters in the explanation) What does this error mean? In addition, when i ran the macro for all 424 records, it took extremely long. After 15 minutes I hit escape and found that only 100 observations were processed. Does anyone have any ideas to solve my problem? Can code be added to either my option1 or option2 macros to have this run more efficiently... or should i start from scratch? Please help! THANKS... -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
255
And if you use mrexcel or google, you're actually going through some web
interface to get to the active newsgroups. I think you are confusing ExcelForum with MrExcel. I don't believe MrExcel interacts with the newsgroups. -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... Here's a link to a post that tried to explain how "option explicit" is your friend: http://groups.google.co.uk/groups?th...apeXSPAM.c om ======== Debra Dalgleish has a nice list of books at: http://www.contextures.com/xlbooks.html For VBA: John Walkenbach's is a nice one to start with. I think that John Green (and others) is nice, too (for a second book??). See if you can find them in your local bookstore and you can choose what one you like best. ============ And if you use mrexcel or google, you're actually going through some web interface to get to the active newsgroups. Google is a very nice archive, but I find it much easier using a newsreader (like Outlook Express) to read the active messages (and to post). Saved from a previous post: If you have Outlook Express installed, try clicking on these links (or copy and paste into MSIE). news://msnews.microsoft.com/microsof...ic.excel.setup news://msnews.microsoft.com/microsoft.public.excel.misc news://msnews.microsoft.com/microsof...heet.functions news://msnews.microsoft.com/microsof...excel.newusers news://msnews.microsoft.com/microsof...el.programming (and a few more for MSWord) news://msnews.microsoft.com/microsof....docmanagement news://msnews.microsoft.com/microsof...word.word97vba news://msnews.microsoft.com/microsof....word.newusers news://msnews.microsoft.com/microsof...ord.pagelayout news://msnews.microsoft.com/microsof...ord.vba.addins news://msnews.microsoft.com/microsof....vba.beginners news://msnews.microsoft.com/microsof....customization news://msnews.microsoft.com/microsof...rd.vba.general news://msnews.microsoft.com/microsof....vba.userforms news://msnews.microsoft.com/microsof....word6-7macros (You can always connect to more later) Here are some links that explain it better: Chip Pearson has some notes written by Leonard Meads at: http://www.cpearson.com/excel/DirectConnect.htm David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/xlnews.htm http://www.mvps.org/dmcritchie/excel/oe6.htm http://www.mvps.org/dmcritchie/excel/oe6nws01.htm Tushar Mehta's notes at: http://www.tushar-mehta.com/misc_tut...e_ng/index.htm And if you're looking for old posts: Or you can use google (maybe a few hours behind) to search for stuff you've posted (and find the replies, too) http://groups.google.com/advanced_group_search http://groups.google.com/advanced_gr...Excel*&num=100 Ron de Bruin has an excel addin that you may like: http://www.rondebruin.nl/Google.htm wrote: Dave, Thanks for the quick reply... I'll try running your code from work on Monday. For the meantime... i'd like to learn more about the fundamentals of excel vba programming. I'm not much of a programmer. For instance, I'm not even sure what "Option Explicit" does. Do you have any suggestions on sites or books that I should read? I learn better from seeing examples. I have a hard time trying to code from scratch. Also, I saw your note on public.excel http://groups-beta.google.com/group/...270cd75fab6936 Let me explain. At first, I posted my question on the Mr Excel Message Board a few days ago, and someone helped me. But I came across other issues and didn't get a response on those. Then this morning I "disovered" the google forums.. so i figured i try it out, posting it here (excel.progamming) but I accidentally forgot to include a better descriptive subject line. I noticed my mistake right away, but I couldn't figure how to re-edit the subject line once it was posted. I was just concerned that most people would over look my post because it had a vague subject. So i thought it wouldn't hurt to post a reference on public.excel because (1) I wasn't sure which forum was "better" and (2) to include a better Subject Line other than "255". You had said it's unusual to post to multiple forums... did you mean by posting it at two forums within Google. Or Google and Mr. Excel? Dave Peterson wrote: I think you were hit with excel's limit of returning 255 characters from a closed workbook. If you have to keep the formulas, then as soon as these formulas recalculate (just recalculate--not rerunning the macro), then the results of the formula are gonna get truncated again. One option is to open the workbook in your sub (essentially the suggestion you got), retrieve the value in code and plop that returned value into the worksheet. But the bad news is that if you don't want truncation, you have to open up those workbooks. That could make the routine slow. Option Explicit Sub Option3() Dim myCount As Long Dim iRow As Long Dim myFileName As String Dim myWorksheetName As String Dim myFolder As String Dim testStr As String Dim res As Variant Dim myValue As Variant Dim tempWkbk As Workbook Dim testWks As Worksheet myFolder = "C:\My Documents\Survey\" myWorksheetName = "Explanations" With ActiveSheet myCount = .Range(.Range("A5"), .Range("A5").End(xlDown)).Count + 4 For iRow = 5 To myCount myFileName = myFolder & .Cells(iRow, 1) & ".xls" testStr = "" On Error Resume Next testStr = Dir(myFileName) On Error GoTo 0 If testStr = "" Then 'file not found myValue = "File Not found" Else Set tempWkbk = Workbooks.Open(Filename:=myFileName, _ ReadOnly:=True) Set testWks = Nothing On Error Resume Next Set testWks = tempWkbk.Worksheets(myWorksheetName) On Error GoTo 0 If testWks Is Nothing Then myValue = "Worksheet not found" Else res = Application.VLookup(.Cells(iRow, 3).Value, _ testWks.Range("A1:b100"), 2, False) If IsError(res) Then myValue = "Missing from Table" Else myValue = res End If End If tempWkbk.Close savechanges:=False End If .Cells(iRow, 15).Value = myValue Next iRow End With End Sub (I wouldn't have a guess why you got the 1004 error without knowing what line caused the error.) wrote: Hello, This is my first posting on this forum. I first posted my question on the Mr Excel Message Board - http://www.mrexcel.com/board2/viewto...891&highlight= ... I got some help, but i'm still having some problems. Here's what I need to do: I have a worksheet that lists 424 records displaying "file names" (in column A) and "line numbers" (in column C). I'm using the vlookup funciton in my macro to retrieve explanations from files in another folder. '----------------------------------------------- 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 '----------------------------------------------- This works reasonably well (and fast), with the exception that if an explanation was longer than 255 characters it gets truncated. A person on Mr Excel responded and suggested that I use the following code instead: '----------------------------------------------- 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 '----------------------------------------- I ran this macro for the sample of 20 records that had more than 255 characters and only one resulted in having an error "Run-time error '1004': Application-defined or object-defined error" (I found this record had 1,104 characters in the explanation) What does this error mean? In addition, when i ran the macro for all 424 records, it took extremely long. After 15 minutes I hit escape and found that only 100 observations were processed. Does anyone have any ideas to solve my problem? Can code be added to either my option1 or option2 macros to have this run more efficiently... or should i start from scratch? Please help! THANKS... -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
255
I don't recall every visiting MrExcel, so that's entirely possible.
Thanks for the correction. Tom Ogilvy wrote: And if you use mrexcel or google, you're actually going through some web interface to get to the active newsgroups. I think you are confusing ExcelForum with MrExcel. I don't believe MrExcel interacts with the newsgroups. -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... Here's a link to a post that tried to explain how "option explicit" is your friend: http://groups.google.co.uk/groups?th...apeXSPAM.c om ======== Debra Dalgleish has a nice list of books at: http://www.contextures.com/xlbooks.html For VBA: John Walkenbach's is a nice one to start with. I think that John Green (and others) is nice, too (for a second book??). See if you can find them in your local bookstore and you can choose what one you like best. ============ And if you use mrexcel or google, you're actually going through some web interface to get to the active newsgroups. Google is a very nice archive, but I find it much easier using a newsreader (like Outlook Express) to read the active messages (and to post). Saved from a previous post: If you have Outlook Express installed, try clicking on these links (or copy and paste into MSIE). news://msnews.microsoft.com/microsof...ic.excel.setup news://msnews.microsoft.com/microsoft.public.excel.misc news://msnews.microsoft.com/microsof...heet.functions news://msnews.microsoft.com/microsof...excel.newusers news://msnews.microsoft.com/microsof...el.programming (and a few more for MSWord) news://msnews.microsoft.com/microsof....docmanagement news://msnews.microsoft.com/microsof...word.word97vba news://msnews.microsoft.com/microsof....word.newusers news://msnews.microsoft.com/microsof...ord.pagelayout news://msnews.microsoft.com/microsof...ord.vba.addins news://msnews.microsoft.com/microsof....vba.beginners news://msnews.microsoft.com/microsof....customization news://msnews.microsoft.com/microsof...rd.vba.general news://msnews.microsoft.com/microsof....vba.userforms news://msnews.microsoft.com/microsof....word6-7macros (You can always connect to more later) Here are some links that explain it better: Chip Pearson has some notes written by Leonard Meads at: http://www.cpearson.com/excel/DirectConnect.htm David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/xlnews.htm http://www.mvps.org/dmcritchie/excel/oe6.htm http://www.mvps.org/dmcritchie/excel/oe6nws01.htm Tushar Mehta's notes at: http://www.tushar-mehta.com/misc_tut...e_ng/index.htm And if you're looking for old posts: Or you can use google (maybe a few hours behind) to search for stuff you've posted (and find the replies, too) http://groups.google.com/advanced_group_search http://groups.google.com/advanced_gr...Excel*&num=100 Ron de Bruin has an excel addin that you may like: http://www.rondebruin.nl/Google.htm wrote: Dave, Thanks for the quick reply... I'll try running your code from work on Monday. For the meantime... i'd like to learn more about the fundamentals of excel vba programming. I'm not much of a programmer. For instance, I'm not even sure what "Option Explicit" does. Do you have any suggestions on sites or books that I should read? I learn better from seeing examples. I have a hard time trying to code from scratch. Also, I saw your note on public.excel http://groups-beta.google.com/group/...270cd75fab6936 Let me explain. At first, I posted my question on the Mr Excel Message Board a few days ago, and someone helped me. But I came across other issues and didn't get a response on those. Then this morning I "disovered" the google forums.. so i figured i try it out, posting it here (excel.progamming) but I accidentally forgot to include a better descriptive subject line. I noticed my mistake right away, but I couldn't figure how to re-edit the subject line once it was posted. I was just concerned that most people would over look my post because it had a vague subject. So i thought it wouldn't hurt to post a reference on public.excel because (1) I wasn't sure which forum was "better" and (2) to include a better Subject Line other than "255". You had said it's unusual to post to multiple forums... did you mean by posting it at two forums within Google. Or Google and Mr. Excel? Dave Peterson wrote: I think you were hit with excel's limit of returning 255 characters from a closed workbook. If you have to keep the formulas, then as soon as these formulas recalculate (just recalculate--not rerunning the macro), then the results of the formula are gonna get truncated again. One option is to open the workbook in your sub (essentially the suggestion you got), retrieve the value in code and plop that returned value into the worksheet. But the bad news is that if you don't want truncation, you have to open up those workbooks. That could make the routine slow. Option Explicit Sub Option3() Dim myCount As Long Dim iRow As Long Dim myFileName As String Dim myWorksheetName As String Dim myFolder As String Dim testStr As String Dim res As Variant Dim myValue As Variant Dim tempWkbk As Workbook Dim testWks As Worksheet myFolder = "C:\My Documents\Survey\" myWorksheetName = "Explanations" With ActiveSheet myCount = .Range(.Range("A5"), .Range("A5").End(xlDown)).Count + 4 For iRow = 5 To myCount myFileName = myFolder & .Cells(iRow, 1) & ".xls" testStr = "" On Error Resume Next testStr = Dir(myFileName) On Error GoTo 0 If testStr = "" Then 'file not found myValue = "File Not found" Else Set tempWkbk = Workbooks.Open(Filename:=myFileName, _ ReadOnly:=True) Set testWks = Nothing On Error Resume Next Set testWks = tempWkbk.Worksheets(myWorksheetName) On Error GoTo 0 If testWks Is Nothing Then myValue = "Worksheet not found" Else res = Application.VLookup(.Cells(iRow, 3).Value, _ testWks.Range("A1:b100"), 2, False) If IsError(res) Then myValue = "Missing from Table" Else myValue = res End If End If tempWkbk.Close savechanges:=False End If .Cells(iRow, 15).Value = myValue Next iRow End With End Sub (I wouldn't have a guess why you got the 1004 error without knowing what line caused the error.) wrote: Hello, This is my first posting on this forum. I first posted my question on the Mr Excel Message Board - http://www.mrexcel.com/board2/viewto...891&highlight= ... I got some help, but i'm still having some problems. Here's what I need to do: I have a worksheet that lists 424 records displaying "file names" (in column A) and "line numbers" (in column C). I'm using the vlookup funciton in my macro to retrieve explanations from files in another folder. '----------------------------------------------- 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 '----------------------------------------------- This works reasonably well (and fast), with the exception that if an explanation was longer than 255 characters it gets truncated. A person on Mr Excel responded and suggested that I use the following code instead: '----------------------------------------------- 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 '----------------------------------------- I ran this macro for the sample of 20 records that had more than 255 characters and only one resulted in having an error "Run-time error '1004': Application-defined or object-defined error" (I found this record had 1,104 characters in the explanation) What does this error mean? In addition, when i ran the macro for all 424 records, it took extremely long. After 15 minutes I hit escape and found that only 100 observations were processed. Does anyone have any ideas to solve my problem? Can code be added to either my option1 or option2 macros to have this run more efficiently... or should i start from scratch? Please help! THANKS... -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
255
No, Mr Excel doesn't provide access to the newsgroups I don't believe.
-- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... I don't recall every visiting MrExcel, so that's entirely possible. Thanks for the correction. Tom Ogilvy wrote: And if you use mrexcel or google, you're actually going through some web interface to get to the active newsgroups. I think you are confusing ExcelForum with MrExcel. I don't believe MrExcel interacts with the newsgroups. -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... Here's a link to a post that tried to explain how "option explicit" is your friend: http://groups.google.co.uk/groups?th...apeXSPAM.c om ======== Debra Dalgleish has a nice list of books at: http://www.contextures.com/xlbooks.html For VBA: John Walkenbach's is a nice one to start with. I think that John Green (and others) is nice, too (for a second book??). See if you can find them in your local bookstore and you can choose what one you like best. ============ And if you use mrexcel or google, you're actually going through some web interface to get to the active newsgroups. Google is a very nice archive, but I find it much easier using a newsreader (like Outlook Express) to read the active messages (and to post). Saved from a previous post: If you have Outlook Express installed, try clicking on these links (or copy and paste into MSIE). news://msnews.microsoft.com/microsof...ic.excel.setup news://msnews.microsoft.com/microsoft.public.excel.misc news://msnews.microsoft.com/microsof...heet.functions news://msnews.microsoft.com/microsof...excel.newusers news://msnews.microsoft.com/microsof...el.programming (and a few more for MSWord) news://msnews.microsoft.com/microsof....docmanagement news://msnews.microsoft.com/microsof...word.word97vba news://msnews.microsoft.com/microsof....word.newusers news://msnews.microsoft.com/microsof...ord.pagelayout news://msnews.microsoft.com/microsof...ord.vba.addins news://msnews.microsoft.com/microsof....vba.beginners news://msnews.microsoft.com/microsof....customization news://msnews.microsoft.com/microsof...rd.vba.general news://msnews.microsoft.com/microsof....vba.userforms news://msnews.microsoft.com/microsof....word6-7macros (You can always connect to more later) Here are some links that explain it better: Chip Pearson has some notes written by Leonard Meads at: http://www.cpearson.com/excel/DirectConnect.htm David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/xlnews.htm http://www.mvps.org/dmcritchie/excel/oe6.htm http://www.mvps.org/dmcritchie/excel/oe6nws01.htm Tushar Mehta's notes at: http://www.tushar-mehta.com/misc_tut...e_ng/index.htm And if you're looking for old posts: Or you can use google (maybe a few hours behind) to search for stuff you've posted (and find the replies, too) http://groups.google.com/advanced_group_search http://groups.google.com/advanced_gr...Excel*&num=100 Ron de Bruin has an excel addin that you may like: http://www.rondebruin.nl/Google.htm wrote: Dave, Thanks for the quick reply... I'll try running your code from work on Monday. For the meantime... i'd like to learn more about the fundamentals of excel vba programming. I'm not much of a programmer. For instance, I'm not even sure what "Option Explicit" does. Do you have any suggestions on sites or books that I should read? I learn better from seeing examples. I have a hard time trying to code from scratch. Also, I saw your note on public.excel http://groups-beta.google.com/group/...270cd75fab6936 Let me explain. At first, I posted my question on the Mr Excel Message Board a few days ago, and someone helped me. But I came across other issues and didn't get a response on those. Then this morning I "disovered" the google forums.. so i figured i try it out, posting it here (excel.progamming) but I accidentally forgot to include a better descriptive subject line. I noticed my mistake right away, but I couldn't figure how to re-edit the subject line once it was posted. I was just concerned that most people would over look my post because it had a vague subject. So i thought it wouldn't hurt to post a reference on public.excel because (1) I wasn't sure which forum was "better" and (2) to include a better Subject Line other than "255". You had said it's unusual to post to multiple forums... did you mean by posting it at two forums within Google. Or Google and Mr. Excel? Dave Peterson wrote: I think you were hit with excel's limit of returning 255 characters from a closed workbook. If you have to keep the formulas, then as soon as these formulas recalculate (just recalculate--not rerunning the macro), then the results of the formula are gonna get truncated again. One option is to open the workbook in your sub (essentially the suggestion you got), retrieve the value in code and plop that returned value into the worksheet. But the bad news is that if you don't want truncation, you have to open up those workbooks. That could make the routine slow. Option Explicit Sub Option3() Dim myCount As Long Dim iRow As Long Dim myFileName As String Dim myWorksheetName As String Dim myFolder As String Dim testStr As String Dim res As Variant Dim myValue As Variant Dim tempWkbk As Workbook Dim testWks As Worksheet myFolder = "C:\My Documents\Survey\" myWorksheetName = "Explanations" With ActiveSheet myCount = .Range(.Range("A5"), .Range("A5").End(xlDown)).Count + 4 For iRow = 5 To myCount myFileName = myFolder & .Cells(iRow, 1) & ".xls" testStr = "" On Error Resume Next testStr = Dir(myFileName) On Error GoTo 0 If testStr = "" Then 'file not found myValue = "File Not found" Else Set tempWkbk = Workbooks.Open(Filename:=myFileName, _ ReadOnly:=True) Set testWks = Nothing On Error Resume Next Set testWks = tempWkbk.Worksheets(myWorksheetName) On Error GoTo 0 If testWks Is Nothing Then myValue = "Worksheet not found" Else res = Application.VLookup(.Cells(iRow, 3).Value, _ testWks.Range("A1:b100"), 2, False) If IsError(res) Then myValue = "Missing from Table" Else myValue = res End If End If tempWkbk.Close savechanges:=False End If .Cells(iRow, 15).Value = myValue Next iRow End With End Sub (I wouldn't have a guess why you got the 1004 error without knowing what line caused the error.) wrote: Hello, This is my first posting on this forum. I first posted my question on the Mr Excel Message Board - http://www.mrexcel.com/board2/viewto...891&highlight= ... I got some help, but i'm still having some problems. Here's what I need to do: I have a worksheet that lists 424 records displaying "file names" (in column A) and "line numbers" (in column C). I'm using the vlookup funciton in my macro to retrieve explanations from files in another folder. '----------------------------------------------- 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 '----------------------------------------------- This works reasonably well (and fast), with the exception that if an explanation was longer than 255 characters it gets truncated. A person on Mr Excel responded and suggested that I use the following code instead: '----------------------------------------------- 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 '----------------------------------------- I ran this macro for the sample of 20 records that had more than 255 characters and only one resulted in having an error "Run-time error '1004': Application-defined or object-defined error" (I found this record had 1,104 characters in the explanation) What does this error mean? In addition, when i ran the macro for all 424 records, it took extremely long. After 15 minutes I hit escape and found that only 100 observations were processed. Does anyone have any ideas to solve my problem? Can code be added to either my option1 or option2 macros to have this run more efficiently... or should i start from scratch? Please help! THANKS... -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
255
I was trying to agree with you <bg.
(So that's entirely possible <that you are correct.) Tom Ogilvy wrote: No, Mr Excel doesn't provide access to the newsgroups I don't believe. -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... I don't recall every visiting MrExcel, so that's entirely possible. Thanks for the correction. Tom Ogilvy wrote: And if you use mrexcel or google, you're actually going through some web interface to get to the active newsgroups. I think you are confusing ExcelForum with MrExcel. I don't believe MrExcel interacts with the newsgroups. -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... Here's a link to a post that tried to explain how "option explicit" is your friend: http://groups.google.co.uk/groups?th...apeXSPAM.c om ======== Debra Dalgleish has a nice list of books at: http://www.contextures.com/xlbooks.html For VBA: John Walkenbach's is a nice one to start with. I think that John Green (and others) is nice, too (for a second book??). See if you can find them in your local bookstore and you can choose what one you like best. ============ And if you use mrexcel or google, you're actually going through some web interface to get to the active newsgroups. Google is a very nice archive, but I find it much easier using a newsreader (like Outlook Express) to read the active messages (and to post). Saved from a previous post: If you have Outlook Express installed, try clicking on these links (or copy and paste into MSIE). news://msnews.microsoft.com/microsof...ic.excel.setup news://msnews.microsoft.com/microsoft.public.excel.misc news://msnews.microsoft.com/microsof...heet.functions news://msnews.microsoft.com/microsof...excel.newusers news://msnews.microsoft.com/microsof...el.programming (and a few more for MSWord) news://msnews.microsoft.com/microsof....docmanagement news://msnews.microsoft.com/microsof...word.word97vba news://msnews.microsoft.com/microsof....word.newusers news://msnews.microsoft.com/microsof...ord.pagelayout news://msnews.microsoft.com/microsof...ord.vba.addins news://msnews.microsoft.com/microsof....vba.beginners news://msnews.microsoft.com/microsof....customization news://msnews.microsoft.com/microsof...rd.vba.general news://msnews.microsoft.com/microsof....vba.userforms news://msnews.microsoft.com/microsof....word6-7macros (You can always connect to more later) Here are some links that explain it better: Chip Pearson has some notes written by Leonard Meads at: http://www.cpearson.com/excel/DirectConnect.htm David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/xlnews.htm http://www.mvps.org/dmcritchie/excel/oe6.htm http://www.mvps.org/dmcritchie/excel/oe6nws01.htm Tushar Mehta's notes at: http://www.tushar-mehta.com/misc_tut...e_ng/index.htm And if you're looking for old posts: Or you can use google (maybe a few hours behind) to search for stuff you've posted (and find the replies, too) http://groups.google.com/advanced_group_search http://groups.google.com/advanced_gr...Excel*&num=100 Ron de Bruin has an excel addin that you may like: http://www.rondebruin.nl/Google.htm wrote: Dave, Thanks for the quick reply... I'll try running your code from work on Monday. For the meantime... i'd like to learn more about the fundamentals of excel vba programming. I'm not much of a programmer. For instance, I'm not even sure what "Option Explicit" does. Do you have any suggestions on sites or books that I should read? I learn better from seeing examples. I have a hard time trying to code from scratch. Also, I saw your note on public.excel http://groups-beta.google.com/group/...270cd75fab6936 Let me explain. At first, I posted my question on the Mr Excel Message Board a few days ago, and someone helped me. But I came across other issues and didn't get a response on those. Then this morning I "disovered" the google forums.. so i figured i try it out, posting it here (excel.progamming) but I accidentally forgot to include a better descriptive subject line. I noticed my mistake right away, but I couldn't figure how to re-edit the subject line once it was posted. I was just concerned that most people would over look my post because it had a vague subject. So i thought it wouldn't hurt to post a reference on public.excel because (1) I wasn't sure which forum was "better" and (2) to include a better Subject Line other than "255". You had said it's unusual to post to multiple forums... did you mean by posting it at two forums within Google. Or Google and Mr. Excel? Dave Peterson wrote: I think you were hit with excel's limit of returning 255 characters from a closed workbook. If you have to keep the formulas, then as soon as these formulas recalculate (just recalculate--not rerunning the macro), then the results of the formula are gonna get truncated again. One option is to open the workbook in your sub (essentially the suggestion you got), retrieve the value in code and plop that returned value into the worksheet. But the bad news is that if you don't want truncation, you have to open up those workbooks. That could make the routine slow. Option Explicit Sub Option3() Dim myCount As Long Dim iRow As Long Dim myFileName As String Dim myWorksheetName As String Dim myFolder As String Dim testStr As String Dim res As Variant Dim myValue As Variant Dim tempWkbk As Workbook Dim testWks As Worksheet myFolder = "C:\My Documents\Survey\" myWorksheetName = "Explanations" With ActiveSheet myCount = .Range(.Range("A5"), .Range("A5").End(xlDown)).Count + 4 For iRow = 5 To myCount myFileName = myFolder & .Cells(iRow, 1) & ".xls" testStr = "" On Error Resume Next testStr = Dir(myFileName) On Error GoTo 0 If testStr = "" Then 'file not found myValue = "File Not found" Else Set tempWkbk = Workbooks.Open(Filename:=myFileName, _ ReadOnly:=True) Set testWks = Nothing On Error Resume Next Set testWks = tempWkbk.Worksheets(myWorksheetName) On Error GoTo 0 If testWks Is Nothing Then myValue = "Worksheet not found" Else res = Application.VLookup(.Cells(iRow, 3).Value, _ testWks.Range("A1:b100"), 2, False) If IsError(res) Then myValue = "Missing from Table" Else myValue = res End If End If tempWkbk.Close savechanges:=False End If .Cells(iRow, 15).Value = myValue Next iRow End With End Sub (I wouldn't have a guess why you got the 1004 error without knowing what line caused the error.) wrote: Hello, This is my first posting on this forum. I first posted my question on the Mr Excel Message Board - http://www.mrexcel.com/board2/viewto...891&highlight= ... I got some help, but i'm still having some problems. Here's what I need to do: I have a worksheet that lists 424 records displaying "file names" (in column A) and "line numbers" (in column C). I'm using the vlookup funciton in my macro to retrieve explanations from files in another folder. '----------------------------------------------- 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 '----------------------------------------------- This works reasonably well (and fast), with the exception that if an explanation was longer than 255 characters it gets truncated. A person on Mr Excel responded and suggested that I use the following code instead: '----------------------------------------------- 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 '----------------------------------------- I ran this macro for the sample of 20 records that had more than 255 characters and only one resulted in having an error "Run-time error '1004': Application-defined or object-defined error" (I found this record had 1,104 characters in the explanation) What does this error mean? In addition, when i ran the macro for all 424 records, it took extremely long. After 15 minutes I hit escape and found that only 100 observations were processed. Does anyone have any ideas to solve my problem? Can code be added to either my option1 or option2 macros to have this run more efficiently... or should i start from scratch? Please help! THANKS... -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
255
Dave - I ran this code today and it worked nicely. Thanks so much!
|
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
255
EXCELlent!
jroo wrote: Dave - I ran this code today and it worked nicely. Thanks so much! -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
