PDA

View Full Version : Solved: Code: Filldown is Bombing-- Help Please



YellowLabPro
09-05-2006, 04:59 AM
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--


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


thanks for having a look

YLP

mdmackillop
09-05-2006, 05:23 AM
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

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

Cyberdude
09-05-2006, 12:47 PM
Malcolm, why is it even necessary to refer to the worksheet?
When I tested ".FillDown", I just wrote a simple statement:
Range("A1:A10").FillDown and it worked just fine. And why clutter it with all the "With's"?? It seems to me that "ActiveSheet" is implied here.

Norie
09-05-2006, 12:55 PM
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.

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

mdmackillop
09-05-2006, 02:47 PM
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
09-06-2006, 03:30 AM
Hi everyone,
That definitely did the trick, well almost-- :think: ...
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... :giggle

YLP aka Yelp