Combining IF statements with FIND function

uppie

New Member
Joined
Jan 14, 2004
Messages
2
I am trying to write VBA code to translate variable text data in Excel. I need to do a FIND on certain text strings that are not always present. If the text string is present, I then need to run delimitors on it and copy and paste the results to another sheet. If it is not present, I need the macro to move on the next FIND for another text string. I currently have all the code written to do the FINDs and manipulating the results, but cannot solve the issue of how to say in the code, IF the FIND has no results, jump to the next FIND. Please help!!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Without seeing your specific code, any responses are going to be a bit generic...

In general a find takes a form along the lines of:

Set rngCell = rngToSearch.Find(What:=varLookingFor, LookAt:=xlPart, LookIn:=xlFormulas)

You can test to see if anything was found by checking to see if the result IS Nothing.

Code:
If rngCell Is Nothing Then
...code if not found...
End If

or alternatively

Code:
If Not rngCell Is Nothing Then
...Code if found...
End If

HTH
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Welcome to the Board!

Might help if you could post the code you're working with?
 

uppie

New Member
Joined
Jan 14, 2004
Messages
2
Thanks for the quick responses. Per your requests, here a sample of the code I have in which I FIND the string "Consultant" on Sheet 1, delimit the cell to the right of the cell where "Consultant" is found, then copy and paste the information to Sheet 11. Immediately after this code, I have it returning to Sheet 1 and starting an exact duplicate of this code, but searching for a different text string. You will no doubt find the code to be a bit archaic and cumbersome. That is because I began by recording the bulk of it using the Macro Record utility.

Cells.Find(What:="Consultant", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(0, 1).Activate
Application.CutCopyMode = False
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar _
:="-", 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))
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet11").Select
ActiveCell.Offset(0, 1).Activate
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Sheet1").Select
ActiveCell.Offset(0, 1).Activate
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet11").Select
ActiveCell.Offset(0, 1).Activate
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Sheet1").Select
ActiveCell.Offset(0, 1).Activate
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet11").Select
ActiveCell.Offset(0, 1).Activate
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Sheet1").Select
ActiveCell.Offset(0, 1).Activate
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet11").Select
ActiveCell.Offset(0, 1).Activate
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Sheet1").Select
ActiveCell.Offset(0, 1).Activate
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet11").Select
ActiveCell.Offset(0, 1).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1))
Selection.Copy
ActiveCell.Offset(0, 1).Activate
Application.CutCopyMode = False
ActiveCell.Offset(0, 1).Activate
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Sheet1").Select
ActiveCell.Offset(0, 1).Activate
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet11").Select
ActiveCell.Offset(0, 1).Activate
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Sheet1").Select
ActiveCell.Offset(0, 1).Activate
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet11").Select
ActiveCell.Offset(0, 1).Activate
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Sheet1").Select
ActiveCell.Offset(0, 1).Activate
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet11").Select
ActiveCell.Offset(0, 1).Activate
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Sheet1").Select
ActiveWindow.LargeScroll ToRight:=-1
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
OK, wow :eek: that macro recorder is a bit of a slob. We'll get that cleaned up in a bit. But first a couple of questions:

1. Can you post an example of the text that is located to the right of "Consultant". It looks like you're double-parsing the fifth (or sixth?) cell of this and I'm trying to understand that bit.
2. How do you know where to paste to on Sheet11. As written, your code is relying on the ActiveCell on Sheet11 being the correct target. Is there a way you can describe how you pick the target cell? E.g. "Last empty cell in column A".
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top