Printable View
If the value in column 3 in the row j of array sn is identical to the value in column 1 of row jj in Array sp, then stop the loop.Code:For jj = 2 To UBound(sp)
If sn(j, 3) = sp(jj, 1) Then Exit For
Next
Als de waarde van kolom 3 in rij j van Array sn gelijk is aan de waarde van kolom in rij jj van Array sp, stop dan het zoeken.
Stop which loop?Quote:
If the value in column 3 in the row j of array sn is identical to the value in column 1 of row jj in Array sp, then stop the loop.
Code:Sub t()
For i = 1 To 3
For J = 1 To 100000
If J = 1 Then Exit For
MsgBox "Inner Loop # " & J
Next
MsgBox "Outer Loop # " & i
Next
End Sub
I see only one loop.Code:For jj = 2 To UBound(sp)
If sn(j, 3) = sp(jj, 1) Then Exit For
Next
If you know a method in VBA which exits an outer loop: please reveal/unveil your secret.... ;)
Me too. What does that loop do?Quote:
see only one loop.
GoToQuote:
If you know a method in VBA which exits an outer loop: please reveal/unveil your secret
Counter > For ToCode:Sub t()
For i = 1 To 3
For J = 1 To 100000
If J = 1 Then GoTo AfterEndOfOuterLoop
Next
MsgBox "Outer Loop # " & i
Next
AfterEndOfOuterLoop:
End Sub
Code:Sub t()
For i = 1 To 3
For J = 1 To 100000
If J = 1 Then
i = 4
Exit For
End If
Next
Next
End Sub
Hi,
I removed the values from grey areas and ran code again.
However it was not completed.
VB only got values for 2 rows only, please check
Attaching 2 images for sample
One more thing to add.roster sheet which is created has repeated names and dates. So I would not like names to be repeated for same dates
In my approach, it's only update rows in the selection that get updated.
Since you have so much data and it seems to be added daily, there did not seem to be a reason to re-compute thousands of lines of data. Just process the new data
I selected A2:A51 and ran the macro and all 2:51 gray areas were filled in
If you select A17 and run the macro, only row 17 is processes
If you really want to re-process all the data, that's easy to change
Deleted dups in roster also
Attachment 20346
At work, I use these settings on my Windows computer to speed things up. It's not exactly Excel help, but it will help the computer process things more efficiently.
Got it Buddy, it was cool and very informative.
I selected around 20000 rows and it calculated that in 30 sec.
I am OK with that.
In case I have to change any column name in Data sheet or rows in Mapping, it needs to be changed in VB also.
Can you tell me the line number for the same
The code didn't get through properly, I copied the code and got error variable or block variable not found.Quote:
Originally Posted by snb
While stepping it has a error on sn(j, 17) = sp(jj, 2)
Reports which I would be sending would be very confidential.
I wanted a (CONFIDENTIAL) Stamp with high transparency and little Slanted to be in between the report.
How can that be done.
I mean on several documents we have seen things like Confidential or Destroy after use.
Why working with VBA when you haven't got a clue ?
You are Brain Child using your programming language skills, I am learning VBA and keep on learning by looking at codes.
I cannot be as smart as you in snb,
I removed Option Explicit
Taking a course in the fundamentals of VBA would be very helpful.
VBA is a language in which we can't communicate if you are unfamiliar with the fundamentals.
Language skill are not hereditary but have to be acquired (at least I had to by studying books for instance, not by reading code; that comes much later)
Thanks for this.
I have already started the course and I am now on Do loop.
:clap2:
Thanks a lot to Paul, SNB and Vbaexpress team.Quote:
Originally Posted by snb
The way you are helping others solve their problem in excel and climb up VBA ladder is appreciated.
Paul/Snb can you tell me which would be a better idea to go with I mean normal loop or arrays loop for the file attached earlier
Apparently you have an empty column (column Q) in your file
You'd better remove that empty column.
You only have to adapt
Code:Sub M_snb()
sn = Sheets("Data").Cells(1).CurrentRegion.resize(,28)
I got error Subscript out of range
Well, that helps a lot. There's over twenty subscripts in that code.Quote:
Originally Posted by Svmaxcel
How about showing the code you're testing with a comment to tell where the error is occurring?
There's been numerous 'discussions' about using Option Explicit and explicitly Dim-ing all variables with a specific Type when ever possibleQuote:
Originally Posted by Svmaxcel
Option Explicit tell VBA that all variable have to be Dim-ed and to me that helps eliminate silly errors
With out Option Explicit this will return nothing when you run the macro since 'asn' is NOT 'ans'
With Option Explicit that would generate an error that could be fixed before running the macroCode:ans = 2 x A(1,2)
MsgBox asn
Code:Option Explicit
Dim ans as Long
ans = 2 x A(1,2)
MsgBox asn ' <<<<<< Compile time error
MsgBox ans ' <<<<<< OK
Same error proofing applies to always Dim-ing variables with the type that the are
It's easy to just Dim everything as Variant, or just leave off the 'As String' part, but if the macro is relying on a variable being a number and you accidently assign a string to it, many lines later
In 'One' the error doesn't show up until many likes after the bad input
In 'Two' the error shows up on the line that generated the error
In 'Three' the code works the first time, but fails the second time
To me, it's easier to debug a problem with Option Explicit and accurate Dim-ing. Maybe eventually I'll get so good I don't make errors like that but intel then, I like to use them
Code:Option Explicit
Sub One()
Dim a, b, c As Variant
a = "asfsadf"
b = 123
'many. many, many lines
c = b * a ' run time Type mis-match error
MsgBox c
End Sub
Sub Two()
Dim a As Long, b As Long, c As Long
a = "asfsadf" ' run time Type mis-match error
b = 123
'many. many, many lines
c = b * a
MsgBox c
End Sub
Sub Three()
Dim a As Long, b As Long, c As Long
Range("A1").Value = 10
a = Range("A1").Value ' No run time Type mis-match error
b = 123
'many. many, many lines
c = b * a
MsgBox c
'later
Range("A1").Value = "asdfasdf"
a = Range("A1").Value ' run time Type mis-match error
b = 123
'many. many, many lines
c = b * a
MsgBox c
End Sub
Excel doesn't have a Watermark capability (like MS Word).Quote:
Originally Posted by Svmaxcel
The workaround is to use an image
In Help search for 'Add Watermark' - just in case, I added directions in the attachment
Your comment isn't helpful.Quote:
Originally Posted by Svmaxcel
That was really helpful.
Now I understand better about Option Explicit, Dim-ing, Variants
Thanks a ton Paul.
Can you also tell me the option to recompute entire sheet?Quote:
Originally Posted by Paul_Hossler
Select the entire sheet.Quote:
option to recompute entire sheet?
I don't think Selecting the entire sheet will do itQuote:
Originally Posted by Svmaxcel
Try
Code:Worksheets("Sheet1").Calculate
I thought "re-compute" meant re-process and your code processed the Selection.
I mean that I don't want to select the range and excel should automatically check last used row and fill in the results
Let us call what Paul's code does a "Process," or "Processing" so we can avoid confusion.
Applicable Range is that Range that was Processed, and/or needs Processing. I think what you meant when you said, "Entire sheet."
As I understand, the Process converts formulas to values.
One can set up Paul's code to Process: A Selection; The entire applicable Range; Or only the unprocessed, (new,) Cells in the applicable Range.
Code:Set Start = Range("A:A").Find(What:="=", LookIn:=xlFormulas)
Fabulous!!!
Marking as Solved