YasserKhalil wrote:why do you use this line of code
Code: Select all
....= Evaluate("=IF(ISERR(" & ws.Range(sRng).Address & ")," & """""" & ",IF(" & ws.Range(sRng).Address & "=0," & """""" & "," & ws.Range(sRng).Address & "))")
I do not always use it. Sometimes I use it. This is because lots of 0s and errors are sometimes not wanted
For example:
Lets say, this is my record file:
https://imgur.com/4GzdOdk" onclick="window.open(this.href);return false;
RecordFile.JPG
Now if I use this code in the Main File without the extra line: ...
Code: Select all
Sub MessyTempData()
Dim RngTemp As Range: Set RngTemp = Thisworkbook.Worksheets("TempRange").Range("A1:E15")
Let RngTemp.Value = "=" & "'" & "C:\Users\Elston\Desktop\YassersFolder\" & "[" & "Clsdrecord1.xlsx" & "]Tabelle1'!A1"
Let RngTemp.Value = RngTemp.Value
End Sub
_..then this is how my temporary range in the Main File looks after running that code:
https://imgur.com/QmwwcJO" onclick="window.open(this.href);return false;
NullsErrorCrap.JPG
_.__________
If I now include the extra line like this:....
Code: Select all
Sub FukOffNullsAndErrors()
Dim RngTemp As Range: Set RngTemp = ThisWorkbook.Worksheets("TempRange").Range("A1:E15")
Let RngTemp.Value = "=" & "'" & "C:\Users\Elston\Desktop\YassersFolder\" & "[" & "Clsdrecord1.xlsx" & "]Tabelle1'!A1"
Let RngTemp.Value = RngTemp.Value
Let RngTemp.Value = Evaluate("=IF(ISERR(" & RngTemp.Address & ")," & """""" & ",IF(" & RngTemp.Address & "=0," & """""" & "," & RngTemp.Address & "))")
End Sub
......then now the 0s and Error is gone:
https://imgur.com/qN6xL8D" onclick="window.open(this.href);return false;
NullsAndErrorDoneGone.JPG
_.____________________________________
The reason why I sometimes need this is:
Sometimes I make calculation of averages in data in my main File. So I do need to make sure that I have empty and not 0, or visa versa, if that is how my actual data is. In other words , I want the temporary range to look exactly like my actual data.
There is one small problem with this extra code line. The problem is that if I do have a 0 in my data, then I do not want that 0 to be lost. So as a workaround, I use a very small number, such as 0.001 instead of 0. My work does not need to be too accurate, so the error caused by this is minimal.
But I would be interested if anyone ever has a better solution to this.
A limitation of the closed workbook reference way is that you only can get at values. So an empty cell or 0 will give you 0 . In my work the difference between 0 or empty in my data is important.
( Another workaround for me is if I replace all empty cells in my data with a value such as “EmptyNowtInIt”. Then I can add another nested If in that Evaluate code line to replace all values of “EmptyNowtInIt” with “” : like this
Change
Evaluate("=IF(ISERR(" & RngTemp.Address & ")," & """""" & ",IF(" & RngTemp.Address & "=0," & """""" & "," & RngTemp.Address & "))")
to
Evaluate("=IF(ISERR(" & RngTemp.Address & ")," & """""" & ",IF(" & RngTemp.Address & "=0," & """""" & ",IF(" & RngTemp.Address & "=""EmptyNowtInIt""," & """""" & "," & RngTemp.Address & ")))")
In other words I change the "pseudo Array formula evaluation"
from
{=IF(ISERR($A$1:$E$15),"",IF($A$1:$E$15=0,"",$A$1:$E$15))}
to
{=IF(ISERR($A$1:$E$15),"",IF($A$1:$E$15=0,"",IF($A$1:$E$15="EmptyNowtInIt","",$A$1:$E$15)))}
I prefer not to do this as then my data looks very messy and is bigger as I have lots of empty cells. As I do not have many 0s in my data, then replacing all 0s with a very small number does not effect my data much. I can do this replacement easilly with a simple VBA code)
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also