PDA

View Full Version : [SOLVED] Please Modify this VBA for New Requirement Or Provide a New One



New_Here
04-21-2017, 12:11 PM
Hi Good Evening (http://www.vbaexpress.com/forum/#) from Sri Lanka (http://www.vbaexpress.com/forum/#),

Please help if possible.

The below WAS my requirement and the solution to it is Below,

Data I had :-

Cell A1 = Phone (http://www.vbaexpress.com/forum/#): 81-74568943 (http://www.vbaexpress.com/forum/#) Fax: -
Cell A2 = Phone (http://www.vbaexpress.com/forum/#): 51-83-9498756 (http://www.vbaexpress.com/forum/#), 5583772359 (http://www.vbaexpress.com/forum/#) Fax: 61-88-4932516 (http://www.vbaexpress.com/forum/#)
Cell A3 = Phone (http://www.vbaexpress.com/forum/#): 51-6785239556 (http://www.vbaexpress.com/forum/#), Fax: 31-13-45225335 (http://www.vbaexpress.com/forum/#)
Cell A4 = Phone (http://www.vbaexpress.com/forum/#): 51-6785239556 (http://www.vbaexpress.com/forum/#), Fax: 31-13-45225335 (http://www.vbaexpress.com/forum/#), Mobile (http://www.vbaexpress.com/forum/#): -

Outcome :-

B1 = Phone (http://www.vbaexpress.com/forum/#)
C1 = 81-74568943 (http://www.vbaexpress.com/forum/#)
D1 = Fax
E1 = - (Or N/A - Not the N/A Error (http://www.vbaexpress.com/forum/#)Message (http://www.vbaexpress.com/forum/#) or a value! error (http://www.vbaexpress.com/forum/#)message (http://www.vbaexpress.com/forum/#), But just the letters (http://www.vbaexpress.com/forum/#) : N/A)
F1 = Mobile (http://www.vbaexpress.com/forum/#)
G1 = - (Or N/A - Not the N/A Error (http://www.vbaexpress.com/forum/#)Message (http://www.vbaexpress.com/forum/#) or a value! error (http://www.vbaexpress.com/forum/#)message (http://www.vbaexpress.com/forum/#), But just the letters (http://www.vbaexpress.com/forum/#) : N/A)

The Formula to get it done is below,


http://www.vbaexpress.com/forum/showthread.php?59246-To-Pull-out-Certain-Set-of-Data-from-a-Cell-Urgent&p=360078#post360078


How should I remodel the above in

My Issue;

A1=Contact (http://www.vbaexpress.com/forum/#) Person: James Anderson Designation: Proprietor
A2=Contact (http://www.vbaexpress.com/forum/#) Person: Gordan Designation: Seller
A3=Contact (http://www.vbaexpress.com/forum/#) Person: David Cameron Miller Designation: Vice President

Solution I require:

to
B1=Contact (http://www.vbaexpress.com/forum/#) Person
C1=James Anderson
D1=Designation
E1=Proprietor

B2=Contact (http://www.vbaexpress.com/forum/#) Person
C2=Gordan
D2=Designation
E2=Seller

B3=Contact (http://www.vbaexpress.com/forum/#) Person
C3=David Cameron Miller
D3=Designation
E3=Vice President

?

Please Help !!!

rlv
04-21-2017, 12:33 PM
You are starting a lot of threads on what is basically the same question.

Paul_Hossler
04-21-2017, 12:37 PM
Try this

Test data on sheet 2 of attachment




Function BreakOut2(s As String) As Variant
Dim v As Variant
Dim aOut As Variant
Dim i As Long
Dim s1 As String

Application.Volatile

s1 = s

s1 = Replace(s1, " Designation", ": Designation")

v = Split(s1, ":")

BreakOut2 = v
End Function

rlv
04-21-2017, 12:37 PM
Also cross posted here

https://www.mrexcel.com/forum/excel-questions/1001680-need-udf-identify-remove-parts-cell-data-range.html

mdmackillop
04-21-2017, 01:11 PM
Please provide your samples in an attached workbook. It saves the responders from replicating what you have already created.

New_Here
04-21-2017, 11:03 PM
01. First and Foremost I should Thank all of you for your continues Support given here.

02. Then I apologize if I caused any inconvenience to you guys the Moderators and Admins Here.

03. I didn't know that Mr excel and you guys are linked to each other. Faster responses are found in both places, that is why I post same questions in both the places.

04. Honestly I do not understand how the UDF's or Macro's work. Formulas I normally understand because we directly work it out in the Sheet itself.

05. I press the Quote option and it shows the entire comment of the next user, Guess that seems to create an issue here as well, therefore will make sure to delete parts which are irrelevant.

06. My Questions have slight variances if you see them. In your eyes it may look the same because you guys know the to decode issues.
At times I just need to remove a word like the UDF =RW(Cell) example you had mentioned.
At some other time I need a specific outcome after removing the Word, that has been the difference. I understand from "RW" I could remove one or few words at a time, but the issue starts when Blanks also start to popup... and also it is a difficult process at my end to have so many colomns. That is why I ask for a new UDF.

07. MdMack, sure will try to do that :)


Sorry again and Thank you.

New_Here
04-21-2017, 11:13 PM
Thank you for your reply & support Chief, and it has been a great amount of help from you as well with my current issue. The Names of the Individuals seems to have space and I managed to solve that Through TRIM function..

Thank you very much again...

mdmackillop
04-22-2017, 12:23 AM
03. I didn't know that Mr excel and you guys are linked to each other.
They are not linked but many responders reply in multiple forums - have a read of this (http://www.excelguru.ca/content.php?184)

04. Honestly I do not understand how the UDF's or Macro's work
Google "VBA for beginners" there are many useful resources out there

05. I press the Quote option and it shows the entire comment
Copy and paste the content; use the quote button to add the formatting

Paul_Hossler
04-22-2017, 06:53 AM
03. I didn't know that Mr excel and you guys are linked to each other. Faster responses are found in both places, that is why I post same questions in both the places.

1 - Not linked, but we think it's polite to let us know that other forums might also be working on the question.

This is in our FAQ and explains a little more. There's a nice link at the bottom for more reasons

http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3



2 - Someone mentioned your 3 very similar requests to parse out a string.

If you had put the entire requirement (all three types) in one question and it would likely be possible to generate a single (but smarter) UDF to handle the complete requirement.

(I'm waiting for a 4th :) )

New_Here
04-23-2017, 01:03 AM
(I'm waiting for a 4th :) )

:D

Honestly and Truly I apologize guys. If not for the support you've given me it would have been very difficult for me.... Thanks again. Highly appreciated...