fizzlefry
asked on
Formula Insert using code in Excel
I am trying to clean up an excel file using several lines of code; everything from cleaning up the headers to adding / deleting columns. I am trying to insert a somewhat complex formula into a column, programatically. Please see attached source file. I inserted the code into a module in the background, but it keeps erroring out. It's like my formula formatting is incorrect. But if I manually insert the formula, it works fine. I simply want to insert the formula in the first column where ANY data appears in column B. I wasn't able to find the logic for the "where data appears in column B" part, but that's what I'm trying, however each time I try with the enclosed code, I get a compiler error. What I ultimately need to do is attach the corresponding location (store) number to the line items, as that detail is only shown in a total line. So if my formula needs to change, so be it. But I can't seem to get it in there automatically.
Export.xls
Export.xls
And in your second line, the fill range needs to include the first cell:
Range("A1").AutoFill Range("A1:A300"), Type:=xlFillDefault
Range("A1").AutoFill Range("A1:A300"), Type:=xlFillDefault
Didn't read your question properly so still something to be done by the looks of it ...
ASKER
Stephen,
First, THANK YOU!!! It seems to work, but will only fill the first cell. I actually need it to feel all cells going down the list (or if possible, just cells in which there is data found in column B.
Either way, when I do this, it does not fill all cells.
First, THANK YOU!!! It seems to work, but will only fill the first cell. I actually need it to feel all cells going down the list (or if possible, just cells in which there is data found in column B.
Either way, when I do this, it does not fill all cells.
OK, try this:
Range("B2", Range("B" & Rows.Count).End(xlUp)).Offset(, -1).Formula = _
"=IF(LEFT(B1,5)=""Total"","""",IF(LEFT(B2,5)=""Total"",VALUE(MID(B2,FIND(""Store"",B2,1)+6,LEN(B2))),A2))"ASKER
That worked, based on the B criteria, however, all my line items show "0" except the line with the "Totals". I need all lines before that "Totals" line to have that location number.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Stephen... I can't find enough ways to thank you and that big, beautiful brain of yours!!! But seriously.... Thank you...
ASKER
A superior solution from a superior poweruser!!!
Range("A1").Formula = "=IF(LEFT(B1,5)=""Total"",