• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - Code decoding

Status
Not open for further replies.
PrevCode = ""
Do Until rs.EOF
CurrCode = Right(rs("Lot Rep Type ID").Value, 5)
If CurrCode <> PrevCode Then
RepCount = RepCount + 1
RepID(RepCount) = Right(rs("Lot Rep Type ID"), 5)
End If
PrevCode = Right(rs("Lot Rep Type ID").Value, 5)
rs.MoveNext
Loop
'end 1.
 

Luke M

Excel Ninja
Staff member
Code:
'First, not sure what the rs or RepID are. They appear
'to be objects that were defined somewhere else/earlier.
'================
 
'Sets a variable to be blank
PrevCode = ""
 
'Start a loop. Looks like the rs.EOF is a Boolean, so will either
'be True or False
Do Until rs.EOF
    'Set the variable to be the last 5 characters of one of the
    'parameters of the rs
    CurrCode = Right(rs("Lot Rep Type ID").Value, 5)
    
    'Compare the 2 variables
    'to True
    If CurrCode <> PrevCode Then
        
        'Increment a counter
        RepCount = RepCount + 1
        
        'Set a parameter of RepID. This is actually bad code, should write:
        'RepID(RepCount) = CurrCode
        'Since you already defined that variable
        RepID(RepCount) = Right(rs("Lot Rep Type ID"), 5)
        
    'End of the If check
    End If
    
    'Sets the PrevCode variable. Again, bad code. Should be:
    'PrevCode = CurrCode
    PrevCode = Right(rs("Lot Rep Type ID").Value, 5)
    
    'Go to the next thing in rs
    rs.MoveNext
    
'End of the rs.EOF loop
Loop
 
'Some comment
'end 1.
 

Debraj

Excel Ninja
PrevCode = ""
A variable called PrevCode, We are assigning a text, which is looks like NOTHING, and has been stored in PrevCode Veriable.. using one of the Right Shift operator “=”, Left shift operator, means the value in right side of the operator, will be stored in the left side..

Do Until rs.EOF
Do the even until.. a connection called RecordSet, in short, somewhere someone denoted it as rs.. read the file, until you found a special string called EndOfFile.. a bookmark in each file.. whichsay where the file has ended.

CurrCode = Right(rs("Lot Rep Type ID").Value, 5)
One column named as “Lot Rep Type ID”. In this column, read its what was written there.. for example, it was “Thank You for your WORDS”, Just read only last 5 character
If CurrCode <> PrevCode Then
If last 5 digit is not equal to “” (Blank) then
RepCount = RepCount + 1
assign RepCount as previous value of RepCount + 1, for the first time RepCount is 0, in next true condition, it will increase to 2.. and so on
RepID(RepCount) = Right(rs("Lot Rep Type ID"), 5)
RepId is a Arry Variable, which can store multiple value in it.. For example RepId(0) = “WORDS”, RepId(1) = “WORLD”, RepId(2) = “THREE” and so on
End If
PrevCode = Right(rs("Lot Rep Type ID").Value, 5)
Now re-set PreCode from “” to WORLD, which was just came, in next step, re-set PreCode from WORDS to WORLD and so on..
rs.MoveNext
Move to the next RecordSet, in Excel language.. read Next Row
Loop
If you have not reached EOF (EndOfFile) go back to the Do Until Line, and Do again all above

'end 1.

OOPS! Luke M already did the job.. :)
Thank God, Chandoo dont have any VBA Forensic Section.. :)
 
Code:
'First, not sure what the rs or RepID are. They appear
'to be objects that were defined somewhere else/earlier.
'================
 
'Sets a variable to be blank
PrevCode = ""
 
'Start a loop. Looks like the rs.EOF is a Boolean, so will either
'be True or False
Do Until rs.EOF
    'Set the variable to be the last 5 characters of one of the
    'parameters of the rs
    CurrCode = Right(rs("Lot Rep Type ID").Value, 5)
   
    'Compare the 2 variables
    'to True
    If CurrCode <> PrevCode Then
       
        'Increment a counter
        RepCount = RepCount + 1
       
        'Set a parameter of RepID. This is actually bad code, should write:
        'RepID(RepCount) = CurrCode
        'Since you already defined that variable
        RepID(RepCount) = Right(rs("Lot Rep Type ID"), 5)
       
    'End of the If check
    End If
   
    'Sets the PrevCode variable. Again, bad code. Should be:
    'PrevCode = CurrCode
    PrevCode = Right(rs("Lot Rep Type ID").Value, 5)
   
    'Go to the next thing in rs
    rs.MoveNext
   
