Formula fails to update all values

trsisko

Board Regular
Joined
May 20, 2008
Messages
176
I use the following forumula.


=IF(ISNA(MATCH(I1,Output!$D:$D,FALSE)),"",INDEX(INDIRECT("Output!$A$1:$D$99"),MATCH(I1,Output!$D:$D,FALSE),3))

with the following code. Problem is that sometimes values are missing on the output sheet. Anyone have any ideas what could be the problem. I have checked that the formala is there and the references are a match. It only happens about once or twice out of 80 times

Code:
Sub pastespecial()
Worksheets("pcsoutput").Cells.Copy
Worksheets("pcsoutput").Cells.pastespecial xlPasteValues
Worksheets("pcsoutput").Cells.pastespecial xlPasteFormats
End Sub
Sub resetPcsout()
Sheets("Temp").Cells.Copy Sheets("PCSOutput").Cells(1, 1)
End Sub

Sub CopyFilePaths()
Sheets("Scheme").Range("C10").Copy Destination:=Sheets("PCSOUTPUT").Range("CT2")
Sheets("Scheme").Range("C11").Copy Destination:=Sheets("PCSOUTPUT").Range("CU2")
Sheets("Scheme").Range("C12").Copy Destination:=Sheets("PCSOUTPUT").Range("CV2")
End Sub
Sub AddValue()
    Dim myRange
    Dim c As Range
    
    With Sheets("PcsOutput")
        Set myRange = Intersect(.Rows(1), .UsedRange)
    End With
    For Each c In myRange
        If c.Value <> "" Then
            c.Value = "Value" & c.Value
        End If
    Next c
End Sub
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I would have to assume that they are not actually a match for some reason - e.g. trailing spaces, numbers versus text. Also, why are you using the INDIRECT("Output!$A$1:$D$99") part? It seems very inefficient, particularly as your row match is done against the whole of column D.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you didn't have a particular reason for putting it there, then yes. I would use:
Code:
=IF(ISNA(MATCH(I1,Output!$D:$D,FALSE)),"",INDEX(Output!$C:$C,MATCH(I1,Output!$D:$D,FALSE)))
 

trsisko

Board Regular
Joined
May 20, 2008
Messages
176
New Formula doesnt work as expected. Doesnt give me the correct answers.

=IF(ISNA(MATCH(AE1,Output!$D:$D,FALSE)),"",INDEX(INDIRECT("Output!$A$1:$D$99"),MATCH(AE1,Output!$D:$D,FALSE),3))

Is there any way I could do this in VBA that would ensure it worked?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
In what way are they incorrect? (that's also not the formula I gave).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,538
Messages
5,596,750
Members
414,097
Latest member
FaeFen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top