How to loop through all rows of a range?

[ConfusedFrank]

Hi,

I have coded a macro that effectively copies a dynamic named range to a sheet called Sales chart. The working named range, called ItemsSold, is a single column consisting of uniquely filtered data.

My problem is the second named range; I want it to look up the source data, called Sales tracking (worksheet), and then sumif each row of itemsSold to find the total number of item sales. I want each of the values returned in the QuantitySold named range (Sales chart).

i know I need to create a loop, but I'm really new to VBA, and I don't know how.

From research, I obtained the following code and amended it to fit my question:

Dim rng As Range
Dim row As Range
Dim cell As Range


Set rng = Range("ItemsSold")


For Each row In rng.Rows
  For Each cell In row.Cells
    Sales Chart.Range("B4").Formula = "=SUM(SUMIF('Sales tracking'B2:B16,'Sales chart'!A4,'Sales tracking'!C2:C16)) 'Relative to each item
  Next cell
Next row

How do I make a macro do this?

If more information is required, please let me know. Thanks in advance.

 

[oldbrewer]

show us some data - just a few rows - with the output you seek

 

[ConfusedFrank]

Sure, I've added two tables below.

Sales tracking: I didn't mention it in my original post, but I have two additional named ranges that my working macro copies from, Item_code and Quantity_sold.
A B C
1 Reference Item code Quantity
2 9 H18 36
3 4 H18 22
4 4 J23 5
Sales chart:
A B
1 Item code Quantity
2 H18 58
3 J23 5

 

[ConfusedFrank]

Table wasn't working, sorry

 

[oldbrewer]

2 9 H18 36		ref	code	quan
3 4 H18 22		9	h18	36
4 4 J23 5			4	h18	22			Sum of quan
Sales chart:		4	j23	5			code	Total
A B								h18	58
1 Item code Quantity							j23	5
2 H18 58								Grand Total	63
3 J23 5
							a simple pivot table
							will get the quantities
							for each code
							these can be extracted to
							a second table
							that will drive the chart
							(start with I5 and check if
							I6 I7 I8 etc are blank or
							equal to Grand Total

<colgroup><col span="8"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[ConfusedFrank]

Thanks so much, I'm glad it's not too complicated!!

 

[ConfusedFrank]

Sorry, I don't know how to extract the data on to a dynamic named range.

I know you can use =GetPivotData to return the total of individual item sales, but I'm really struggling with coding a macro to automatically refer to new potential items.

 

[oldbrewer]

you don't use a macro to make a chart, it can be done straight from the pivot table or by lifting the data from the pivot table

OR IS MAKING A CHART FROM 2 TO SAY 99 ROWS ACCORDING TO THE DATA NOT YOUR REQUIREMENT ?

in this example the third row of data is Grand Total so you return na() as you do for 4th and subsequent which are blank.....

 

[ConfusedFrank]

No sorry, I was being an idiot. I didn't realise you could use a PivotChart. hanks again!