Nested If Statement using VBA (practice)

[nycintern]

Hi.

I am trying to automate a process that can be laborous. So let's say on cell C2:C10, I used vlookup from a data file to pull out a % which ranges from (0%-5%). On the corresponding column D, I would like to have a rating, which is taken out from a rating scale. So my congested IF statement on Column D looks like this: =IF(C2<0.75%,"1",IF(C2<1.5%,"2",IF(C2>1.501%,"3","N/A")))

Which says the value of C2 is between 0 and .75% then we geit it a 1, if its between .75% and 1.5% its a 2, and so on. How can I put this in VBA, and if I could, should I? I am able to get what I need with the Ifstatement.

Please advise,
Intern

 

[Asala42]

Well I suppose you could just have excel enter the formula on your behalf.

Sub Test()

Sheets("Sheet1").Activate
Range("D2").Formula = "=IF(C2<0.75%,""1"",IF(C2<1.5%,""2"",IF(C2>1.501%,""3"",""N/A"")))"
Range("D2").Copy Range("D3:D10")

End Sub

That's not too different from saving the formula though and having it available for later to copy/paste when you pull new data. Are you just trying to make the formula more dynamic in some way? (variable number of if criteria or something?)

 

[nycintern]

Hi. Thank you. I'm trying to learn and understand VBA. Possibly by reading the codes (like the one you posted) and understanding how it all works. I honestly don't know what VBA can do for me and how it can make my life easier b/c i'm not familiar with it and it's beyond my imagination.

 

[Asala42]

It can do a lot - quite a bit more than I originally imagined in fact. I have quite a few reports where the work is literally reduced to:

-Open file
-Hit a button
-Skim results for anything interesting

You have the right idea though, start where the workload is most tedious or repetitive.