• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to put a formula in an active celll using VBA

W

Wim Geuens

Member
Hi,

Can someone please tell me how I can put a formula in an active cell

here is a part of the code, but it doesn't work and gives me an error

Thansk for your help

W
Code: 
    ActiveCell.Offset(0, 1).Select
    rng1 = ActiveCell.Address(False, False)
    Col = Left(rng1, Len(rng1) - Len(Format(rng.Row, "0")))
   
    ActiveCell.Offset(0, 2).Select
    rng2 = ActiveCell.Address(False, False)
    lCol = Left(rng2, Len(rng2) - Len(Format(rng.Row, "0")))
   
    ActiveCell.Offset(39, 0).Select
   
    ActiveCell.Formula = "=(106-SUM(" & Col & "6;" & lCol & "6;" & Col & "10;" & lCol & "10;" & Col & "18;" & lCol & "18;" & Col & "22;" & lCol & "22;" & Col & "32)+((" & Col & "8+" & lCol & "8)/2))"
    ActiveSheet.Calculate
    Uren = ActiveCell.Copy
 
Hi Wim ,

Try this :
Code: 
Sub temp()
    ActiveCell.Offset(0, 1).Select
    rng1 = ActiveCell.Address(False, False)
    Col = Left(rng1, Len(rng1) - Len(Format(Range(rng1).Row, "0")))
 
    ActiveCell.Offset(0, 2).Select
    rng2 = ActiveCell.Address(False, False)
    lCol = Left(rng2, Len(rng2) - Len(Format(Range(rng2).Row, "0")))
 
    ActiveCell.Offset(39, 0).Select
 
    ActiveCell.Formula = "=(106-SUM(" & Col & "6;" & lCol & "6;" & Col & "10;" & lCol & "10;" & Col & "18;" & lCol & "18;" & Col & "22;" & lCol & "22;" & Col & "32)+((" & Col & "8+" & lCol & "8)/2))"
    ActiveSheet.Calculate
    Uren = ActiveCell.Copy
End Sub
Narayan
 
Hi narayan,

Thansk for the quick reply.

The macro runs fine but I get an error on the formula line.
The Col en lCol is defined correctly, but the formula is not pasted into the cell.
If I drop the "=" sign, the text is pasted in the cell, but it needs the "="sign for the formula setting.

Code: 
ActiveCell.Formula = "=(106-SUM(" & Col & "6;" & lCol & "6;" & Col & "10;" & lCol & "10;" & Col & "18;" & lCol & "18;" & Col & "22;" & lCol & "22;" & Col & "32)+((" & Col & "8+" & lCol & "8)/2))"
 
Hi Wim ,

On my system , the Excel separator is the comma "," instead of the semi-colon ";".

If I replace all the semi-colons in the formula statement by commas , I get :

ActiveCell.Formula = "=(106-SUM(" & Col & "6," & lCol & "6," & Col & "10," & lCol & "10," & Col & "18," & lCol & "18," & Col & "22," & lCol & "22," & Col & "32)+((" & Col & "8+" & lCol & "8)/2))"

When I run the macro with the activecell as A2 , it runs properly , and I get the following formula in cell D41.

=(106-SUM(B6,D6,B10,D10,B18,D18,B22,D22,B32)+((B8+D8)/2))

Narayan
 
While applying formula through VBA you need to use "," as argument separator irrespective of your locale requirement I believe. In Excel it will appear with the locale specific argument separator.

Following should work assuming your formula is correct syntactically otherwise ;)
Code: 
Sub tempting()
    ActiveCell.Offset(0, 1).Select
    rng1 = ActiveCell.Address(False, False)
    Col = Left(rng1, Len(rng1) - Len(Format(Range(rng1).Row, "0")))
    ActiveCell.Offset(0, 2).Select
    rng2 = ActiveCell.Address(False, False)
    lCol = Left(rng2, Len(rng2) - Len(Format(Range(rng2).Row, "0")))
    ActiveCell.Offset(39, 0).Select
    ActiveCell.Formula = "=(106-SUM(" & Col & "6," & lCol & "6," & Col & "10," & lCol & "10," & Col & "18," & lCol & "18," & Col & "22," & lCol & "22," & Col & "32)+((" & Col & "8+" & lCol & "8)/2))"
    ActiveSheet.Calculate
    Uren = ActiveCell.Copy
End Sub
 
You must log in or register to reply here.
Back
Top