PDA

View Full Version : Solved: Code Error - Extra Column Created?



Carpiem
07-06-2006, 12:40 PM
Good Afternoon,

Some help would be appreciated with 2 coding errors:

1st - The “Sales” sheet table shows column H (NetSales) with zero sales. Something is horribly wrong. :dunno

Column I sales numbers should appear in column H (NetSales). The “Expected” sheet shows what I was trying to do with code below.


Sub FillTable()
Dim TCol As Long
Dim LastRow As Long

Application.ScreenUpdating = False

'Clear upper table
Rows("1:13").ClearContents

'Set Group Titles
Range("A1") = "Group"
Range("A1").Interior.ColorIndex = 37
Range("A1").Font.Bold = True
Range("A2") = "Drives"
Range("A3") = "GMS"
Range("A4") = "HMI"
Range("A5") = "IC"
Range("A6") = "MCC"
Range("A7") = "Motion"
Range("A8") = "Other"
Range("A9") = "PLC"
Range("A10") = "Safety"
Range("A11") = "Sensors"
Range("A12") = "Software"
Range("A13") = "Total"
Range("A13").Font.Bold = True
Range("A1:A13").HorizontalAlignment = xlLeft

'Select Customer row to last cell and column used
TCol = Range("C16").End(xlToRight).Column

'Copy Customers to upper table - From B1 across
Range("C16").Resize(, TCol).Copy Range("B1")
LastRow = Range("B16").End(xlDown).Row - 1

'Total Sales by Group - Column "B"
Range("B2").Formula = "=SUMIF($B17:$B" & LastRow & ",""Drives"",C17:C" & LastRow & ")"
Range("B3").Formula = "=SUMIF($B17:$B" & LastRow & ",""GMS"",C17:C" & LastRow & ")"
Range("B4").Formula = "=SUMIF($B17:$B" & LastRow & ",""HMI"",C17:C" & LastRow & ")"
Range("B5").Formula = "=SUMIF($B17:$B" & LastRow & ",""IC"",C17:C" & LastRow & ")"
Range("B6").Formula = "=SUMIF($B17:$B" & LastRow & ",""MCC"",C17:C" & LastRow & ")"
Range("B7").Formula = "=SUMIF($B17:$B" & LastRow & ",""Motion"",C17:C" & LastRow & ")"
Range("B8").Formula = "=SUMIF($B17:$B" & LastRow & ",""Other"",C17:C" & LastRow & ")"
Range("B9").Formula = "=SUMIF($B17:$B" & LastRow & ",""PLC"",C17:C" & LastRow & ")"
Range("B10").Formula = "=SUMIF($B17:$B" & LastRow & ",""Safety"",C17:C" & LastRow & ")"
Range("B11").Formula = "=SUMIF($B17:$B" & LastRow & ",""Sensors"",C17:C" & LastRow & ")"
Range("B12").Formula = "=SUMIF($B17:$B" & LastRow & ",""Software"",C17:C" & LastRow & ")"
Range("B13").Formula = "=SUM(C17:C" & LastRow & ")"

Range("B2:B13").Copy Range("B2:B13").Resize(, TCol - 1)
Range("A2").Offset(, TCol).Resize(12) = "=SUM(" & Range("B2").Resize(, TCol - 1).Address(0, 0) & ")"

'Last Column Title = NetSales
Range("A1").End(xlToRight).Offset(0, 1).Value = "NetSales"
Range("A1").End(xlToRight).Interior.ColorIndex = 37
Range("A1").End(xlToRight).Font.Bold = True

'Align Table
Range("B1").End(xlToRight).HorizontalAlignment = xlCenter
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).HorizontalAlignment = xlGeneral

Borders ‘format Upper Table Borders

Range("A2").Select
Application.ScreenUpdating = True
End Sub


Dream Time – Dumb Question

Would VBA recognize the Groups automatically? This applies to the code portion under ? 'Set Group Titles. If yes, would it also be able to automatically adjust the rest of the coding?

I thought this might appeal to the “Gurus” at Vbax. Hope I don’t get an “earful”. :)

------------------------------------------------------------------

2nd- I would like the Vlookup function used in column “B”, to run down as far as column “A” is filled (from at row 16). My “Vlookup” macro is set to a specific depth for Column “A”. So it is pretty useless, as the items in Col “A” change on a daily, weekly and monthly basis…. depending on when run.


