Page 18 of 38 FirstFirst ... 8161718192028 ... LastLast
Results 171 to 180 of 380

Thread: Appendix Thread. ( Codes for other Threads, etc.) Event Coding Drpdown Data validation

  1. #171
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    In support of answer to this main excelfox Excel Forum Thread: http://www.excelfox.com/forum/showth...1188#post11188

    _____ Workbook: 1.xls ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    1
    a
    1
    2
    b
    2
    3
    c
    3
    4
    d
    Worksheet: Tabelle1






    _____ Workbook: 2.xlsx ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    1
    2
    3
    4
    d
    5
    6
    7
    8
    9
    10
    11
    12
    Worksheet: Tabelle1









    The two files shown above are attatched below:
    Attached Files Attached Files

  2. #172
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    In support of answer to this main excelfox Excel Forum Thread: http://www.excelfox.com/forum/showth...1188#post11188

    Sample file:
    _____ Workbook: MainMacroFile.xlsm ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    E
    1
    expiry date mark Brand value
    2
    27.06.2019
    a
    1
    3
    26.06.2019
    b
    2
    4
    25.06.2019
    c
    3
    5
    24.06.2019
    d
    4
    6
    23.06.2019
    e
    5
    7
    22.06.2019
    f
    6
    8
    21.06.2019
    g
    7
    9
    20.06.2019
    h
    8
    10
    27.06.2019
    i
    9
    11
    26.06.2019
    j
    10
    12
    13
    Worksheet: Tabelle1


    _____ Workbook: MainMacroFile.xlsm ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    E
    1
    expiry date mark Brand value
    2
    =TODAY() -(ROW()-3)
    a
    1
    3
    =TODAY() -(ROW()-3)
    b
    2
    4
    =TODAY() -(ROW()-3)
    c
    3
    5
    =TODAY() -(ROW()-3)
    d
    4
    6
    =TODAY() -(ROW()-3)
    e
    5
    7
    =TODAY() -(ROW()-3)
    f
    6
    8
    =TODAY() -(ROW()-3)
    g
    7
    9
    =TODAY() -(ROW()-3)
    h
    8
    10
    27.06.2019
    i
    9
    11
    26.06.2019
    j
    10
    12
    13
    Worksheet: Tabelle1

    Data analysis using VBA arrays
    I personally like to work with VBA arrays. So I put our data into an array, with this code line:
    ThisWorkbook.Worksheets.Item(1).Range("A1").CurrentRegion.Value2
    You can see what is in our arrData() if you step through the coding from within the VB Editor ( Hit key F8 with the cursor in the routine) , then before the run is finished and after the above code line ( Let arrData() ThisWorkbook.Worksheets.Item(1).Range("A1").CurrentRegion.Value2 ) , select any arrData(), and then hit key F9. This will add the array, arrData() to a watch window:
    F9 arrData().JPG: https://imgur.com/02xZas2
    F9 __ arrData().JPG: https://imgur.com/1QKwEb4

    The CurrentRegion
    The CurrentRegion range property of a range ( in this example the range is range A1 ), returns the range connected to that range which can be bordered by either empty columns and rows, or the spreadsheet boundaries. In this example , the CurrentRegion range associated with range A1, is that range enclosed by row 12, column D and the left and top spreadsheet boundaries
    _____ Workbook: MainMacroFile.xlsm ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    E
    1
    expiry date mark Brand value
    2
    27.06.2019
    a
    1
    3
    26.06.2019
    b
    2
    4
    25.06.2019
    c
    3
    5
    24.06.2019
    d
    4
    6
    23.06.2019
    e
    5
    7
    22.06.2019
    f
    6
    8
    21.06.2019
    g
    7
    9
    20.06.2019
    h
    8
    10
    27.06.2019
    i
    9
    11
    26.06.2019
    j
    10
    12
    13
    Worksheet: Tabelle1

    So this is effectively what our arrData() looks like:
    expiry date mark Brand value
    43643
    a
    1
    43642
    b
    2
    43641
    c
    3
    43640
    d
    4
    43639
    e
    5
    43638
    f
    6
    43637
    g
    7
    43636
    h
    8
    43643
    i
    9
    43642
    j
    10

    So , for example, arrData(5, 2) has a value of d, and arrData(5, 3) value is 4
    Effectively a VBA array is a fixed size spreadsheet, ( usually much smaller than a full spreadsheet ) . You cannot see its contents directly, but you can see it using the Watch Window, as discussed above. It can only have limited infomation - you cannot hold in it things like cell size and color infomation. We are using it to hold the .Value2 . .Value2 is the most fundamental value. .Value2 is usually the simple value that you see in the spreadsheet. One exception to this is with dates. The .Value2 of a date is that number held by Excel internally, which is a whole number starting at 1 for the date of January 1, 1900, and increasing by 1 for every day since then.
    So , for example, the .Value2 of January 5, 1900 is 5
    The .Value2 for the current day as I write this is 43643,which I can see if I step through the routine which is given in the example file , and hover over the variable , DteAujourd_hui , which the coding fills with the whole number part of the current date and time
    43643.jpg : https://imgur.com/mMC42MI
    The exact number you see will likely be slightly different , depending on where you are and when you run the routine
    Attached Files Attached Files

  3. #173
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    In support of this main Forum Thread:
    http://www.excelfox.com/forum/showth...1393#post11393

    Test file: target1.xlsx
    _____ Workbook: target1.xlsx ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    1
    2
    1
    100
    1
    2
    3
    2
    100
    1
    2
    4
    Worksheet: Tabelle1


    Test file: (Before) target2.xlsx
    _____ Workbook: target2.xlsx ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    1
    2
    1
    1234
    3
    3
    1234
    4
    Worksheet: Tabelle1


    Now run macro Sub Vixer() , which should fulfil this logic...
    ...If column E of target1.xlsx matches with column A of target2.xlsx then look column O of target1.xlsx is greater or column P of target1.xlsx is greater, whichever is greater calculate the 0.50% of that and multiply that with column K of target1.xlsx and paste the result to target2.xlsx from column C(if column C has data then column D and if column D has data then column E and so on...) the result should be in minus means whatever is the result put minus sign in that along with result

    The main macro file , macro.xlsm , gets populated thus:

    _____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    1
    column A of target2.xlsx column E of target1.xlsx column K of target1.xlsx column O of target1.xlsx column P of target1.xlsx
    2
    1
    1
    100
    1
    2
    3
    3
    2
    100
    1
    2
    4
    Worksheet: Tabelle2


    The test file, target2.xlsx , now gets changed to this
    Test file results After target2.xlsx

    _____ Workbook: target2.xlsx ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    E
    1
    2
    1
    1234
    -1
    3
    3
    1234
    4
    Worksheet: Tabelle1

    macro_ xlsm from Alan.jpg : https://imgur.com/pyf13dA
    Attachment 2347)
    Attached Images Attached Images
    Attached Files Attached Files

  4. #174
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    In support and appendix for this Thread Post:
    http://www.excelfox.com/forum/showth...1395#post11395
    Data Files from Vixer

    target2.xlsx
    _____ Workbook: target2.xlsx ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    1
    Symbol
    2
    ACC
    3
    TCS
    4
    MARICO
    5
    M&MFIN
    6
    TATAELXSI
    7
    BAJAJ-AUTO
    8
    BANKBARODA
    9
    10
    11
    12
    Worksheet: Sheet1






    target1.xls
    _____ Workbook: target1.xls ( Using Excel 2007 32 bit )
    Row\Col A B C D E F G H I J K L M N O P Q R S T U V W X Y
    1 UserId AccountId EntityName Exchg-Seg Symbol Instrument Name Option Type NetBuyValue NetSellValue NetValue NetBuyQty NetSellQty NetQty BEP SellAvgPrice BuyAvgPrice LastTradedPrice MarkToMarket Realized MarkToMarket Unrealized MarkToMarket EL MarkToMarket Trading Symbol Client Context Series/Expiry Strike Price
    2 WC5758 NSE MINDTREE EQ 765.00 760.10 -4.90 1 4 760.10 765.00 760.65 -4.9 -4.9 -4.9 MINDTREE-EQ EQ
    3 WC5758 NSE BHEL EQ 135.30 67.65 -67.65 2 4 1 67.65 67.65 67.65 67.5 -0.15 -0.15 -0.15 BHEL-EQ EQ
    4 WC5758 NSE SIEMENS EQ 2540.40 2504.10 -36.30 2 4 1252.05 1270.20 1253 -36.3 -36.3 -36.3 SIEMENS-EQ EQ
    5 WC5758 NSE SUNTV EQ 945.70 939.60 -6.10 2 4 469.80 472.85 473.6 -6.1 -6.1 -6.1 SUNTV-EQ EQ
    Worksheet: ap-Sheet1

    _____ Workbook: target1.xls ( Using Excel 2007 32 bit )
    UserId AccountId EntityName Exchg-Seg Symbol
    WC5758 NSE MINDTREE
    WC5758 NSE BHEL
    WC5758 NSE SIEMENS
    WC5758 NSE SUNTV
    WC5758 NSE RELCAPITAL
    WC5758 NSE JSWSTEEL
    WC5758 NSE TVSMOTOR
    WC5758 NSE RECLTD
    WC5758 NSE PIDILITIND
    WC5758 NSE VOLTAS
    WC5758 NSE TITAN
    WC5758 NSE PNB
    WC5758 NSE OFSS
    WC5758 NSE YESBANK
    WC5758 NSE MFSL
    WC5758 NSE PETRONET
    WC5758 NSE HDFC
    WC5758 NSE PVR
    WC5758 NSE SUNPHARMA
    WC5758 NSE NIITTECH
    WC5758 NSE GRASIM
    WC5758 NSE LICHSGFIN
    WC5758 NSE MANAPPURAM
    WC5758 NSE KAJARIACER
    WC5758 NSE BERGEPAINT
    WC5758 NSE VEDL
    WC5758 NSE UPL
    WC5758 NSE BAJAJFINSV
    WC5758 NSE ULTRACEMCO
    WC5758 NSE UJJIVAN
    WC5758 NSE TATAGLOBAL
    WC5758 NSE TATAELXSI
    WC5758 NSE STAR
    WC5758 NSE SRTRANSFIN
    WC5758 NSE SRF
    WC5758 NSE SAIL
    WC5758 NSE HAVELLS
    WC5758 NSE MCDOWELL-N
    WC5758 NSE PEL
    WC5758 NSE PAGEIND
    WC5758 NSE NMDC
    WC5758 NSE MOTHERSUMI
    WC5758 NSE MARICO
    WC5758 NSE M&MFIN
    WC5758 NSE L&TFH
    WC5758 NSE JUSTDIAL
    WC5758 NSE IGL
    WC5758 NSE IDFCFIRSTB
    WC5758 NSE IDEA
    WC5758 NSE IDBI
    WC5758 NSE HINDZINC
    WC5758 NSE HINDPETRO
    WC5758 NSE GODREJCP
    WC5758 NSE FEDERALBNK
    WC5758 NSE EXIDEIND
    WC5758 NSE ESCORTS
    WC5758 NSE DISHTV
    WC5758 NSE DHFL
    WC5758 NSE CUMMINSIND
    WC5758 NSE CONCOR
    WC5758 NSE COLPAL
    WC5758 NSE CESC
    WC5758 NSE CENTURYTEX
    WC5758 NSE CASTROLIND
    WC5758 NSE CANBK
    WC5758 NSE CADILAHC
    WC5758 NSE BIOCON
    WC5758 NSE BATAINDIA
    WC5758 NSE BANKINDIA
    WC5758 NSE ASHOKLEY
    WC5758 NSE ARVIND
    WC5758 NSE WIPRO
    WC5758 NSE SBIN
    WC5758 NSE APOLLOHOSP
    WC5758 NSE ADANIPOWER
    WC5758 NSE ADANIENT
    WC5758 NSE TECHM
    WC5758 NSE TCS
    WC5758 NSE TATASTEEL
    WC5758 NSE TATAPOWER
    WC5758 NSE RELIANCE
    WC5758 NSE POWERGRID
    WC5758 NSE NTPC
    WC5758 NSE LUPIN
    WC5758 NSE HINDALCO
    WC5758 NSE LT
    WC5758 NSE IOC
    WC5758 NSE INFY
    WC5758 NSE ICICIBANK
    WC5758 NSE IBULHSGFIN
    WC5758 NSE HEROMOTOCO
    WC5758 NSE HCLTECH
    WC5758 NSE GAIL
    WC5758 NSE EICHERMOT
    WC5758 NSE DRREDDY
    WC5758 NSE COALINDIA
    WC5758 NSE BPCL
    WC5758 NSE BOSCHLTD
    WC5758 NSE BHARTIARTL
    WC5758 NSE BANKBARODA
    WC5758 NSE BAJAJ-AUTO
    WC5758 NSE AUROPHARMA
    WC5758 NSE ASIANPAINT
    WC5758 NSE ADANIPORTS
    WC5758 NSE ACC
    Worksheet: ap-Sheet1

  5. #175
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    Testing for this Thread Post:
    http://www.excelfox.com/forum/showth...ll=1#post11416

    Data Before
    _____ Workbook: ap.xls ( Using Excel 2007 32 bit )
    Row\Col A B C D E F G H I J K L M N O P Q R S T U V W X Y
    1 UserId AccountId EntityName Exchg-Seg Symbol Instrument Name Option Type NetBuyValue NetSellValue NetValue NetBuyQty NetSellQty NetQty BEP SellAvgPrice BuyAvgPrice LastTradedPrice MarkToMarket Realized MarkToMarket Unrealized MarkToMarket EL MarkToMarket Trading Symbol Client Context Series/Expiry
    2 WC5758 NSE MINDTREE EQ ## ## ## 1 4 760.10 765.00 ## -5 -5 -5 MINDTREE-EQ EQ
    3 WC5758 NSE BHEL EQ ## ## ## 2 4 1 ## 67.65 67.65 68 -0 -0 -0 BHEL-EQ EQ
    4 WC5758 NSE SIEMENS EQ ## ## ## 2 4 1252.05 1270.20 ## ## ## ## SIEMENS-EQ EQ
    5 WC5758 NSE SUNTV EQ ## ## ## 2 4 469.80 472.85 ## -6 -6 -6 SUNTV-EQ EQ
    6 WC5758 NSE RELCAPITAL EQ ## ## ## 1 4 58.10 58.50 57 -0 -0 -0 RELCAPITAL-EQ EQ
    7 WC5758 NSE JSWSTEEL EQ ## ## ## 2 4 262.65 263.60 ## -2 -2 -2 JSWSTEEL-EQ EQ
    8 WC5758 NSE TVSMOTOR EQ ## ## ## 1 4 422.30 423.10 ## -1 -1 -1 TVSMOTOR-EQ EQ
    9 WC5758 NSE RECLTD EQ ## ## ## 1 4 138.55 140.50 ## -2 -2 -2 RECLTD-EQ EQ
    10 WC5758 NSE PIDILITIND EQ ## ## ## 1 4 1178.20 1180.00 ## -2 -2 -2 PIDILITIND-EQ EQ
    11 WC5758 NSE VOLTAS EQ ## ## ## 1 4 594.70 595.70 ## -1 -1 -1 VOLTAS-EQ EQ
    12 WC5758 NSE TITAN EQ ## ## ## 1 4 1097.05 1111.00 ## ## ## ## TITAN-EQ EQ
    13 WC5758 NSE PNB EQ ## ## ## 2 4 74.55 74.85 74 -1 -1 -1 PNB-EQ EQ
    14 WC5758 NSE OFSS EQ ## ## ## 2 4 3226.10 3239.95 ## ## ## ## OFSS-EQ EQ
    15
    Worksheet: ap-Sheet1

    Column Y Before ( As above )
    _____ Workbook: ap.xls ( Using Excel 2007 32 bit )
    Row\Col Y
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    Worksheet: ap-Sheet1


    Column Y After running routine Sub Vixer3_For_13_data_rows()
    _____ Workbook: ap.xls ( Using Excel 2007 32 bit )
    Row\Col Y
    1
    2 15.3
    3 1.353
    4 25.404
    5 9.457
    6 1.17
    7 5.272
    8 8.462
    9 2.81
    10 23.6
    11 11.914
    12 22.22
    13 1.497
    14 64.799
    Worksheet: ap-Sheet1

    Macro version for 13 data rows
    Code:
    Sub Vixer3_For_13_data_rows() ' http://www.excelfox.com/forum/showthread.php/2352-calculation-and-multiply-by-vba?p=11416&viewfull=1#post11416
    Rem 0 Open data workbook
    ' Workbooks.Open "F:\Excel0202015Jan2016\ExcelFox\vixer\Von Vixer\ap.xls"
    Rem 1 Workbook and worksheets info
    'Dim Wb1 As Workbook: Set Wb1 = Workbooks.Open("F:\Excel0202015Jan2016\ExcelFox\vixer\Von Vixer\ap.xls") '
    Dim Wb1 As Workbook: Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\ap.xls")
    Dim Ws1 As Worksheet: Set Ws1 = Wb1.Worksheets.Item(1) ' First worksheet, (as worksheet object) in open file "sample1.xlsx"
    Dim Lr As Long
     Let Lr = 14 ' To work with 13 data rows
    
    Rem 3 Main Loop for all data rows
    Dim Cnt As Long ' Main Loop for all data rows ================================================
        ' 3a)(i) ' compare column O is greater or column P is greater
        For Cnt = 2 To Lr ' for 13 data rows starting at row 2
        Dim Bigger As Double
            If Ws1.Range("O" & Cnt & "").Value > Ws1.Range("P" & Cnt & "").Value Then ' if column O is greater
             Let Bigger = Ws1.Range("O" & Cnt & "").Value
            Else
             Let Bigger = Ws1.Range("P" & Cnt & "").Value ' if column P is greater
            End If
        '3a)(ii) calculate the 0.50% of that and multiply the same with column L
        Dim Rslt As Double '
         Let Rslt = Bigger * (0.5 / 100) * Ws1.Range("L" & Cnt & "").Value ' calculate the 0.50% of that and multiply the same with column L
        '3b) paste the result to sample1.xlsx column Y
         Let Ws1.Range("Y" & Cnt & "").Value = Rslt
        Next Cnt '     Main Loop for all rows =====================================================
    
    Rem 4 save the changes and close the file
     Wb1.Close savechanges:=True
    End Sub
    Attached Files Attached Files

  6. #176
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    In support of this Thread:
    http://www.excelfox.com/forum/showth...Paste-by-a-Vba

    If column E of ap.xls matches with column A of leverage.xlsx then copy column E of leverage.xlsx and paste it to column Z of ap.xls

    Before:
    _____ Workbook: ap.xls ( Using Excel 2007 32 bit )
    Row\Col
    B
    C
    D
    E
    Z
    1
    AccountId EntityName Exchg-Seg Symbol
    2
    WC5758 NSE TCS
    3
    WC5758 NSE SRTRANSFIN
    4
    WC5758 NSE MARICO
    5
    WC5758 NSE M&MFIN
    6
    WC5758 NSE 20MICRONS
    7
    WC5758 NSE CONCOR
    8
    WC5758 NSE COALINDIA
    9
    WC5758 NSE BOSCHLTD
    10
    WC5758 NSE BERGEPAINT
    11
    WC5758 NSE 5PAISA
    12
    WC5758 NSE TATAELXSI
    13
    WC5758 NSE HINDPETRO
    14
    WC5758 NSE DISHTV
    15
    Worksheet: ap-Sheet1

    _____ Workbook: LEVERAGE1.xlsx ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    Stock Name pro times ultimate times
    2
    20MICRONS EQ INE144J01027
    19
    26
    36.35
    3
    TCS EQ INE253B01015
    19
    26
    13.5
    4
    3IINFOTECH EQ INE748C01020
    29
    39
    2.05
    5
    3MINDIA EQ INE470A01017
    48
    64
    21299
    6
    5PAISA EQ INE618L01018
    31
    41
    129.5
    7
    63MOONS EQ INE111B01023
    30
    40
    99.15
    8
    8KMILES EQ INE650K01021
    27
    36
    56.5
    9
    Worksheet: Sheet1

    If column E of ap.xls matches with column A of leverage.xlsx then copy column E of leverage.xlsx and paste it to column Z of ap.xls

    After
    example
    cell E2 of ap.xls matches with column A3 of leverage.xlsx then copy E3 of leverage.xlsx and paste it to Z2 of ap.xls



    _____ Workbook: ap.xls ( Using Excel 2007 32 bit )
    Row\Col
    B
    C
    D
    E
    Z
    1
    AccountId EntityName Exchg-Seg Symbol
    2
    WC5758 NSE TCS
    25.823112
    3
    WC5758 NSE SRTRANSFIN
    4
    WC5758 NSE MARICO
    5
    WC5758 NSE M&MFIN
    6
    WC5758 NSE 20MICRONS
    25.823112
    7
    WC5758 NSE CONCOR
    8
    WC5758 NSE COALINDIA
    9
    WC5758 NSE BOSCHLTD
    10
    WC5758 NSE BERGEPAINT
    11
    WC5758 NSE 5PAISA
    40.795512
    12
    WC5758 NSE TATAELXSI
    13
    WC5758 NSE HINDPETRO
    14
    WC5758 NSE DISHTV
    Worksheet: ap-Sheet1
    Attached Files Attached Files

  7. #177
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    For http://www.excelfox.com/forum/showth...ll=1#post11436

    Before:
    _____ Workbook: ap.xls ( Using Excel 2007 32 bit )
    Row\Col
    B
    C
    D
    E
    Z
    1
    AccountId EntityName Exchg-Seg Symbol
    2
    WC5758 NSE TCS
    3
    WC5758 NSE SRTRANSFIN
    4
    WC5758 NSE MARICO
    5
    WC5758 NSE M&MFIN
    6
    WC5758 NSE 20MICRONS
    7
    WC5758 NSE CONCOR
    8
    WC5758 NSE COALINDIA
    9
    WC5758 NSE BOSCHLTD
    10
    WC5758 NSE BERGEPAINT
    11
    WC5758 NSE 5PAISA
    12
    WC5758 NSE TATAELXSI
    13
    WC5758 NSE HINDPETRO
    14
    WC5758 NSE DISHTV
    Worksheet: ap-Sheet1

    _____ Workbook: LEVERAGE1.xlsx ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    E
    F
    1
    Stock Name pro times ultimate times
    2
    20MICRONS EQ INE144J01027
    19
    26
    36.35
    3
    TCS EQ INE253B01015
    19
    26
    13.5
    4
    3IINFOTECH EQ INE748C01020
    29
    39
    2.05
    5
    3MINDIA EQ INE470A01017
    48
    64
    21299
    6
    5PAISA EQ INE618L01018
    31
    41
    129.5
    7
    63MOONS EQ INE111B01023
    30
    40
    99.15
    8
    8KMILES EQ INE650K01021
    27
    36
    56.5
    Worksheet: Sheet1

    After:
    _____ Workbook: ap.xls ( Using Excel 2007 32 bit )
    Row\Col
    B
    C
    D
    E
    Z
    1
    AccountId EntityName Exchg-Seg Symbol
    2
    WC5758 NSE TCS
    25.823112
    3
    WC5758 NSE SRTRANSFIN
    4
    WC5758 NSE MARICO
    5
    WC5758 NSE M&MFIN
    6
    WC5758 NSE 20MICRONS
    25.823112
    7
    WC5758 NSE CONCOR
    8
    WC5758 NSE COALINDIA
    9
    WC5758 NSE BOSCHLTD
    10
    WC5758 NSE BERGEPAINT
    11
    WC5758 NSE 5PAISA
    40.795512
    12
    WC5758 NSE TATAELXSI
    13
    WC5758 NSE HINDPETRO
    14
    WC5758 NSE DISHTV
    Worksheet: ap-Sheet1

  8. #178
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    In support of this Thread:
    http://www.excelfox.com/forum/showthread.php/2364-Delete-row
    http://www.excelfox.com/forum/showth...364-Delete-row


    _____ Workbook: BasketOrder..xlsx ( Using Excel 2007 32 bit )
    Row\Col A B C D E F G H I J K L M N O P Q
    1 NSE EQ ACC NA NA NA 0 1 0 BUY MARKET NA CLI MIS DAY WC5758 NA
    2 NSE EQ ADANIPORTS NA NA NA 0 1 0 SELL MARKET NA CLI MIS DAY WC5758 NA
    3 NSE EQ AMBUJACEM NA NA NA 0 1 0 BUY MARKET NA CLI MIS DAY WC5758 NA
    4 NSE EQ ASIANPAINT NA NA NA 0 1 0 BUY MARKET NA CLI MIS DAY WC5758 NA
    5 NSE EQ AXISBANK NA NA NA 0 1 0 BUY MARKET NA CLI MIS DAY WC5758 NA
    6 NSE EQ BANKBARODA NA NA NA 0 1 0 SELL MARKET NA CLI MIS DAY WC5758 NA
    7
    Worksheet: BasketOrder. (1)


    _____ Workbook: 1.xls ( Using Excel 2007 32 bit )
    Row\Col A B C D E F G H I
    1 Exchange Symbol Series/Expiry Open High Low Prev Close LTP
    2 NSE ADANIPORTS EQ 409 409 398.65 407.2 402
    3 NSE ABC EQ 216.2 219.15 215.15 215.8 218
    4 NSE ASIANPAINT EQ 1409 1441.95 1401.85 1404.2 1441.3
    5 NSE AXISBANK EQ 732.9 739.3 728.15 727.45 733.65
    6 NSE BANKBARODA EQ 118.8 119.15 114.7 118.35 115.25
    7 NSE BHARTIARTL EQ 342.95 348.5 337.4 342.55 343.05
    8
    Worksheet: 1-Sheet1



    The Process:
    If cells of column C of basketorder.xlsx matches with cells of column B of 1.xlsx then delete the entire row of 1.xlsx(here entire row means the cells which matches delete that entire row)


    _____ Workbook: BasketOrder..xlsx ( Using Excel 2007 32 bit )
    NSE EQ ACC NA NA
    NSE EQ ADANIPORTS NA NA
    NSE EQ AMBUJACEM NA NA
    NSE EQ ASIANPAINT NA NA
    NSE EQ AXISBANK NA NA
    NSE EQ BANKBARODA NA NA
    Worksheet: BasketOrder. (1)

    _____ Workbook: 1.xls ( Using Excel 2007 32 bit )
    Exchange Symbol Series/Expiry Open High Low Prev Close LTP
    NSE ADANIPORTS EQ 409 409 398.65 407.2 402
    NSE ABC EQ 216.2 219.15 215.15 215.8 218
    NSE ASIANPAINT EQ 1409 1441.95 1401.85 1404.2 1441.3
    NSE AXISBANK EQ 732.9 739.3 728.15 727.45 733.65
    NSE BANKBARODA EQ 118.8 119.15 114.7 118.35 115.25
    NSE BHARTIARTL EQ 342.95 348.5 337.4 342.55 343.05
    Worksheet: 1-Sheet1

  9. #179
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    Using data from last post

    Before=
    _____ Workbook: 1.xls ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Exchange Symbol Series/Expiry Open High Low Prev Close LTP
    2
    NSE ADANIPORTS EQ
    409
    409
    398.65
    407.2
    402
    3
    NSE ABC EQ
    216.2
    219.15
    215.15
    215.8
    218
    4
    NSE ASIANPAINT EQ
    1409
    1441.95
    1401.85
    1404.2
    1441.3
    5
    NSE AXISBANK EQ
    732.9
    739.3
    728.15
    727.45
    733.65
    6
    NSE BANKBARODA EQ
    118.8
    119.15
    114.7
    118.35
    115.25
    7
    NSE BHARTIARTL EQ
    342.95
    348.5
    337.4
    342.55
    343.05
    8
    Worksheet: 1-Sheet1

    After=
    _____ Workbook: 1.xls ( Using Excel 2007 32 bit )
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Exchange Symbol Series/Expiry Open High Low Prev Close LTP
    2
    NSE ABC EQ
    216.2
    219.15
    215.15
    215.8
    218
    3
    NSE BHARTIARTL EQ
    342.95
    348.5
    337.4
    342.55
    343.05
    4
    Worksheet: 1-Sheet1



    Code:
    '
    Sub Vixer7() ' http://www.excelfox.com/forum/showthread.php/2364-Delete-row
    Rem 1 Workbook and worksheets info
    '1a) Workbook info
    Dim Wbm As Workbook: Set Wbm = ThisWorkbook ' The workbook containing macro
    Dim Wb1 As Workbook, Wb2 As Workbook ' (These will be set later when the workbooks are opened)
    Dim strWb1 As String: Let strWb1 = "1.xls"
    Dim strWb2 As String: Let strWb2 = "BasketOrder.xlsx" ' "BasketOrder..xlsx"
    '1b) Worksheets info
    Dim Ws1 As Worksheet, Ws2 As Worksheet ' (These will be set later when the workbooks are opened)
    Dim Lr1 As Long, Lr2 As Long: Let Lr1 = 7: Lr2 = 6 ' For sample file
    Rem 2 Open files   ..... we have to Open all the files all files are closed except the vba placed file
    ' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\BasketOrder..xlsx"
     Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb2
     Set Wb2 = ActiveWorkbook '
     Set Ws2 = Wb2.Worksheets.Item(1)
    ' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\1.xls"
     Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb1
     Set Wb1 = ActiveWorkbook ' The workbook just opened will now be the current active workbook
     Set Ws1 = Wb1.Worksheets.Item(1)
    Rem 3 The Process ..."....If cells of column C of basketorder.xlsx matches with cells of column B of 1.xlsx then delete the entire row of 1.xlsx...."....
    ' 3a) Range.Find Method  range info
    ' 3a)(i) Search range ( range to be searched )
    Dim rngSrch As Range: Set rngSrch = Ws2.Range("C1:C" & Lr2 & "") ' .."....column C of basketorder.xlsx
    ' 3a)(ii)' Data range, items to be searched for
    Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "") '  .."....cells of column B of 1.xlsx
    ' 3b) MAIN LOOP for all cells in basketorder.xlsx
    Dim Cnt As Long '_====================================MAIN LOOP===========================================
        For Cnt = Lr2 To 1 Step -1 ' data range to be searched for.... Important: I am going to delete rows in a loop: usually do such delete things in a backward loop. This is because I then effectively do a process on a cell or cells "behind me". So the process is done on a cell or cells no longer being considered. If I do the looping conventionally in the forward direction, then modification caused by the delete may effect the cells above, particularly their position. This can cause problems: After a delete, the cells above "move down". On the next loop I will then consider a cell above where I just was. So I will likely miss the next row to be considered, since that now occupies the position of the current loop. An alternative would be to loop forward, but after a delete to reduce the Loop count, Cnt, by 1. But changing the loop count variable in a loop is generally considered to be a bad idea     https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop.html#post3929967
        Dim MtchedCel As Variant ' For the range object of a matched cell if found, if not found it will be  Nothing  , so we must use a variant to allow for the type of  Range  or  Nothing
         Set MtchedCel = rngSrch.Find(What:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, Lookat:=xlWhole, Searchdirection:=xlNext, MatchCase:=True) ' rngDta.Item(Cnt) will be a cell of column C of basketorder.xlsx
            If Not MtchedCel Is Nothing Then ' If  cell of column C of basketorder.xlsx matches with cells of column B of 1.xlsx  Then .....
             rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp ' ..... delete the entire row of 1.xlsx
            Else
            End If
            
        Next Cnt '_====================================MAIN LOOP===============================================
    Rem 4  ...."... after the process close and save the file so that changes should be saved
     Wb1.Close savechanges:=True
    End Sub
    Attached Files Attached Files

  10. #180
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10

    Making Lr dynamic ( using rng.End(XlUp) for a single column. )

    Extra notes in support of this Thread:
    http://www.excelfox.com/forum/showth...364-Delete-row
    http://www.excelfox.com/forum/showth...ll=1#post11463
    http://www.excelfox.com/forum/showthread.php/2364-Delete-row





    Making Lr dynamic
    ( using rng.End(XlUp) for a single column. )


    For example, from http://www.excelfox.com/forum/showth...364-Delete-row :
    BasketOrder.xlsx for column C, Lr is 6
    ( BasketOrder.xlsx : https://app.box.com/s/v4b19po7jtjmh7wcswykbij3y896dv05
    )
    _____ Workbook: BasketOrder.xlsx ( Using Excel 2007 32 bit )
    Row\Col B C D
    1 EQ ACC NA
    2 EQ ADANIPORTS NA
    3 EQ AMBUJACEM NA
    4 EQ ASIANPAINT NA
    5 EQ AXISBANK NA
    6 EQ BANKBARODA NA
    7
    Worksheet: BasketOrder. (1)

    Lr2, for column C is :
    Ws2.Range("C" & Ws2.Rows.Count).End(xlUp).Row
    or
    Ws2.Cells.Item(Ws2.Rows.Count, 3).End(xlUp).Row
    or
    Ws2.Cells.Item(Ws2.Rows.Count, "C").End(xlUp).Row


    To explain:
    '_- 1 :- Rows.Count Property of a worksheet
    Ws2.Range("C" & Ws2.Rows.Count)
    or
    Ws2.Cells.Item(Ws2.Rows.Count, 3)
    or
    Ws2.Cells.Item(Ws2.Rows.Count, "C")


    For Excel 2007 and higher versions ( .xlsx .xlsm ), this is 1048576 rows in a worksheet ( ImmediateWindow RowsCount XL 2007.JPG : https://imgur.com/NHHdylV )
    Ws2.Range("C" & 1048576)
    or
    Ws2.Cells.Item(1048576, 3)
    or
    Ws2.Cells.Item(1048576, "C")

    This is the last cell in column C:
    _____ Workbook: BasketOrder.xlsx ( Using Excel 2007 32 bit )
    Row\Col B C D
    1048574
    1048575
    1048576
    Worksheet: BasketOrder. (1)

    So we are at the bottom of the worksheet….

    '_- 2 :- .End(xlUp) Property action
    This is the same as keyboard keys _ Ctrl+UpArrow
    Ctrl + UpArrow.JPG : https://imgur.com/w5w8KxZ

    ….This action will take you back up to the next filled cell:
    _End(XlUp).JPG : https://imgur.com/JQJxc1s

    ….So we are at the last filled cell in column C

    '_- 3 :- .Row Property
    This will give you the row number of the cell
    _Row.JPG : https://imgur.com/bMpaBOv




    For example, from http://www.excelfox.com/forum/showth...364-Delete-row :
    1.xls for column B, Lr is 7
    ( 1.xls : https://app.box.com/s/beqlzzl3nwjff2ocyz4ox8twu5jnqd6e )
    _____ Workbook: 1.xls ( Using Excel 2007 32 bit )
    Row\Col A B C
    1 Exchange Symbol Series/Expiry
    2 NSE ADANIPORTS EQ
    3 NSE ABC EQ
    4 NSE ASIANPAINT EQ
    5 NSE AXISBANK EQ
    6 NSE BANKBARODA EQ
    7 NSE BHARTIARTL EQ
    8
    Worksheet: 1-Sheet1

    Lr1, for column B is :
    Ws1.Range("B" & Ws1.Rows.Count).End(xlUp).Row
    or
    Ws1.Cells.Item(Ws1.Rows.Count, 2).End(xlUp).Row
    or
    Ws1.Cells.Item(Ws1.Rows.Count, "B").End(xlUp).Row


    To explain:
    '_- 1 :- Rows.Count Property of a worksheet
    Ws1.Range("B" & Ws1.Rows.Count))
    or
    Ws1.Cells.Item(Ws1.Rows.Count, 2)
    or
    Ws1.Cells.Item(Ws1.Rows.Count, "B")


    For Excel 97 - 2003 ( .xls ), this is 65536 rows in a worksheet ( ImmediateWindow RowsCount XL 2003.JPG : https://imgur.com/iOmrf9n )
    Ws1.Range("B" & 65536 ))
    or
    Ws1.Cells.Item( 65536 , 2)
    or
    Ws1.Cells.Item( 65536 , "B")


    This is the last cell in column B:
    (Last Worksheet Row in XL 2003.JPG : https://imgur.com/iaEPoZG )
    Attachment 2401
    _____ Workbook: 1.xls ( Using Excel 2007 32 bit )
    Row\Col A B C
    65533
    65534
    65535
    65536
    Worksheet: 1-Sheet1

    So we are at the bottom of the worksheet …

    '_- 2 :- .End(xlUp) Property action
    This is the same as keyboard keys _ Ctrl+UpArrow
    Ctrl + UpArrow.JPG : https://imgur.com/w5w8KxZ
    Attachment 2402

    …This action will take you back up to the next filled cell:
    _End(XlUp) XL2003.JPG : https://imgur.com/JYPd95V
    Attachment 2403

    ….So we are at the last filled cell in column B

    '_- 3 :- .Row Property
    This will give you the row number of the cell
    _ Row.JPG : https://imgur.com/ZWCFvmr
    Attachment 2404





    Example Demo
    For uploaded files..
    BasketOrder.xlsx : https://app.box.com/s/v4b19po7jtjmh7wcswykbij3y896dv05
    1.xls : https://app.box.com/s/beqlzzl3nwjff2ocyz4ox8twu5jnqd6e

    run this macro
    Code:
    Sub Vixer8_MakingLrDynamic() ' http://www.excelfox.com/forum/showthread.php/2364-Delete-row?p=11463&viewfull=1#post11463
    '
    Rem 1 Workbook and worksheets info
    '1a) Workbook info
    Dim Wbm As Workbook: Set Wbm = ThisWorkbook ' The workbook containing macro
    Dim Wb1 As Workbook, Wb2 As Workbook ' (These will be set later when the workbooks are opened)
    Dim strWb1 As String: Let strWb1 = "1.xls"                             '      --- 1.xls      :       https://app.box.com/s/beqlzzl3nwjff2ocyz4ox8twu5jnqd6e
    Dim strWb2 As String: Let strWb2 = "BasketOrder.xlsx" ' "BasketOrder..xlsx"   --- BasketOrder.xlsx : https://app.box.com/s/v4b19po7jtjmh7wcswykbij3y896dv05
    '1b) Worksheets info
    Dim Ws1 As Worksheet, Ws2 As Worksheet ' (These will be set later when the workbooks are opened)
    '                 Dim Lr1 As Long, Lr2 As Long ' To be determined from files                                                                            : Let Lr1 = 7: Lr2 = 6 ' For sample files
    Rem 2 Open files   ..... we have to Open all the files all files are closed except the vba placed file
    ' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\BasketOrder..xlsx"
     Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb2
     Set Wb2 = ActiveWorkbook '
     Set Ws2 = Wb2.Worksheets.Item(1)
    ' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\1.xls"
     Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb1
     Set Wb1 = ActiveWorkbook ' The workbook just opened will now be the current active workbook
     Set Ws1 = Wb1.Worksheets.Item(1)
    
    
    
    Rem 3 making Lr dynamic
    Dim Lr2 As Long
     Let Lr2 = Ws2.Range("C" & Ws2.Rows.Count).End(xlUp).Row
     Let Lr2 = Ws2.Cells.Item(Ws2.Rows.Count, 3).End(xlUp).Row
     Let Lr2 = Ws2.Cells.Item(Ws2.Rows.Count, "C").End(xlUp).Row
    Dim Lr1 As Long
     Let Lr1 = Ws1.Range("B" & Ws1.Rows.Count).End(xlUp).Row
     Let Lr1 = Ws1.Cells.Item(Ws1.Rows.Count, 2).End(xlUp).Row
     Let Lr1 = Ws1.Cells.Item(Ws1.Rows.Count, "B").End(xlUp).Row
    
    '3b) demo
     Ws2.Activate
     MsgBox prompt:="Lr in worksheet " & Ws2.Name & ", in workbook " & Wb2.Name & " is   " & Lr2 & vbCrLf & "(last row in worksheet is   " & Ws2.Rows.Count & ")"
     Ws1.Activate
     MsgBox prompt:="Lr in worksheet " & Ws1.Name & ", in workbook " & Wb1.Name & " is   " & Lr1 & vbCrLf & "(last row in worksheet is   " & Ws1.Rows.Count & ")"
    
    Rem 4 close files
    Wb2.Close: Wb1.Close
    End Sub







    Ref:
    https://www.excelforum.com/hello-int...ess-forum.html
    http://www.excelfox.com/forum/showth...ll=1#post10192
    http://www.excelfox.com/forum/showth...1466#post11466











    BasketOrder.xlsx : https://app.box.com/s/v4b19po7jtjmh7wcswykbij3y896dv05
    1.xls : https://app.box.com/s/beqlzzl3nwjff2ocyz4ox8twu5jnqd6e
    Attached Images Attached Images
    Attached Files Attached Files

Similar Threads

  1. Replies: 189
    Last Post: 02-06-2025, 02:53 PM
  2. Replies: 293
    Last Post: 09-24-2020, 01:53 AM
  3. Appendix Thread. Diet Protokol Coding Adaptions
    By DocAElstein in forum Test Area
    Replies: 6
    Last Post: 09-05-2019, 10:45 AM
  4. Restrict data within the Cell (Data Validation)
    By dritan0478 in forum Excel Help
    Replies: 1
    Last Post: 07-27-2017, 09:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •