the Calculate Function

[Jackeb]

OK it has gotten me well frustrated now.

could someone please explain to me how the calculate function works in excel because it is NOT logical, sometimes it causes functions to do some strange things...... very strange.

For example, with the keyword calculate in the sub it didn't recognise the phrase:

set lfg = activecell.address

but once removed from the sub it worked.... logical i think not!

however, i need it in the routine because i am populating an array with numbers that have just been coppied and need to be calculated.

please help me...... i am too logiced out to understand.

Ed

[dk]

OK it has gotten me well frustrated now.

could someone please explain to me how the calculate function works in excel because it is NOT logical, sometimes it causes functions to do some strange things...... very strange.

For example, with the keyword calculate in the sub it didn't recognise the phrase:

set lfg = activecell.address

but once removed from the sub it worked.... logical i think not!

however, i need it in the routine because i am populating an array with numbers that have just been coppied and need to be calculated.

please help me...... i am too logiced out to understand.

Ed

Hi,

First of all the reason why this line wasn't understood :-

set lfg = activecell.address

Activecell.Address will return a string e.g. $A$3. The Set keyword is used when you're assigning an object variable to something, not strings, numbers, dates, etc. This would be valid:-

Set lfg = Activecell

lfg would then be an object variable representing the Activecell range object.

As for your calculate question, can you post the rest of your code and indicate where the code is falling down? There are several ways to use the Calculate method and it would be helpful to see your code.

Regards,
Dan

[Jackeb]

Dan,

sorry about the first point... slipup...

code is:

Sub UpdateRawData()

Application.ScreenUpdating = False

rangevarpop

If dcol1 = "" Then
MsgBox "No Update criteria selected for the First selection, please use the update properties button!", 49
Else
update1st
End If

If dcol2 = "" Then
MsgBox "No Update criteria selected for the Second selection, please use the update properties button!", 49
Else
update2nd
End If

Set selrange1 = Nothing
Set Homerange1 = Nothing
Set selrange2 = Nothing
Set Homerange2 = Nothing

Application.ScreenUpdating = True

If unit = "CL GT35" Then
ccud
End If

End Sub

Sub update1st()
Dim ar

Set selrange1 = Worksheets(unit & " Calculations").Range("" & Fmula1 & "")
Set Homerange1 = Worksheets(unit & " Calculations").Range("" & hme1 & "")

selrange1.Copy
Homerange1.Select
ar = ActiveCell.row


Do Until Range("" & dcol1 & ar + 1 & "").Value = ""
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ar = ar + 1
Loop

Application.CutCopyMode = False

calculate

End Sub

Sub update2nd()
Dim ar

If unit = "CL GT35" Then
Exit Sub
End If

Set selrange2 = Worksheets(unit & " Calculations").Range("" & Fmula2 & "")
Set Homerange2 = Worksheets(unit & " Calculations").Range("" & hme2 & "")

selrange2.Copy
Homerange2.Select
ar = ActiveCell.row

Do Until Range("" & dcol2 & ar + 1 & "").Value = ""
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ar = ar + 1
Loop

Application.CutCopyMode = False

calculate

End Sub


the update1 runs fine but the update 2 doesn't

solved by removing the calculates from within the update code and putting one in the updaterawdata sub.

it seams illogical and several outher problems with seamingly spurious reasons appear to be due to the calculate function... so how does it work.. got me there??

Ed