'End of the rs.EOF loop
Loop
 
'Some comment
'end 1.
-----

Thanks Luke...its really made me understand, the entire coding is as follows, request you to simplify that as well :). It wud be a great help
--------


'2. Get replicated category from replicated asset, change rep type id to be cdx* so it will be tossed later in the process
For x = 1 To RepCount
sql = "SELECT EXTRACT_MASTER.* FROM EXTRACT_MASTER WHERE ((Extract_Master![Lot Rep Type Id]) = 'rsun" & RepID(x) & "')"
Set rs = db.OpenRecordset(sql)
RepCat(x) = rs("Lot TAS Category")
RepCoup(x) = rs("Sum Cpn Rt")
rs.Edit
rs("Lot Rep Type ID") = "CDX" & RepID(x)
rs.Update
Next x
'end 2.


'3. Update base asset with replicated category, coupon, TAS File Type, and change rep type id to be cds* so it will NOT be tossed later,
For x = 1 To RepCount
sql = "SELECT EXTRACT_MASTER.* FROM EXTRACT_MASTER WHERE ((Extract_Master![Lot Rep Type Id]) = 'rstn" & RepID(x) & "')"
Set rs = db.OpenRecordset(sql)
Do Until rs.EOF
rs.Edit
rs("Lot TAS Category") = "P" & RepCat(x)
'rs("Coupon") = RepCoup(x)
rs("Lot Rep Type ID") = "CDS" & RepID(x)
Select Case RepCat(x)
Case "A3"
rs("TAS File Type") = "EPAZ1"
Case "A2"
rs("TAS File Type") = "EPAZ2"
Case "A1"
rs("TAS File Type") = "EPAZ3"
Case "B3"
rs("TAS File Type") = "EPAZ4"
Case "B2"
rs("TAS File Type") = "EPAZ5"
Case "B1"
rs("TAS File Type") = "EPAZ6"
End Select
rs.Update
rs.MoveNext
Loop
Next x
'end 3.

RepCount = 0

'1. Get the numeric piece of the rep code for CDS/Strip strategy assets
sql = "SELECT EXTRACT_MASTER.* FROM EXTRACT_MASTER WHERE ((Extract_Master![Lot Rep Type Id]) Like 'SSTU*') " & _
"ORDER BY Extract_Master![Lot Rep Type Id]"
Set rs = db.OpenRecordset(sql)

PrevCode = ""

Do Until rs.EOF
CurrCode = Right(rs("Lot Rep Type ID").Value, 5)
If CurrCode <> PrevCode Then
RepCount = RepCount + 1
RepID(RepCount) = Right(rs("Lot Rep Type ID"), 5)
End If
PrevCode = Right(rs("Lot Rep Type ID").Value, 5)
rs.MoveNext
Loop
'end 1.


'2. Get replicated category from replicated asset, change rep type id to be cdx* so it will be tossed later in the process
For x = 1 To RepCount
sql = "SELECT EXTRACT_MASTER.* FROM EXTRACT_MASTER WHERE ((Extract_Master![Lot Rep Type Id]) = 'sstr" & RepID(x) & "')"
Set rs = db.OpenRecordset(sql)
RepCat(x) = Right(rs("Lot TAS Category"), 2)
RepCoup(x) = rs("Sum Cpn Rt")
Next x
'end 2.


'3. Update base asset with replicated category, coupon, TAS File Type, and change rep type id to be cds* so it will NOT be tossed later,
For x = 1 To RepCount
sql = "SELECT EXTRACT_MASTER.* FROM EXTRACT_MASTER WHERE ((Extract_Master![Lot Rep Type Id]) = 'sstu" & RepID(x) & "')"
Set rs = db.OpenRecordset(sql)
Do Until rs.EOF
rs.Edit
rs("Lot TAS Category") = "W" & RepCat(x)
'rs("Coupon") = RepCoup(x)
Select Case RepCat(x)
Case "A3"
rs("TAS File Type") = "EPAX1"
Case "A2"
rs("TAS File Type") = "EPAX2"
Case "A1"
rs("TAS File Type") = "EPAX3"
Case "B3"
rs("TAS File Type") = "EPAX4"
Case "B2"
rs("TAS File Type") = "EPAX5"
Case "B1"
rs("TAS File Type") = "EPAX6"
End Select
rs.Update
rs.MoveNext
Loop
Next x
'end 3.


MsgBox ("Done")

End Sub
 

Luke M

Excel Ninja
Staff member
Hi Ashish.

I've added comments to new portions of the code. There are many lines that are just duplicates of what I've explained before, so I didn't add comments to them. Let us know if there are any specific lines you are having trouble understanding.
Code:
'I'm assuming the title got cut off
Sub SomeName()
'Might also have some Dim statements to define your variables
 
 
'2. Get replicated category from replicated asset, change rep type id to be cdx* so it will be tossed later in the process
'Setup a loop statement
For x = 1 To RepCount
    
    'Define a variable string. Looks like we're getting data from a database somewhere
    Sql = "SELECT EXTRACT_MASTER.* FROM EXTRACT_MASTER WHERE ((Extract_Master![Lot Rep Type Id]) = 'rsun" & RepID(x) & "')"
    
    'rs is set to be the returned dataset
    Set rs = Db.OpenRecordset(Sql)
    
    'Define 2 variables from the returned data set
    RepCat(x) = rs("Lot TAS Category")
    RepCoup(x) = rs("Sum Cpn Rt")
    
    'Open the dataset editor
    rs.Edit
    
    'Rename part of the dataset
    rs("Lot Rep Type ID") = "CDX" & RepID(x)
    
    'Return dataset to main database
    rs.Update
Next x
'end 2.
 
 
'3. Update base asset with replicated category, coupon, TAS File Type, and change rep type id to be cds* so it will NOT be tossed later,
'Another loop statement
For x = 1 To RepCount
    Sql = "SELECT EXTRACT_MASTER.* FROM EXTRACT_MASTER WHERE ((Extract_Master![Lot Rep Type Id]) = 'rstn" & RepID(x) & "')"
    Set rs = Db.OpenRecordset(Sql)
    
    'Different type of loop. Note we'll be stuck in this loop until rs.EOF = TRUE
    Do Until rs.EOF
        rs.Edit
        rs("Lot TAS Category") = "P" & RepCat(x)
        'rs("Coupon") = RepCoup(x)
        rs("Lot Rep Type ID") = "CDS" & RepID(x)
        
        
        'Select Case statement used when multiple possible paths to take. Depending on value of RepCat
        'something will happen. The appropriate Case is performed, all others are ignored
        Select Case RepCat(x)
            Case "A3"
                rs("TAS File Type") = "EPAZ1"
            Case "A2"
                rs("TAS File Type") = "EPAZ2"
            Case "A1"
                rs("TAS File Type") = "EPAZ3"
            Case "B3"
                rs("TAS File Type") = "EPAZ4"
            Case "B2"
                rs("TAS File Type") = "EPAZ5"
            Case "B1"
                rs("TAS File Type") = "EPAZ6"
        End Select
        rs.Update
        rs.MoveNext
    Loop
Next x
'end 3.
 
RepCount = 0
 
'1. Get the numeric piece of the rep code for CDS/Strip strategy assets
Sql = "SELECT EXTRACT_MASTER.* FROM EXTRACT_MASTER WHERE ((Extract_Master![Lot Rep Type Id]) Like 'SSTU*') " & _
"ORDER BY Extract_Master![Lot Rep Type Id]"
Set rs = Db.OpenRecordset(Sql)
 
PrevCode = ""
 
Do Until rs.EOF
    CurrCode = Right(rs("Lot Rep Type ID").Value, 5)
    If CurrCode <> PrevCode Then
        RepCount = RepCount + 1
        RepID(RepCount) = Right(rs("Lot Rep Type ID"), 5)
    End If
    PrevCode = Right(rs("Lot Rep Type ID").Value, 5)
    rs.MoveNext
