Novice looking to improve code

[JimmyCobblers]

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

    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

Sub ConvSel()

With Selection


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


End Sub

 

[s hal]

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:

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

 

[VoG]

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:

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

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

 

[s hal]

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]

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]

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

 

[VoG]

Try

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]

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.