Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 25
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 25
Default 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 ?


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Web Query Results to an Array Raul Excel Programming 0 November 6th 08 04:12 PM
ARRAY QUERY JingleRock Excel Programming 4 September 23rd 07 04:42 PM
Array data in SQL Query? mazzarin Excel Programming 4 June 26th 06 08:07 PM
Web Query & Array Questions! gr8guy Excel Programming 1 August 25th 04 11:05 PM
Query for data in an array anonymous Excel Programming 2 January 13th 04 12:51 PM


All times are GMT +1. The time now is 08:24 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"