Filldown is not working as desired

[YellowLabPro]

[VBA]
Sub FillDown()
Dim wsSource As Worksheet
Dim i As Long, j As Long, lrwSource As Long
Dim wbn As String, wsn As String
Set wsSource = ActiveSheet
'Set WS1 = Workbooks("TgsItemRecordCreatorMaster.xls").Sheets("RECORDCREATOR")
lrwSource = LR(wsSource, 9)
ActiveWorkbook.Save

Columns("A:IV").EntireColumn.Hidden = False

' changes to Upper case
For j = 8 To 19
For i = 4 To lrwSource
Cells(i, j).Value = Ucase(Cells(i, j).Value)
Next i
Next j
' formats colors properly
For i = 4 To lrwSource
If Not (IsEmpty(Cells(i, 14))) Then
Cells(i, 14).Value = "/" & Cells(i, 14)
End If
Cells(i, 14).Replace what:=",", replacement:="/"
Cells(i, 14).Replace what:=" ", replacement:="/"
Cells(i, 14).Replace what:="//", replacement:="/"
Next i

' copies values down
With wsSource
.Range("A4:G" & lrwSource).FillDown
.Range("BJ4:BK" & lrwSource).FillDown
.Range("R4:R" & lrwSource).FillDown
.Range("T4:Y" & lrwSource).FillDown
.Range("AA4:AB" & lrwSource).FillDown
.Range("AD4:AE" & lrwSource).FillDown
.Range("AG4:AH" & lrwSource).FillDown
.Range("AI4:AM" & lrwSource).FillDown
.Range("AP4:BB" & lrwSource).FillDown
.Range("P4:P" & lrwSource).FillDown
.Range("A6D" & lrwSource).Interior.ColorIndex = 0
.Range("C4:S4").Copy
.Range("C6:S" & lrwSource).PasteSpecial (xlPasteFormats)
.Range("Z4").Copy
.Range("Z6:Z" & lrwSource).PasteSpecial (xlPasteFormats)
.Range("AC4").Copy
.Range("AC6:AC" & lrwSource).PasteSpecial (xlPasteFormats)

.Calculate
End With
Call LenHelper
[H6].Activate
End Sub
[/VBA]

This is the troubled range:
.Range("P4:P" & lrwSource).FillDown

I have tried two different approaches. The goal is to evaluate the values in col. "J". If the value in col. "J" is "snbj", then the adjacent cell in col "P" would populate w/ a value of "09".

Approach 1: The formula is hosted in col. "P": =if(j4="snbj","09","")
Results: as the code loops through the range and if finds a value of "snbj" in col. "J" then it returns a value of "9" not "09". Then it loops through again and the formula is removed along w/ the value completely returning a blank cell.

Approach 2: The formula is hosted in cols. "BJ and BK" to try and address the formatting issues of general and text: col "BJ4" =if(j4="snbj","09","") , formula in "BK4" =bj4 and then back in col "P" =if(bj4="","",bj4"). This gets filled down from the code. This works fine and the values show up properly in BK and BJ and are filled down properly, but values and formulas are removed in col. "P" again.

I have also attempted to format the cells as text, but this fails as the formula is now text rather than a dynamic formula, general format seems to not like a two digit number beginning w/ a zero, yet in cols. BJ and BK it maintains a two digit w/ a leading zero.

Scratching my head over here.

Help is appreciated.

Doug

[Bob Phillips]

Hard to say Doug, ran through fine with the trivial data I created.

How about the workbook to try it on?

[YellowLabPro]

Sure, can I email it to you? It is large due to the code not the data.

[Bob Phillips]

I think I may be mis-understanding Doug. That code all works fine for me, but I am now thinking that is not the problem. Can you re-state what you are having difficulty with?

[mdmackillop]

Hi Doug
For 09 you could Custom Format the columns as 00.
You could also try =if(j4="snbj","'09","") (apostrophe 09), which will insert 09 into the cell, but may have unexpected results with other procedures.
Regards
Malcolm

[YellowLabPro]

Bob:
Sorry for the delay... I had not received any notice that anyone answered.
Also I emailed you the worksheet.
The problem is: I want to populate the year, "09", in column P, if in column J the term "snbj" exists.

Whatever I have tried, my code appears to delete the formula in col. J wiping clean my values.

[YellowLabPro]

Hi Malcolm,
I have formatted my column as custom and use "0?" and this works. The column returns 09, however when I run the filldown code, everything, including formatting is deleted.

Doug