I already asked several questions for this code I'm writing for work, and in another post ( here : How to insert, through VBA, a formula in a cell with special characters like "-" or "$"? ), JvdV gave me that formula :
With Worksheets(LabourSheet)
.Cells(PosStartLineCalc + 1, PosStartColumnLt).Formula = "=IF(or(" & .Cells(PosStartLineCalc, PosStartColumnLt).Address(False, False) & "=""-""," & .Cells(PosStartLineCalc, PosStartColumnLt).Address(False, False) & "=""X""),""-"",Cars!" & .Cells(PosStartLine + (TPICode * 3) - 3, Split(Cells(1, col).Address, "$")(1)).Address(False, False) & "*" & .Cells(PosStartLineCalc, PosStartColumnLt).Address(False, False) & "*Data!$C$8)"
End With
Which once in the cells give, for example : =IF(OR(C4="-";C4="X");"-";Cars!C4*C4*Data!$C$8)
No surprise if I tell you that I don't understand the VBA side fully...
Anyway, it works, but I would need to add some "$" dollar signs as I copy past the whole table and the "Cars!C4" should stay as it is...
So, how to add nice dollar signs on the "Cars!C4" to become "Cars!$C$4"?
I tried it this way :
",Cars!" & .Cells("$" & PosStartLine + (TPICode * 3) - 3, "$" & Split(Cells(1, col).Address, "$")(1)).Address(False, False)
",Cars!" & .Cells(""$"" & PosStartLine + (TPICode * 3) - 3, ""$"" & Split(Cells(1, col).Address, "$")(1)).Address(False, False)
",Cars!" & .Cells(chr(36) & PosStartLine + (TPICode * 3) - 3, chr(36) & Split(Cells(1, col).Address, "$")(1)).Address(False, False)
it gives me a "type mismatch" error :'(
Nothing worked...
I searched on internet, of course, but I always find pages where they explain that you can press F4 and for what the dollar signs are...
Thank's by advance !
.Cells(PosStartLine + (TPICode * 3) - 3, Split(Cells(1, col).Address, "$")(1)).Address(False, False)
In this piece of code, change the arguments given to .address to (True,True)
.Cells(PosStartLine + (TPICode * 3) - 3, Split(Cells(1, col).Address, "$")(1)).Address(**True, True**)