Need help AutoFilling a formula in a column

[pawest]

Hello,
I'm pulling data from a CSV file. The CSV file has a "Quantity" column that contains the absolute value of the quantity. I want to add a new "Qty" column and an if statement to say if certain criteria is met, make the value negative.

Here's what I have so far from the recorder... weirdly, this works on one spreadsheet and not another when referencing the same data... how can I make this better and more dynamic?

        Range("W1").Select
        ActiveCell.FormulaR1C1 = "Qty"
        ActiveCell.Offset(1, 0).Range("A1").Select
        ActiveCell.FormulaR1C1 = "=IF(RC[-24]=""B"",RC[-23],RC[-23]*-1)"
        Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & finalRow)

 

[texasalynn]

can you tell us in words what the macro is suppose to do and perhaps a small sample of your data layout

 

[pawest]

Sure thing....
A piece of my code pulls many CSV files into one workbork for daily analysis. One of the CSV files has a "Quantity" column that contains absolute values of the quantity. There are some changes being made. Now, every time I pull a certain csv file, I want to add a column called "Qty" to mirror the "Quantity" but make cells negative, should be they be negative based on criteria. So, I'm trying to add a column after the last column of data from the CSV file (the column after last column is Column W), enter "Qty" in the first cell, and use .Formula to enter an "=IF" function that says, if another cell equals "x", make the "Qty" cell negative.

Hope you can understand what I'm trying to accomplish. Thanks!

 

[AlphaFrog]

You could replace your above code with this...

    Range("W1").Value = "Qty"
    Range("W2:W" & Finalrow).FormulaR1C1 = "=IF(RC[-24]=""B"",RC[-23],RC[-23]*-1)"

I don't know what you mean by it doesn't work on the other sheet. Does it error? Is Finalrow not calculated properly? What exactly happens or doesn't happen?

 

[texasalynn]

the problem is your formula is saying that if the 24 columns to the left of W (stop there) that would be column IU in Excel 2003 version

Also you can change the last part of your formula to just put the negative in front of it instead of *-1

ActiveCell.FormulaR1C1 = "=IF(RC[-24]=""B"",RC[-23],-RC[-23])"

 

[pawest]

I'll try to be more detailed and answer both of your replies in my response:

I combined both of your replies and I'm now using this code:

        Range("W1").Value = "Qty"
        Range("W2:W" & finalRow).FormulaR1C1 = "=IF(RC[-15]=""B"",RC[-14],-RC[-14])"

When I run this, it first places a "Qty" into W1, but then inputs the formulas in W1 and W2 only and obviously there's a #value error in W1 since the if statement is referenceing text titles. It doesn't doesnt seem to enter it in 2 and aufofill down. It could be entering it in W2 and autofilling up?

In terms of an error on another sheet, I don't really know why... it was a VBA macro error and I can't remember the code. My apologies.

 

[Norie]

How are you calculating finalrow?

 

[AlphaFrog]

I'll try to be more detailed and answer both of your replies in my response:

I combined both of your replies and I'm now using this code:

        Range("W1").Value = "Qty"
        Range("W2:W" & finalRow).FormulaR1C1 = "=IF(RC[-15]=""B"",RC[-14],-RC[-14])"

When I run this, it first places a "Qty" into W1, but then inputs the formulas in W1 and W2 only and obviously there's a #value error in W1 since the if statement is referenceing text titles. It doesn't doesnt seem to enter it in 2 and aufofill down. It could be entering it in W2 and autofilling up?

It means FinalRow = 1. So it's putting the formula in W2:W1

You don't show how you are calculating Finalrow. I think that's your problem.

 

[pawest]

Norie,

finalRow = Cells(Rows.count, 1).End(xlUp).Row

You were on to something. I was referencing W which doesn't contain the range of data, obviously just the value in W.

However, I switched it to: finalRow = Range("A1048576").End(xlUp).Row

...and it still caused the same error.

 

[Norie]

Try adding sheet references to the code.

For example, if you want to fill the formula down on Sheet1.

With Worksheets("Sheet1")
      finalRow = .Range("A" & Rows.Count).End(xlUp).Row
      .Range("W2:W" & finalRow).FormulaR1C1 = "=IF(RC[-15]=""B"",RC[-14],-RC[-14])"
End With