Loop & Case Statement Help

**jo5h9o** · 12-07-2012, 10:25 AM

Hello,

I have a basic knowledge of VBA and I can read and write simple vba for excel 2003.

Below is the vba code I am struggling with.

Please Login or Register  to view this content.

Basically what I am wanting to do, but I am stuck on, is to repeat the formulas above but for the next cell so for example I want it to do this

Select Case Range("AN2").Value
Case Is = "AMOUNT"
Range("AG2").Value = "DECIMAL"
Range("AH2").Value = "18"
Range("AI2").Value = "3"

Then move on to this

Select Case Range("AN3").Value
Case Is = "AMOUNT"
Range("AG3").Value = "DECIMAL"
Range("AH3").Value = "18"
Range("AI3").Value = "3"

As each cell will have a value in from a determined list i have set out i want it to repeat this until it hits a blank cell.

Hope I made this understandable it's very hard for me to explain.

Any help is appreciated, I am sure there is a much simplier way to the way i have done, at the moment all the code is doing is moving on to the next blank cell and repeating the formula only on cell AG2 etc (which I am aware i am telling it to do) but I am unsure how to move this to the next cell and repeat the formula.

Thank you.

**Mike_e** · 12-07-2012, 10:50 AM

Here you go...try this...

Please Login or Register  to view this content.

**aelgadi** · 12-07-2012, 10:58 AM

First of all you should use Code Tags as per Forum Rule 3

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

Please Login or Register  to view this content.

**jo5h9o** · 12-07-2012, 11:00 AM

Hello Mike,

Thank you very much for your response.

However this vba still does the same as mine does.

Maybe i didn't explain this as well as i could have.

What i want it to do is check cell AN2 for the word Amount then if an2 is there populate decimal in ag2 then 18 in ah2 and 3 ai2 which it does already.

then once it has checked for amount, description, date etc in an2 i want it to then check an3 for the same and then if its description I want it to populate CHAR in ag3 50 in ah3 and nothing in ai3 then move on to an4 until it reaches a blank..

Hope that make a bit more sense, again thank you for trying to help!

**JOHN H. DAVIS** · 12-07-2012, 11:02 AM

Maybe:

Please Login or Register  to view this content.

**jo5h9o** · 12-07-2012, 11:06 AM

John, thank you so much. I new there would be a simplier way. This has helped me loads. Its a project i needed completed for work before 4pm uk time and this has finished it...thank you very very much !

**JOHN H. DAVIS** · 12-07-2012, 11:10 AM

You are welcome. Glad to help out, and thanks for the feedback. However, please acknowledge Forum Rule No. 9:

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.