Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference 2 arrays in a For Loop
In the following I am trying to copy and paste data from one sheet to another
but arranged differently (so I can import into a database). I what to do this by defining the source ranges in the array mySourceMAT and the destination ranges in the array myDestMAT. I'm pretty sure I've got the source part right but not the dest part in my For loop. i.e. Range(myDestMAT(k)).Select as this where it debugs and the element is empty. Any ideas? Bruce Sub import() Dim k As Integer Dim mySourceMAT ReDim mySourceMAT(1 To 5) Dim myDestMAT(1 To 5) 'Application.ScreenUpdating = False 'variables myPeriod = Sheets("Instructions").Range("B16") myMarket = Sheets("Instructions").Range("B17") mySourceMAT = Array("B8:B18", "D8:D18", "F8:F18", "H8:H18", "J8:J18") myDestMAT(1) = Array("E2") myDestMAT(2) = Array("F2") myDestMAT(3) = Array("G2") myDestMAT(4) = Array("H2") myDestMAT(5) = Array("I2") myClear = "2:100" ' myDestMAT (1) 'clear contents With Sheets("Import").Rows(myClear) .ClearContents End With 'Start Update k = 1 For Each a In mySourceMAT Sheets("Inputs").Select Range(a).Select Selection.Copy Sheets("Import").Select Range(myDestMAT(k)).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False k = k + 1 Next a Range("A1").Select Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference 2 arrays in a For Loop
Hi,
Remove ARRAY( ..) as below: myDestMAT(1) = "E2" myDestMAT(2) = "F2" myDestMAT(3) = "G2" myDestMAT(4) = "H2" myDestMAT(5) = "I2" Add this before your clear statement Sheets("Import").Select HTH "Bruce" wrote: In the following I am trying to copy and paste data from one sheet to another but arranged differently (so I can import into a database). I what to do this by defining the source ranges in the array mySourceMAT and the destination ranges in the array myDestMAT. I'm pretty sure I've got the source part right but not the dest part in my For loop. i.e. Range(myDestMAT(k)).Select as this where it debugs and the element is empty. Any ideas? Bruce Sub import() Dim k As Integer Dim mySourceMAT ReDim mySourceMAT(1 To 5) Dim myDestMAT(1 To 5) 'Application.ScreenUpdating = False 'variables myPeriod = Sheets("Instructions").Range("B16") myMarket = Sheets("Instructions").Range("B17") mySourceMAT = Array("B8:B18", "D8:D18", "F8:F18", "H8:H18", "J8:J18") myDestMAT(1) = Array("E2") myDestMAT(2) = Array("F2") myDestMAT(3) = Array("G2") myDestMAT(4) = Array("H2") myDestMAT(5) = Array("I2") myClear = "2:100" ' myDestMAT (1) 'clear contents With Sheets("Import").Rows(myClear) .ClearContents End With 'Start Update k = 1 For Each a In mySourceMAT Sheets("Inputs").Select Range(a).Select Selection.Copy Sheets("Import").Select Range(myDestMAT(k)).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False k = k + 1 Next a Range("A1").Select Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
Help - Type mismatch when running loop with strings from arrays | Excel Programming | |||
Excel VBA Loop & Variable Reference | Excel Programming | |||
Worksheet_Change - loop within a loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |