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.

Sub Macro1() 
Do 
Select Case Range("AN2").Value 
Case Is = "AMOUNT" 
Range("AG2").Value = "DECIMAL" 
Range("AH2").Value = "18" 
Range("AI2").Value = "3" 
Case Is = "IDENTIFIER" 
Range("AG2").Value = "BIGINT" 
Range("AH2", ["AI2"]).Value = " " 
Case Is = "CODE" 
Range("AG2").Value = "CHAR" 
Range("AH2").Value = "6" 
Range("AI2").Value = " " 
Case Is = "COUNT" 
Range("AG2").Value = "SMALLINT" 
Range("AH2", ["AI2"]).Value = " " 
Case Is = "DATE" 
Range("AG2").Value = "DATE" 
Range("AH2", ["AI2"]).Value = " " 
Case Is = "DATE" 
Range("AG2").Value = "DATE" 
Range("AH2", ["AI2"]).Value = " " 
Case Is = "DESCRIPTION" 
Range("AG2").Value = "CHAR" 
Range("AH2").Value = "50" 
Range("AI2").Value = " " 
Case Is = "INDICATOR" 
Range("AG2").Value = "CHAR" 
Range("AH2").Value = "1" 
Range("AI2").Value = " " 
Case Is = "PERCENT" 
Range("AG2").Value = "DECIMAL" 
Range("AH2").Value = "9" 
Range("AI2").Value = "5" 
Case Is = "RATE" 
Range("AG2").Value = "DECIMAL" 
Range("AH2").Value = "7" 
Range("AI2").Value = "5" 
Case Is = "SCORE" 
Range("AG2").Value = "SMALLINT" 
Range("AH2", ["AI2"]).Value = " " 
Case Is = "TIME" 
Range("AG2").Value = "TIME" 
Range("AH2").Value = "6" 
Range("AI2").Value = " " 
Case Is = "TIMESTAMP" 
Range("AG2").Value = "TIMESTAMP" 
Range("AH2").Value = "26" 
Range("AI2").Value = " " 
Case Is = "STRING" 
Range("AG2").Value = "CHAR" 
Range("AH2").Value = " " 
Range("AI2").Value = " " 
End Select 

ActiveCell.Offset(1, 0).Select 
Loop Until IsEmpty(ActiveCell.Value) 

End Sub

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...

Sub Macro1() 

Sheets("MySheet").Range("AN2").Select

Do 
Select Case Range("AN2").Value 
Case Is = "AMOUNT" 
Range("AG2").Value = "DECIMAL" 
Range("AH2").Value = "18" 
Range("AI2").Value = "3" 
Case Is = "IDENTIFIER" 
Range("AG2").Value = "BIGINT" 
Range("AH2", ["AI2"]).Value = " " 
Case Is = "CODE" 
Range("AG2").Value = "CHAR" 
Range("AH2").Value = "6" 
Range("AI2").Value = " " 
Case Is = "COUNT" 
Range("AG2").Value = "SMALLINT" 
Range("AH2", ["AI2"]).Value = " " 
Case Is = "DATE" 
Range("AG2").Value = "DATE" 
Range("AH2", ["AI2"]).Value = " " 
Case Is = "DATE" 
Range("AG2").Value = "DATE" 
Range("AH2", ["AI2"]).Value = " " 
Case Is = "DESCRIPTION" 
Range("AG2").Value = "CHAR" 
Range("AH2").Value = "50" 
Range("AI2").Value = " " 
Case Is = "INDICATOR" 
Range("AG2").Value = "CHAR" 
Range("AH2").Value = "1" 
Range("AI2").Value = " " 
Case Is = "PERCENT" 
Range("AG2").Value = "DECIMAL" 
Range("AH2").Value = "9" 
Range("AI2").Value = "5" 
Case Is = "RATE" 
Range("AG2").Value = "DECIMAL" 
Range("AH2").Value = "7" 
Range("AI2").Value = "5" 
Case Is = "SCORE" 
Range("AG2").Value = "SMALLINT" 
Range("AH2", ["AI2"]).Value = " " 
Case Is = "TIME" 
Range("AG2").Value = "TIME" 
Range("AH2").Value = "6" 
Range("AI2").Value = " " 
Case Is = "TIMESTAMP" 
Range("AG2").Value = "TIMESTAMP" 
Range("AH2").Value = "26" 
Range("AI2").Value = " " 
Case Is = "STRING" 
Range("AG2").Value = "CHAR" 
Range("AH2").Value = " " 
Range("AI2").Value = " " 
End Select 

ActiveCell.Offset(1, 0).Select 
Loop Until IsEmpty(ActiveCell.Offset(0, 0))
End Sub

**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:

your code here ...
and here ...
and here

**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:

Sub jo5h9o()

Dim i As Long
Dim lr As Long

Application.ScreenUpdating = False

lr = Cells(Rows.Count, 40).End(xlUp).Row

For i = lr To 2 Step -1

Select Case Range("AN" & i).Value

    Case Is = "AMOUNT"

        Range("AG" & i).Value = "DECIMAL"
        Range("AH" & i).Value = "18"
        Range("AI" & i).Value = "3"

    Case Is = "IDENTIFIER"

        Range("AG" & i).Value = "BIGINT"
        Range("AH" & i).Value = " "
        Range("AI" & i).Value = " "

    Case Is = "CODE"

        Range("AG" & i).Value = "CHAR"
        Range("AH" & i).Value = "6"
        Range("AI" & i).Value = " "

    Case Is = "COUNT"

        Range("AG" & i).Value = "SMALLINT"
        Range("AH" & i).Value = " "
        Range("AI" & i).Value = " "

    Case Is = "DATE"

        Range("AG" & i).Value = "DATE"
        Range("AH" & i).Value = " "
        Range("AI" & i).Value = " "

    Case Is = "DATE"

        Range("AG" & i).Value = "DATE"
        Range("AH" & i).Value = " "
        Range("AI" & i).Value = " "

    Case Is = "DESCRIPTION"

        Range("AG" & i).Value = "CHAR"
        Range("AH" & i).Value = "50"
        Range("AI" & i).Value = " "

    Case Is = "INDICATOR"

        Range("AG" & i).Value = "CHAR"
        Range("AH" & i).Value = "1"
        Range("AI" & i).Value = " "

    Case Is = "PERCENT"

        Range("AG" & i).Value = "DECIMAL"
        Range("AH" & i).Value = "9"
        Range("AI" & i).Value = "5"

    Case Is = "RATE"

        Range("AG" & i).Value = "DECIMAL"
        Range("AH" & i).Value = "7"
        Range("AI" & i).Value = "5"

    Case Is = "SCORE"

        Range("AG" & i).Value = "SMALLINT"
        Range("AH" & i).Value = " "
        Range("AI" & i).Value = " "

    Case Is = "TIME"

        Range("AG" & i).Value = "TIME"
        Range("AH" & i).Value = "6"
        Range("AI" & i).Value = " "

    Case Is = "TIMESTAMP"

        Range("AG" & i).Value = "TIMESTAMP"
        Range("AH" & i).Value = "26"
        Range("AI" & i).Value = " "

    Case Is = "STRING"

        Range("AG" & i).Value = "CHAR"
        Range("AH" & i).Value = " "
        Range("AI" & i).Value = " "

End Select

Next i

Application.ScreenUpdating = True

End Sub

**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.