Pasting string - variable in cell - ERROR

[Darmlucht]

Dear all,

I have some slight knowledge about VBA and writing code.
But this has my mind boggled, and probably it is something really stupid.

I am trying to copy and paste Format and Formulas, from the row above.
By the push of a button.
The formulas should be 'dynamic', in the way that any cell reference is unique and changed from one line to the next.

But now I get the "Run-time error '1004': Application-defined or object-defined error"

Private Sub CommandButton2_Click()
Dim Frmull As String, frmulll As String


Arow = Range("A2").End(xlDown).Address
Brow = Range("A2").End(xlDown).Row
crow = Brow + 1
Arow = "$A$" & crow
Nrow = "$N$" & crow
therow = "$C$" & crow

Frmull = "=IF(A" & crow & "<>"";IF($D" & crow & "="";0;1);"")"
'below is the formula that I want to paste 
frmulll = "=IF(" & Arow & "<>""" + """" + ";" + "IF($D" & crow & "=""" + """" + ";0;1);"""")"

Range("A3:N3").Copy 'this is the format
CRange = Arow & ":" & Nrow

'Range("A" & Lrow & ":N" & Lrow).PasteSpecial Paste:=xlPasteFormats

Range(CRange).PasteSpecial Paste:=xlPasteFormats

'Range("C" & Crow).Select
'ActiveCell.FormulaR1C1 = "=R[-1]C[0]"
'ActiveCell.Formula = "=R[-1]C[0]"

Frmul = Range("C" & Brow).Formula
Range("C" & crow).Select
ActiveCell = frmulll

End Sub

I know it doesn't look all too good, I am in no way a professional in writing VBA, but usually I can make it work.
If someone has an idea about what is going wrong, please, it would make my life a lot easier.
This is just one example, but if this is fixed, I can apply it to other issues I'm having as well.
Thank you all in advance!

Kind regards,
D.

 

[NdNoviceHlp]

Hi Darmlucht. It's likely that you need to add a specific sheet reference to all of your range code...

Sheets("Sheet1").Range("A2").End(xlDown).Address

If this doesn't work, please indicate on what line of code the error occurs. HTH. Dave

 

[Darmlucht]

Hi Darmlucht. It's likely that you need to add a specific sheet reference to all of your range code...

Sheets("Sheet1").Range("A2").End(xlDown).Address

If this doesn't work, please indicate on what line of code the error occurs. HTH. Dave

Thank you for the quick response, much appreciated!

I have added 'Sheets("PO's").' to all my ranges but still, it gives the same error message.
Error happens on line "ActiveCell = frmulll"
If I replace the "= frmulll" with for example "= "Test", "ActiveCell = "Test"", then it doesnt give an error, and writes Test in the specified cell.
But if I make it a variable (frmulll = string), it goes into error.

I also tried to put the whole formula that is in 'frmulll' after the =, but that doesn't work as well.

Also, if I define another string variable, and make it a simpler string like "Test", it also works just fine.

TestString = "Test"
ActiveCell = TestString

This works just fine..

So I am guessing it is the string itself that is causing issues. But I have no clue how to fix that.
I hope this makes sense, otherwise I'll try to explain further.

Thank you in advance!

 

[NdNoviceHlp]

This should work. Dave

Sheets("PO's").Range("C" & crow)= frmulll

 

[Darmlucht]

Sadly, it still doesn't.

I think I'll have to find another way to build up the formula in seperate cells and then concatenate them or something?
I think all the "" marks are making it difficult for Excel/VBA.

Or does it work if you try it?

Anyway, big thank you again for the suggestion!
I'm going to keep trying different methods of fabricating strings with variables

 

[NdNoviceHlp]

Trial this and see if your code is correct...

Frmull = "=IF(A" & crow & "<>"";IF($D" & crow & "="";0;1);"")"
'below is the formula that I want to paste 
frmulll = "=IF(" & Arow & "<>""" + """" + ";" + "IF($D" & crow & "=""" + """" + ";0;1);"""")"
msgbox "Frmull " & Frmull & "  frmulll " & frmulll

Doesn't work or there is an error? Dave

 

[Darmlucht]

Dear
My apologies for the late response.
This also resulted in the same error.

I think I will leave it for now, and maybe come back to this when I have more time to spend on this.
I'm doing it all manually, but my coworkers mess it up all the time, thats why I wanted to make something a bit more foolproof.

Thank you all for thinking along with me on this!
I hope I'll find the answer some day

 

[Alex Blakenburg]

Can you give these a try:
1)

ActiveCell.FormulaLocal = frmulll

2)

frmulll = "=IF(" & Arow & "<>""" + """" + ";" + "IF($D" & crow & "=""" + """" + ";0;1);"""")"
frmulll = Replace(frmulll, ";", ",")            ' Convert ";" to "," - English language syntax
ActiveCell.Formula = frmulll

 

[Darmlucht]

Can you give these a try:
1)

ActiveCell.FormulaLocal = frmulll

2)

frmulll = "=IF(" & Arow & "<>""" + """" + ";" + "IF($D" & crow & "=""" + """" + ";0;1);"""")"
frmulll = Replace(frmulll, ";", ",")            ' Convert ";" to "," - English language syntax
ActiveCell.Formula = frmulll

Dear Alex,

I gave it a try, and I think the second suggestion might do the trick.
It's giving an error still, but there is something happening, instead of just prompting an error.
some of the formatting is copied, and the formula is almost there.

I think if I tinker with it a bit, I can make it work!
If so, I will post my solution here, but for now your is the closest to solving it.

Thanks again for all the effort and brainpower!

I will come back to mark as solved once it is really solved!

Kind regards,

 

[Alex Blakenburg]

Thanks for the update. Let us know how you go.