Replace a column of numbers with specific phrases

[Henner]

Hi,

Firstly, many thanks to tbardoni, Brian, Juan and Aladin for your help yesterday. Well done guys.

In column 'C' on my spreadsheet, I would like to replace all the zero's with the function:
="Expected "&TEXT(WORKDAY(TODAY(),2),"dd-mmm-yy")

Also, where there is a number greater than zero in column C, I would like to add the word 'updated' after the number (e.g. 1 updated, 27 updated).

Column C contains about 30,000 numbers so if anyone can tell me how I can do this in one go (a macro ?), it would be appreciated.

Thanks,
Paul

 

[maxflia10]

Paul,

I'm pretty sure, you can't do it with a formula, as you'll get a circular reference. You can do it in another column with an IF formula

=if(c1>=0,updated,"")

 

[Jim North]

Paul,

I believe this will do it... be sure to back it up before using it!!! ... and Ian gets part of the credit!!

<pre>

Sub InsertFormula()

Dim Sell As Range
Dim LastRow As Long
Dim Rng As Range
Const frm As String = "=""Expected "" & Text(WORKDAY(TODAY(), 2), ""dd-mmm-yy"")"


Dim firstaddress As String

' turn off screen updating
Application.ScreenUpdating = False

' get the number of rows used on Sheet1
Range("C65536").Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row

' point to the data from Sheet 1
Set Rng = Range("C1:C" & LastRow)

For Each Sell In Rng
' find the value in sheet 2 column E
If Sell.Value = 0 Then
' insert formula
Range(Sell.Address).Formula = "=""Expected "" & Text(WORKDAY(TODAY(), 2), ""dd-mmm-yy"")"
Else
' replace the value
Sell.Value = Sell.Value & " Updated"

End If

Next Sell

'point to cell A1 on Sheet1
Range("C1").Select
Application.ScreenUpdating = True

End Sub




</pre>

 

[Henner]

Hi Jim,

You are a complete and utter genius (and your friend, Ian)!

Thanks a million for your help.

Best regards,
Paul
This message was edited by Henner on 2002-09-03 11:56

 

[Henner]

This message was edited by Henner on 2002-09-03 11:57