Referencing another field if result is N/A

[Brian]

Hello,

I have a situation where I am referencing data on another workbook. The
reason behind this is the data in Column H can be entered incorrectly and
often is. I am able to prevent this going forward but can not do so for the
items already entered. I enter a formula via VBA, however, sometimes the
result will be #N/A and if so I would prefer to leave the old value and just
highlight it that it needs to be verified by hand.

I am not sure if I can just "leave" the value as it has pretty much already
been replaced. Due to this, I copy the column to a different column and what
I hope to do is copy back the same row from column Z back to H if #N/A... I
have tried to write this 50 different ways but for some reason I am having
major problems. This is how I enter the formula and copy the pre formula
value to Z... Any help will be greatly appreciated!

With Workbooks("P-Score and Q-Score trends.xlsx").Worksheets("FO Ref List")
Columns("H").Copy
Range("Z1").PasteSpecial
With .Range("G5", .Range("G5").End(xlDown)).Offset(, 1)
.FormulaR1C1 = "=INDEX('[March 2010 ADIM 1047 report
(4).xls]ADIMR1047'!C21,MATCH(RC7,'[March 2010 ADIM 1047 report
(4).xls]ADIMR1047'!C26,0))%26"", ""%26INDEX('[March 2010 ADIM 1047 report
(4).xls]ADIMR1047'!C20,MATCH(RC7,'[March 2010 ADIM 1047 report
(4).xls]ADIMR1047'!C26,0))"
.Value = .Value
End With
End With

Thank you!

 

[Bernie Deitrick]

Brian,

Put the values into column Z, then get a formula to work of the form

=IF(ISNA( Current Formula ), Z5, Current Formula)

Once the formula works, use the macro recorder to record entering the
formula in the cells to get the correct VBA code.

HTH,
Bernie
MS Excel MVP

 

[Brian]

I was actually coming here to say I figured it out earlier today lol I ended
up going with IFERROR and it works so I don't hink I will change that around
again lol.

=IFERROR(INDEX('[March 2010 ADIM 1047
report.xls]ADIMR1047'!C21,MATCH(RC7,'[March 2010 ADIM 1047
report.xls]ADIMR1047'!C26,0))&"", ""&INDEX('[March 2010 ADIM 1047
report.xls]ADIMR1047'!C20,MATCH(RC7,'[March 2010 ADIM 1047
report.xls]ADIMR1047'!C26,0)),RC26)"

Brian,

Put the values into column Z, then get a formula to work of the form

=IF(ISNA( Current Formula ), Z5, Current Formula)

Once the formula works, use the macro recorder to record entering the
formula in the cells to get the correct VBA code.

HTH,
Bernie
MS Excel MVP

Hello,

I have a situation where I am referencing data on another workbook. The
reason behind this is the data in Column H can be entered incorrectly and
often is. I am able to prevent this going forward but can not do so for
the
items already entered. I enter a formula via VBA, however, sometimes the
result will be #N/A and if so I would prefer to leave the old value and
just
highlight it that it needs to be verified by hand.

I am not sure if I can just "leave" the value as it has pretty much
already
been replaced. Due to this, I copy the column to a different column and
what
I hope to do is copy back the same row from column Z back to H if #N/A...
I
have tried to write this 50 different ways but for some reason I am having
major problems. This is how I enter the formula and copy the pre formula
value to Z... Any help will be greatly appreciated!

With Workbooks("P-Score and Q-Score trends.xlsx").Worksheets("FO Ref
List")
Columns("H").Copy
Range("Z1").PasteSpecial
With .Range("G5", .Range("G5").End(xlDown)).Offset(, 1)
.FormulaR1C1 = "=INDEX('[March 2010 ADIM 1047 report
(4).xls]ADIMR1047'!C21,MATCH(RC7,'[March 2010 ADIM 1047 report
(4).xls]ADIMR1047'!C26,0))%26"", ""%26INDEX('[March 2010 ADIM 1047 report
(4).xls]ADIMR1047'!C20,MATCH(RC7,'[March 2010 ADIM 1047 report
(4).xls]ADIMR1047'!C26,0))"
.Value = .Value
End With
End With

Thank you!

.