Results 1 to 16 of 16
Thread: Excel VBA copy/paste
Hybrid View
-
11th August 2010 16:49 #1
Excel VBA copy/paste
, , .
1.xls, , 2.xls(2 , ). :conf, 1, 1 . endloop statement-a. , 1, vlookup , .
/. , // , .
.
believing
-
11th August 2010 18:58 #2Registered User
Join Date: Oct:2003
Location:
Posts: 4,317
. Sheet, property Range, Copy.
- Sheet End . ( , .) . - . .
, property .
, lookup . Excel , - , . MS Access.
-
12th August 2010 00:40 #3
-
12th August 2010 08:13 #4Registered User
Join Date: Oct:2003
Location:
Posts: 4,317
, , , . , property End . - properties Range.
, MS Office VBA. - VB, . , - -.
-
12th August 2010 08:16 #5Registered User
Join Date: Aug:2009
Location:
Posts: 265
, Destination - .Code:Sheet1.Range("A1:G100").Copy Destination:=Sheet2.Range("A1")
( Excel) Sheet1, Rows, Address .. .Code:Sheet1.UsedRange
- - , .. , , Ctrl-End.Code:Sheet1.Cells.SpecialCells(xlCellTypeLastCell)
, , VBA.
-
13th August 2010 13:32 #6
copy , .
Sheets("Min_max_planning_report2007") ( ) , sheet1. - VBE project window-a Sheet1(Min_max_planning_report2007), "Sheet1", "Min_max_planning_report2007", , (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
), sheet1 sheet1.
a copy , .
vlookup_adder , .
Do...loop until
Loop until - IsEmpty(ActiveCell.Offset(1, 0)) IsEmpty(ActiveCell.Offset(2, 0)) - c++ (&&) .Code:Do ActiveCell.Formula = "=-vlookup-" ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell.Offset(1, 0))
:
:Code:Loop Until (IsEmpty(ActiveCell.Offset(1, 0)) && IsEmpty(ActiveCell.Offset(2, 0)))
, vlookup active cell. , vlookup , .believing
-
13th August 2010 14:18 #7Registered User
Join Date: Aug:2009
Location:
Posts: 265
-
14th August 2010 13:19 #8
copy , .
, .
Counter , . Counter = Counter.Offset(1, 0) , Counter A5. counter , select, .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
believing
-
14th August 2010 13:22 #9
.
.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
-
14th August 2010 16:43 #10
, .

, , vlookupCode: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
:
( 3 , IF ISNA)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))"
:Code:ActiveCell.Formula = "=100"
Run-time Error '1004':
Application-defined or object-defined error
A5 A100 , (R[i]C). ?
believing
-
14th August 2010 14:13 #11Registered User
Join Date: Oct:2003
Location:
Posts: 4,317
IncrementOffsetX IncrementOffsetY . Set, Counter

, RAnge.Cells - .. Range.Cells(1,1) range, /. .
-
14th August 2010 14:18 #12
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
-
14th August 2010 19:04 #13
:
- 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
-
15th August 2010 01:15 #14
, , .
5 :
"ActiveCell.Formula = ", , Compile Error: Expected: end of statementCode:=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))
, 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:
:R1C1Code: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))"
Last edited by 0wn3r; 15th August 2010 at 01:36.
believing
-
15th August 2010 01:39 #15
, - 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
-
15th August 2010 01:44 #16
, , .
:
, , , RC[-7]:RC[-4] C18:F18, C:FCode: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))"
, R1C[-7]:R1000[-4]
!
Last edited by 0wn3r; 15th August 2010 at 02:26.
believing




Reply With Quote


R9 280,
7th May 2023, 21:28 in