Basic problem is that not ALL have a phone number or email which was NOT
mentioned. So, a more extensive re-write is required. What is this for?
Don Guillett
SalesAid Software
dguille...@gmail.com
From: Cab Boose
Sent: Friday, December 16, 2011 3:46 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ VBA select data in BOLD
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
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
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
