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