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

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

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