Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another E-mail problem
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another E-mail problem
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= """ 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 = """ 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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another E-mail problem
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" wrote: 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= """ 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 = """ 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" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another E-mail problem
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 & """ End If End With Next End With End Sub -- Rick (MVP - Excel) "N1KO" wrote in message ... 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" wrote: 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= """ 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 = """ 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" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail Merge Problem | Excel Discussion (Misc queries) | |||
Send mail problem | Excel Programming | |||
Problem in mail attachment when using CDO | Excel Programming | |||
Problem with Mail code | Excel Programming | |||
Mail merge-problem | Excel Discussion (Misc queries) |