Vba code

Status
Not open for further replies.

Andrews Felix

New Member
Joined
Sep 17, 2022
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
Hi,

If i have a data for (A1:100, C1:100) and here column C is empty and we have to calculate values based on other two columns. For example if A1 > 100 and B1 is "dog" i need C1 to be "good". Here column B will have three different types of animal and column A will have number upto 300. ">100" "100 to 200" and "200 to 300". Column C should get the values, if column A and B meets a criteria.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
but what values for each criteria? Basically you have 9 cases dog < 100, dog between 100 and 200, and dog 200-300, then cat <100, cat 100-200 and so on.

You may use a loop and for each row check these cases.
The code could look like

VBA Code:
dim i as long
for i = 1 to 100
  if cells(i,"B")= "dog" then
     if  cells(i,"A")<100 then
       cells(i,"C")= "good"
     elseif  cells(i,"A")<200 then ' note you don't have to test if it's not smaller than 100, because if it were, the previous statement would be used
       cells(i,"C")= "medium"
     elseif  cells(i,"A")<300 then 
       cells(i,"C")= "bad"
    end if
  elseif cells(i,"B")= "cat" then
     if  cells(i,"A")<100 then
       cells(i,"C")= "excellent"
     elseif  cells(i,"A")<200 then
'... and so on. observe closing each of if ... end if
next i

If you have more data rows, of if you have more than 9 possible combinations ,the approach would probably be ineffective and it would be wise to use for instance arrays of Acolumn and B column values on input, then 2D array with assignments of each of combinations and an array on an output written to the worksheet when all rows (stored in 2 input tables) were processed
 
Upvote 0
Solution
Hi sir,

Thank you for your reply. Please help me with this, if column A is yes, and column B is advance and column c is <0.5 then D= good. And if A is yes, B is advanced and c is >500 and less than or equal to 1 then it is great. And if A is yes and b is advanced and c is greater than 1 or less than or equal to 2 then it is best.
IMG_20220917_205649.jpg
 
Upvote 0
Also one more condition it shouldnt be just limited to 7 rows, it for whole column. Thanks in advance
 
Upvote 0
Duplicate to: Vba code

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top