copying all columns of a row from an array to a "target" cell ?

[jxb]

to all

Looking at different ideas about searching through a range of cells for the value in the 1st column of an array (array is Nrow*10columns)
Sort of vlookup() or match() if you like. Nothing new/hasn't been done before . Used List of() in the past (or was it a Dictionary ?) as can use

.Exist

bur I am very rusty with all this. Maybe turning the s1t column of the array into a

Collection

might be faster
A few steps are broken down as a 1st pass (easier for me) but I suspect one can merge a few line eg setting range

im myarray As Variant ' this is a Nrow * 10column
'myarray = rgfull.Value  'to be added at some point

Dim wktarget As Worksheet: Set wktarget = Worksheets("target")
Dim irowtarget As Integer: irowtarget = 2
Dim icoltarget As Integer: icoltarget = 2
Dim rgID As Range

With wktarget
    'Worksheets("target").Range(Cells(irowtarget, icoltarget)).Select  ' this is not working
    .Range("B2").Select
    '.Range(Selection, Selection.End(xlDown)).Select
    Set rgID = .Range(Selection, Selection.End(xlDown))
End With

For Each c In rgID
    
    For ii = 0 To UBound(myarray)
    'find the row in the array matching the value of cell being processed
    'if found it then copy the data in the array row ii to the targetsheet
        If myarray(ii, 0) <> c.Value Then
            GoTo Skiptonext
        Else
            'past row ii, column 1 to 9 to cell(.Activecell.row,3)
            datafromarray = myarray(ii,1:ii,9) <---syntax required!!
            wktarget.Range(c.Address.Row, 3).Value = datafromarray
        End If
Skiptonext:
    Next

Next c

 

[rlv01]

There are enough problems with what you have posted that I find myself wanting to recommend that you start over; but it's hard to tell what you are trying to accomplish.

1. Avoid using Select or Selection. This is almost always possible to do. Instead define range objects and work on them directly.
2. If you want to find something in a range, search the range (range.find). Don't complicate things with arrays unless there is a demonstrated need, such as unacceptably slow performance. Most of the time there is not.
3. Declare your variables. This will make life easier for you and for anyone trying to help you. If you are going to declare some, then declare them all. You have declared several, but then inexplicably failed to declare several more (c,ii, datafromarray). Adding Option Explicit to the top of each code module will force variable declaration and is good practice.

Sub RangeFindExample()
    Dim wktarget As Worksheet
    Dim rgID As Range, rgfull As Range, R As Range, c As Range
    
    Set wktarget = ActiveSheet    'Worksheets("target")
    Set rgfull = wktarget.Range("G1:P20")
    
    With wktarget
        Set rgID = .Range("B2", .Range("B" & .Rows.Count).End(xlUp))
    End With
    
    'loop thru each cell in range rgID and search for that value in range rgfull
    For Each c In rgID
        Set R = rgfull.Find(What:=c.Value, Lookat:=xlWhole)
        If Not R Is Nothing Then
            Debug.Print "Found '" & R.Value & "' at address " & R.Address
            c.Offset(0, 3).Value = "Found '" & R.Value & "' at address " & R.Address
        Else
            c.Offset(0, 3).Value = "('" & c.Value & "' not found)"
        End If
    Next c
End Sub

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Data			Results		chylous	quaere	greeter	hyocholic	dewaxes	deadpans	immerging	sapote	chevre	hyocholic
2	quaere			Found 'quaere' at address $H$1		quota	bioseston	archeus	quota	bayonet	unheavy	facit	circinal	canicide	quota
3	bioseston			Found 'bioseston' at address $H$2		tarriers	duhat	caravans	whitens	lipolytic	thwarters	beauti	donor	riveret	whitens
4	duhat			Found 'duhat' at address $H$3		josephine	todidae	willywaw	dishcloth	callants	peckiness	boodledom	marathi	townees	dishcloth
5	todidae			Found 'todidae' at address $H$4		unseated	cedron	medicare	escript	huck	that	tensure	framing	oenomaus	escript
6	bandless			Found 'bandless' at address $M$6		chocked	ambsaces	vined	mensches	forest	dillweed	bandless	aperitive	chimbley	mensches
7	ricasso			Found 'ricasso' at address $N$7		thwarters	caprylic	epitheca	peshkar	heptanoic	rhynchote	vented	ricasso	topple	peshkar
8	redbuds			Found 'redbuds' at address $O$9		biali	faulkland	trimoric	servage	gestor	gull	unseated	orangeat	feedbag	servage
9	gravid			('gravid' not found)		dihedrons	gamori	jugular	urucury	amantillo	moneywort	hickwall	lineally	redbuds	urucury
10	shogunal			Found 'shogunal' at address $P$10		circinal	ungilded	grecian	expressed	pipet	moid	domino	royt	dialonian	shogunal
11	chylous			Found 'chylous' at address $G$1		urbanists	Fonoscine	faulkland	frizzlier	malikzadi	beakers	imbreathe	ridders	codol	frizzlier
12	nonoscine			('nonoscine' not found)		humulene	formylate	macrourid	bootlaces	woody	muddying	egos	drokpa	sculk	bootlaces
13	formylate			Found 'formylate' at address $H$12		thairm	gnawing	trochidae	veriscope	foggier	tess	tannined	theophile	dogfish	veriscope
14	gnawing			Found 'gnawing' at address $H$13		unseated	fibrinose	scalepan	gambolled	scandiums	unsalness	epicarpal	vellums	cedron	gambolled
15	fibrinose			Found 'fibrinose' at address $H$14		shoran	pancakes	isinglass	turtle	holostome	granules	reface	roadworks	monmouth	lamp
16	Jumper			('Jumper' not found)		sculk	oograph	papyrin	dird	outseek	marybud	mani	banjoist	ryegrass	dird
17	oograph			Found 'oograph' at address $H$16		champacs	molosse	fifish	nodosaur	physicker	pauperate	blodite	isostere	roadworks	nodosaur
18	molosse			Found 'molosse' at address $H$17		shawnees	filius	relong	apollonia	camelot	hotbeds	klva	kegler	nonesuch	apollonia
19	filius			Found 'filius' at address $H$18		younkers	rudy	deforcing	cesareans	bodock	idion	faulkland	jumelle	ovotestis	cesareans
20	rudy			Found 'rudy' at address $H$19		intsv	epiphegus	saghavart	shogunal	mesonasal	bassi	fumewort	fungoidal	paralalia	shoegunal
21	epiphegus			Found 'epiphegus' at address $H$20
Sheet1

 

[jxb]

Thanks
Looks like I forgot about .Find() and based on your example might do what I had in mind.

I was only thinking about array() as I may have to do something with the data before copying so an array might be better. My workflow seems to suggest that doing what I need to do post copying using xls directly might be better long term (for the user). So array might be out anyway.

There are a lot of things I need to test. For example I cannot have .end(xlup) to set rgID. Must be xldown. Small things that I need to finalise but still checking/agreeing a lot things about data format
———

The use of selection was a left-over of some testing, I never actually use it

Variables are always defined - again it appears just because of copy-paste to test bits of coding

John

 

[rlv01]

The use of selection was a left-over of some testing, I never actually use it

Variables are always defined - again it appears just because of copy-paste to test bits of coding

John

No one here is a mind reader. In the future, please explain this kind of thing in advance.