Solved: Code: Filldown is Bombing-- Help Please

[YellowLabPro]

I have been working on this piece of code for several days now... trying to tweak it. Something is bombing Excel now. There is nothing too large that should be doing this, there are about 100 records, so it must be the code.

It bombs on the first instruction--

[vba]
Sub FillDown_2()
Dim ws1 As Worksheet
Dim LRow As Long
Dim UpperCase
Dim Rng As Range
Dim c As Range, rg As Range, cel As Range
Set ws1 = Workbooks("TGS Item Record Creator.xls").Sheets("Record Creator")
'Variable
LRow = ws1.Cells(Rows.Count, 3).End(xlUp).Row

'On Error Resume Next
' Set Rng = Cells.SpecialCells(xlCellTypeConstants, 2)
' For Each c In Rng
' c.Value = UCase(c.Value)
' Next c
With Range("a5:g" & Range("h" & Rows.Count).End(xlUp).Row)
.FillDown
End With
With Range("t5:y" & Range("h" & Rows.Count).End(xlUp).Row)
.FillDown
End With
[/vba]

thanks for having a look

YLP

[mdmackillop]

Hi Yelp
You're carefully setting a worksheet variable, then not using it. If you are not in the relevant sheet then your code will fail as Range will default to the active sheet.
Try
[vba]
With ws1
With .Range("a5:g" & .Range("h" & Rows.Count).End(xlUp).row)
.FillDown
End With
With .Range("t5:y" & .Range("h" & Rows.Count).End(xlUp).row)
.FillDown
End With
End With

[/vba]

[Cyberdude]

Malcolm, why is it even necessary to refer to the worksheet?
When I tested ".FillDown", I just wrote a simple statement:
[vba]Range("A1:A10").FillDown[/vba] and it worked just fine. And why clutter it with all the "With's"?? It seems to me that "ActiveSheet" is implied here.

[Norie]

CyberDude

I think Malcolm's quite right regarding referring to the worksheet.

If you don't then VBA assumes that you mean the active worksheet when ever you refer to a range.

We don't know what sheet is active when YLP is running the code, if it's not the Record Creator that could be causing problems.

As to the Withs, I agree in this case they are a little confusing.
[vba]
ws1.Range("a5:g" & ws1.Range("h" & Rows.Count).End(xlUp).Row).FillDown
ws1.Range("t5:y" & ws1.Range("h" & Rows.Count).End(xlUp).Row).FillDown[/vba]

[mdmackillop]

Hi Sid,
It's as Norie says. Yelp might be running the code from a different workbook, hence the WorkBook and SheetName variable.
I agree about the With statements. Not necessary here.

[YellowLabPro]

Hi everyone,
That definitely did the trick, well almost-- ...
What was going on was the range had been altered due to additional columns being added to the original sheet. The code just did not know how to handle it. W/ Malcolm's and Norie's code it forced me to debug all the way back and find the error. It also helped me understand a little more generally.

Thanks once again for your help, could never do it w/out you guys...

and I guess Yelp is going to stick...

YLP aka Yelp