Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time error 1004????
This piece of code worked just fine:
Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Worksheets(2) Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng searches = UBound(ReplaceArray, 1) For srchCnt = 1 To searches findIt = ReplaceArray(srchCnt, 1) ReplaceWith = ReplaceArray(srchCnt, 2) InColumn = ReplaceArray(srchCnt, 3) Set rFound = Columns(InColumn).Find(findIt) - code snipped here <- Until I added and deleted some worksheets. Now I am getting a run-time error 1004 at the last line of that code and Rng1 = the value from cell A5. So I tried this: With Worksheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With And this, With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With But neither of those work. Can someone tell me how to fix this? And, if possible tell me what I am not getting about this? Maybe suggest some online reading. I have already ordered 2 books that were earlier suggested, but I need to get this project done before they get here. Thanks for any help and insights. Ken Loomis |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time error 1004????
Hi Ken,
It is not clear (to me!) where you say your error is occurring. Also, why do you set the Rng1 and Rng2 variables to ranges on Worksheets(2) and then immediately reassign these variables to ranges on the Replace Info sheet. Perhaps you could post the entire relevant code portion? --- Regards, Norman "Ken Loomis" wrote in message ... This piece of code worked just fine: Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Worksheets(2) Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng searches = UBound(ReplaceArray, 1) For srchCnt = 1 To searches findIt = ReplaceArray(srchCnt, 1) ReplaceWith = ReplaceArray(srchCnt, 2) InColumn = ReplaceArray(srchCnt, 3) Set rFound = Columns(InColumn).Find(findIt) - code snipped here <- Until I added and deleted some worksheets. Now I am getting a run-time error 1004 at the last line of that code and Rng1 = the value from cell A5. So I tried this: With Worksheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With And this, With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With But neither of those work. Can someone tell me how to fix this? And, if possible tell me what I am not getting about this? Maybe suggest some online reading. I have already ordered 2 books that were earlier suggested, but I need to get this project done before they get here. Thanks for any help and insights. Ken Loomis |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time error 1004????
Sorry. I just tried to copy and paste too many segments.
I get the run-time error (1004) at this line: Set rFound = Columns(InColumn).Find(findIt) Instead of getting a range in Rng1, I get the value of the cell at A5 on the "Replace Info" sheet. I modified this sub from the code I got he http://archive.baarns.com/excel/faq/xd_rang1.asp#5 If I really understood it, I might be able to fix what I broke. What I need to do is replace all occurances of a string in a column with its replacement string. The array from rhe "Replace Info" sheet contains the string to serach for, the the string to replace it with and the column to look in. For example, "STREET", "ST" and 3 Here is the complete sub: Sub ReplaceAllStuffInNAHBO() Dim ReplaceArray As Variant Dim rFound As Range Dim szFirst As String Dim iCount As Integer Dim rngTarget As Range Dim searches As Integer Dim srchCnt As Integer Dim n As Integer Dim m As Integer Dim findIt As String Dim ReplaceWith As String Dim InColumn As Integer Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Sheets(2) Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng searches = UBound(ReplaceArray, 1) For srchCnt = 1 To searches findIt = ReplaceArray(srchCnt, 1) ReplaceWith = ReplaceArray(srchCnt, 2) InColumn = ReplaceArray(srchCnt, 3) Set rFound = Columns(InColumn).Find(findIt) iCount = 0 Do While Not rFound Is Nothing ''' Store address of first occurrence If szFirst = "" Then szFirst = rFound.Address ElseIf rFound.Address = szFirst Then Exit Do ''' If we have looped around, quit End If rFound.Value = Application.Substitute(rFound.Value, _ findIt, ReplaceWith) iCount = iCount + 1 Set rFound = Columns(InColumn).Cells.FindNext(rFound) Loop If ShowMsgs Then MsgBox "Replaced " & iCount & " occurrences of " & findIt & " with " & ReplaceWith End If Next srchCnt End Sub "Norman Jones" wrote in message ... Hi Ken, It is not clear (to me!) where you say your error is occurring. Also, why do you set the Rng1 and Rng2 variables to ranges on Worksheets(2) and then immediately reassign these variables to ranges on the Replace Info sheet. Perhaps you could post the entire relevant code portion? --- Regards, Norman "Ken Loomis" wrote in message ... This piece of code worked just fine: Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Worksheets(2) Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng searches = UBound(ReplaceArray, 1) For srchCnt = 1 To searches findIt = ReplaceArray(srchCnt, 1) ReplaceWith = ReplaceArray(srchCnt, 2) InColumn = ReplaceArray(srchCnt, 3) Set rFound = Columns(InColumn).Find(findIt) - code snipped here <- Until I added and deleted some worksheets. Now I am getting a run-time error 1004 at the last line of that code and Rng1 = the value from cell A5. So I tried this: With Worksheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With And this, With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With But neither of those work. Can someone tell me how to fix this? And, if possible tell me what I am not getting about this? Maybe suggest some online reading. I have already ordered 2 books that were earlier suggested, but I need to get this project done before they get here. Thanks for any help and insights. Ken Loomis |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time error 1004????
Hi Ken,
Until I added and deleted some worksheets ... In your code line: Set rFound = Columns(InColumn).Find(findIt) Columns is not qualified and, therefore, refes to the active sheet. An added worksheet becomes the active sheet and Columns may reference the wrong sheet. It is much safer to explicitly qualify your ranges: Set RFound = Sheets("Replace Info").Columns(InColumn).Find(findit) This way, the correct range will be referenced even if you add or delete sheets. --- Regards, Norman "Ken Loomis" wrote in message ... This piece of code worked just fine: Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Worksheets(2) Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng searches = UBound(ReplaceArray, 1) For srchCnt = 1 To searches findIt = ReplaceArray(srchCnt, 1) ReplaceWith = ReplaceArray(srchCnt, 2) InColumn = ReplaceArray(srchCnt, 3) Set rFound = Columns(InColumn).Find(findIt) - code snipped here <- Until I added and deleted some worksheets. Now I am getting a run-time error 1004 at the last line of that code and Rng1 = the value from cell A5. So I tried this: With Worksheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With And this, With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With But neither of those work. Can someone tell me how to fix this? And, if possible tell me what I am not getting about this? Maybe suggest some online reading. I have already ordered 2 books that were earlier suggested, but I need to get this project done before they get here. Thanks for any help and insights. Ken Loomis |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time error 1004????
Hi Ken,
In addition to my adjacent post, depending on the position of added/deleted sheets, the sheet referenced by : Sheets(2) will change. To demonstrate, from the intermediate window: ? Sheets(2).Name Sheet2 Sheets.Add Sheets befo Sheets("Sheet2") ? Sheets(2).Name Sheet4 So, use the Sheets name instead of its index and qualify the Columns range by prefacing it with the appropriate sheet. --- Regards, Norman "Ken Loomis" wrote in message ... Sorry. I just tried to copy and paste too many segments. I get the run-time error (1004) at this line: Set rFound = Columns(InColumn).Find(findIt) Instead of getting a range in Rng1, I get the value of the cell at A5 on the "Replace Info" sheet. I modified this sub from the code I got he http://archive.baarns.com/excel/faq/xd_rang1.asp#5 If I really understood it, I might be able to fix what I broke. What I need to do is replace all occurances of a string in a column with its replacement string. The array from rhe "Replace Info" sheet contains the string to serach for, the the string to replace it with and the column to look in. For example, "STREET", "ST" and 3 Here is the complete sub: Sub ReplaceAllStuffInNAHBO() Dim ReplaceArray As Variant Dim rFound As Range Dim szFirst As String Dim iCount As Integer Dim rngTarget As Range Dim searches As Integer Dim srchCnt As Integer Dim n As Integer Dim m As Integer Dim findIt As String Dim ReplaceWith As String Dim InColumn As Integer Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Sheets(2) Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng searches = UBound(ReplaceArray, 1) For srchCnt = 1 To searches findIt = ReplaceArray(srchCnt, 1) ReplaceWith = ReplaceArray(srchCnt, 2) InColumn = ReplaceArray(srchCnt, 3) Set rFound = Columns(InColumn).Find(findIt) iCount = 0 Do While Not rFound Is Nothing ''' Store address of first occurrence If szFirst = "" Then szFirst = rFound.Address ElseIf rFound.Address = szFirst Then Exit Do ''' If we have looped around, quit End If rFound.Value = Application.Substitute(rFound.Value, _ findIt, ReplaceWith) iCount = iCount + 1 Set rFound = Columns(InColumn).Cells.FindNext(rFound) Loop If ShowMsgs Then MsgBox "Replaced " & iCount & " occurrences of " & findIt & " with " & ReplaceWith End If Next srchCnt End Sub "Norman Jones" wrote in message ... Hi Ken, It is not clear (to me!) where you say your error is occurring. Also, why do you set the Rng1 and Rng2 variables to ranges on Worksheets(2) and then immediately reassign these variables to ranges on the Replace Info sheet. Perhaps you could post the entire relevant code portion? --- Regards, Norman "Ken Loomis" wrote in message ... This piece of code worked just fine: Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Worksheets(2) Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng searches = UBound(ReplaceArray, 1) For srchCnt = 1 To searches findIt = ReplaceArray(srchCnt, 1) ReplaceWith = ReplaceArray(srchCnt, 2) InColumn = ReplaceArray(srchCnt, 3) Set rFound = Columns(InColumn).Find(findIt) - code snipped here <- Until I added and deleted some worksheets. Now I am getting a run-time error 1004 at the last line of that code and Rng1 = the value from cell A5. So I tried this: With Worksheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With And this, With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With But neither of those work. Can someone tell me how to fix this? And, if possible tell me what I am not getting about this? Maybe suggest some online reading. I have already ordered 2 books that were earlier suggested, but I need to get this project done before they get here. Thanks for any help and insights. Ken Loomis |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time error 1004????
Thanks for the comments. They are helping me understand better what is being
references. I have used the debugger to get a little further in understanding why I get the error, but still don't know how to fix it. Given this piece of code: Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng And this data in the "Replace Info" worksheet: PLACE PL 3 APT # 3 UNIT # 3 Note: Place is in cell A5 I would expect ReplaceArray to contain: ReplaceArray(1,1) = "PLACE" ReplaceArray(1,2) = "PL" ReplaceArray(1,3) = 3 ReplaceArray(2,1) = "APT" ReplaceArray(2,2) = "#" ReplaceArray(2,3) = 3 ReplaceArray(3,1) = "UNIT" ReplaceArray(3,2) = "#" ReplaceArray(3,3) = 3 But instead, the first row of ReplaceArray contains the data from cells A1, B1, C1. Since C1 is empty, ReplaceArray(1,3) = 0. The InColumn get assigned the value of ReplaceArray(1,3) or zero. So the statement that causes the 1004 error: Set rFound = Columns(InColumn).Find(findIt) is trying to access column zero which will cause the error. How do I modify the code above so that ReplaceArray is populated with the data in the cells starting at A5 and continuing thru column D and the last row on the worksheet? Ken Loomis "Norman Jones" wrote in message ... Hi Ken, Until I added and deleted some worksheets ... In your code line: Set rFound = Columns(InColumn).Find(findIt) Columns is not qualified and, therefore, refes to the active sheet. An added worksheet becomes the active sheet and Columns may reference the wrong sheet. It is much safer to explicitly qualify your ranges: Set RFound = Sheets("Replace Info").Columns(InColumn).Find(findit) This way, the correct range will be referenced even if you add or delete sheets. --- Regards, Norman "Ken Loomis" wrote in message ... This piece of code worked just fine: Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Worksheets(2) Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng searches = UBound(ReplaceArray, 1) For srchCnt = 1 To searches findIt = ReplaceArray(srchCnt, 1) ReplaceWith = ReplaceArray(srchCnt, 2) InColumn = ReplaceArray(srchCnt, 3) Set rFound = Columns(InColumn).Find(findIt) - code snipped here <- Until I added and deleted some worksheets. Now I am getting a run-time error 1004 at the last line of that code and Rng1 = the value from cell A5. So I tried this: With Worksheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With And this, With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With But neither of those work. Can someone tell me how to fix this? And, if possible tell me what I am not getting about this? Maybe suggest some online reading. I have already ordered 2 books that were earlier suggested, but I need to get this project done before they get here. Thanks for any help and insights. Ken Loomis |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time error 1004????
Thanks for the comments. They are helping me understand better what is being
references. I have used the debugger to get a little further in understanding why I get the error, but still don't know how to fix it. Given this piece of code: Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng And this data in the "Replace Info" worksheet: PLACE PL 3 APT # 3 UNIT # 3 Note: Place is in cell A5 I would expect ReplaceArray to contain: ReplaceArray(1,1) = "PLACE" ReplaceArray(1,2) = "PL" ReplaceArray(1,3) = 3 ReplaceArray(2,1) = "APT" ReplaceArray(2,2) = "#" ReplaceArray(2,3) = 3 ReplaceArray(3,1) = "UNIT" ReplaceArray(3,2) = "#" ReplaceArray(3,3) = 3 But instead, the first row of ReplaceArray contains the data from cells A1, B1, C1. Since C1 is empty, ReplaceArray(1,3) = 0. The InColumn get assigned the value of ReplaceArray(1,3) or zero. So the statement that causes the 1004 error: Set rFound = Columns(InColumn).Find(findIt) is trying to access column zero which will cause the error. How do I modify the code above so that ReplaceArray is populated with the data in the cells starting at A5 and continuing thru column D and the last row on the worksheet? Ken Loomis "Norman Jones" wrote in message ... Hi Ken, Until I added and deleted some worksheets ... In your code line: Set rFound = Columns(InColumn).Find(findIt) Columns is not qualified and, therefore, refes to the active sheet. An added worksheet becomes the active sheet and Columns may reference the wrong sheet. It is much safer to explicitly qualify your ranges: Set RFound = Sheets("Replace Info").Columns(InColumn).Find(findit) This way, the correct range will be referenced even if you add or delete sheets. --- Regards, Norman "Ken Loomis" wrote in message ... This piece of code worked just fine: Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Worksheets(2) Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng searches = UBound(ReplaceArray, 1) For srchCnt = 1 To searches findIt = ReplaceArray(srchCnt, 1) ReplaceWith = ReplaceArray(srchCnt, 2) InColumn = ReplaceArray(srchCnt, 3) Set rFound = Columns(InColumn).Find(findIt) - code snipped here <- Until I added and deleted some worksheets. Now I am getting a run-time error 1004 at the last line of that code and Rng1 = the value from cell A5. So I tried this: With Worksheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With And this, With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With But neither of those work. Can someone tell me how to fix this? And, if possible tell me what I am not getting about this? Maybe suggest some online reading. I have already ordered 2 books that were earlier suggested, but I need to get this project done before they get here. Thanks for any help and insights. Ken Loomis |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time error 1004????
Hi Ken,
Check Column D on "Replace Info" worksheet - Wht is the last populated cell? If I put "X", say, in D7, the ReplaceArray contents are as you intended. --- Regards, Norman "Ken Loomis" wrote in message ... Thanks for the comments. They are helping me understand better what is being references. I have used the debugger to get a little further in understanding why I get the error, but still don't know how to fix it. Given this piece of code: Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng And this data in the "Replace Info" worksheet: PLACE PL 3 APT # 3 UNIT # 3 Note: Place is in cell A5 I would expect ReplaceArray to contain: ReplaceArray(1,1) = "PLACE" ReplaceArray(1,2) = "PL" ReplaceArray(1,3) = 3 ReplaceArray(2,1) = "APT" ReplaceArray(2,2) = "#" ReplaceArray(2,3) = 3 ReplaceArray(3,1) = "UNIT" ReplaceArray(3,2) = "#" ReplaceArray(3,3) = 3 But instead, the first row of ReplaceArray contains the data from cells A1, B1, C1. Since C1 is empty, ReplaceArray(1,3) = 0. The InColumn get assigned the value of ReplaceArray(1,3) or zero. So the statement that causes the 1004 error: Set rFound = Columns(InColumn).Find(findIt) is trying to access column zero which will cause the error. How do I modify the code above so that ReplaceArray is populated with the data in the cells starting at A5 and continuing thru column D and the last row on the worksheet? Ken Loomis "Norman Jones" wrote in message ... Hi Ken, Until I added and deleted some worksheets ... In your code line: Set rFound = Columns(InColumn).Find(findIt) Columns is not qualified and, therefore, refes to the active sheet. An added worksheet becomes the active sheet and Columns may reference the wrong sheet. It is much safer to explicitly qualify your ranges: Set RFound = Sheets("Replace Info").Columns(InColumn).Find(findit) This way, the correct range will be referenced even if you add or delete sheets. --- Regards, Norman "Ken Loomis" wrote in message ... This piece of code worked just fine: Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Worksheets(2) Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng searches = UBound(ReplaceArray, 1) For srchCnt = 1 To searches findIt = ReplaceArray(srchCnt, 1) ReplaceWith = ReplaceArray(srchCnt, 2) InColumn = ReplaceArray(srchCnt, 3) Set rFound = Columns(InColumn).Find(findIt) - code snipped here <- Until I added and deleted some worksheets. Now I am getting a run-time error 1004 at the last line of that code and Rng1 = the value from cell A5. So I tried this: With Worksheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With And this, With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With But neither of those work. Can someone tell me how to fix this? And, if possible tell me what I am not getting about this? Maybe suggest some online reading. I have already ordered 2 books that were earlier suggested, but I need to get this project done before they get here. Thanks for any help and insights. Ken Loomis |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time error 1004????
Hi Ken,
Try Changing: Set Rng2 = .Cells(Rows.Count, "D").End(xlUp) To: Set Rng2 = .Cells(Rows.Count, "C").End(xlUp) --- Regards, Norman "Ken Loomis" wrote in message ... Thanks for the comments. They are helping me understand better what is being references. I have used the debugger to get a little further in understanding why I get the error, but still don't know how to fix it. Given this piece of code: Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng And this data in the "Replace Info" worksheet: PLACE PL 3 APT # 3 UNIT # 3 Note: Place is in cell A5 I would expect ReplaceArray to contain: ReplaceArray(1,1) = "PLACE" ReplaceArray(1,2) = "PL" ReplaceArray(1,3) = 3 ReplaceArray(2,1) = "APT" ReplaceArray(2,2) = "#" ReplaceArray(2,3) = 3 ReplaceArray(3,1) = "UNIT" ReplaceArray(3,2) = "#" ReplaceArray(3,3) = 3 But instead, the first row of ReplaceArray contains the data from cells A1, B1, C1. Since C1 is empty, ReplaceArray(1,3) = 0. The InColumn get assigned the value of ReplaceArray(1,3) or zero. So the statement that causes the 1004 error: Set rFound = Columns(InColumn).Find(findIt) is trying to access column zero which will cause the error. How do I modify the code above so that ReplaceArray is populated with the data in the cells starting at A5 and continuing thru column D and the last row on the worksheet? Ken Loomis "Norman Jones" wrote in message ... Hi Ken, Until I added and deleted some worksheets ... In your code line: Set rFound = Columns(InColumn).Find(findIt) Columns is not qualified and, therefore, refes to the active sheet. An added worksheet becomes the active sheet and Columns may reference the wrong sheet. It is much safer to explicitly qualify your ranges: Set RFound = Sheets("Replace Info").Columns(InColumn).Find(findit) This way, the correct range will be referenced even if you add or delete sheets. --- Regards, Norman "Ken Loomis" wrote in message ... This piece of code worked just fine: Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Worksheets(2) Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng searches = UBound(ReplaceArray, 1) For srchCnt = 1 To searches findIt = ReplaceArray(srchCnt, 1) ReplaceWith = ReplaceArray(srchCnt, 2) InColumn = ReplaceArray(srchCnt, 3) Set rFound = Columns(InColumn).Find(findIt) - code snipped here <- Until I added and deleted some worksheets. Now I am getting a run-time error 1004 at the last line of that code and Rng1 = the value from cell A5. So I tried this: With Worksheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With And this, With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With But neither of those work. Can someone tell me how to fix this? And, if possible tell me what I am not getting about this? Maybe suggest some online reading. I have already ordered 2 books that were earlier suggested, but I need to get this project done before they get here. Thanks for any help and insights. Ken Loomis |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time error 1004????
Thanks Norman that was it.
"Norman Jones" wrote in message ... Hi Ken, Try Changing: Set Rng2 = .Cells(Rows.Count, "D").End(xlUp) To: Set Rng2 = .Cells(Rows.Count, "C").End(xlUp) --- Regards, Norman "Ken Loomis" wrote in message ... Thanks for the comments. They are helping me understand better what is being references. I have used the debugger to get a little further in understanding why I get the error, but still don't know how to fix it. Given this piece of code: Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng And this data in the "Replace Info" worksheet: PLACE PL 3 APT # 3 UNIT # 3 Note: Place is in cell A5 I would expect ReplaceArray to contain: ReplaceArray(1,1) = "PLACE" ReplaceArray(1,2) = "PL" ReplaceArray(1,3) = 3 ReplaceArray(2,1) = "APT" ReplaceArray(2,2) = "#" ReplaceArray(2,3) = 3 ReplaceArray(3,1) = "UNIT" ReplaceArray(3,2) = "#" ReplaceArray(3,3) = 3 But instead, the first row of ReplaceArray contains the data from cells A1, B1, C1. Since C1 is empty, ReplaceArray(1,3) = 0. The InColumn get assigned the value of ReplaceArray(1,3) or zero. So the statement that causes the 1004 error: Set rFound = Columns(InColumn).Find(findIt) is trying to access column zero which will cause the error. How do I modify the code above so that ReplaceArray is populated with the data in the cells starting at A5 and continuing thru column D and the last row on the worksheet? Ken Loomis "Norman Jones" wrote in message ... Hi Ken, Until I added and deleted some worksheets ... In your code line: Set rFound = Columns(InColumn).Find(findIt) Columns is not qualified and, therefore, refes to the active sheet. An added worksheet becomes the active sheet and Columns may reference the wrong sheet. It is much safer to explicitly qualify your ranges: Set RFound = Sheets("Replace Info").Columns(InColumn).Find(findit) This way, the correct range will be referenced even if you add or delete sheets. --- Regards, Norman "Ken Loomis" wrote in message ... This piece of code worked just fine: Dim Rng1 As Range, Rng2 As Range Dim MyRng As Range With Worksheets(2) Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With Set MyRng = Range(Rng1, Rng2) ReplaceArray = MyRng searches = UBound(ReplaceArray, 1) For srchCnt = 1 To searches findIt = ReplaceArray(srchCnt, 1) ReplaceWith = ReplaceArray(srchCnt, 2) InColumn = ReplaceArray(srchCnt, 3) Set rFound = Columns(InColumn).Find(findIt) - code snipped here <- Until I added and deleted some worksheets. Now I am getting a run-time error 1004 at the last line of that code and Rng1 = the value from cell A5. So I tried this: With Worksheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With And this, With Sheets("Replace Info") Set Rng1 = .Range("A5") '<<=== Given Start cell Set Rng2 = .Cells(Rows.count, "D").End(xlUp) End With But neither of those work. Can someone tell me how to fix this? And, if possible tell me what I am not getting about this? Maybe suggest some online reading. I have already ordered 2 books that were earlier suggested, but I need to get this project done before they get here. Thanks for any help and insights. Ken Loomis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error 1004... | Excel Discussion (Misc queries) | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
Run-time error 1004 | Excel Programming | |||
Run Time Error 1004 | Excel Programming | |||
Run time error 1004 | Excel Programming |