Results 1 to 16 of 16

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    user-friendly 0wn3r's Avatar
    Join Date: Mar:2006
    Location: Bulgaria,Plovdiv
    Posts: 1,165

    Excel VBA copy/paste

    , , .
    1.xls, , 2.xls(2 , ). :conf, 1, 1 . endloop statement-a. , 1, vlookup , .

    /. , // , .

    .
    believing

  2. #2
    Registered User
    Join Date: Oct:2003
    Location:
    Posts: 4,317
    . Sheet, property Range, Copy.
    - Sheet End . ( , .) . - . .
    , property .

    , lookup . Excel , - , . MS Access.

  3. #3
    user-friendly 0wn3r's Avatar
    Join Date: Mar:2006
    Location: Bulgaria,Plovdiv
    Posts: 1,165
    Quote Originally Posted by bsb View Post
    . Sheet, property Range, Copy.
    - Sheet End . ( , .) . - . .
    , property .

    , lookup . Excel , - , . MS Access.
    , 500 , . , , .

    Range() . sheet.range(a1:g100)() , a1 g100, ? ! Copy , .

    , , - , . , 1 10 , 12 22 ..

    , - , vlookup J, , g100, range-a (1:g100), ? (g100) end statement?

    . !
    believing

  4. #4
    Registered User
    Join Date: Oct:2003
    Location:
    Posts: 4,317
    , , , . , property End . - properties Range.
    , MS Office VBA. - VB, . , - -.

  5. #5
    Registered User
    Join Date: Aug:2009
    Location:
    Posts: 265
    Code:
    Sheet1.Range("A1:G100").Copy Destination:=Sheet2.Range("A1")
    , Destination - .
    Code:
    Sheet1.UsedRange
    ( Excel) Sheet1, Rows, Address .. .
    Code:
    Sheet1.Cells.SpecialCells(xlCellTypeLastCell)
    - - , .. , , Ctrl-End.

    , , VBA.

  6. #6
    user-friendly 0wn3r's Avatar
    Join Date: Mar:2006
    Location: Bulgaria,Plovdiv
    Posts: 1,165
    copy , .

    Code:
    Sub copy_excel_workbook()
    Workbooks.Open Filename:="C:\Documents and Settings\Nasko\Desktop\nggmh\Min_max_planning_report2007"
    Workbooks("Min_max_planning_report2007").Sheets("Min_max_planning_report2007").Range("A1:M100").copy Workbooks("REPORT").Sheets("Sheet1").Range("A1")
    Workbooks("Min_max_planning_report2007").Close
    End Sub
    Sheets("Min_max_planning_report2007") ( ) , sheet1. - VBE project window-a Sheet1(Min_max_planning_report2007), "Sheet1", "Min_max_planning_report2007", , (), sheet1 sheet1.

    a copy , .

    vlookup_adder , .

    Do...loop until
    Code:
        Do
    
        ActiveCell.Formula = "=-vlookup-"
    
        ActiveCell.Offset(1, 0).Select
    
        Loop Until IsEmpty(ActiveCell.Offset(1, 0))
    Loop until - IsEmpty(ActiveCell.Offset(1, 0)) IsEmpty(ActiveCell.Offset(2, 0)) - c++ (&&) .


    :
    Code:
     Loop Until (IsEmpty(ActiveCell.Offset(1, 0)) && IsEmpty(ActiveCell.Offset(2, 0)))
    :
    , vlookup active cell. , vlookup , .
    believing

  7. #7
    Registered User
    Join Date: Aug:2009
    Location:
    Posts: 265
    Quote Originally Posted by 0wn3r View Post
    ...
    Sheets("Min_max_planning_report2007") ( ) , sheet1.
    ...
    . - .

    Quote Originally Posted by 0wn3r View Post
    ...
    .
    ...
    , ActiveWorkbook. , , ThisWorkbook.
    const, - .
    , .

    Quote Originally Posted by 0wn3r View Post
    ...
    c++ (&&) .
    ...
    VBA -, && and.

    Quote Originally Posted by 0wn3r View Post
    ...
    , vlookup active cell.
    ...
    FormulaR1C1, - Formula.

  8. #8
    user-friendly 0wn3r's Avatar
    Join Date: Mar:2006
    Location: Bulgaria,Plovdiv
    Posts: 1,165
    copy , .

    , .
    Code:
    Sub fill_vlookup()
        Dim i As Integer
        Dim intRowCount As Integer
        Dim Counter As Range
        Set Counter = Range("A5")
        intRowCount = Sheets("Sheet1").UsedRange.Rows.Count
        ActiveSheet.Range("J5").Select
        For i = 1 To intRowCount
        ActiveCell.Formula = "=-vlookup-"
        If IsEmpty(Counter.Offset(1, 0)) = True Then
        Counter = Counter.Offset(3, 0)
        ActiveCell.Offset(3, 0).Select
        Else:
        Counter = Counter.Offset(1, 0)
        ActiveCell.Offset(1, 0).Select
        End If
        Next i
    End Sub
    Counter , . Counter = Counter.Offset(1, 0) , Counter A5. counter , select, .
    believing

  9. #9
    Registered User bpk's Avatar
    Join Date: Feb:2008
    Location: xxx
    Posts: 9,979
    .
    .Cells(i,j) .
    24/7 Asus P9X79 Deluxe+H2O i7-3930K @4625 MHz+H2O 64 GB Geil DDR3 11-12-12-30/2333 MHz+H2O 2*HD7970 VaporX 6 GB+H2O ROG Phoebus+Onkyo HT-S990 THX RAID0:2*256 GB Samsung 830 SSD+RAID0:4*1TB WD1000DHTZ+..EXTs CM HAF-X Seasonic SS-1250XM Gold 1250W Logitech G15 Gaming+G9x 30" Dell 3007WFP-HC @2560x1600

  10. #10
    user-friendly 0wn3r's Avatar
    Join Date: Mar:2006
    Location: Bulgaria,Plovdiv
    Posts: 1,165
    , .

    Code:
    Sub fill_vlookup()
        Dim i As Integer
        Dim intRowCount As Integer
        intRowCount = Range("A65536").End(xlUp).Row
        ActiveSheet.Range("J5").Select
        For i = 5 To intRowCount
        ActiveCell.Formula = "=100"  '    
        If IsEmpty(Cells(i + 1, 1)) = True Then
        ActiveCell.Offset(3, 0).Select
        i = i + 2
        Else:
        ActiveCell.Offset(1, 0).Select
        End If
        Next i
    End Sub
    , , vlookup
    :

    Code:
    ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(R[i]C),Sheet1!C:F,4,FALSE)), IF(ISNA(VLOOKUP(R[i]C),Sheet2!C:F,4,FALSE)), IF(ISNA(VLOOKUP(R[i]C),Sheet3!C:F,4,FALSE)),,VLOOKUP(R[i]C),Sheet1!C:F,4,FALSE)), VLOOKUP(R[i]C),Sheet2!C:F,4,FALSE)),VLOOKUP(R[i]C),Sheet3!C:F,4,FALSE))"
    ( 3 , IF ISNA)


    Code:
    ActiveCell.Formula = "=100"
    :
    Run-time Error '1004':
    Application-defined or object-defined error

    A5 A100 , (R[i]C). ?
    believing

  11. #11
    Registered User
    Join Date: Oct:2003
    Location:
    Posts: 4,317
    IncrementOffsetX IncrementOffsetY . Set, Counter

    , RAnge.Cells - .. Range.Cells(1,1) range, /. .

  12. #12
    Registered User bpk's Avatar
    Join Date: Feb:2008
    Location: xxx
    Posts: 9,979
    Range ...
    , Range(Cells(i,j))
    24/7 Asus P9X79 Deluxe+H2O i7-3930K @4625 MHz+H2O 64 GB Geil DDR3 11-12-12-30/2333 MHz+H2O 2*HD7970 VaporX 6 GB+H2O ROG Phoebus+Onkyo HT-S990 THX RAID0:2*256 GB Samsung 830 SSD+RAID0:4*1TB WD1000DHTZ+..EXTs CM HAF-X Seasonic SS-1250XM Gold 1250W Logitech G15 Gaming+G9x 30" Dell 3007WFP-HC @2560x1600

  13. #13
    Registered User bpk's Avatar
    Join Date: Feb:2008
    Location: xxx
    Posts: 9,979
    :
    - VLOOKUP- ;
    - 2 IF- , 3 ( VLOOKUP , 2- 3- , - VLOOKUP );
    - IF 5 , (2 );
    - ( 4, 2).
    .
    24/7 Asus P9X79 Deluxe+H2O i7-3930K @4625 MHz+H2O 64 GB Geil DDR3 11-12-12-30/2333 MHz+H2O 2*HD7970 VaporX 6 GB+H2O ROG Phoebus+Onkyo HT-S990 THX RAID0:2*256 GB Samsung 830 SSD+RAID0:4*1TB WD1000DHTZ+..EXTs CM HAF-X Seasonic SS-1250XM Gold 1250W Logitech G15 Gaming+G9x 30" Dell 3007WFP-HC @2560x1600

  14. #14
    user-friendly 0wn3r's Avatar
    Join Date: Mar:2006
    Location: Bulgaria,Plovdiv
    Posts: 1,165
    , , . 5 :
    Code:
    =IF(ISNA(VLOOKUP(A5,Sheet1!C:F,4,FALSE)), IF(ISNA(VLOOKUP(A5,Sheet2!C:F,4,FALSE)), IF(ISNA(VLOOKUP(A5,Sheet3!C:F,4,FALSE)),"Not Found",VLOOKUP(A5,Sheet3!C:F,4,FALSE)), VLOOKUP(A5,Sheet2!C:F,4,FALSE)),VLOOKUP(A5,Sheet1!C:F,4,FALSE))
    "ActiveCell.Formula = ", , Compile Error: Expected: end of statement , 2 . . , R1C1

    EDIT:
    :
    Code:
     ActiveCell.Formula = "=IF(ISNA(VLOOKUP(A5,Sheet4!C:F,4,FALSE)),"Not Found",VLOOKUP(A5,Sheet4!C:F,4,FALSE))"
    , .

    EDIT2:
    :
    Code:
    ActiveCell.Formula = "=IF(ISNA(VLOOKUP(A5,Sheet2!C:F,4,FALSE)),IF(ISNA(VLOOKUP(A5,Sheet3!C:F,4,FALSE)),IF(ISNA(VLOOKUP(A5,Sheet4!C:F,4,FALSE)),,VLOOKUP(A5,Sheet4!C:F,4,FALSE)), VLOOKUP(A5,Sheet3!C:F,4,FALSE)), VLOOKUP(A5,Sheet2!C:F,4,FALSE))"
    R1C1
    Last edited by 0wn3r; 15th August 2010 at 01:36.
    believing

  15. #15
    Registered User bpk's Avatar
    Join Date: Feb:2008
    Location: xxx
    Posts: 9,979
    , - Not Found (""Not Found"").
    , .
    .

    . . .
    24/7 Asus P9X79 Deluxe+H2O i7-3930K @4625 MHz+H2O 64 GB Geil DDR3 11-12-12-30/2333 MHz+H2O 2*HD7970 VaporX 6 GB+H2O ROG Phoebus+Onkyo HT-S990 THX RAID0:2*256 GB Samsung 830 SSD+RAID0:4*1TB WD1000DHTZ+..EXTs CM HAF-X Seasonic SS-1250XM Gold 1250W Logitech G15 Gaming+G9x 30" Dell 3007WFP-HC @2560x1600

  16. #16
    user-friendly 0wn3r's Avatar
    Join Date: Mar:2006
    Location: Bulgaria,Plovdiv
    Posts: 1,165
    Quote Originally Posted by bpk View Post
    , - Not Found (""Not Found"").
    , .
    .

    . . .
    , , .


    :
    Code:
    ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-9],Sheet2!RC[-7]:RC[-4],4,FALSE)),IF(ISNA(VLOOKUP(RC[-9],Sheet3!RC[-7]:RC[-4],4,FALSE)),IF(ISNA(VLOOKUP(RC[-9],Sheet4!RC[-7]:RC[-4],4,FALSE)),,VLOOKUP(RC[-9],Sheet4!RC[-7]:RC[-4],4,FALSE)), VLOOKUP(RC[-9],Sheet3!RC[-7]:RC[-4],4,FALSE)), VLOOKUP(RC[-9],Sheet2!RC[-7]:RC[-4],4,FALSE))"
    , , , RC[-7]:RC[-4] C18:F18, C:F

    , R1C[-7]:R1000[-4] !
    Last edited by 0wn3r; 15th August 2010 at 02:26.
    believing

Posting Permissions

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

Copyright © 1999-2011 . .
iskamPC.com | mobility.BG | Bloody's Techblog | | 3D Vision Blog |