Dear Guys
Good Morning
Can you amend my vba as per attached link
https://www.dropbox.com/s/mqecoiuv3k...ING3.xlsm?dl=0
Cell A6 JOHN MR SMITH AND MR STEVE JACK result should be Dear Messrs in B6 and Banking Facilities in cell C6.
tHANKS/REGARDS
RAKESH
Hi Rakesh,
try this
'.......
For Each currentCell In targetRange
If InStr(1, LCase$(currentCell), "mr ", vbBinaryCompare) > 0 And _
InStr(1, LCase$(currentCell), "mrs ", vbBinaryCompare) > 0 Then
currentCell.Offset(, salutationColumn - 1) = "Dear Sir/Madame"
currentCell.Offset(, commentColumn - 1) = "Banking Facilities"
ElseIf InStr(1, LCase$(currentCell), "mrs ", vbBinaryCompare) > 0 Then
currentCell.Offset(, salutationColumn - 1) = "Dear Madame"
currentCell.Offset(, commentColumn - 1) = "Banking Facility"
''''''''''''''''added begin '''''''''''
ElseIf LCase$(currentCell) Like "*mr*mr*" Then
currentCell.Offset(, salutationColumn - 1) = "Dear Messrs"
currentCell.Offset(, commentColumn - 1) = "Banking Facilities"
''''''''''''added end '''''''''''''''''''
ElseIf InStr(1, LCase$(currentCell), "mr ", vbBinaryCompare) > 0 Then
currentCell.Offset(, salutationColumn - 1) = "Dear Sir"
currentCell.Offset(, commentColumn - 1) = "Banking Facility"
ElseIf InStr(1, LCase$(currentCell), "miss ", vbBinaryCompare) > 0 Then
currentCell.Offset(, salutationColumn - 1) = "Dear Miss"
currentCell.Offset(, commentColumn - 1) = "Banking Facility"
End If
Next currentCell
'.......thanks nilem . you are genius. you are a boss.
regards
rakesh
Hi Nilem
I refer to the above , there are individual names in my previous sheets. If i have both individual names and companies name instead .For individual names it's working fine but for comapnies it's not working
For example
ABC LTD
CBD LTEE
CAA LIMITED
result should be "The Managing Director" in B6 and " your banking faciity "in C6.
can you help
thanks/regards
rakesh
Don’t have an account yet? Register yourself now and be a part of our community!