PDA

View Full Version : Solved: Help Needed in debugging



vidya lakshm
09-22-2010, 06:57 AM
Hi,
I am getting an "Runtime error 1004 Application-defined or object defined error" on the highlighted line when I run the code.

Private Sub CommandButton1_Click()
Dim rowPosition As String
Dim ConditionCount
Dim ConditionTargetRange As Range
Dim FileNameTargetRange As Range
Dim SourceRange As Range
Dim TargetOffSet
TargetOffSet = 1
Dim newSheet As Worksheet
Dim mrgRange As String
counter = 2
Dim countVar As Integer

For Each cell In Range("B2:B100")
If cell.Value <> "" Then
currCell = cell.Value
rowPosition = cell.Row
If (currCell <> prevCell) Then
TargetOffSet = 1
Set newSheet = Worksheets.Add
newSheet.Name = cell.Value
Set ConditionTargetRange = Worksheets(newSheet.Name).Range("B2:AZ4")
Set FileNameTargetRange = Worksheets(newSheet.Name).Range("A1:AZ2")
newSheet.Activate
newSheet.Cells(3, TargetOffSet) = Range("C1").Value
newSheet.Cells(2, TargetOffSet) = Range("D1").Value
newSheet.Cells(4, TargetOffSet) = Range("E1").Value
End If

newSheet.Cells(2, TargetOffSet + 2) = Range("D" + rowPosition).Value

newSheet.Cells(3, TargetOffSet + 2) = Range("C" + rowPosition).Value
newSheet.Cells(4, TargetOffSet + 2) = Range("E" + rowPosition).Value
ConditionCount = WorksheetFunction.CountA(Range("F" + rowPosition + ":Z" + rowPosition))

countVar = ConditionCount

Set SourceRange = Worksheets("Sheet4").Range("F" + (rowPosition) + ":Z" + rowPosition)

ConditionTargetRange.Offset(3, TargetOffSet).Resize(1, (ConditionCount)).Value = SourceRange.Cells(1, 1).Resize(1, (ConditionCount)).Value

newSheet.Range(newSheet.Cells(4, (counter + TargetOffSet)), newSheet.Cells(4, (TargetOffSet + countVar + 1))).Select
With selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Borders.LineStyle = xlDash

End With
selection.Merge

newSheet.Range(newSheet.Cells(3, (counter + TargetOffSet)), newSheet.Cells(3, (TargetOffSet + countVar + 1))).Select
With selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Borders.LineStyle = xlDash
End With
selection.Merge

newSheet.Range(newSheet.Cells(2, (counter + TargetOffSet)), newSheet.Cells(2, (TargetOffSet + countVar + 1))).Select
With selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Borders.LineStyle = xlDash

End With
selection.Merge
TargetOffSet = (TargetOffSet) + ConditionCount + 2
mrgRange = TargetOffSet
prevCell = cell.Value
End If
Next cell


End Sub



Can anyone let me know the reason for the error.

p45cal
09-22-2010, 07:18 AM
Because in that line conditioncount is 0 when you get to row 62.
This is because in the line:
ConditionCount = WorksheetFunction.CountA(Range("F" + rowPosition + ":Z" + rowPosition)) the range Range("F" + rowPosition + ":Z" + rowPosition) is empty.

You cant .resize(nn,0).