For each if then issues

**chad portman** · 09-23-2012, 04:47 PM

I have attached a spreadsheet with a small set of sample data. I am wanting to write a macro that will plug in the commission rate in col C on sheet Data based on the person sales from col B using the table listed on sheet Rate table.
I was thinking the best answer for this would be a for each/if then style macro. I know i could use a vlookup to do this and it is what i am currently doing however the amount of data I am needing to do this for and the number of times i have to do this each day i think the macro answer is better so if you guys could help that would be great. I have no experience writing a for each macro as i have only just started my macro side of excel.

Thanks for all the help

**Special-K** · 09-23-2012, 04:52 PM

Nothing attached

**chad portman** · 09-23-2012, 06:33 PM

edited first post added attachment to the first post

**mike7952** · 09-23-2012, 07:08 PM

Give this a try. Add .99 to the values in Column C on the Rate Table.

Please Login or Register  to view this content.

**chad portman** · 09-23-2012, 07:13 PM

I added the .99 as you said sorry i forgot that part when making a mock sample.

However i added your code and told it to run and nothing happens at all.

**mike7952** · 09-23-2012, 07:19 PM

Sorry I put the exit for in wrong. This will work

Please Login or Register  to view this content.

**chad portman** · 09-23-2012, 07:25 PM

Yea that works and super fast as well. WOW!! Now I just need to see if i can figure out what this code actually means and adapt it to my file. Due to the sensitive nature of my data I had to make the sample slightly different but I should be able to backwards engineer from here will help me more in the long run that way aways.

**chad portman** · 09-23-2012, 09:25 PM

Ok so I understand most of the code and was trying to adjust it to my needs but my head is hurting so I thought I would try to ask it another way. I still need to do the same however there are two commission rate which are dependent on the state the sales rep is in. I have update the file with the new info and attached it here to see if it makes sense to you.

**mike7952** · 09-23-2012, 10:08 PM

It would of been Nice if On the Rate Table Sheet where you have the "-" in column B, could of been the state code NC or SC. But this should work.

Please Login or Register  to view this content.

**mike7952** · 09-23-2012, 10:20 PM

Also everywhere theres a ">" or "<" sign we need to add an "=" ">=" "<=" So that if Sales happens to be a EVEN Amount it will find the rate. I wasnt thinking before, cause Im been watching football all day.

**chad portman** · 09-23-2012, 10:53 PM

Please Login or Register  to view this content.

What is the purpose of this part of the code since those cells are already blank?

**mike7952** · 09-23-2012, 11:00 PM

Well lastrow I should of used in the For Next Loop but looks like I forgot to. The clearcontents just clears the range before running the macro again.

Please Login or Register  to view this content.

**chad portman** · 09-24-2012, 12:12 AM

This fixed the issues i was having thank you for your time hope your football games went as you hoped

For each if then issues

LinkBack

Thread Tools

Rate This Thread

Display

For each if then issues

Re: For each if then issues

Re: For each if then issues

Re: For each if then issues

Re: For each if then issues

Re: For each if then issues

Re: For each if then issues

Re: For each if then issues

Re: For each if then issues

Re: For each if then issues

Re: For each if then issues

Re: For each if then issues

Re: For each if then issues

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions