PDA

View Full Version : Solved: Filling empty cells across workbooks with zero



Orange_Wed
07-28-2008, 11:56 AM
Hello again All,

I'd like to start by thanking everyone who's given me a hand lately. The help is most appreciated.

My next question revolves around inserting zeros into blank cells in a template. I only want to fill data down to the last row containing data in it.

I've got some code that exports data from one book to the other, and i'd like to sort this data (as my export function leaves many rows open) so that all of the rows are sorted (Ascending) by the data in the "A" column. Then I'd like to fill the empty cells in columns "C" thru "H" with zeros, with only those rows with data in them being filled with zeros.

This is the code I have for export. The only reason I want to sort the data is the fact that i'm running off of "count" to count the rows and any attempts at skipping blank rows have ended up with endless loops that have crashed excel.


Private Sub Export_Click()
Dim template As Worksheet
Dim source As Worksheet
Dim count As Integer
Set template = Workbooks("template.xls").Worksheets("Sheet1")
Set source = Workbooks("testingpage.xls").Worksheets("BUR")
count = 1
With source
Do
If .Cells(count, 9) <> 0 Then
If .Cells(count, 8) Like "L*" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1, 4) = .Cells(count, 9)
template.Cells(count + 1, 3) = .Cells(count, 11)
ElseIf .Cells(count, 8) Like "M*" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1, 5) = .Cells(count, 9)
ElseIf .Cells(count, 8) Like "S*" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1, 6) = Cells(count, 9)
ElseIf .Cells(count, 8) Like "R*" Then
template.Cells(count + 1, 1) = Cells(count, 8)
template.Cells(count + 1, 6) = .Cells(count, 9)
ElseIf .Cells(count, 8) Like "T*" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1, 8) = .Cells(count, 9)
ElseIf .Cells(count, 8) Like "W*" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1, 5) = .Cells(count, 9)
ElseIf .Cells(count, 8) Like "P*" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1, 8) = .Cells(count, 6)
ElseIf .Cells(count, 8) Like "E*" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1, 8) = .Cells(count, 9)
ElseIf .Cells(count, 8) Like "900" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1 & 8) = .Cells(count, 9)
End If
End If
count = count + 1
Loop Until count = 100
End With

With template
Do
If .Cells(count + 1, 3) < 1 Then
.Cells(count + 1, 3) = "0"
End If
If .Cells(count + 1, 4) < 1 Then
.Cells(count + 1, 4) = "0"
End If
If .Cells(count + 1, 5) < 1 Then
.Cells(count + 1, 5) = "0"
End If
If .Cells(count + 1, 6) < 1 Then
.Cells(count + 1, 6) = "0"
End If
If .Cells(count + 1, 7) < 1 Then
.Cells(count + 1, 7) = "0"
End If
If .Cells(count + 1, 8) < 1 Then
.Cells(count + 1, 8) = "0"
End If
count = count - 1
Loop While count > 1
End With
End Sub


The zero-fill method I'm using results in a hundred rows of zeros being added. It would be much better if I could end the export at the end of data in the source sheet, but i've not been able to figure that out in a way that works, either.

Attached is a copy of the two workbooks I'm working with

For now, I'm just wondering if anyone can explain a method of going only to the end of the data on a page in a loop, and how I'd fill in the blank cells with zeros.

Thanks Kindly

Wednesday

Paul_Hossler
07-28-2008, 12:59 PM
Needs some error checking, but would somethig like this help?


Worksheets("Sheet1").Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = 0


Paul

Orange_Wed
07-28-2008, 02:45 PM
Thanks for the quick reply!

Not exactly what I was looking for, though the code is most appreciated. I think I might have a use for that bit in another project.

I've pretty well got the issue resolved using basic do...while loops and if statements, but I'd be most appreciative if someone could tell me what i would use to stop a loop at the end of a file.

For instance:


Dim row As Integer, column As Integer
Do
If Cells(row, column) = "" Then
Cells(row, column) = "0"
End If
row = row + 1
column = column + 1
End If
While Row < 'end of file code?


I'd rather work it this way than having to do a loop/if combination to detect a blank and fill it with a zero. This would also cut out the need to remove extra rows before importing.

Also, would anyone be able to tell me how to sort across workbooks? If I want to send some information from one workbook to a template, how would i sort the information exported after I've sent it?

Here is the existing code I've hacked out:
Private Sub Export_Click()
Dim template As Worksheet
Dim source As Worksheet
Dim count As Integer
Set template = Workbooks("template.xls").Worksheets("Sheet1")
Set source = Workbooks("testingpage.xls").Worksheets("BUR")
count = 1

template.Cells(2, 3) = 0
template.Cells(2, 4) = 0
template.Cells(2, 5) = 0
template.Cells(2, 6) = 0
template.Cells(2, 7) = 0
template.Cells(2, 8) = 0

With source
Do
If .Cells(count, 9) <> 0 Then
If .Cells(count, 8) Like "L*" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1, 4) = .Cells(count, 9)
template.Cells(count + 1, 3) = .Cells(count, 11)
ElseIf .Cells(count, 8) Like "M*" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1, 5) = .Cells(count, 9)
ElseIf .Cells(count, 8) Like "S*" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1, 6) = Cells(count, 9)
ElseIf .Cells(count, 8) Like "R*" Then
template.Cells(count + 1, 1) = Cells(count, 8)
template.Cells(count + 1, 6) = .Cells(count, 9)

ElseIf .Cells(count, 8) Like "T*" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1, 8) = .Cells(count, 9)

ElseIf .Cells(count, 8) Like "W*" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1, 5) = .Cells(count, 9)

ElseIf .Cells(count, 8) Like "P*" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1, 8) = .Cells(count, 6)

ElseIf .Cells(count, 8) Like "E*" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1, 8) = .Cells(count, 9)

ElseIf .Cells(count, 8) Like "900" Then
template.Cells(count + 1, 1) = .Cells(count, 8)
template.Cells(count + 1 & 8) = .Cells(count, 9)

End If
End If
count = count + 1
Loop Until count = 100
End With

With template
Do
If .Cells(count + 1, 3) = "" Then
.Cells(count + 1, 3) = "0"
End If
If .Cells(count + 1, 4) = "" Then
.Cells(count + 1, 4) = "0"
End If
If .Cells(count + 1, 5) = "" Then
.Cells(count + 1, 5) = "0"
End If
If .Cells(count + 1, 6) = "" Then
.Cells(count + 1, 6) = "0"
End If
If .Cells(count + 1, 7) = "" Then
.Cells(count + 1, 7) = "0"
End If
If .Cells(count + 1, 8) = "" Then
.Cells(count + 1, 8) = "0"
End If
count = count - 1
Loop While count > 1

r = 2

'doesnt't work===============================================
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'============================================================

Do
If Cells(r, 1) = "" Then
Cells(r, 2) = ""
Cells(r, 3) = ""
Cells(r, 4) = ""
Cells(r, 5) = ""
Cells(r, 6) = ""
Cells(r, 7) = ""
Cells(r, 8) = ""
End If
r = r + 1
Loop While r < 150
End With
End Sub

The sort portion doens't work due to the fact that It's based on a selection I haven't made in another workbook. This much I understand, what I don't get is how to link that sort routine to the other workbook.

Any help would be greatly appreciated!

Thanks again in advance!

Wednesday

Orange_Wed
08-01-2008, 08:41 AM
Well, looks like I fixed it.

Thanks all.