Results 1 to 7 of 7

Button code to change offset cell in different sheet

Threaded View

  1. 05-17-2016, 12:05 AM #1
    23inzane10
    23inzane10 is offline
    Registered User
    Join Date
    05-16-2016
    Location
    Philippines
    MS-Off Ver
    excel
    Posts
    40

    Button code to change offset cell in different sheet

    Any can help me simplify this code thanks. sorry newbie

    I am trying to change the offset file in every cell using a button, but i have bunch off sheet to use to. And my code is so long and having hard time to scroll it down.

    Thank you.

    Private Sub CommandButton1_Click()
    Dim barangay As Integer
    
    barangay = Range("B7").Value
'POBLACION
If barangay = 1 Then Range("A4").Value = "=OFFSET(Poblacion!A1,$B$1,0)"
If barangay = 1 Then Range("B4").Value = "=OFFSET(Poblacion!B1,$B$1,0)"
If barangay = 1 Then Range("C4").Value = "=OFFSET(Poblacion!C1,$B$1,0)"
If barangay = 1 Then Range("D4").Value = "=OFFSET(Poblacion!D1,$B$1,0)"
If barangay = 1 Then Range("E4").Value = "=OFFSET(Poblacion!E1,$B$1,0)"
If barangay = 1 Then Range("F4").Value = "=OFFSET(Poblacion!F1,$B$1,0)"
If barangay = 1 Then Range("G4").Value = "=OFFSET(Poblacion!G1,$B$1,0)"
If barangay = 1 Then Range("H4").Value = "=OFFSET(Poblacion!H1,$B$1,0)"
If barangay = 1 Then Range("I4").Value = "=OFFSET(Poblacion!I1,$B$1,0)"
If barangay = 1 Then Range("J4").Value = "=OFFSET(Poblacion!J1,$B$1,0)"
If barangay = 1 Then Range("K4").Value = "=OFFSET(Poblacion!K1,$B$1,0)"
If barangay = 1 Then Range("L4").Value = "=OFFSET(Poblacion!L1,$B$1,0)"
If barangay = 1 Then Range("M4").Value = "=OFFSET(Poblacion!M1,$B$1,0)"
If barangay = 1 Then Range("N4").Value = "=OFFSET(Poblacion!N1,$B$1,0)"
If barangay = 1 Then Range("O4").Value = "=OFFSET(Poblacion!O1,$B$1,0)"
If barangay = 1 Then Range("P4").Value = "=OFFSET(Poblacion!P1,$B$1,0)"
If barangay = 1 Then Range("Q4").Value = "=OFFSET(Poblacion!Q1,$B$1,0)"
If barangay = 1 Then Range("R4").Value = "=OFFSET(Poblacion!R1,$B$1,0)"
If barangay = 1 Then Range("S4").Value = "=OFFSET(Poblacion!S1,$B$1,0)"
If barangay = 1 Then Range("T4").Value = "=OFFSET(Poblacion!T1,$B$1,0)"
If barangay = 1 Then Range("U4").Value = "=OFFSET(Poblacion!U1,$B$1,0)"
If barangay = 1 Then Range("V4").Value = "=OFFSET(Poblacion!V1,$B$1,0)"
If barangay = 1 Then Range("W4").Value = "=OFFSET(Poblacion!W1,$B$1,0)"
If barangay = 1 Then Range("X4").Value = "=OFFSET(Poblacion!X1,$B$1,0)"
If barangay = 1 Then Range("Y4").Value = "=OFFSET(Poblacion!Y1,$B$1,0)"
If barangay = 1 Then Range("Z4").Value = "=OFFSET(Poblacion!Z1,$B$1,0)"
If barangay = 1 Then Range("AA4").Value = "=OFFSET(Poblacion!AA1,$B$1,0)"
If barangay = 1 Then Range("AB4").Value = "=OFFSET(Poblacion!AB1,$B$1,0)"
If barangay = 1 Then Range("AC4").Value = "=OFFSET(Poblacion!AC1,$B$1,0)"
If barangay = 1 Then Range("AD4").Value = "=OFFSET(Poblacion!AD1,$B$1,0)"
If barangay = 1 Then Range("AE4").Value = "=OFFSET(Poblacion!AE1,$B$1,0)"
If barangay = 1 Then Range("AF4").Value = "=OFFSET(Poblacion!AF1,$B$1,0)"
If barangay = 1 Then Range("AG4").Value = "=OFFSET(Poblacion!AG1,$B$1,0)"
If barangay = 1 Then Range("AH4").Value = "=OFFSET(Poblacion!AH1,$B$1,0)"
If barangay = 1 Then Range("AI4").Value = "=OFFSET(Poblacion!AI1,$B$1,0)"
If barangay = 1 Then Range("AJ4").Value = "=OFFSET(Poblacion!AJ1,$B$1,0)"
If barangay = 1 Then Range("AK4").Value = "=OFFSET(Poblacion!AK1,$B$1,0)"
If barangay = 1 Then Range("AL4").Value = "=OFFSET(Poblacion!AL1,$B$1,0)"
If barangay = 1 Then Range("AM4").Value = "=OFFSET(Poblacion!AM1,$B$1,0)"
If barangay = 1 Then Range("AN4").Value = "=OFFSET(Poblacion!AN1,$B$1,0)"
If barangay = 1 Then Range("AO4").Value = "=OFFSET(Poblacion!AO1,$B$1,0)"
If barangay = 1 Then Range("AP4").Value = "=OFFSET(Poblacion!AP1,$B$1,0)"
If barangay = 1 Then Range("AQ4").Value = "=OFFSET(Poblacion!AQ1,$B$1,0)"
If barangay = 1 Then Range("AR4").Value = "=OFFSET(Poblacion!AR1,$B$1,0)"
If barangay = 1 Then Range("AS4").Value = "=OFFSET(Poblacion!AS1,$B$1,0)"

'Binarzang
If barangay = 2 Then Range("A4").Value = "=OFFSET(Binarzang!A1,$B$1,0)"
If barangay = 2 Then Range("B4").Value = "=OFFSET(Binarzang!B1,$B$1,0)"
If barangay = 2 Then Range("C4").Value = "=OFFSET(Binarzang!C1,$B$1,0)"
If barangay = 2 Then Range("D4").Value = "=OFFSET(Binarzang!D1,$B$1,0)"
If barangay = 2 Then Range("E4").Value = "=OFFSET(Binarzang!E1,$B$1,0)"
If barangay = 2 Then Range("F4").Value = "=OFFSET(Binarzang!F1,$B$1,0)"
If barangay = 2 Then Range("G4").Value = "=OFFSET(Binarzang!G1,$B$1,0)"
If barangay = 2 Then Range("H4").Value = "=OFFSET(Binarzang!H1,$B$1,0)"
If barangay = 2 Then Range("I4").Value = "=OFFSET(Binarzang!I1,$B$1,0)"
If barangay = 2 Then Range("J4").Value = "=OFFSET(Binarzang!J1,$B$1,0)"
If barangay = 2 Then Range("K4").Value = "=OFFSET(Binarzang!K1,$B$1,0)"
If barangay = 2 Then Range("L4").Value = "=OFFSET(Binarzang!L1,$B$1,0)"
If barangay = 2 Then Range("M4").Value = "=OFFSET(Binarzang!M1,$B$1,0)"
If barangay = 2 Then Range("N4").Value = "=OFFSET(Binarzang!N1,$B$1,0)"
If barangay = 2 Then Range("O4").Value = "=OFFSET(Binarzang!O1,$B$1,0)"
If barangay = 2 Then Range("P4").Value = "=OFFSET(Binarzang!P1,$B$1,0)"
If barangay = 2 Then Range("Q4").Value = "=OFFSET(Binarzang!Q1,$B$1,0)"
If barangay = 2 Then Range("R4").Value = "=OFFSET(Binarzang!R1,$B$1,0)"
If barangay = 2 Then Range("S4").Value = "=OFFSET(Binarzang!S1,$B$1,0)"
If barangay = 2 Then Range("T4").Value = "=OFFSET(Binarzang!T1,$B$1,0)"
If barangay = 2 Then Range("U4").Value = "=OFFSET(Binarzang!U1,$B$1,0)"
If barangay = 2 Then Range("V4").Value = "=OFFSET(Binarzang!V1,$B$1,0)"
If barangay = 2 Then Range("W4").Value = "=OFFSET(Binarzang!W1,$B$1,0)"
If barangay = 2 Then Range("X4").Value = "=OFFSET(Binarzang!X1,$B$1,0)"
If barangay = 2 Then Range("Y4").Value = "=OFFSET(Binarzang!Y1,$B$1,0)"
If barangay = 2 Then Range("Z4").Value = "=OFFSET(Binarzang!Z1,$B$1,0)"
If barangay = 2 Then Range("AA4").Value = "=OFFSET(Binarzang!AA1,$B$1,0)"
If barangay = 2 Then Range("AB4").Value = "=OFFSET(Binarzang!AB1,$B$1,0)"
If barangay = 2 Then Range("AC4").Value = "=OFFSET(Binarzang!AC1,$B$1,0)"
If barangay = 2 Then Range("AD4").Value = "=OFFSET(Binarzang!AD1,$B$1,0)"
If barangay = 2 Then Range("AE4").Value = "=OFFSET(Binarzang!AE1,$B$1,0)"
If barangay = 2 Then Range("AF4").Value = "=OFFSET(Binarzang!AF1,$B$1,0)"
If barangay = 2 Then Range("AG4").Value = "=OFFSET(Binarzang!AG1,$B$1,0)"
If barangay = 2 Then Range("AH4").Value = "=OFFSET(Binarzang!AH1,$B$1,0)"
If barangay = 2 Then Range("AI4").Value = "=OFFSET(Binarzang!AI1,$B$1,0)"
If barangay = 2 Then Range("AJ4").Value = "=OFFSET(Binarzang!AJ1,$B$1,0)"
If barangay = 2 Then Range("AK4").Value = "=OFFSET(Binarzang!AK1,$B$1,0)"
If barangay = 2 Then Range("AL4").Value = "=OFFSET(Binarzang!AL1,$B$1,0)"
If barangay = 2 Then Range("AM4").Value = "=OFFSET(Binarzang!AM1,$B$1,0)"
If barangay = 2 Then Range("AN4").Value = "=OFFSET(Binarzang!AN1,$B$1,0)"
If barangay = 2 Then Range("AO4").Value = "=OFFSET(Binarzang!AO1,$B$1,0)"
If barangay = 2 Then Range("AP4").Value = "=OFFSET(Binarzang!AP1,$B$1,0)"
If barangay = 2 Then Range("AQ4").Value = "=OFFSET(Binarzang!AQ1,$B$1,0)"
If barangay = 2 Then Range("AR4").Value = "=OFFSET(Binarzang!AR1,$B$1,0)"
If barangay = 2 Then Range("AS4").Value = "=OFFSET(Binarzang!AS1,$B$1,0)"

