Calculate Range
 

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:D").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: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 http://www.officekb.com

Calculate Range
 

Did you paste this from your code?

<application.calulate

should read

Application.Calculate

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"saman110 via OfficeKB.com" <u35670@uwe wrote in message news:7f841d1b9f0c3@uwe...
| 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:D").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: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 http://www.officekb.com
|

Calculate Range
 

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:D").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: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.a...excel/200802/1

Calculate Range
 

Why not post your actual code?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"saman110 via OfficeKB.com" <u35670@uwe wrote in message news:7f894a83fe637@uwe...
|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:D").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: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.a...excel/200802/1
|