Current cell formula and and vba string error

[random_work]

Hi.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Im trying to sum the cell to the left and the cell 2 steps to the left in a excel, I have to paste this code to different places in sheet decided by a macro.<o></o>
I found the formula to be<o></o>
=INDIREKT(ADRESS(RAD();KOLUMN()-2;4;1))+INDIREKT(ADRESS(RAD();KOLUMN()-1;4;1))<o></o>
Simular in englich<o></o>
=INDIRECT(ADDRESS (ROW();COLUMN()-2;4;1))+INDIRECT(ADDRESS (ROW();COLUMN()-1;4;1))<o></o>
This formula works but my problem are that I have to past it in to different cell with a macro/VBA and then the vba refuses…<o></o>
Simplified the code are<o></o>
Sub test()<o></o>
Dim del(1 To 2) As String
Dim iCount As Integer<o></o>
iCount = 1
‘del(1) = "INDIREKT(ADRESS(RAD();KOLUMN()-2;4;1))+INDIREKT(ADRESS(RAD();KOLUMN()-1;4;1))"
del(1)=”INDIRECT(ADDRESS (ROW();COLUMN()-2;4;1))+INDIRECT(ADDRESS (ROW();COLUMN()-1;4;1))”
ActiveCell = "=" + del(iCount)
End Sub<o></o>
This giving me error and the macro don’t add the string to activecell but if I past it manually it works.<o></o>
Why? Howe do I fix it and do any one have a better solution? <o></o>

 

[CharlesChuckieCharles]

Try

del(1)=”=INDIRECT(ADDRESS (ROW();COLUMN()-2;4;1))+INDIRECT(ADDRESS (ROW();COLUMN()-1;4;1))”

note inserted '=' "=Indirect....

ActiveCell.Formula = del(iCount)

 

[random_work]

That does not work.... Same error.
if i thry your sugestion in excell (swedish brand )

Sub testx()
Dim del(1 To 2) As String
Dim iCount As Integer
iCount = 1
del(1) = "=INDIREKT(ADRESS(RAD();KOLUMN()-2;4;1))+INDIREKT(ADRESS(RAD();KOLUMN()-1;4;1))"
ActiveCell.Formula = del(iCount)
End Sub

It does not work.

 

[CharlesChuckieCharles]

I just tested this and it works fine

Sub testx()
Dim del(1 To 2) As String
Dim iCount As Integer
iCount = 1
del(iCount) = "=INDIRECT(ADDRESS(ROW(),COLUMN()-2,4,1))+INDIRECT(ADDRESS(ROW(),COLUMN()-1,4,1))"
ActiveCell.Formula = del(iCount)
End Sub

selected a cell in column C
ran the macro
it added same row Col A to Same Row Col B
worked fine

why you are using INDIRECT?

 

[CharlesChuckieCharles]

actually

del(iCount) = "=INDIRECT( " & ActiveCell.Offset(, -2).Address & ")+INDIRECT(" & ActiveCell.Offset(, -1).Address & ")"

Might be a better solution?

Or this to maintain relative reference

del(iCount) = "=INDIRECT( " & Replace(ActiveCell.Offset(, -2).Address, "$", "") & ")+INDIRECT(" & Replace(ActiveCell.Offset(, -1).Address, "$", "") & ")"

 

[random_work]

It Works Thanks!
I think some thing are wrong in the translation betwean swedich and english versions.

Sub Strange()
Dim del(1 To 2) As String
Dim iCount As Integer
iCount = 1
'This work "english version" but not to type in to the cell manualy
del(iCount) = "INDIRECT(ADDRESS(ROW(),COLUMN()-2,4,1))+INDIRECT(ADDRESS(ROW(),COLUMN()-1,4,1))"
ActiveCell.Formula = "=" + del(iCount)
' This does not work "swedish version" but it works by typing in to the cell manualy
del(iCount) = "INDIREKT(ADRESS(RAD();KOLUMN()-2;4;1))+INDIREKT(ADRESS(RAD();KOLUMN()-1;4;1))"
ActiveCell.Formula = "=" + del(iCount)
' This is tested on Ver:14.0.4760.1000 32-bit swedish version of Excell
End Sub


Thanks a lot now it work for me any way!

 

[Andrew Poulsom]

In VBA you have to use the comma as the argument separator even if Excel uses the semicolon.

 

[random_work]

Im not using vba to calculate "the thing" im using vba to past the code in to a cell. VBA are using the "code" as text string.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
And when vba have paste the code in to a cell i believe excel should behave the same way if i pasted the code in the cell by hand ore pasted by vba code...<o></o>
Microsoft are notifyed lets se what they reply..

 

[Andrew Poulsom]

You use commas in the string that you pass to the Formula property. They will be converted to semicolons automatically.

 

[random_work]

! Ok. So a textstring that excel seas as a formula coming from vba/macro are handled different then the textstrings that comes from the Ctr+V /Crtl+C? That was odd but its a explanation. Thanks.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>