|
If Cells.Find can't find anything
Excel Experts,
I think I want to create an If/Then structure based on whether Cells.Find finds what it is looking for. More specifically, My code copies a number of values from Workbook "NCEntry" to "MNC". Each of the values has a label in the cell to the left of it. Some of the values are always there but others are only there on some days. Simplified, my workbooks and code are as follows: Workbook "NCEntry" A B 1 MnrT4 500 2 MnrWA 1000 Workbook "NCEntry" A B 1 4TT4 500 2 77WA 1000 <Code Sub Update4TT4_4TT5_77WAWB() Windows("NCEntry").Activate Sheets("IEAccts").Select On Error Resume Next Cells.Find(What:="MnrT4").Offset(, 1).Select Set MnrT4 = Selection Cells.Find(What:="MnrWA").Offset(, 1).Select Set MnrWA = Selection Windows("MNC").Activate Sheets("NetCap").Select Cells.Find(What:="4TT4", After:=ActiveCell, SearchOrder:=xlByColumns, _ SearchDirection:=xlNext).Offset(, 1).Select Selection.Formula = "=" & MnrT4.Value & "+RC[1]" Cells.Find(What:="77WA", After:=ActiveCell, SearchOrder:=xlByColumns, _ SearchDirection:=xlNext).Offset(, 1).Select Selection.ClearContents Selection.Value = MnrWA End Sub My problem is that the entry MnrWA is not on sheet NCEntry every day. On those days, the variable MnrWA gets set to the value for MnrT4 since that was the last cell selected. What I want to add to my code is something that says If Cells.Find(What:="MnrWA").Offset(,1).Select doesn't find anything Then Do Nothing Else Set MnrWA = Selection End If What code would I add to do this. I recognize that I could change the structure to Set MnrWA = Cells.Find(What:="MnrWA").Offset(,1) but I need to use a similar test in other situations so I am interested in how to do it with the If..Then structure. Thanks in advance, Alan -- achidsey |
If Cells.Find can't find anything
Any time you sue find code you really need to use a range object to deal with
exactly this issue of not finding anything dim rngFound as Range set rngFound = Cells.Find("MnrT4") if rngfound is nothing then msgbox "Couldn't Find MnrT4" else rngfound.select endif or something like that... -- HTH... Jim Thomlinson "achidsey" wrote: Excel Experts, I think I want to create an If/Then structure based on whether Cells.Find finds what it is looking for. More specifically, My code copies a number of values from Workbook "NCEntry" to "MNC". Each of the values has a label in the cell to the left of it. Some of the values are always there but others are only there on some days. Simplified, my workbooks and code are as follows: Workbook "NCEntry" A B 1 MnrT4 500 2 MnrWA 1000 Workbook "NCEntry" A B 1 4TT4 500 2 77WA 1000 <Code Sub Update4TT4_4TT5_77WAWB() Windows("NCEntry").Activate Sheets("IEAccts").Select On Error Resume Next Cells.Find(What:="MnrT4").Offset(, 1).Select Set MnrT4 = Selection Cells.Find(What:="MnrWA").Offset(, 1).Select Set MnrWA = Selection Windows("MNC").Activate Sheets("NetCap").Select Cells.Find(What:="4TT4", After:=ActiveCell, SearchOrder:=xlByColumns, _ SearchDirection:=xlNext).Offset(, 1).Select Selection.Formula = "=" & MnrT4.Value & "+RC[1]" Cells.Find(What:="77WA", After:=ActiveCell, SearchOrder:=xlByColumns, _ SearchDirection:=xlNext).Offset(, 1).Select Selection.ClearContents Selection.Value = MnrWA End Sub My problem is that the entry MnrWA is not on sheet NCEntry every day. On those days, the variable MnrWA gets set to the value for MnrT4 since that was the last cell selected. What I want to add to my code is something that says If Cells.Find(What:="MnrWA").Offset(,1).Select doesn't find anything Then Do Nothing Else Set MnrWA = Selection End If What code would I add to do this. I recognize that I could change the structure to Set MnrWA = Cells.Find(What:="MnrWA").Offset(,1) but I need to use a similar test in other situations so I am interested in how to do it with the If..Then structure. Thanks in advance, Alan -- achidsey |
If Cells.Find can't find anything
Jim, Thanks again. Alan -- achidsey "achidsey" wrote: Excel Experts, I think I want to create an If/Then structure based on whether Cells.Find finds what it is looking for. More specifically, My code copies a number of values from Workbook "NCEntry" to "MNC". Each of the values has a label in the cell to the left of it. Some of the values are always there but others are only there on some days. Simplified, my workbooks and code are as follows: Workbook "NCEntry" A B 1 MnrT4 500 2 MnrWA 1000 Workbook "NCEntry" A B 1 4TT4 500 2 77WA 1000 <Code Sub Update4TT4_4TT5_77WAWB() Windows("NCEntry").Activate Sheets("IEAccts").Select On Error Resume Next Cells.Find(What:="MnrT4").Offset(, 1).Select Set MnrT4 = Selection Cells.Find(What:="MnrWA").Offset(, 1).Select Set MnrWA = Selection Windows("MNC").Activate Sheets("NetCap").Select Cells.Find(What:="4TT4", After:=ActiveCell, SearchOrder:=xlByColumns, _ SearchDirection:=xlNext).Offset(, 1).Select Selection.Formula = "=" & MnrT4.Value & "+RC[1]" Cells.Find(What:="77WA", After:=ActiveCell, SearchOrder:=xlByColumns, _ SearchDirection:=xlNext).Offset(, 1).Select Selection.ClearContents Selection.Value = MnrWA End Sub My problem is that the entry MnrWA is not on sheet NCEntry every day. On those days, the variable MnrWA gets set to the value for MnrT4 since that was the last cell selected. What I want to add to my code is something that says If Cells.Find(What:="MnrWA").Offset(,1).Select doesn't find anything Then Do Nothing Else Set MnrWA = Selection End If What code would I add to do this. I recognize that I could change the structure to Set MnrWA = Cells.Find(What:="MnrWA").Offset(,1) but I need to use a similar test in other situations so I am interested in how to do it with the If..Then structure. Thanks in advance, Alan -- achidsey |
| All times are GMT +1. The time now is 01:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com