How to loop a calculation & If statement

[sam71]

Hi all,

I have been struggling to find an answer for the code on a simple calulation.

I recorded a macro on one cell and got this code but need it to relate to all rows until the data stops!
Basically column H is muliplied by 1.15 to get an answer in column I

Range("I2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*1.15"

I also need to run an if statement against a column so I can get an idea if a product is in stock or out of stock!

I manually use =IF(AB2>0, "0", "1")

 

[VoG]

Try

LR = Range("H" & Rows.Count).End(xlUp).Row
Range("I2:I" & LR).FormulaR1C1 = "=RC[-1]*1.15"

 

[sam71]

Thanks so much....

That seemed to work!

Any ideas on the IF coding?

 

[VoG]

Try like this: change AC to the correct column

Range("AC2:AC" & LR).Formula = "=IF(AB2>0,0,1)"

 

[sam71]

I had the 0 & 1 around the wrong way but it also works a treat!

thank you

 

[sam71]

How would I go about having Column N = somthing if Column D was a certain thing

The reason I need this is that I will be importing product data from multiple supplers however I want to keep set Category structure!

For example column D (Category Code) is "16A" so I want column N (Category) to be "Network|Cables|"

Is there a way to have this in one pice of code or would I need a new code for every different entry in column D? Column D has upto 50 different codes!

 

[VoG]

The easiest way would be to create a table of codes and descriptions (maybe on another sheet) then use VLOOKUP http://www.contextures.com/xlFunctions02.html

 

[sam71]

Thanks for that! I actually thought of something similar might need to be done! Had a look at the link and most of it went straight over my head (just woke up though)