Active.Cell = InStr(Active.Cell.Offset(-1, 0), "Mr & Mrs")
The above is macro that I wrote following a guide I found online, but it doesn't work, and is returning the error of "Object required"
There is data in columnA, and I am just trying to find where the text "Mr & Mrs" starts. If there is a way of running this in such a way that it ends up like the following;
if A1 contains "Mr & Mrs", then InStr(etc.....
As I could then edit it to suit other examples
ActiveCell is one word.
Remove period.
It's still showing the same error
If the activecell is in row1, it will raise the error, otherwise no idea.
Hello officeworker3,
Maybe it would be better if you attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Regards.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
Hi and thanks for the advice guys.
I've managed to do...something in vba, I was attempting to take the information from the left of the "&" into one cell, take the info to the right into another cell, and then split that to be able
to concatenate the Mr and Mrs with the Surname. I'm not sure what I ended up making, but it didn't exactly work, so feel free to remove it.
I only noticed once it's been uploaded, but sheet1 is before, and sheet2 is the after
Thanks again
1. Your sample data is in column A. Therefore, the line:
ActiveCell.Offset(0, -1)is trying to look one column to the left of column A. ---> Error
2. The InStr function returns the position of one string within another, so the line:
ActiveCell.Value = InStr(ActiveCell.Offset(0, -1).Value, "Mr & Mrs")would, if not for the previous error, have replaced the contents of the active cell with a 1 since Mr & Mrs is at the beginning of the string. Offset jumps other cells at a given distance from the achor cell, not to positions within strings in a cell.
Unfortunately, you have other errors...
Please Login or Register to view this content.
For instance, you have:
- Find(" ", b3, 1) which is a worksheet function, not a VBA function. You need to use Instr, but you have to specify where in the string to start.
- Some cells have "&", others "and" and others neither.
- ActiveCell.Value = "Mr" & ActiveCell.Offset(0, 2) is trying to replace the contents of the active cell with "Mr" and contents of the cell 2 columns to the right, i.e. column C
Last edited by protonLeah; 09-20-2018 at 06:32 PM.
Ben Van Johnson
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks