# Rookie trying to fix formula

• Hi all,

I think I got myself in a bit over my head here. I have a very long formula and am struggling to break it out. I found a few helpful threads on here but I cannot mimic the solution accurately.

I am trying to split the lines but have been unsuccessful in having the macro work. Each number represents a date. The formulas help to ensure that the best date is always provided.

I have also added the formulas. Each of the formulas has been tested separately and as a whole. Here are the formulas separated below.

If Anybody cares to look at this, I would appreciate it very much. I have worked too long for this to fail but I am at a loss as to why it will not work.

Edited once, last by royUK: add code tags ().

• It seems to me like you'll need to

1)define some variables,

2) setup a loop

3) insert your if then criteria in this loop as it progresses from the top of the spreadsheet to the bottom within the above mentioned loop.

Does this make sense?

• Hi Ayjay,

It is 3:00am here so I am going to have to go to sleep! I will try to understand your response tomorrow.

Thank you very much!

• Thank you Roy!

Going forward, I will use better titles and paste code correctly.

Grenaldo

• Hi Ayjay,

This does make sense. I will try to make a loop in the next few days and will report if this was the solution.

Thank you!

Grenaldo

• alternatively , you could also try using nested if then formulas. Excel allows upto six if then criteria, if i remember correctly.

would look something like this.

=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F"))))

• Maybe a vlookup like the sheet attached will also yield the same result.

Alternatively you could use a combination of a vlookup and a match function

hope it works out

• Hi Ayjay,

I had originally made a nested formula but I wasn't able to get it to work successfully. I think I used more than six. I will try making a smaller formula in each line to see if this will work.

Yesterday, I worked to learn how to make a macro loop. I am trying to decide if I want to use the nested formula or a macro loop. I believe the macro loop is the best way to do this.

Thank you,

Grenaldo

• I revised my formula. It now works. The original lines were too long. I also needed to add commas to ensure that the formula continued with each line as well. In addition, the parentheses ending each formula had to be placed at the end of the sub.