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
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