If Cannot find skip???
 

Hi,

I have built a report that pulls in information from another
spreadsheet. The information I take from this spread sheet can be in
various locations on the spreadsheet and therefore I'm unable to point
to a specific cell.

As each piece of information that I take from the sheet has a title, I
have used the recorder to find the specific titles. However there is
one part of the spreadsheet that does not always come through it
depends if the user has selected it. This is where my report is falling
over its basically saying find e.g. Street Address and as it can't find
it it is debugging.

What I want it to do is find street address if its there then carry on
with the code if not then skip to the next part. I have tried an IF but
I must be doing it wrong as I cant get it to work.

I have copied the part of the code I have recorded below as this may
assist:

It would be good if I could wrap something around it saying that if it
cant find "Street Address" then skip this part.

Cells.Find(What:="Street Address", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(rowoffset:=4, columnoffset:=0).Select
Range(Selection, Selection.Offset(15, 0)).Select
Selection.Copy
Windows("Book1.xls").Activate
Worksheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11,
1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1),
Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24,
1), Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1)), TrailingMinusNumbers:=True
Range("A1").Select

Any help would be much appriciated

Many Thanks

If Cannot find skip???
 

In general, you can use a range object variable to represent the found cell.

Dim FoundCell as Range
....
Set Foundcell = cells.find(what:=.....)

Then test that to see if your string was found:

if foundcell is nothing then
'skip it
else
'do all the work
end if



wrote:

Hi,

I have built a report that pulls in information from another
spreadsheet. The information I take from this spread sheet can be in
various locations on the spreadsheet and therefore I'm unable to point
to a specific cell.

As each piece of information that I take from the sheet has a title, I
have used the recorder to find the specific titles. However there is
one part of the spreadsheet that does not always come through it
depends if the user has selected it. This is where my report is falling
over its basically saying find e.g. Street Address and as it can't find
it it is debugging.

What I want it to do is find street address if its there then carry on
with the code if not then skip to the next part. I have tried an IF but
I must be doing it wrong as I cant get it to work.

I have copied the part of the code I have recorded below as this may
assist:

It would be good if I could wrap something around it saying that if it
cant find "Street Address" then skip this part.

Cells.Find(What:="Street Address", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(rowoffset:=4, columnoffset:=0).Select
Range(Selection, Selection.Offset(15, 0)).Select
Selection.Copy
Windows("Book1.xls").Activate
Worksheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11,
1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1),
Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24,
1), Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1)), TrailingMinusNumbers:=True
Range("A1").Select

Any help would be much appriciated

Many Thanks

--

Dave Peterson

If Cannot find skip???
 

Thanks Dave,

That is brilliant.


Dave Peterson wrote:

In general, you can use a range object variable to represent the found cell.

Dim FoundCell as Range
...
Set Foundcell = cells.find(what:=.....)

Then test that to see if your string was found:

if foundcell is nothing then
'skip it
else
'do all the work
end if



wrote:

Hi,

I have built a report that pulls in information from another
spreadsheet. The information I take from this spread sheet can be in
various locations on the spreadsheet and therefore I'm unable to point
to a specific cell.

As each piece of information that I take from the sheet has a title, I
have used the recorder to find the specific titles. However there is
one part of the spreadsheet that does not always come through it
depends if the user has selected it. This is where my report is falling
over its basically saying find e.g. Street Address and as it can't find
it it is debugging.

What I want it to do is find street address if its there then carry on
with the code if not then skip to the next part. I have tried an IF but
I must be doing it wrong as I cant get it to work.

I have copied the part of the code I have recorded below as this may
assist:

It would be good if I could wrap something around it saying that if it
cant find "Street Address" then skip this part.

Cells.Find(What:="Street Address", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(rowoffset:=4, columnoffset:=0).Select
Range(Selection, Selection.Offset(15, 0)).Select
Selection.Copy
Windows("Book1.xls").Activate
Worksheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11,
1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1),
Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24,
1), Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1)), TrailingMinusNumbers:=True
Range("A1").Select

Any help would be much appriciated

Many Thanks

--

Dave Peterson