Cell formula reference to another worksheet cell with macro update

[fbk]

Hello,

i have an excel file with 2 worksheets:
Worksheet A has a cell with formula reference reading from a cell on work sheet B.
There is a macro on the file, the macro will import data from another file into worksheet B.
The issue is i am getting error on Cell formula of worksheet A: #REF!

Kindly advise

Regards,
FBK

 

[mikerickson]

What is the formula and the macro that causes the error?

 

[fbk]

this is the formula:

and this is the macro:

 

[mikerickson]

Your formula is refering to cell C2 and you are deleting the cells in row 2, hense the #REF error.
replace the C2 in your formula with INDEX(C:C,2,1)

 

[fbk]

thank you for your reply.
however when i run the macro, excel update the formula from the following:

to the following:

 

[mikerickson]

Another option would be to have the macro put the formula in the cell after you've moved everything around.

 

[fbk]

am trying to replicate your idea using the following macro, but it didnt work:
Sub Macro1()
'
' Macro1 Macro
'

'
Dim sht As Worksheet
Dim PROJECTAIN As String
Dim PROJECTAIN2 As String
Set sht = Sheets("PROJECTA_Out")

For i = 2 To 10
Columns("E:E").Select
PROEJCTAIN = "(PROJECTA_In!C" & i & ")"
PROJECTAIN2 = "(INDEX(PROJECTA_In!C:C," & i & ",1)"
sht.Cells.Replace What:=PROJECTAIN, Replacement:=PROJECTAIN2, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Next i



End Sub