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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
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)))
 
Upvote 0
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?
 
Upvote 0
In what way are they incorrect? (that's also not the formula I gave).
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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
Back
Top