Loop
'end 1.
 
 
'2. Get replicated category from replicated asset, change rep type id to be cdx* so it will be tossed later in the process
For x = 1 To RepCount
    Sql = "SELECT EXTRACT_MASTER.* FROM EXTRACT_MASTER WHERE ((Extract_Master![Lot Rep Type Id]) = 'sstr" & RepID(x) & "')"
    Set rs = Db.OpenRecordset(Sql)
    RepCat(x) = Right(rs("Lot TAS Category"), 2)
    RepCoup(x) = rs("Sum Cpn Rt")
Next x
'end 2.
 
 
'3. Update base asset with replicated category, coupon, TAS File Type, and change rep type id to be cds* so it will NOT be tossed later,
For x = 1 To RepCount
    Sql = "SELECT EXTRACT_MASTER.* FROM EXTRACT_MASTER WHERE ((Extract_Master![Lot Rep Type Id]) = 'sstu" & RepID(x) & "')"
    Set rs = Db.OpenRecordset(Sql)
    Do Until rs.EOF
        rs.Edit
        rs("Lot TAS Category") = "W" & RepCat(x)
        'rs("Coupon") = RepCoup(x)
        Select Case RepCat(x)
            Case "A3"
                rs("TAS File Type") = "EPAX1"
            Case "A2"
                rs("TAS File Type") = "EPAX2"
            Case "A1"
                rs("TAS File Type") = "EPAX3"
            Case "B3"
                rs("TAS File Type") = "EPAX4"
            Case "B2"
                rs("TAS File Type") = "EPAX5"
            Case "B1"
                rs("TAS File Type") = "EPAX6"
        End Select
        rs.Update
        rs.MoveNext
    Loop
Next x
'end 3.
 
'Displays a message box at the end notifying the user the macro is finished
MsgBox ("Done")
 
End Sub
 

Excel_coder

New Member
Hello Team,

Could you please help me decode the below code?

My excel does not calculate results if I type SW** in my input cell. But it calculates fine for TW** input.

I want to calculate for SW** input but it does not calculate and deliver results instead it shows a red color in SW** input.

Please help me debug the same.



Code:
Dim Typ_$, Typ$ '--------------------------------------------------------------------------------------------------------------------

'      Cells(7, 8).Value = "No."
: Typ_$ = Cells(ze, 13).Value: '      '''Fig. for single printout
' Cells(ze, 43).Value = Typ$
 
If Typ_$ = "TW 10" Or Typ_$ = "TW10" Then Typ$ = "c"
If Typ_$ = "TW 15" Or Typ_$ = "TW15" Then Typ$ = "a"
If Typ_$ = "TW 20" Or Typ_$ = "TW20" Then Typ$ = "b"
If Typ_$ = "TW 25" Or Typ_$ = "TW25" Then Typ$ = "e"
If Typ_$ = "TW 30" Or Typ_$ = "TW30" Then Typ$ = "d"
If Typ_$ = "TW 31" Or Typ_$ = "TW31" Then Typ$ = "d"
If Typ_$ = "TW 50" Or Typ_$ = "TW50" Then Typ$ = "a"

: SW_$ = Cells(15, 13).Value:           'v2.8 SCRUTON-WELL option <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
  If Left(Typ_$, 1) <> "S" Then SW_$ = ""
If SW_$ = "+" Then                      '
  If Typ_$ = "SW 10" Or Typ_$ = "SW10" Then Typ$ = "c"
  If Typ_$ = "SW 15" Or Typ_$ = "SW15" Then Typ$ = "a"
  If Typ_$ = "SW 20" Or Typ_$ = "SW20" Then Typ$ = "b"
  If Typ_$ = "SW 25" Or Typ_$ = "SW25" Then Typ$ = "e"
  If Typ_$ = "SW 30" Or Typ_$ = "SW30" Then Typ$ = "d"
  If Typ_$ = "SW 31" Or Typ_$ = "SW31" Then Typ$ = "d"
  If Typ_$ = "SW 50" Or Typ_$ = "SW50" Then Typ$ = "a"
Else                                    '--------------------v2.8
  SW_$ = ""
' Cells(15, 13).Value = SW_$
End If                                  '--------------------v2.8

If Typ$ = "" Then Cells(ze, 13).Font.ColorIndex = 3: Exit Sub
 
Status
Not open for further replies.
Top