PDA

View Full Version : Solved: if (RC[1]) = 1 then "=TEXT(RC[-1],""MMM/YY"")"



coserria
03-28-2008, 03:14 AM
here we are,

I have dates in Column A or RC1, and in Column B I need the results of "=Text9RC[-1],"MMM/YY")"

This needs to take place if Column A has a value. if it is null I do not want an entry in Column B

her is my question I have I know that I have to set code to check column a for a 1 value. if it true then next cell. this can be performed also as a fill down, but only as far as A has a value.

Can someone give an example that I can look at I i understand how to just look at a single cell

I have this that deletes a row if the value is 0

I understand how it is working for the most part.
Set rng = Range(ws.Rows(1), Rows(ws.Cells.SpecialCells(xlCellTypeLastCell).Row()))
For Rw = rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(Rw).EntireRow) = 0 Then
rng.Rows(Rw).EntireRow.Delete
End If
Next Rw

Bob Phillips
03-28-2008, 03:26 AM
Set rng = Range(ws.Rows(1), Rows(ws.Cells.SpecialCells(xlCellTypeLastCell).Row()))
rng.Columns(2).FormulaR1C1 = "=IF(RC[-1]="""","""",TEXT(RC[-1],""MMM/YY""))"

coserria
03-28-2008, 04:36 AM
XLD,

Thanks,

it did not work the first run, until I took a closer look at what I was doing. was using in a different sub that had the variant for so as sht.

Ok so looking at this I see that the range is set as ws that would be the active worksheet, row 1 checks if 1 or 0 value for the cells then if true then inserts the foumula in the cells in column 2.

Correct?


Set rng = Range(ws.Rows(1), Rows(ws.Cells.SpecialCells(xlCellTypeLastCell).Row()))
rng.Columns(2).FormulaR1C1 = "=IF(RC[-1]="""","""",TEXT(RC[-1],""MMM/YY""))"

Bob Phillips
03-28-2008, 04:40 AM
No, it inserts a formula in all rows, but the formula checks whether A is blank or not.