Sub Vlookup()
'Return Group
Range("B17").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R16C1:R1000C1,GroupCodes!C1:C2,2,0)"
Selection.AutoFill Destination:=Range("B17:B31")
Range("B17:B207").Select
Range("A17").Select

'Convert Vlookup To Values
Range("B16").Select
Range(Selection, Selection.End(xlDown)).Copy
Range("B16").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

Range("A2").Select
End Sub


Thank you,

Brian

OBP
07-06-2006, 12:47 PM
Hello again, was the work that I did on your last post OK?

mdmackillop
07-07-2006, 12:44 AM
Try

'Total Sales by Group rows - starting Column "B"
Range("B2:G12").FormulaR1C1 = "=SUMIF(R17C2:R31C2,RC1,R17C[1]:R31C[1])"
Range("H2:H12").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
Range("B13:H13").FormulaR1C1 = "=SUM(R[-11]C:R[-1]C)"

Carpiem
07-07-2006, 07:45 AM
Madainn mhath MD,

Very neat how you reduced 11 lines to 3. :clap:

The NetSales numbers still end up 1 column to the right of where it should be.

Customer1 Customer2 NetSales Wrong Column
100.00 100.00 0.00 200.00


Have not idea why this is happening. Tried stepping through the code but that didn't reveal anything to me.


As always a pleasure to hear from you.....:thumb

Brian

Aussiebear
07-07-2006, 07:59 AM
Brian, Your formulas in Cells I2:I13 indicate =Sum B to H, Should they not read B to G?

Carpiem
07-07-2006, 11:30 AM
Brian, Your formulas in Cells I2:I13 indicate =Sum B to H, Should they not read B to G?

G'Day Aussiebear,

Absolutley, but I am too thick to fix it.

The "Epected " sheet in the uploaded file shows what I was attempting to do with vba.

Any ideas from the "Land Down Under" would be Bonzer! :thumb

I am fast losing what sanity I have. :rotlaugh:

Thank you ,,,,, Brian

Aussiebear
07-07-2006, 02:42 PM
I simply edited your formulas by hand to reflect the range as B2:G2 etc then selected all the modified cells and dragged across to the proper location and accepted the message re replacing the cell contents.

mdmackillop
07-07-2006, 03:47 PM
Hi Brian
My code puts the net sales in Column H with = B:G, Column I is not used
Regards
Malcolm
BTW, you can compact your align table code a bit, and no need to select.

Range([B2], [B2].End(xlToRight).End(xlDown)).HorizontalAlignment = xlGeneral

Carpiem
07-07-2006, 03:49 PM
Hi Aussiebear,

I am somewhat confused... downloaded your file as well as the original uploaded to this thread. Compared the "Sales" & "Expected" sheets and can't see any differences in the cell formulas between the the 2 files.

Will keep playing the code until it works and then see if I get it.

Thank you for having a look at this. :thumb

Brian

Carpiem
07-07-2006, 04:10 PM
Hi Brian
My code puts the net sales in Column H with = B:G, Column I is not used
Regards
Malcolm

Good Evening Malcolm,

What am I doing wrong here? :banghead:

Using the original file, which I just downloaded to myself, deleted these lines:



'Total Sales by Group rows - starting Column "B"
Range("B2").Formula = "=SUMIF($B17:$B" & LastRow & ",""Drives"",C17:C" & LastRow & ")"
Range("B3").Formula = "=SUMIF($B17:$B" & LastRow & ",""GMS"",C17:C" & LastRow & ")"
Range("B4").Formula = "=SUMIF($B17:$B" & LastRow & ",""HMI"",C17:C" & LastRow & ")"
Range("B5").Formula = "=SUMIF($B17:$B" & LastRow & ",""IC"",C17:C" & LastRow & ")"
Range("B6").Formula = "=SUMIF($B17:$B" & LastRow & ",""MCC"",C17:C" & LastRow & ")"
Range("B7").Formula = "=SUMIF($B17:$B" & LastRow & ",""Motion"",C17:C" & LastRow & ")"
Range("B8").Formula = "=SUMIF($B17:$B" & LastRow & ",""Other"",C17:C" & LastRow & ")"
Range("B9").Formula = "=SUMIF($B17:$B" & LastRow & ",""PLC"",C17:C" & LastRow & ")"
Range("B10").Formula = "=SUMIF($B17:$B" & LastRow & ",""Safety"",C17:C" & LastRow & ")"
Range("B11").Formula = "=SUMIF($B17:$B" & LastRow & ",""Sensors"",C17:C" & LastRow & ")"
Range("B12").Formula = "=SUMIF($B17:$B" & LastRow & ",""Software"",C17:C" & LastRow & ")"
Range("B13").Formula = "=SUM(C17:C" & LastRow & ")"