'Cabaruan
If barangay = 3 Then Range("A4").Value = "=OFFSET(Cabaruan!A1,$B$1,0)"
If barangay = 3 Then Range("B4").Value = "=OFFSET(Cabaruan!B1,$B$1,0)"
If barangay = 3 Then Range("C4").Value = "=OFFSET(Cabaruan!C1,$B$1,0)"
If barangay = 3 Then Range("D4").Value = "=OFFSET(Cabaruan!D1,$B$1,0)"
If barangay = 3 Then Range("E4").Value = "=OFFSET(Cabaruan!E1,$B$1,0)"
If barangay = 3 Then Range("F4").Value = "=OFFSET(Cabaruan!F1,$B$1,0)"
If barangay = 3 Then Range("G4").Value = "=OFFSET(Cabaruan!G1,$B$1,0)"
If barangay = 3 Then Range("H4").Value = "=OFFSET(Cabaruan!H1,$B$1,0)"
If barangay = 3 Then Range("I4").Value = "=OFFSET(Cabaruan!I1,$B$1,0)"
If barangay = 3 Then Range("J4").Value = "=OFFSET(Cabaruan!J1,$B$1,0)"
If barangay = 3 Then Range("K4").Value = "=OFFSET(Cabaruan!K1,$B$1,0)"
If barangay = 3 Then Range("L4").Value = "=OFFSET(Cabaruan!L1,$B$1,0)"
If barangay = 3 Then Range("M4").Value = "=OFFSET(Cabaruan!M1,$B$1,0)"
If barangay = 3 Then Range("N4").Value = "=OFFSET(Cabaruan!N1,$B$1,0)"
If barangay = 3 Then Range("O4").Value = "=OFFSET(Cabaruan!O1,$B$1,0)"
If barangay = 3 Then Range("P4").Value = "=OFFSET(Cabaruan!P1,$B$1,0)"
If barangay = 3 Then Range("Q4").Value = "=OFFSET(Cabaruan!Q1,$B$1,0)"
If barangay = 3 Then Range("R4").Value = "=OFFSET(Cabaruan!R1,$B$1,0)"
If barangay = 3 Then Range("S4").Value = "=OFFSET(Cabaruan!S1,$B$1,0)"
If barangay = 3 Then Range("T4").Value = "=OFFSET(Cabaruan!T1,$B$1,0)"
If barangay = 3 Then Range("U4").Value = "=OFFSET(Cabaruan!U1,$B$1,0)"
If barangay = 3 Then Range("V4").Value = "=OFFSET(Cabaruan!V1,$B$1,0)"
If barangay = 3 Then Range("W4").Value = "=OFFSET(Cabaruan!W1,$B$1,0)"
If barangay = 3 Then Range("X4").Value = "=OFFSET(Cabaruan!X1,$B$1,0)"
If barangay = 3 Then Range("Y4").Value = "=OFFSET(Cabaruan!Y1,$B$1,0)"
If barangay = 3 Then Range("Z4").Value = "=OFFSET(Cabaruan!Z1,$B$1,0)"
If barangay = 3 Then Range("AA4").Value = "=OFFSET(Cabaruan!AA1,$B$1,0)"
If barangay = 3 Then Range("AB4").Value = "=OFFSET(Cabaruan!AB1,$B$1,0)"
If barangay = 3 Then Range("AC4").Value = "=OFFSET(Cabaruan!AC1,$B$1,0)"
If barangay = 3 Then Range("AD4").Value = "=OFFSET(Cabaruan!AD1,$B$1,0)"
If barangay = 3 Then Range("AE4").Value = "=OFFSET(Cabaruan!AE1,$B$1,0)"
If barangay = 3 Then Range("AF4").Value = "=OFFSET(Cabaruan!AF1,$B$1,0)"
If barangay = 3 Then Range("AG4").Value = "=OFFSET(Cabaruan!AG1,$B$1,0)"
If barangay = 3 Then Range("AH4").Value = "=OFFSET(Cabaruan!AH1,$B$1,0)"
If barangay = 3 Then Range("AI4").Value = "=OFFSET(Cabaruan!AI1,$B$1,0)"
If barangay = 3 Then Range("AJ4").Value = "=OFFSET(Cabaruan!AJ1,$B$1,0)"
If barangay = 3 Then Range("AK4").Value = "=OFFSET(Cabaruan!AK1,$B$1,0)"
If barangay = 3 Then Range("AL4").Value = "=OFFSET(Cabaruan!AL1,$B$1,0)"
If barangay = 3 Then Range("AM4").Value = "=OFFSET(Cabaruan!AM1,$B$1,0)"
If barangay = 3 Then Range("AN4").Value = "=OFFSET(Cabaruan!AN1,$B$1,0)"
If barangay = 3 Then Range("AO4").Value = "=OFFSET(Cabaruan!AO1,$B$1,0)"
If barangay = 3 Then Range("AP4").Value = "=OFFSET(Cabaruan!AP1,$B$1,0)"
If barangay = 3 Then Range("AQ4").Value = "=OFFSET(Cabaruan!AQ1,$B$1,0)"
If barangay = 3 Then Range("AR4").Value = "=OFFSET(Cabaruan!AR1,$B$1,0)"
If barangay = 3 Then Range("AS4").Value = "=OFFSET(Cabaruan!AS1,$B$1,0)"


