Query for Array !!!
 

Here comes a Question,Hope someone has done it before.

Scenario :- This macro is doing checking the Range of 1 lac rows by putting into an array and marking the last column as wrong if any negative entry comes in any column,This would be done for all rows one by one(Ya by Array),

What to achieve as always i want to remove this if condition and making this macro a effective one,Ya question comes how?, This can be possible when this macro check all the columns for a single row in one time not one by one cell value, I have seen evaluate function works in these kind of scenario where you need to work with output only.

something like Evaluate(Min(Lbound(Varout,2),ubound(varout,2)) <0 then mark wrong in column G !!,I am not sure how evaluate works here but any help would be appreciated basically i want to see the logics of different minds.

1) I can do it with the help of helper column but i don't

Sub Array1()


Dim varout() As Variant
varout = Range("A1:K100000")

t = Timer
temp = 0
ReDim Preserve varout(1 To 100000, 1 To 13)

For i = LBound(varout) To UBound(varout)
For j = LBound(varout, 2) To UBound(varout, 2)
'Debug.Print varout(i, j)
If varout(i, j) < 0 Then
Sheets("sheet1").Range("L" & i).Value = "wrong"
End If
Next
' MsgBox temp
Next

MsgBox Timer - t
End Sub

Query for Array !!!
 

First thing you need to do is get your own logic sorted out!!! You say
you want to 'flag' the last column with "wrong", but state the last col
as "G" of a 12 col array. (Doesn't make sense)

Here's how I might approach this task...

Sub Flag_NegativeValues()
Dim n&, rngData As Range, lLastCol&, lLastRow&

Set rngData = ActiveSheet.UsedRange
With rngData
lLastCol = .Columns.Count: lLastRow = .Rows.Count
End With 'rngData

With Application
For n = 1 To lLastRow
If .CountIf(.Index(rngData, n, 0), "<0") 0 Then
Cells(n, lLastCol + 1) = "wrong"
End If
Next 'n
End With 'Application
End Sub

...where I write directly to the sheet rather than rebuild an output
array that has to be written later. If you prefer to build an output
array then...

Sub Flag_NegativeValues2()
Dim vData, n&, lRows&, lCols&, rng As Range

Set rng = ActiveSheet.UsedRange: vData = rng
lRows = UBound(vData): lCols = UBound(vData, 2)
ReDim Preserve vData(1 To lRows, 1 To lCols + 1)

With Application
For n = LBound(vData) To UBound(vData)
If .CountIf(.Index(rng, n, 0), "<0") 0 Then
vData(n, UBound(vData, 2)) = "wrong"
End If
Next 'n
ActiveSheet.UsedRange.Resize(UBound(vData), UBound(vData, 2)) =
vData
End With 'Application
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Query for Array !!!
 

Hi Mandeep,

Am Tue, 8 Dec 2015 00:11:41 -0800 (PST) schrieb Mandeep Baluja:

Scenario :- This macro is doing checking the Range of 1 lac rows by putting into an array and marking the last column as wrong if any negative entry comes in any column,This would be done for all rows one by one(Ya by Array),

What to achieve as always i want to remove this if condition and making this macro a effective one,Ya question comes how?, This can be possible when this macro check all the columns for a single row in one time not one by one cell value, I have seen evaluate function works in these kind of scenario where you need to work with output only.

something like Evaluate(Min(Lbound(Varout,2),ubound(varout,2)) <0 then mark wrong in column G !!,I am not sure how evaluate works here but any help would be appreciated basically i want to see the logics of different minds.

with this amount of data it is hard to find a faster way.
Try:

Sub Test()
Dim ptrn, re, Match
Dim varData As Variant, varout() As Variant
Dim i As Long, j As Long, LRow As Long

ptrn = "-\d"

LRow = Cells(Rows.Count, 1).End(xlUp).Row
varData = Range("A1:K" & LRow)

Set re = CreateObject("vbscript.Regexp")
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True

ReDim Preserve varout(UBound(varData) - 1, 0)
For i = LBound(varData) To UBound(varData)
For j = LBound(varData, 2) To UBound(varData, 2)
Set Match = re.Execute(varData(i, j))
If Match.Count 0 Then
varout(i - 1, 0) = "wrong"
Exit For
End If
Next
Next
Range("L1").Resize(UBound(varData)) = varout
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Query for Array !!!
 

hi claus, GS both of you have given a appreciable logic and the scenario is taking less than 3 seconds which is very much better than my previous one, One thing Claus in your code same if condition works there but how it's fast while running the loops,

Is Regex pattern evaluation is faster than the normal comparison operator ? if yes how ?

Query for Array !!!
 

Hi Mandeep,

Am Tue, 8 Dec 2015 09:45:45 -0800 (PST) schrieb Mandeep Baluja:

Is Regex pattern evaluation is faster than the normal comparison operator ? if yes how ?

with the pattern and the meta characters Regex can be almost 10 times
faster than normal comparison.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional