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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

s hal

Board Regular
Joined
Apr 10, 2013
Messages
198
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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

s hal

Board Regular
Joined
Apr 10, 2013
Messages
198
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.
 

JimmyCobblers

New Member
Joined
Apr 19, 2013
Messages
25

ADVERTISEMENT

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 ....:()
 

JimmyCobblers

New Member
Joined
Apr 19, 2013
Messages
25
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:
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

JimmyCobblers

New Member
Joined
Apr 19, 2013
Messages
25
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,865
Messages
5,598,539
Members
414,246
Latest member
allyciv

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
Top