Another E-mail problem

[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!

 

[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)

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!

 

[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?

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)

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!

 

[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)

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?

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)

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!