how to reference a cell through vba

Question

I just wrote some codes which pick up values from one workbook,worksheet and paste them in another workbook, worksheet. Currently it pastes in as values. Here are some parts of my code:

Do Until Cells(i, 9) = "Net Income"
 If Cells(i, 9) = "BONDS INTEREST EARNED" Then
    Bonds_IE = Cells(i + 1, 9)

and then with another book activated,

Cells(j - 5, 4) = Bonds_IE

I want this to show up as a reference to where it pulled the information from. for Ex: K:\etc\etc\etc[etc.xlsx] etc!$A$1

I feel like this should be super simple but I am not sure.. Please help :( Thanks!!

Answer 1

VBA code does a lot of things implicitly. If we make everything explicit, the problem becomes clearer:

Do Until CStr(ActiveSheet.Cells(i, 9).Value) = "Net Income"
 If CStr(ActiveSheet.Cells(i, 9).Value) = "BONDS INTEREST EARNED" Then
    Bonds_IE = CStr(ActiveSheet.Cells(i + 1, 9).Value)

Note:

• Implicit string conversions, made explicit with CStr. These will blow up with a type mismatch error if the cell contains an error value. You may want to verify whether IsError returns False given a cell's value, before doing anything with that value.

• Implicit ActiveSheet references. These will blow up with run-time error 1004 if you use them against a qualified Range call that isn't referring to the active sheet, e.g.:

  Sheet12.Range(Cells(i, 1), Cells(i + 1, 1)) = 42 'Sheet12 isn't active
  

• Implicit default member calls. Cells returns a Range object; its default member points to its Value. That's why you can do Cells(j - 5, 4) = foo and still have compilable code. Making member calls explicit helps better understand what's really going on when you read the code.

As already mentioned, Range.Value gets/sets the range's value. What you want is its .Formula.

Answer 2

You just need to reference the .Formula of the cell, instead of the default, which is .Value.

Do Until Cells(i, 9) = "Net Income"
 If Cells(i, 9) = "BONDS INTEREST EARNED" Then
    Bonds_IE = Cells(i + 1, 9).Formula

Cells(j - 5, 4).Formula = Bonds_IE

Answer 3

Try using the source cell's Address to create a Formula:

Dim Bonds_IE As String
Do Until Cells(i, 9) = "Net Income"
    If Cells(i, 9) = "BONDS INTEREST EARNED" Then
        Bonds_IE = "=" & Cells(i + 1, 9).Address(RowAbsolute:=True, _
                                                 ColumnAbsolute:=True, _
                                                 External:=True)
'...
'...
Cells(j - 5, 4).Formula = Bonds_IE

One trick with this though ... Address(External:=True) will not include the path to the original workbook, so the resultant string needs to be applied as a Formula to the other cell before the original workbook is closed. That way, when the original workbook is closed, Excel will automatically update the formula to include the path as well.