PDA

View Full Version : [SOLVED] Coding alternating blank lines w emails



JG85
01-09-2009, 08:43 AM
I have a list of 4200 emails in column A in the format of xxxx@yyyy.com. I need to code something like this:

edit 1
set email-pattern "spam@spammer.com"
next
edit 2
set email-pattern "badguy@spam.com"
next


Any help or places to look for info would be great! Thanks!

lucas
01-09-2009, 08:52 AM
Welcome to the forum. You want a blank line between each email addy?

Artik
01-09-2009, 08:54 AM
I do not understand your problem. Try again.

Artik

JG85
01-09-2009, 08:59 AM
Thank you! Sorry, i misworded. I dont need to insert a blank line, i meant i needed to insert the text above. For instance, Column A has list of emails. I need to insert the text around the emails. Example below:

Looks like this now:

Column A
Spam@spammer.com
Badguy@spam.com
xxxxxx@yyyyy.com

Trying to get it to look like this:

Column A
edit 1
set email-pattern "spam@spammer.com"
next
edit 2
set email-pattern "badguy@spam.com"
next
edit 3
set email-pattern xxxxx@yyyy.com
next

Bob Phillips
01-09-2009, 09:05 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 0 Step -1
If i < LastRow Then
.Rows(i + 1).Insert
With .Cells(i + 1, TEST_COLUMN)
.Value = "edit" & i + 1
.Font.Underline = xlUnderlineStyleNone
.Font.ColorIndex = 0
End With
End If
If i > 0 Then
.Rows(i + 1).Insert
With .Cells(i + 1, TEST_COLUMN)
.Value = "next"
.Font.Underline = xlUnderlineStyleNone
.Font.ColorIndex = 0
End With
.Cells(i, TEST_COLUMN).Value = "set email-pattern " & .Cells(i, TEST_COLUMN).Value
End If
Next i
End With
End Sub

JG85
01-09-2009, 09:13 AM
Wow that was quick! what do i need to add to put the emails in paranthesis?

Bob Phillips
01-09-2009, 09:25 AM
I had written it after you first post, you beat me to the draw



Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 0 Step -1
If i < LastRow Then
.Rows(i + 1).Insert
With .Cells(i + 1, TEST_COLUMN)
.Value = "edit" & i + 1
.Font.Underline = xlUnderlineStyleNone
.Font.ColorIndex = 0
End With
End If
If i > 0 Then
.Rows(i + 1).Insert
With .Cells(i + 1, TEST_COLUMN)
.Value = "next"
.Font.Underline = xlUnderlineStyleNone
.Font.ColorIndex = 0
End With
.Cells(i, TEST_COLUMN).Value = "set email-pattern """ & .Cells(i, TEST_COLUMN).Value & """"
End If
Next i
End With
End Sub