Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Loop in column and paste as per condition

0

 I am looking for vba code that will do the following as mentioned below

File Name- Percentage.xlsx(This file is not opened so open the same by vba)
Code will be placed in test.xlsb file
I have a data in column C
If column C is above 90 then put 1% in Column G
If column C is above 80 then put 7% in Column G
If column C is above 50 then put 50% in Column G
i will add more these type of additional conditions if its required
Loop in column C and if condition met then as per condition put the data in column G
save it and close the file

Answer
Discuss

Discussion

Why do you want a macro to do this? A funtion in col G sounds to be a better solution.
Also I guess you mean 50 to 80 whenyou say > 50 as bothe 80 and 90 are above 50.
Another guess would be less than 50 to  should show 100%
k1w1sm (rep: 197) Sep 4, '19 at 3:48 pm
First of all Thnx for asking this question
If column C is above 90 then put in column G 1%

If column C is above 80 and below 90 then 7% in column G like this way
kajaljha56788 (rep: 14) Sep 4, '19 at 10:16 pm
Add to Discussion

Answers

0
Selected Answer
=IF(c1>50,IF(c1>80,IF(c1>90,"1%","7%"),"50%"),"out of range")
Discuss

Discussion

The solution you have offered is a case for INDEX/MATCH. I believe the formula below is easier to read and manage than the nested IFs you suggested.
=INDEX({1,7,50,"out of range"},MATCH(C1,{1000,90,80,50},-1))

Note that the Index array could be defined as {"1%","7%","50%","out of range"}. If it is assigned numbers the "%" must be added by the cell format.
The MATCH array must be in descending order if the match type = -1, meaning that a number >50 is sought and 50 will fail to match. If >=50 is desired the sequence must be ascending and the match type = 1. The first number (1000 here) must be greater than any number that might ever occur, possibly like 10^6. The effect is that any number smaller than it will qualify for the next match, fail there, and therefore return 1 as the match location which translates into 1% in the Index array. In an ascending array the first position must be smaller than any number that might ever occur in C1, probably 0.
Variatus (rep: 4889) Sep 4, '19 at 10:43 pm
Thnx Both of u
kajaljha56788 (rep: 14) Sep 5, '19 at 12:16 pm
Yep index match is much simpler solution.
I have never used it but will try and remember it should I have a similar need.
Thanks
k1w1sm (rep: 197) Sep 5, '19 at 4:30 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login