Another E-mail problem

N

N1KO

I need some help with the formula below,

Sub EmailAddresses()

Sheets("Distbun").Activate
Range("C3").Select
Do
If IsNumeric(ActiveCell.Offset(0, -1).Value) Then
ActiveCell.Value = "=Text(B3, "000000") & "@" & "xxx.com""

End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row = 1852
End Sub

I need to select C3 then check C2 to see whether its numberic. If it is i
need the value from C2 to be entered in C3 in the format of" 000000" (6
digits [including 0's at the front if necessary]) then @xxx.com

Then i need it to loop until row 1852 (roughly)

It's doing my nut in as it seems i can't make a cell contents equal the
formula, which in turn will enter the e-mail address!

All help is appreciated!
 
R

Rick Rothstein

You didn't say you wanted to create the formula through VB; you don't have your quote marks balanced correctly (you need to use two quote marks for each quote mark that is to appear in the text). Try replacing this line...
ActiveCell.Value = "=Text(B3, "000000") & "@" & "xxx.com""

with this...

ActiveCell.Formula= "=Text(B3,""000000"")&""@""&""xxx.com"""

although I note that you are using the "xxx" and not using a variable or cell reference which means we can shorten that a little bit...

ActiveCell.Formula = "=Text(B3,""000000"")&""@xxx.com"""

Also note that I am assigning the text to the Formula property of the range so that it will become an active formula in the worksheet.

--
Rick (MVP - Excel)


N1KO said:
I need some help with the formula below,

Sub EmailAddresses()

Sheets("Distbun").Activate
Range("C3").Select
Do
If IsNumeric(ActiveCell.Offset(0, -1).Value) Then
ActiveCell.Value = "=Text(B3, "000000") & "@" & "xxx.com""

End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row = 1852
End Sub

I need to select C3 then check C2 to see whether its numberic. If it is i
need the value from C2 to be entered in C3 in the format of" 000000" (6
digits [including 0's at the front if necessary]) then @xxx.com

Then i need it to loop until row 1852 (roughly)

It's doing my nut in as it seems i can't make a cell contents equal the
formula, which in turn will enter the e-mail address!

All help is appreciated!
 
N

N1KO

Hi Rick,

Thanks for that it works, now i need to make it so "B3" become "B4", "B5",
etc, etc when i loop it down to row 1852.

Is there any possibility of this?

Rick Rothstein said:
You didn't say you wanted to create the formula through VB; you don't have your quote marks balanced correctly (you need to use two quote marks for each quote mark that is to appear in the text). Try replacing this line...
ActiveCell.Value = "=Text(B3, "000000") & "@" & "xxx.com""

with this...

ActiveCell.Formula= "=Text(B3,""000000"")&""@""&""xxx.com"""

although I note that you are using the "xxx" and not using a variable or cell reference which means we can shorten that a little bit...

ActiveCell.Formula = "=Text(B3,""000000"")&""@xxx.com"""

Also note that I am assigning the text to the Formula property of the range so that it will become an active formula in the worksheet.

--
Rick (MVP - Excel)


N1KO said:
I need some help with the formula below,

Sub EmailAddresses()

Sheets("Distbun").Activate
Range("C3").Select
Do
If IsNumeric(ActiveCell.Offset(0, -1).Value) Then
ActiveCell.Value = "=Text(B3, "000000") & "@" & "xxx.com""

End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row = 1852
End Sub

I need to select C3 then check C2 to see whether its numberic. If it is i
need the value from C2 to be entered in C3 in the format of" 000000" (6
digits [including 0's at the front if necessary]) then @xxx.com

Then i need it to loop until row 1852 (roughly)

It's doing my nut in as it seems i can't make a cell contents equal the
formula, which in turn will enter the e-mail address!

All help is appreciated!
 
R

Rick Rothstein

I've modified your code to get rid of all the Activates and Selects (it is almost never required to actually activate sheets or select cells to manipulate them) and, in addition, do what you asked for. See if this works for you...

Sub EmailAddresses()
With Sheets("Distbun")
For X = 3 To 1852
With .Cells(X, "B")
If IsNumeric(.Value) Then
.Formula = "=Text(B" & X & ",""000000"")&""@xxx.com"""
End If
End With
Next
End With
End Sub

--
Rick (MVP - Excel)


N1KO said:
Hi Rick,

Thanks for that it works, now i need to make it so "B3" become "B4", "B5",
etc, etc when i loop it down to row 1852.

Is there any possibility of this?

Rick Rothstein said:
You didn't say you wanted to create the formula through VB; you don't have your quote marks balanced correctly (you need to use two quote marks for each quote mark that is to appear in the text). Try replacing this line...
ActiveCell.Value = "=Text(B3, "000000") & "@" & "xxx.com""

with this...

ActiveCell.Formula= "=Text(B3,""000000"")&""@""&""xxx.com"""

although I note that you are using the "xxx" and not using a variable or cell reference which means we can shorten that a little bit...

ActiveCell.Formula = "=Text(B3,""000000"")&""@xxx.com"""

Also note that I am assigning the text to the Formula property of the range so that it will become an active formula in the worksheet.

--
Rick (MVP - Excel)


N1KO said:
I need some help with the formula below,

Sub EmailAddresses()

Sheets("Distbun").Activate
Range("C3").Select
Do
If IsNumeric(ActiveCell.Offset(0, -1).Value) Then
ActiveCell.Value = "=Text(B3, "000000") & "@" & "xxx.com""

End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row = 1852
End Sub

I need to select C3 then check C2 to see whether its numberic. If it is i
need the value from C2 to be entered in C3 in the format of" 000000" (6
digits [including 0's at the front if necessary]) then @xxx.com

Then i need it to loop until row 1852 (roughly)

It's doing my nut in as it seems i can't make a cell contents equal the
formula, which in turn will enter the e-mail address!

All help is appreciated!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

E-mail addresses 2
Efficient looping 10
Bigger Problem 0
Error 2042 problem with vlookup 0
Excel hangs 1
Returning vars to calling sub 4
Returning values to calling sub 0
Help with formula in vb coding 5

Top