Novice looking to improve code

JimmyCobblers

New Member
Joined
Apr 19, 2013
Messages
25
Hi all

I'm a novice in VBA and I was wondering if I could tidy up a part of my code?

Basically with the below I edit two columns on different sheets, using the same range criteria and same formatting (ConvSel).

Can this be 'condensed'/improved? Any particular programming concept that I could read up on? (Only just started reading J.Walkenbach's Power Programming)

Thanks


Code:
    Range("A2", ActiveSheet.Range("A2").End(xlDown)).Select    
    Call ConvSel


    Sheets("SUR").Select
    
    Range("A2", ActiveSheet.Range("A2").End(xlDown)).Select
     
    Call ConvSel

And ConvSel is as below

Code:
Sub ConvSel()

With Selection


    .NumberFormat = "General"
    .Value = .Value2
    
    End With


End Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
You don't have to select the cells to format them (you generally dont have to specifically select anything to d osomething with it), so your code could be the following:

Code:
With Sheets("SUR")
    With Range("A2", Range("A2").End(xlDown))
        .NumberFormat = "General"
        .Value = .Value2
    End With
End With
 
Upvote 0
You don't have to select the cells to format them (you generally dont have to specifically select anything to d osomething with it), so your code could be the following:

Rich (BB code):
With Sheets("SUR")
    With Range("A2", Range("A2").End(xlDown))
        .NumberFormat = "General"
        .Value = .Value2
    End With
End With

Correct but missing a couple of dots there

Rich (BB code):
        With Sheets("SUR")
            With .Range("A2", .Range("A2").End(xlDown))
                .NumberFormat = "General"
                .Value = .Value2
            End With
        End With
 
Upvote 0
Ah yes, pesky dots! :)

And Jimmy, I personally wouldn't split out what you were doing into two subs - it makes it hard to follow the code. If you wanted to do that same thing to all sheets, for instance, I would do a For Each... type thing or some other type of loop.
 
Upvote 0
Hi S hal/VoG

Thanks a lot for the code, and the tip about the not selecting to format. Typical newbie error I guess?

s hal How can I set it that the code is just for two specific sheets? Something wtih arrays? (I'm not sure ....:()
 
Upvote 0
Code:
With Sheets("SUR Query")

            With .Range("A2", .Range("A2").End(xlDown))
                .NumberFormat = "General"
                .Value = .Value2
            End With
        End With

     With Sheets("SLA")
            With .Range("A2", .Range("A2").End(xlDown))
                .NumberFormat = "General"
                .Value = .Value2
            End With
        End With

Hi Any idea how to get the above working so there's only one With-End structure in there? Can't seem to work it out....:confused:
 
Upvote 0
Try

Code:
Dim ws As Worksheet
For Each ws In Sheets(Array("SUR Query", "SLA"))
    With ws
        With .Range("A2", .Range("A2").End(xlDown))
            .NumberFormat = "General"
            .Value = .Value2
        End With
    End With
Next ws
 
Upvote 0
Thanks Peter, that works. I was trying to do something like ...With ("SUR Query, "SLA")
Ah well, sometime soon I'll get stuck in reading about Arrays. Always helps when I can use it in a 'real' example.
 
Upvote 0

Forum statistics

Threads
1,211,850
Messages
6,104,361
Members
447,902
Latest member
chriswebs23

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