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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,820
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
38,820
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
38,820
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).
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,632
Messages
5,838,473
Members
430,549
Latest member
jayjay2022

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