CODEWITHSUNDEEP
vbaexcel
S.AY
S.AY

Reputation: 1

Apply vba to multiple cells

I have a code that can generate page number on cells. But I want it apply to mutiple cells in one time instead of single cells.

Sub pagenumber()

'updateby Extendoffice 20160506

    Dim xVPC As Integer
    Dim xHPC As Integer
    Dim xVPB As VPageBreak
    Dim xHPB As HPageBreak
    Dim xNumPage As Integer
    xHPC = 1
    xVPC = 1
    If ActiveSheet.PageSetup.Order = xlDownThenOver Then
        xHPC = ActiveSheet.HPageBreaks.Count + 1
    Else
        xVPC = ActiveSheet.VPageBreaks.Count + 1
    End If
    xNumPage = 1
    For Each xVPB In ActiveSheet.VPageBreaks
        If xVPB.Location.Column > ActiveCell.Column Then Exit For
        xNumPage = xNumPage + xHPC
    Next
    For Each xHPB In ActiveSheet.HPageBreaks
        If xHPB.Location.Row > ActiveCell.Row Then Exit For
        xNumPage = xNumPage + xVPC
    Next
    ActiveCell = "Page " & xNumPage & " of " & Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
End Sub

What can i do for this? Is it also possible for apply the code to highlighted cells?

Upvotes: 0

Views: 334

Answers (1)

Vityata
Vityata

Reputation: 43595

At the end write this:

Range("A1:B10")="Page "&xNumPage&" of "& Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")

Instead of: 

 ActiveCell = "Page "&xNumPage& " of " & Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")

Making sure that Range("A1:B10") is the range to which you want to apply the numbers.

How to avoid using Select in Excel VBA

Upvotes: 1

Related Questions