VBA to insert IF statement

[pharris1]

Hi there,

I have a worksheet that contains data arranged in fields from columns A to J. The relevant columns for the purposes of the macro are columns B (customer codes) and G (sales values). Column B may contain a single instance of a customer code, or multiple (over 50), depending on how active a customer has been. The worksheet always contains many different customer codes with varying numbers of rows for each customer, sorted by customer. What I'm trying to do is write a macro that will loop through the worksheet and insert a blank line immediately after a change in value of column B (customer code), and in this blank line, insert the customer code in column B (which comes from the cell immediately above), a sum formula for all values within a range that relate to that particular customer in column G, and an IF statement in column J that relates to the SUM formula. The loop concludes when there are no longer values in column B.

I have code that allows me to insert blank lines on change in values in column B and the SUM formula, but the IF statement is a problem.

Please Login or Register  to view this content.

My IF statement that references the SUM formula in column G is place in the same row in column J. An example is:

Please Login or Register  to view this content.

Thanks very much for any help.
Peter

[pharris1]

Here's a copy of the spreadsheet I'm working with.

[Gregor y]

why not just add the following below the lines of code that start with: Cells(dRow, 7).FormulaR1C1

Please Login or Register  to view this content.

also, depending on how accurate your if statments need to be you may want to consider

Please Login or Register  to view this content.

[pharris1]

I've got a working version of the macro in a version of the attached spreadsheet which operates perfectly, using a formula very similar to the code you posted. However, when I copy the code and paste it into a module for a different spreadsheet (with exactly the same structure/arrangement of columns), it inserts the IF statement into every single line, not just the blank lines created by the macro. I've really no idea what the problem is.

It works fine with the sample worksheet, but in a different worksheet, with the same structure, it fills every row in column J with IF statements.

Any ideas would be greatly appreciated.
Peter

[pharris1]

I've tested the macro on data formatted as a table and it fails, while it works perfectly on plain data without any formatting. So, perhaps this is the problem. When I copy all the data into another worksheet in the same file but Paste Special > Values, it drops the formatting and consequently the macro works fine.
I've not encountered this problem before.

[Gregor y]

Sorry my clairvoyance can only go so far, and eventually a mechanic must see the car they’re trying to repair instead of just knowing it’s a Ford; however to just take a blind shot in the dark I’d guess that you probably need to add something similar to this at the beginning of your sub.

Please Login or Register  to view this content.

[pharris1]

You would make an expert clairvoyant mechanic. Works a treat.