YellowLabPro
11-07-2008, 05:24 AM
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("A6:DD" & 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
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
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("A6:DD" & 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
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