VBA For Loop for multiple ranges not transferring properly

[Lil Stinker]

I stink at understanding VBA For Loops so maybe you can help me. I'm looking for a way to record/copy quantities on Sheet1: Column I, Rows 10:16 and Column Q, Rows 10:16 to Sheet2: Columns I:V, End xlUp.Row (next available row).
Below is what I have been trying to make work but the first thing that goes wrong is the data transfers to the wrong starting column on Sheet2. It should start in Column I but it drops the data into H instead. The second issue is dealing with the second column on Sheet1... I don't know enough to even know where to begin with that issue. So far, the existing For Loop transfers data just not to the right location. I tried adding another For Loop after the first to account for the second column's quantities but that just negated the first For Loop... and still copied the data into the wrong columns.

Sub RA_SaveRecord()

Dim raROW As Long, equipROW As Long

With Sheet1
If .Range("AB4").Value = True Then
    raROW = Sheet2.Range("A10001").End(xlUp).Row + 1
    .Range("Q4").Value = .Range("AB6").Value
    Sheet2.Range("B" & raROW).Value = .Range("Q4").Value
End If
For equipROW = 10 To 16
    Sheet2.Cells(raROW, equipROW - 2).Value = .Range("I" & equipROW).Value 'add quantities
Next equipROW
End With

End Sub

I'd appreciate any knowledge you can pass my way. If you need more specifics, please let me know.

 

[Alex Blakenburg]

You really need to work out the process flow.
I asked if the headings and column I & Q Item list would always match and be in the same order. The answer wa yes.
That is not what you are now indicating.
So we are back to my previously asked question of what happens if you have a heading that has a quantity and is not in either column I or J ?
Also why are there formulas in the range that we previously identified as the copy to range ?

Have a think about how the RA Receipt and the Record Receipt product lists are updated and how they are kept in sync.
One has to be the source they can't both be.

 

[Lil Stinker]

Hi Alex,
Apologies for any confusion. It's a work in progress.
Nevertheless, here is the final version, no more changes, but a much larger data set.

The sample below displays how the data is entered on the RAreceipt sheet. The columns or cells that are grayed out include formulas in them and the numbers in these cells are not transferred to the record.

3-2 mrexcelTEST.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
9		RETURNS					QTY	RTND	Missing	Dmgd	non AW				QTY	RTND	Missing	Dmgd	non AW
10				Fiddlesticks			155	10	145		1	Certain Items			10	10	0	1
11		Out Date		Doohickies			210	10	200	2		Flim Flam			50	45	5
12		2/14/2023		Thingamajigs			315	10	305		5	Flotsam Jetsam			50	20	30
13				Watchacallits			315	10	305			Bits n' Bots
14		Return Date		Teenyboppers			15	1	14			Mother Loads			8	1	7
15		6/2/2023		Random Stuff			15	1	14			Little Thingies			10	2	8
16				Diggerydoos			15	1	14			Miscellaneous			8	1	7
RA Receipt

Cell Formulas
Range	Formula
H9,P9	H9	=IF($AD$4="Item Out","Check","RTND")
J9,R9	J9	=IF($AD$4="Item Out","Dbl Chk","Dmgd")
K9,S9	K9	=IF($AD$4="Item Out","","non AW")
G10	G10	=SUM(COUNTA($B$24:$O$38))
B11	B11	=IF(AND($P$8="Local Pickup",$AD$4="Item Out"),"Pickup Date",IF(AND($P$8="Local Delivery",$AD$4="Item Out"),"Delivery Date",IF($AD$4="Return","Out Date","Arrival Date")))
G14	G14	=SUM(COUNTA($P$24:$Q$38))
G15:G16	G15	=SUM(COUNTA($R$24:$S$38))
I9,Q9	I9	=IF($AD$4="Item Out","","Missing")
I10:I16	I10	=IFERROR($G10-$H10,"")
Q10:Q16	Q10	=IF(ISBLANK($O10),"",$O10-$P10)

The Receipt Record has matching column headers for every cell displayed in the sample, skipping any cells with formulas. The order the column headers are in go as follows:
Starts with Doohickies QTY, Thingamajigs QTY, Watchacallits QTY, Fiddlesticks RTND, Doohickies RTND, Thingamajigs RTND, Watchacallits RTND, Teenyboppers RTND, Random Stuff RTND, Diggerydoos RTND, skips all Missing cells and continues with Fiddlesticks Dmgd and so on. After Diggerydoos non AW, then we go to the next group of items beginning with Certain Items QTY and so on. 52 cells in total on the RA Receipt form, 52 columns on the Receipt Record. Not all cells on the RA Receipt will have numbers entered but the item names and column headers on both the RA Receipt and Record will not change. Each row, aside from the header row, on the Record sheet represents one Receipt.

With such a large dataset, what would work best to transfer the data to the correct cells while also keeping the macro as efficient as possible?