Replaced them with your much improved:



'Total Sales by Group rows - starting Column "B" - MD
Range("B2:G12").FormulaR1C1 = "=SUMIF(R17C2:R31C2,RC1,R17C[1]:R31C[1])"
Range("H2:H12").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
Range("B13:H13").FormulaR1C1 = "=SUM(R[-11]C:R[-1]C)"



Cleared rows 1:13 on the "Sales" sheet and ran the FillTable macro. Honestly.... it filled Column "I" with the totals of B:G.and Column "H" is filled with zero's.

I am booking my "Padded Cell" ..... really! :)

My Regards,

Brian

mdmackillop
07-07-2006, 04:13 PM
For a dynamic lookup try
'Total Sales by Group rows - starting Column "B"
LRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2:G12").FormulaR1C1 = "=SUMIF(R17C2:R" & LRow & "C2,RC1,R17C[1]:R" & LRow & "C[1])"
Range("H2:H12").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
Range("B13:H13").FormulaR1C1 = "=SUM(R[-11]C:R[-1]C)"

Aussiebear
07-07-2006, 04:15 PM
I just edited the formula's that had been listed as I2:I13 on the "expected" sheet.

I have left the VBA stuff to the magicians, as it's all way over my head

mdmackillop
07-07-2006, 04:18 PM
Here's my file
Edit:
Revised to include some additional concepts. Something for the weekend!

Carpiem
07-08-2006, 03:54 AM
Hello Aussiebear,

You gave it a "Fair Go". As for the code... I hear you. You might want to take a gander a the latest file from Malcolm. It is a gem.

Brian

Carpiem
07-08-2006, 05:08 AM
Here's my file
Edit:
Revised to include some additional concepts. Something for the weekend!


Hello Malcolm,

Thank you very much. Some very snazzy "Concepts". :clap:

It will take a while for me to understand what you have done. Would you be alright with a few questions later on?

On the topic of working things out. If I add or delete names (columns) the "NetSales" numbers will, once again, end up one column to the right.

Initially thought the toughest part was the top table catering to adding and deleting names. I would never have thought this last column was going to be a problem.

Is this type of application always so tricky?

Have a great weekend Malcolm... again "Thank you" for pulling me out of the deep end.

My regards,

Brian

mdmackillop
07-08-2006, 09:10 AM
Hi Brian,
I was missing the bit that the number of columns might change, hence the extra column. :banghead:
I've reposted my workbook using your TCol variable to handle this event.

Carpiem
07-08-2006, 05:26 PM
Evening Malcolm,

You are a "Guru" of note!!! :bow:

Using the Dictionary to that index type thing was very clever.

Thank you.... :beerchug:

My regards,

Brian

Aussiebear
07-08-2006, 11:01 PM
Malcolm always scores highly in the talent department.

Carpiem
07-10-2006, 06:40 AM
Hi Brian,
I was missing the bit that the number of columns might change, hence the extra column. :banghead:
I've reposted my workbook using your TCol variable to handle this event.

Morning Malcolm,

I marked this solved.... it most certainly was. :thumb

Sent this off to my "Head Shed" and got some rude remark about there being a Security Risk associated with the Dictionary thing.

No bloody idea what they mean. :dunno

So I think I have to go back to hard coding the Group for the top table.

Am wondering if you can tell me what the issue might be?

My regards,

Brian

mdmackillop
07-10-2006, 07:54 AM
Hi Brian,
No idea what they mean. Maybe they could clarify?

Carpiem
07-10-2006, 09:16 AM
Hi Malcolm,

Too precious this is..... "It doesn't matter what our concerns are. Just don't use this in any shared workbooks!" :eek:

This prissy response may have to do with Italy beating France. Our "Head Shed" is in Montreal. :rotlaugh: :rotlaugh:

Have a great day,

Brian