easy formula = HARD with ".Formula" property in VBA

[naznorb]

Here's a formula I'm using:

=IF(tree.1!$A1<>"",tree.1!$A1,"")

Basically, I'm copying data from one sheet to another (please, don't ask me how I got myself into this situation!), and trying to avoid 0s for blank cells.

Problem: I have to do this to 10,000s of cells, so I had a handy on-open macro:

FinalRow = Worksheets("tree.1").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("tree").Activate
For q = 1 To FinalRow
ActiveSheet.Cells(q, 1).Formula = "=tree.1!A" & q
Next

Worked great -- but I desperately need the blank-cell trap in there. How do I deal with the double-quotes (which presumably themselves have to be inside quotes)?

Have a feeling it's something dopey or simple, but haven't been able to find anything with search of Forum or FAQs. Any help much appreciated.​

 

[Aaron Blood]

Here's a formula I'm using:

=IF(tree.1!$A1<>"",tree.1!$A1,"")

Translates to this as a VBA string:

"=IF(tree.1!$A1<>"""",tree.1!$A1,"""")"

I'll let you work out how to concatenate it together with variables in your code.

 

[Jonmo1]

Try

ActiveSheet.Cells(q, 1).Value = Sheets("tree.1").Cells(q, 1).Value

 

[mikerickson]

If you just want the values, you could use

With Worksheets("tree.1")
    With Range(.Cells(1,1), .Cells(Rows.Count, 1).End(xlUp))
        ActiveSheet.Range(.Address).Value = .Value
    End With
End With

If you want a formula in the cells, to react to changes in tree.1, you can eliminate the 0's by concatinating a "". (Caution, this will turn all the numbers in the tree.1 range into text on the active sheet)

With Worksheets("tree.1")
    With Range(.Cells(1,1), .Cells(Rows.Count, 1).End(xlUp))
        ActiveSheet.Range(.Address).FormulaR1C1 = "=tree.1!RC1 & """" "
    End With
End With