S
saman110 via OfficeKB.com
Hello all.
This macro copies a range form sheet1 and paste it to sheet2 and inserts a
formula in 3 columns.
My problem is after pasting the formula all cells in formula columns do not
get calculated. Is there anyway to tell excel that go to each cell and
calculate?
I tried application.calulate to private subs but no luck.
Sub myma()
Dim RowNdx As Long
Dim LastRow As Long
Dim name As String
name = InputBox("Enter Customer Name")
Sheets("Sheet1").Select
Columns("G:H").Select
Selection.Copy
Sheets("Sheet2").Select
Columns("C").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("C1").Select
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
With Cells(RowNdx, "C")
.Offset(0, -2).Value = name
.Offset(0, -1).Formula = "=COUNTIF(D,D1)"
.Offset(0, 2).Formula = "=COUNTIF(C:C,C1)"
.Offset(0, 3).Formula = "=IF(E1<B1,E1,B1)"
application.calulate
End With
Next RowNdx
End Sub
This macro copies a range form sheet1 and paste it to sheet2 and inserts a
formula in 3 columns.
My problem is after pasting the formula all cells in formula columns do not
get calculated. Is there anyway to tell excel that go to each cell and
calculate?
I tried application.calulate to private subs but no luck.
Sub myma()
Dim RowNdx As Long
Dim LastRow As Long
Dim name As String
name = InputBox("Enter Customer Name")
Sheets("Sheet1").Select
Columns("G:H").Select
Selection.Copy
Sheets("Sheet2").Select
Columns("C").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("C1").Select
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
With Cells(RowNdx, "C")
.Offset(0, -2).Value = name
.Offset(0, -1).Formula = "=COUNTIF(D,D1)"
.Offset(0, 2).Formula = "=COUNTIF(C:C,C1)"
.Offset(0, 3).Formula = "=IF(E1<B1,E1,B1)"
application.calulate
End With
Next RowNdx
End Sub