Calculate Range

[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

 

[Niek Otten]

Did you paste this from your code?

<application.calulate>

should read

Application.Calculate

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[saman110 via OfficeKB.com]

I tried that also, but did not work. My guss is a macro that force each cell
to calculate.

thx.

 

[Niek Otten]

Why not post your actual code?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I tried that also, but did not work. My guss is a macro that force each cell
| to calculate.
|
| thx.
|
| saman110 wrote:
| >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
|
| --
| Message posted via OfficeKB.com
| http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200802/1
|