1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

how to simplify this vba code

Discussion in 'Programming' started by cr3at1v3, Nov 27, 2018.

  1. #1
    hello all, maybe some of you can help simplify my code.
    i would like automate the header title to Field+ColumnNo in all worksheets of an excel.
    below is my vba code:

    Sub HeaderTitle()
      Dim WS As Worksheet
     
      For Each WS In Worksheets
      WS.Range("A1").Value = "Field1"
      WS.Range("B1").Value = "Field2"
      WS.Range("C1").Value = "Field3"
      WS.Range("D1").Value = "Field4"
      WS.Range("E1").Value = "Field5"
      WS.Range("F1").Value = "Field6"
      WS.Range("G1").Value = "Field7"
      WS.Range("H1").Value = "Field8"
      WS.Range("I1").Value = "Field9"
      WS.Range("J1").Value = "Field10"
      WS.Range("K1").Value = "Field11"
      WS.Range("L1").Value = "Field12"
      WS.Range("M1").Value = "Field13"
      WS.Range("N1").Value = "Field14"
      WS.Range("O1").Value = "Field15"
      WS.Range("P1").Value = "Field16"
      WS.Range("Q1").Value = "Field17"
      WS.Range("R1").Value = "Field18"
      WS.Range("S1").Value = "Field19"
      WS.Range("T1").Value = "Field20"
      WS.Range("U1").Value = "Field21"
      WS.Range("V1").Value = "Field22"
      WS.Range("W1").Value = "Field23"
      WS.Range("X1").Value = "Field24"
      WS.Range("Y1").Value = "Field25"
      WS.Range("Z1").Value = "Field26"
      WS.Range("AA1").Value = "Field27"
      WS.Range("AB1").Value = "Field28"
      WS.Range("AC1").Value = "Field29"
      WS.Range("AD1").Value = "Field30"
      WS.Range("AE1").Value = "Field31"
      WS.Range("AF1").Value = "Field32"
      WS.Range("AG1").Value = "Field33"
      WS.Range("AH1").Value = "Field34"
      WS.Range("AI1").Value = "Field35"
      WS.Range("AJ1").Value = "Field36"
      WS.Range("AK1").Value = "Field37"
      WS.Range("AL1").Value = "Field38"
      WS.Range("AM1").Value = "Field39"
      WS.Range("AN1").Value = "Field40"
      WS.Range("AO1").Value = "Field41"
      WS.Range("AP1").Value = "Field42"
      WS.Range("AQ1").Value = "Field43"
      WS.Range("AR1").Value = "Field44"
      WS.Range("AS1").Value = "Field45"
      WS.Range("AT1").Value = "Field46"
      WS.Range("AU1").Value = "Field47"
      WS.Range("AV1").Value = "Field48"
      WS.Range("AW1").Value = "Field49"
      WS.Range("AX1").Value = "Field50"
      WS.Range("AY1").Value = "Field51"
      WS.Range("AZ1").Value = "Field52"
      WS.Range("BA1").Value = "Field53"
      WS.Range("BB1").Value = "Field54"
      WS.Range("BC1").Value = "Field55"
      WS.Range("BD1").Value = "Field56"
      WS.Range("BE1").Value = "Field57"
      WS.Range("BF1").Value = "Field58"
      WS.Range("BG1").Value = "Field59"
      WS.Range("BH1").Value = "Field60"
      WS.Range("BI1").Value = "Field61"
      WS.Range("BJ1").Value = "Field62"
      WS.Range("BK1").Value = "Field63"
      WS.Range("BL1").Value = "Field64"
      WS.Range("BM1").Value = "Field65"
      WS.Range("BN1").Value = "Field66"
      WS.Range("BO1").Value = "Field67"
      WS.Range("BP1").Value = "Field68"
      WS.Range("BQ1").Value = "Field69"
      WS.Range("BR1").Value = "Field70"
      WS.Range("BS1").Value = "Field71"
      WS.Range("BT1").Value = "Field72"
    
    End Sub
    Code (markup):

    thanks in advance..
     
    cr3at1v3, Nov 27, 2018 IP
  2. cr3at1v3

    cr3at1v3 Greenhorn

    Messages:
    13
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #2
    hello guys, i have found something. but how can i put the string "Field" before the number?

    code is:
    Sub HeaderTitle()
        Dim WS As Worksheet
        Dim x As Long
    
        For Each WS In Worksheets  
            For x = 1 To 72
              WS.Cells(1, x).Value = x
            Next x
        Next
    
    End Sub
    Code (markup):
    thanks
     
    cr3at1v3, Nov 27, 2018 IP
  3. qwikad.com

    qwikad.com Illustrious Member Affiliate Manager

    Messages:
    7,151
    Likes Received:
    1,656
    Best Answers:
    29
    Trophy Points:
    475
    #3
    You should post on stackoverflow. It may take awhile before you hear from anyone on here.
     
    qwikad.com, Nov 27, 2018 IP
  4. cr3at1v3

    cr3at1v3 Greenhorn

    Messages:
    13
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #4
    thanks.

    already found the solution.

    code below:
    Sub HeaderTitle()
        Dim WS As Worksheet
        Dim x As Long
    
        For Each WS In Worksheets 
            For x = 1 To 72
              WS.Cells(1, x).Value = "Field" & x
            Next x
        Next
    
    End Sub
    Code (markup):
     
    cr3at1v3, Nov 27, 2018 IP