Help with macro

Question

I inherited a spreadsheet which contains a recorded macro. However, it now has an error and I am trying to fix. I have re-recorded the macro for formatting but now I am stuck with the table population. Can you help me with the code to do the following below as describe? I have 2 sheets that I would like to populate from my formatted data. The first sheet is called X sales history contains 2 tables both gets the data from the raw data sheet. The first table shows the sales figures by product by state. Ex. cell C1 = SUM of all sales $ (from raw data column H)for product A in header "SA" in table 1.

cell c2 = sum of all sales $ (from raw data column H)for product B in header "SA" in table 1.

cell d2 = sum of all sales $ (from raw data column H)for product a in header "WA" in table 1.

Product Total SA WA VIC NSW QLD TAS A $0 B $0 C $0 D $0 E $0

This continues until all the cells from last 12 months header has been populated.

Table 2 (sheet X sales history) starts on cell 26 to populate. This should sum up qty (from raw data sheets column G) by product by state.

Product Total SA WA VIC NSW QLD TAS A 0 B 0 C 0 D 0

cell 26 = sum of all sales $ (column G in raw data sheets) for product a in WA.

The last table would be on the wrap analysis sheet. wrap line items are variable (depends on the number of items as recorded in the raw data list). The list of wrap line items should be filled starting from row 2 downwards. - the month column (col B) from raw data sheet is 23 months rolling data from last year same month to present. Can the script fill this months automatically, too? This should start from b1 to x1 then add a total column. It should look like this

Wrap Line Items M1 M2 M3 M4 M5 M6...M23 Total 1 10 0 1 11 3 2 2 total 12 0 1 Next the value of CELL B1 = SUM OF the month that matches column F in raw data and the header column in table 3 from sheet wrap analysis (must be sum of).

If you can add the totol for the columns and the rows as well. Thank you very much. Appreciate the help.

Grace

Answer

Grace,

I would like to help you and I think I can, but I am not following exactly what you want me to do. If you could send me a sample file with annotation about what you want where and what data to use produce what you want, I can provide some code or ask some clarifying questions. If you can send the sample file and explanation to twogilvy@msn.com, then I can try to help you figure it out.

Question

Some sample data for column A is below

25 45 84 (This cell is blank due to a formula) 15 14

What I want is that a macro should go from A1:A50 and in the first instance it finds a blank cell, it selects 45 columns to the right and all the rows below (based on xl down).

With this selection the macro clears all content.

I hope this makes sense and I hope you can help.

Answer

Sub Answer()

   For i = 1 To 50
       If Len(Cells(i, 1).Value) = 0 Then
           Set rgtoclear = Cells(i, 1).Offset(0, 45).End(xlDown)
           Set rgtoclear = Range(Cells(i, 1).Offset(0, 45), rgtoclear.Address)
           rgtoclear.ClearContents
           Exit Sub
       End If
   Next

End Sub