Hi Don & Noorain
Thanks for your comments and ideas.
I am taking one of our address lists and reducing the information in it as
we will be doing everything by email and phone. Address etc not now
required. Will also once this first task finished then classify into
Catergory types for industries etc. A small 7 person business with some
manufacturing and also online sales being developed. Overall it leaves us
with just the information we need. The example is only a portion of the
workbook.
My apologies for not mentioning that not all companies will have a phone #
and/or email address.
Regards
Charlie Harris
On Sun, Dec 18, 2011 at 5:22 AM, NOORAIN ANSARI <noorain.ans...@gmail.com>wrote:
> Dear Cab Boose,
>
> Please use try function.
>
> Copy this formula and paste it in define name and provide name range *
> IsBold
> =GET.CELL(20,INDIRECT("RC[1]",FALSE))
>
> See attached sheet for reference
> *
> --
> Thanks & regards,
> Noorain Ansari
> *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/>
> *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/>
>
> On Sat, Dec 17, 2011 at 3:16 AM, Cab Boose <swch...@gmail.com> wrote:
>
>> Hi Don
>>
>> The new code works well for the first few lines and then starts to repeat
>> the same Phone number and same email address even though it has the company
>> name correct. A strange thing. Note that athe repeat starts on the same
>> row as the first email in G ?
>>
>> See attached.
>>
>> Thanks and regards
>>
>> Charlie
>>
>> On Sat, Dec 17, 2011 at 3:29 AM, dguillett1 <dguille...@gmail.com> wrote:
>>
>>> should do it
>>>
>>> Option Explicit
>>> Sub foreachcoSAS()
>>> Dim lr As Long
>>> Dim dlr As Long
>>> Dim i As Long
>>> Dim ph As Long
>>> Dim em As Long
>>> Dim c As Range
>>> i = 1
>>> lr = Cells(Rows.Count, "g").End(xlUp).Row
>>> For Each c In Range("g1:g" & lr)
>>> dlr = Cells(Rows.Count, "H").End(xlUp).Row + 1
>>> If c.Font.Bold Then
>>> 'MsgBox c
>>> ph = Columns("g").Find(What:="ph", After:=Cells(i, "G"),
>>> LookIn:=xlValues, _
>>> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
>>> 'MsgBox ph
>>> em = Columns("g").Find(What:="email", After:=Cells(i, "G"),
>>> LookIn:=xlValues, _
>>> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
>>> 'MsgBox em
>>> Cells(dlr, "H").Value = Cells(c.Row, "g").Value
>>> Cells(dlr, "i").Value = Cells(ph, "g").Value
>>> Cells(dlr, "j").Value = Cells(em, "g").Value
>>> i = i + em
>>> End If
>>> Next
>>> Columns("H:j").AutoFit
>>> End Sub
>>>
>>> Don Guillett
>>> SalesAid Software
>>> dguille...@gmail.com
>>>
>>> *From:* Cab Boose <swch...@gmail.com>
>>> *Sent:* Friday, December 16, 2011 12:28 AM
>>> *To:* excel-macros@googlegroups.com
>>> *Subject:* Re: $$Excel-Macros$$ VBA select data in BOLD
>>>
>>> Hi Don
>>> Thanks for code. Wow it works great and fast.
>>> In the line of code that puts the info into col H , how do I put into
>>> 3 different colums ? Say H, I, J.
>>> I could run text to columns on the result but would prefer to do it
>>> with code
>>> Thanks
>>> Charlie
>>>
>>>
>>> On Fri, Dec 16, 2011 at 1:56 PM, dguillett1 <dguille...@gmail.com>wrote:
>>>
>>>> One way
>>>>
>>>> Option Explicit
>>>>
>>>> Sub foreachcoSAS()
>>>> Dim lr As Long
>>>> Dim i As Long
>>>> Dim ph As Long
>>>> Dim em As Long
>>>> Dim c As Range
>>>> Dim ms As String
>>>>
>>>> i = 1
>>>> lr = Cells(Rows.Count, "g").End(xlUp).Row
>>>> For Each c In Range("g1:g" & lr)
>>>> If c.Font.Bold Then
>>>> 'MsgBox c
>>>> ph = Columns("g").Find(What:="ph", After:=Cells(i, "G"),
>>>> LookIn:=xlValues, _
>>>> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
>>>> 'MsgBox ph
>>>> em = Columns("g").Find(What:="email", After:=Cells(i, "G"),
>>>> LookIn:=xlValues, _
>>>> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
>>>> 'MsgBox em
>>>> ms = Cells(c.Row, "g") & " " & Cells(ph, "g") & " " & Cells(em, "g")
>>>> Cells(Rows.Count, "h").End(xlUp).Offset(1) = ms
>>>> 'MsgBox ms
>>>> i = i + em
>>>> End If
>>>> Next
>>>> Columns("H").AutoFit
>>>> End Sub
>>>>
>>>> Don Guillett
>>>> SalesAid Software
>>>> dguille...@gmail.com
>>>>
>>>> *From:* Cab Boose <swch...@gmail.com>
>>>> *Sent:* Thursday, December 15, 2011 4:12 PM
>>>> *To:* excel-macros@googlegroups.com
>>>> *Subject:* $$Excel-Macros$$ VBA select data in BOLD
>>>>
>>>> Hi
>>>>
>>>> Have addresses like this: (columnG)
>>>>
>>>> ABC COMPANY
>>>> 123 Delta St
>>>> Town
>>>> Ph 0800123456
>>>> fax 234567
>>>> email 1...@dfg.com
>>>>
>>>> Sometimes there are more rows with irrelvant informtation
>>>>
>>>> I want to select 3 of the rows :
>>>>
>>>> ABC COMPANY Ph 0800123456 email 1...@dfg.com
>>>>
>>>> And place them in a single row in column A, B, C
>>>>
>>>> Company line will always be BOLD
>>>> Phone line will always start with 'Ph'
>>>> Email line will always start with 'email' or have the '@'
>>>>
>>>>
>>>> Once done the original codes will all be deleted leaving just the 3
>>>> columns A-C
>>>> I will do a sort then and get all the blank rows deleted.
>>>>
>>>>
>>>>
>>>>
>>>> For Each cell In RangeG:
>>>> If cell.Font.Bold = True Then
>>>> Range.SelectA:
>>>> Paste
>>>> ......................I have got lost already.
>>>>
>>>>
>>>> Can you give some ideas for me.
>>>>
>>>> Thanks
>>>>
>>>>
>>>> Charlie Harris
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> FORUM RULES (934+ members already BANNED for violation)
>>>>
>>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>>> will not get quick attention or may not be answered.
>>>>
>>>> 2) Don't post a question in the thread of another member.
>>>>
>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>> measure.
>>>>
>>>> 4) Acknowledge the responses you receive, good or bad.
>>>>
>>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>>> signatures are prohibited.
>>>>
>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>> Forum owners and members are not responsible for any loss.
>>>>
>>>>
>>>> ------------------------------------------------------------------------------------------------------
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>> --
>>>> FORUM RULES (934+ members already BANNED for violation)
>>>>
>>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>>> will not get quick attention or may not be answered.
>>>>
>>>> 2) Don't post a question in the thread of another member.
>>>>
>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>> measure.
>>>>
>>>> 4) Acknowledge the responses you receive, good or bad.
>>>>
>>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>>> signatures are prohibited.
>>>>
>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>> Forum owners and members are not responsible for any loss.
>>>>
>>>>
>>>> ------------------------------------------------------------------------------------------------------
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>
>>>
>>> --
>>> FORUM RULES (934+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> ------------------------------------------------------------------------------------------------------
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> --
>>> FORUM RULES (934+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> ------------------------------------------------------------------------------------------------------
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>
>> --
>> FORUM RULES (934+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com
>
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
