neaten up code

[cheveley]

my code writing skills leave a lot to be desired but i have written some code that works fine but i would like to know if i am replicating anything, or if there is redundant commands in my code. thanks.

If Worksheets("Drop down & vlookup tables").Range("h2") = "Number" Then Sheets("Main Input sect").Select Range("f106:j106").Select
Selection.NumberFormat = "#,##0"
Range("e106").Select
If Worksheets("Drop down & vlookup tables").Range("h2") = "Percentage" Then Sheets("Main Input sect").Select Range("f106:j106").Select
Selection.NumberFormat = "0.0%"
Range("e106").Select
If Worksheets("Drop down & vlookup tables").Range("h2") = "2 Decimal" Then Sheets("Main Input sect").Select Range("f106:j106").Select
Selection.NumberFormat = "#,##0.0"
Range("e106").Select

 

[pritcham]

Hi

Only one suggestion from me - take a look at the "Select Case" statement in the VBA helpfile - should make things look a little cleaner/clearer (not sure what impact if any it may have on performance though I suspect it won't have any noticable downgrades).

Hope that helps
Martin

 

[cheveley]

thanks martin.

instead of Select Range("f106:j106") i would rather name that range "fone" and use the name in the code, thereby if the row numbers change my code wont need to be changed.

i cant seem the get the code working to select a defined name ("fone") rather than ("f106:j106").

any suggestions?

 

[pritcham]

Hi

I use the following in my code (bearing in mind the code is in Access and it's using automation to link to Excel - hence the objXL identifier bit) and it works fine:

tempname = "My Range"
objXL.worksheets("My Worksheet name").Range(tempname).Select

Hope that helps
Martin

 

[cheveley]

thanks, adapted your code.

 

[razzandy]

Hi

What I do is add the name to the workbook then in my code just refer to it like this:

Sheets(“Sheet1”).Range(“Your Named Range”).select

Hope this helps

Ryan A UK

 

[razzandy]

What about this?

If Worksheets("Drop down & vlookup tables").Range("h2") = "2 Decimal" Then Format(Sheets("Main Input sect").Range("Your Named Range") ,”#,##0.0”)
End if

 

[Norie]

Do you actually have the named range already?

Dim strFormat As String

Select Case Worksheets("Drop down & vlookup tables").Range("h2").Value
    Case "Number"
        strfomart = "#,##0"
    Case "Percentage"
        strFormat = "0.0%"
    Case "2 Decimal"
        strFormat = "#,##0.0"
End Select

Sheets("Main Input sect").Range("f106:j106").NumberFormat = strFormat

 

[cheveley]

that will do the trick.