View Single Post
  #13   Report Post  
Posted to microsoft.public.excel.programming
L. Howard L. Howard is offline
external usenet poster
 
Posts: 852
Default Array list writing to an Array of 'scattered cells' ?

On Friday, March 24, 2017 at 2:59:42 AM UTC-7, GS wrote:
Another question, if I may?

With the sSrc$ = "A1,C3,E5,G7,I10 range on Sheet3, how would I make the
sTgt$ = "P2,N4,L6,J8,H10" be Sheet4

Howard

Sub Copy_Scattered_Cells_Garry_2()
Const sSrc$ = "A1,C3,E5,G7,I10": Const sTgt$ = "P2,N4,L6,J8,H10"
Dim n&, vaSrc, vaTgt

vaSrc = Split(sSrc, ","): vaTgt = Split(sTgt, ",")

For n = LBound(vaSrc) To UBound(vaSrc)

Range(vaTgt(n)) = Range(vaSrc(n))

Next 'n
End Sub


Typically, source data is on the active sheet and is being sent to a target
sheet which may or may not be in the same workbook. Claus' reply refs both
sheets as being in the same workbook. I'm inclined to ref the workbook so
there's no ambiguity...

Dim wksSrc As Worksheet, wksTgt As Worksheet

Set wksSrc = ThisWorkbook.Sheets("Sheet3")
Set wksTgt = ThisWorkbook.Sheets("Sheet4")

-OR- '//if copying to 1 or more workbooks...

Sub CopyScatteredCells()
Dim wkbSrc As Workbook, wkbTgt As Workbook
Dim wksSrc As Worksheet, wksTgt As Worksheet

Set wkbSrc = ThisWorkbook: Set wkbTgt = Workbooks("Other.xls")
Set wksSrc = wkbSrc.Sheets("Sheet3")
Set wksTgt = wkbTgt.Sheets("Sheet4")

'Do stuff...
wkbTgt.Close SaveChanges:=True

Set wkbTgt = Workbooks.Open("C:\SomeOther.xls")
Set wksTgt = wkbTgt.Sheets("Sheet4")

'Do more stuff...
wkbTgt.Close True

Cleanup: '//error handler exit
Set wksSrc = Nothing: Set wkbSrc = Nothing
Set wksTgt = Nothing: Set wkbTgt = Nothing
End Sub

--
Garry


Hi Garry,

I built the two Other/SomeOther sheets and it works perfect.

Thanks a lot for the code. It sure is fast, but there's not very much data.

Howard