neaten up code

cheveley

Active Member
Joined
Sep 24, 2002
Messages
273
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Do you actually have the named range already?
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top