'Camaal
If barangay = 4 Then Range("A4").Value = "=OFFSET(Camaal!A1,$B$1,0)"
If barangay = 4 Then Range("B4").Value = "=OFFSET(Camaal!B1,$B$1,0)"
If barangay = 4 Then Range("C4").Value = "=OFFSET(Camaal!C1,$B$1,0)"
If barangay = 4 Then Range("D4").Value = "=OFFSET(Camaal!D1,$B$1,0)"
If barangay = 4 Then Range("E4").Value = "=OFFSET(Camaal!E1,$B$1,0)"
If barangay = 4 Then Range("F4").Value = "=OFFSET(Camaal!F1,$B$1,0)"
If barangay = 4 Then Range("G4").Value = "=OFFSET(Camaal!G1,$B$1,0)"
If barangay = 4 Then Range("H4").Value = "=OFFSET(Camaal!H1,$B$1,0)"
If barangay = 4 Then Range("I4").Value = "=OFFSET(Camaal!I1,$B$1,0)"
If barangay = 4 Then Range("J4").Value = "=OFFSET(Camaal!J1,$B$1,0)"
If barangay = 4 Then Range("K4").Value = "=OFFSET(Camaal!K1,$B$1,0)"
If barangay = 4 Then Range("L4").Value = "=OFFSET(Camaal!L1,$B$1,0)"
If barangay = 4 Then Range("M4").Value = "=OFFSET(Camaal!M1,$B$1,0)"
If barangay = 4 Then Range("N4").Value = "=OFFSET(Camaal!N1,$B$1,0)"
If barangay = 4 Then Range("O4").Value = "=OFFSET(Camaal!O1,$B$1,0)"
If barangay = 4 Then Range("P4").Value = "=OFFSET(Camaal!P1,$B$1,0)"
If barangay = 4 Then Range("Q4").Value = "=OFFSET(Camaal!Q1,$B$1,0)"
If barangay = 4 Then Range("R4").Value = "=OFFSET(Camaal!R1,$B$1,0)"
If barangay = 4 Then Range("S4").Value = "=OFFSET(Camaal!S1,$B$1,0)"
If barangay = 4 Then Range("T4").Value = "=OFFSET(Camaal!T1,$B$1,0)"
If barangay = 4 Then Range("U4").Value = "=OFFSET(Camaal!U1,$B$1,0)"
If barangay = 4 Then Range("V4").Value = "=OFFSET(Camaal!V1,$B$1,0)"
If barangay = 4 Then Range("W4").Value = "=OFFSET(Camaal!W1,$B$1,0)"
If barangay = 4 Then Range("X4").Value = "=OFFSET(Camaal!X1,$B$1,0)"
If barangay = 4 Then Range("Y4").Value = "=OFFSET(Camaal!Y1,$B$1,0)"
If barangay = 4 Then Range("Z4").Value = "=OFFSET(Camaal!Z1,$B$1,0)"
If barangay = 4 Then Range("AA4").Value = "=OFFSET(Camaal!AA1,$B$1,0)"
If barangay = 4 Then Range("AB4").Value = "=OFFSET(Camaal!AB1,$B$1,0)"
If barangay = 4 Then Range("AC4").Value = "=OFFSET(Camaal!AC1,$B$1,0)"
If barangay = 4 Then Range("AD4").Value = "=OFFSET(Camaal!AD1,$B$1,0)"
If barangay = 4 Then Range("AE4").Value = "=OFFSET(Camaal!AE1,$B$1,0)"
If barangay = 4 Then Range("AF4").Value = "=OFFSET(Camaal!AF1,$B$1,0)"
If barangay = 4 Then Range("AG4").Value = "=OFFSET(Camaal!AG1,$B$1,0)"
If barangay = 4 Then Range("AH4").Value = "=OFFSET(Camaal!AH1,$B$1,0)"
If barangay = 4 Then Range("AI4").Value = "=OFFSET(Camaal!AI1,$B$1,0)"
If barangay = 4 Then Range("AJ4").Value = "=OFFSET(Camaal!AJ1,$B$1,0)"
If barangay = 4 Then Range("AK4").Value = "=OFFSET(Camaal!AK1,$B$1,0)"
If barangay = 4 Then Range("AL4").Value = "=OFFSET(Camaal!AL1,$B$1,0)"
If barangay = 4 Then Range("AM4").Value = "=OFFSET(Camaal!AM1,$B$1,0)"
If barangay = 4 Then Range("AN4").Value = "=OFFSET(Camaal!AN1,$B$1,0)"
If barangay = 4 Then Range("AO4").Value = "=OFFSET(Camaal!AO1,$B$1,0)"
If barangay = 4 Then Range("AP4").Value = "=OFFSET(Camaal!AP1,$B$1,0)"
If barangay = 4 Then Range("AQ4").Value = "=OFFSET(Camaal!AQ1,$B$1,0)"
If barangay = 4 Then Range("AR4").Value = "=OFFSET(Camaal!AR1,$B$1,0)"
If barangay = 4 Then Range("AS4").Value = "=OFFSET(Camaal!AS1,$B$1,0)"



End Sub
    Last edited by 23inzane10; 05-17-2016 at 01:43 AM. Reason: admin ask because its against forum rules
    Reply With QuoteRegister To Reply
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Want to simplify my Macro
    By Hamjea1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-10-2016, 04:03 PM
  2. How to simplify this macro
    By aneshdas in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 01-14-2014, 01:11 AM
  3. I need macro/ vba help to simplify a report
    By sjoseph in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-02-2013, 03:30 PM
  4. Simplify macro
    By iamasimpleman in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-29-2011, 07:01 AM
  5. How do I simplify this macro?
    By snooze24 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-20-2009, 04:47 PM
  6. Simplify the Macro of Looping
    By ccl28 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-10-2006, 05:20 AM
  7. Simplify a macro
    By Einar in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-23-2005, 05:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1