Loop Question

B

Beachcomber1224

New Member
Joined
May 12, 2011
Messages
18
Hi Everyone,

I am trying to loop the following script that is in column B throughout all columns. The way I will enter this is for each column, it is a specific person's information.

I was thinking/hoping to have something like this in it "Loop Until IsEmpty(ActiveCell.Offset(-135, 1))"

Thanks for any help!

Code: 
Public Sub T_Score_Total_Male()
Range("B139").Select
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "0" Then ActiveCell.FormulaR1C1 = "34"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "1" Then ActiveCell.FormulaR1C1 = "34"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "2" Then ActiveCell.FormulaR1C1 = "35"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "3" Then ActiveCell.FormulaR1C1 = "35"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "4" Then ActiveCell.FormulaR1C1 = "36"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "5" Then ActiveCell.FormulaR1C1 = "36"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "6" Then ActiveCell.FormulaR1C1 = "37"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "7" Then ActiveCell.FormulaR1C1 = "37"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "8" Then ActiveCell.FormulaR1C1 = "38"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "9" Then ActiveCell.FormulaR1C1 = "38"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "10" Then ActiveCell.FormulaR1C1 = "39"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "11" Then ActiveCell.FormulaR1C1 = "39"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "12" Then ActiveCell.FormulaR1C1 = "40"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "13" Then ActiveCell.FormulaR1C1 = "40"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "14" Then ActiveCell.FormulaR1C1 = "41"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "15" Then ActiveCell.FormulaR1C1 = "41"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "16" Then ActiveCell.FormulaR1C1 = "42"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "17" Then ActiveCell.FormulaR1C1 = "42"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "18" Then ActiveCell.FormulaR1C1 = "42"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "19" Then ActiveCell.FormulaR1C1 = "43"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "20" Then ActiveCell.FormulaR1C1 = "43"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "21" Then ActiveCell.FormulaR1C1 = "44"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "22" Then ActiveCell.FormulaR1C1 = "44"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "23" Then ActiveCell.FormulaR1C1 = "45"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "24" Then ActiveCell.FormulaR1C1 = "45"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "25" Then ActiveCell.FormulaR1C1 = "46"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "26" Then ActiveCell.FormulaR1C1 = "46"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "27" Then ActiveCell.FormulaR1C1 = "47"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "28" Then ActiveCell.FormulaR1C1 = "47"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "29" Then ActiveCell.FormulaR1C1 = "48"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "30" Then ActiveCell.FormulaR1C1 = "48"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "31" Then ActiveCell.FormulaR1C1 = "49"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "32" Then ActiveCell.FormulaR1C1 = "49"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "33" Then ActiveCell.FormulaR1C1 = "50"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "34" Then ActiveCell.FormulaR1C1 = "50"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "35" Then ActiveCell.FormulaR1C1 = "51"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "36" Then ActiveCell.FormulaR1C1 = "51"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "37" Then ActiveCell.FormulaR1C1 = "52"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "38" Then ActiveCell.FormulaR1C1 = "52"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "39" Then ActiveCell.FormulaR1C1 = "53"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "40" Then ActiveCell.FormulaR1C1 = "53"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "41" Then ActiveCell.FormulaR1C1 = "53"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "42" Then ActiveCell.FormulaR1C1 = "54"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "43" Then ActiveCell.FormulaR1C1 = "54"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "44" Then ActiveCell.FormulaR1C1 = "55"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "45" Then ActiveCell.FormulaR1C1 = "55"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "46" Then ActiveCell.FormulaR1C1 = "56"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "47" Then ActiveCell.FormulaR1C1 = "56"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "48" Then ActiveCell.FormulaR1C1 = "57"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "49" Then ActiveCell.FormulaR1C1 = "57"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "50" Then ActiveCell.FormulaR1C1 = "58"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "51" Then ActiveCell.FormulaR1C1 = "58"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "52" Then ActiveCell.FormulaR1C1 = "59"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "53" Then ActiveCell.FormulaR1C1 = "59"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "54" Then ActiveCell.FormulaR1C1 = "60"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "55" Then ActiveCell.FormulaR1C1 = "60"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "56" Then ActiveCell.FormulaR1C1 = "61"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "57" Then ActiveCell.FormulaR1C1 = "61"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "58" Then ActiveCell.FormulaR1C1 = "62"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "59" Then ActiveCell.FormulaR1C1 = "62"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "60" Then ActiveCell.FormulaR1C1 = "63"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "61" Then ActiveCell.FormulaR1C1 = "63"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "62" Then ActiveCell.FormulaR1C1 = "64"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "63" Then ActiveCell.FormulaR1C1 = "64"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "64" Then ActiveCell.FormulaR1C1 = "64"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "65" Then ActiveCell.FormulaR1C1 = "65"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "66" Then ActiveCell.FormulaR1C1 = "65"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "67" Then ActiveCell.FormulaR1C1 = "66"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "68" Then ActiveCell.FormulaR1C1 = "66"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "69" Then ActiveCell.FormulaR1C1 = "67"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "70" Then ActiveCell.FormulaR1C1 = "67"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "71" Then ActiveCell.FormulaR1C1 = "68"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "72" Then ActiveCell.FormulaR1C1 = "68"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "73" Then ActiveCell.FormulaR1C1 = "69"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "74" Then ActiveCell.FormulaR1C1 = "69"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "75" Then ActiveCell.FormulaR1C1 = "70"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "76" Then ActiveCell.FormulaR1C1 = "70"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "77" Then ActiveCell.FormulaR1C1 = "71"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "78" Then ActiveCell.FormulaR1C1 = "71"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "79" Then ActiveCell.FormulaR1C1 = "72"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "80" Then ActiveCell.FormulaR1C1 = "72"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "81" Then ActiveCell.FormulaR1C1 = "73"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "82" Then ActiveCell.FormulaR1C1 = "73"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "83" Then ActiveCell.FormulaR1C1 = "74"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "84" Then ActiveCell.FormulaR1C1 = "74"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "85" Then ActiveCell.FormulaR1C1 = "75"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "86" Then ActiveCell.FormulaR1C1 = "75"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "87" Then ActiveCell.FormulaR1C1 = "76"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "88" Then ActiveCell.FormulaR1C1 = "76"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "89" Then ActiveCell.FormulaR1C1 = "76"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "90" Then ActiveCell.FormulaR1C1 = "77"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "91" Then ActiveCell.FormulaR1C1 = "77"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "92" Then ActiveCell.FormulaR1C1 = "78"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "93" Then ActiveCell.FormulaR1C1 = "78"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "94" Then ActiveCell.FormulaR1C1 = "79"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "95" Then ActiveCell.FormulaR1C1 = "79"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "96" Then ActiveCell.FormulaR1C1 = "80"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "97" Then ActiveCell.FormulaR1C1 = "80"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "98" Then ActiveCell.FormulaR1C1 = "81"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "99" Then ActiveCell.FormulaR1C1 = "81"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "100" Then ActiveCell.FormulaR1C1 = "82"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "101" Then ActiveCell.FormulaR1C1 = "82"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "102" Then ActiveCell.FormulaR1C1 = "83"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "103" Then ActiveCell.FormulaR1C1 = "83"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "104" Then ActiveCell.FormulaR1C1 = "84"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "105" Then ActiveCell.FormulaR1C1 = "84"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "106" Then ActiveCell.FormulaR1C1 = "85"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "107" Then ActiveCell.FormulaR1C1 = "85"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "108" Then ActiveCell.FormulaR1C1 = "86"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "109" Then ActiveCell.FormulaR1C1 = "86"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "110" Then ActiveCell.FormulaR1C1 = "87"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "111" Then ActiveCell.FormulaR1C1 = "87"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "112" Then ActiveCell.FormulaR1C1 = "87"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "113" Then ActiveCell.FormulaR1C1 = "88"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "114" Then ActiveCell.FormulaR1C1 = "88"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "115" Then ActiveCell.FormulaR1C1 = "89"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "116" Then ActiveCell.FormulaR1C1 = "89"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "117" Then ActiveCell.FormulaR1C1 = "90"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "118" Then ActiveCell.FormulaR1C1 = "90"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "119" Then ActiveCell.FormulaR1C1 = "91"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "120" Then ActiveCell.FormulaR1C1 = "91"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "121" Then ActiveCell.FormulaR1C1 = "92"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "122" Then ActiveCell.FormulaR1C1 = "92"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "123" Then ActiveCell.FormulaR1C1 = "93"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "124" Then ActiveCell.FormulaR1C1 = "93"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "125" Then ActiveCell.FormulaR1C1 = "94"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "126" Then ActiveCell.FormulaR1C1 = "94"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "127" Then ActiveCell.FormulaR1C1 = "95"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "128" Then ActiveCell.FormulaR1C1 = "95"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "129" Then ActiveCell.FormulaR1C1 = "96"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "130" Then ActiveCell.FormulaR1C1 = "96"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "131" Then ActiveCell.FormulaR1C1 = "97"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "132" Then ActiveCell.FormulaR1C1 = "97"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "133" Then ActiveCell.FormulaR1C1 = "98"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "134" Then ActiveCell.FormulaR1C1 = "98"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "135" Then ActiveCell.FormulaR1C1 = "98"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "136" Then ActiveCell.FormulaR1C1 = "99"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "137" Then ActiveCell.FormulaR1C1 = "99"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "138" Then ActiveCell.FormulaR1C1 = "100"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "139" Then ActiveCell.FormulaR1C1 = "100"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "140" Then ActiveCell.FormulaR1C1 = "101"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "141" Then ActiveCell.FormulaR1C1 = "101"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "142" Then ActiveCell.FormulaR1C1 = "102"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "143" Then ActiveCell.FormulaR1C1 = "102"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "144" Then ActiveCell.FormulaR1C1 = "103"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "145" Then ActiveCell.FormulaR1C1 = "103"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "146" Then ActiveCell.FormulaR1C1 = "104"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "147" Then ActiveCell.FormulaR1C1 = "104"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "148" Then ActiveCell.FormulaR1C1 = "105"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "149" Then ActiveCell.FormulaR1C1 = "105"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "150" Then ActiveCell.FormulaR1C1 = "106"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "151" Then ActiveCell.FormulaR1C1 = "106"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "152" Then ActiveCell.FormulaR1C1 = "107"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "153" Then ActiveCell.FormulaR1C1 = "107"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "154" Then ActiveCell.FormulaR1C1 = "108"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "155" Then ActiveCell.FormulaR1C1 = "108"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "156" Then ActiveCell.FormulaR1C1 = "109"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "157" Then ActiveCell.FormulaR1C1 = "109"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "158" Then ActiveCell.FormulaR1C1 = "109"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "159" Then ActiveCell.FormulaR1C1 = "110"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "160" Then ActiveCell.FormulaR1C1 = "110"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "161" Then ActiveCell.FormulaR1C1 = "111"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "162" Then ActiveCell.FormulaR1C1 = "111"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "163" Then ActiveCell.FormulaR1C1 = "112"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "164" Then ActiveCell.FormulaR1C1 = "112"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "165" Then ActiveCell.FormulaR1C1 = "113"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "166" Then ActiveCell.FormulaR1C1 = "113"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "167" Then ActiveCell.FormulaR1C1 = "114"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "168" Then ActiveCell.FormulaR1C1 = "114"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "169" Then ActiveCell.FormulaR1C1 = "115"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "170" Then ActiveCell.FormulaR1C1 = "115"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "171" Then ActiveCell.FormulaR1C1 = "116"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "172" Then ActiveCell.FormulaR1C1 = "116"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "173" Then ActiveCell.FormulaR1C1 = "117"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "174" Then ActiveCell.FormulaR1C1 = "117"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "175" Then ActiveCell.FormulaR1C1 = "118"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "176" Then ActiveCell.FormulaR1C1 = "118"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "177" Then ActiveCell.FormulaR1C1 = "119"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "178" Then ActiveCell.FormulaR1C1 = "119"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "179" Then ActiveCell.FormulaR1C1 = "120"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "180" Then ActiveCell.FormulaR1C1 = "120"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "181" Then ActiveCell.FormulaR1C1 = "120"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "182" Then ActiveCell.FormulaR1C1 = "121"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "183" Then ActiveCell.FormulaR1C1 = "121"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "184" Then ActiveCell.FormulaR1C1 = "122"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "185" Then ActiveCell.FormulaR1C1 = "122"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "186" Then ActiveCell.FormulaR1C1 = "123"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "187" Then ActiveCell.FormulaR1C1 = "123"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "188" Then ActiveCell.FormulaR1C1 = "124"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "189" Then ActiveCell.FormulaR1C1 = "124"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "190" Then ActiveCell.FormulaR1C1 = "125"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "191" Then ActiveCell.FormulaR1C1 = "125"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "192" Then ActiveCell.FormulaR1C1 = "126"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "193" Then ActiveCell.FormulaR1C1 = "126"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "194" Then ActiveCell.FormulaR1C1 = "127"
If ActiveCell.Offset(-135, 0) = "1" And ActiveCell.Offset(-1, 0) = "195" Then ActiveCell.FormulaR1C1 = "127"

End Sub
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Provided that is your actual code, not a simplified example, then That can all be reduced to

Code: 
With Range("B139")
    If .Offset(-135, 0).Value = "1" Then .Value = 34 + Int(.Offset(-1,0).Value / 2)
End With

Then to make it loop through all columns, up to say Z
Code: 
Dim C As Range
For Each c In Range("B139:Z139")
    If c.Offset(-135, 0).Value = "1" Then c.Value = 34 + Int(c.Offset(-1,0).Value / 2)
Next c

Hope that helps.
 
Upvote 0
Thanks for your help jonmo1.

When I entered the script, the values didn't translate exactly. For example when I entered:
30 the output was 49 (should be 48)
80 the output was 74 (should be 72)
194 the output was 131 (should be 127)

Apologies for not including this other piece but the reason I have this code
Code: 
If ActiveCell.Offset(-135, 0) = "1"
is because 1 stands for male, which have different scores. I also have a
Code: 
If ActiveCell.Offset(-135, 0) = "2"
where 2 stands for female. The reason I did not include it in the original post was that I figured if I could figure out a way to loop the males, I could apply it to the female script. Sorry if this confused things.

Any thoughts? Thanks again for your help!
 
Upvote 0
Ahh, thought I saw a pattern there...
The number at the end was incrimenting 1 for every 2
34
34
35
35
36
36

But I see now that doesn't remain constant in the whole code.
You have 42 3 times, and a few others.
So my suggestion to simplify won't work..

But the method for looping is still valid...

Rich (BB code): 
Dim C As Range
For Each c In Range("B139:Z139")
   If c.Offset(-135, 0) = "1" And c.Offset(-1, 0) = "0" Then c.FormulaR1C1 = "34"
   'Repeat that line for every case.
Next c
 
Upvote 0
I am having some trouble with the script, it is likely that I am not coding it correctly. I would greatly appreciate soemone correcting my (hopefully) small syntax mistakes.

I tried the following script
Code: 
Public Sub T_Score_Total_Male()
Dim C As Range
For Each C In Range("B139:IV139")
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "0" Then C.FormulaR1C1 = "34"
Dim C As Range
For Each C In Range("B139:IV139")
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "1" Then C.FormulaR1C1 = "34"
Dim C As Range
For Each C In Range("B139:IV139")
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "2" Then C.FormulaR1C1 = "35"
Dim C As Range
For Each C In Range("B139:IV139")
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "3" Then C.FormulaR1C1 = "35"
Dim C As Range
For Each C In Range("B139:IV139")
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "4" Then C.FormulaR1C1 = "36"
Dim C As Range
For Each C In Range("B139:IV139")
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "5" Then C.FormulaR1C1 = "36"
End Sub

But got the following error "Complie Error: Duplicate declaration in current scope" and it highlighted this part
Code: 
[COLOR=red]Dim C As Range
[/COLOR]For Each C In Range("B139:IV139")
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "1" Then C.FormulaR1C1 = "34"

I then tried the following code
Code: 
Public Sub T_Score_Total_Male()
Dim C As Range
For Each C In Range("B139:IV139")
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "0" Then C.FormulaR1C1 = "34"
Next C("B139:IV139")
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "1" Then C.FormulaR1C1 = "34"
Next C("B139:IV139")
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "2" Then C.FormulaR1C1 = "35"
Next C("B139:IV139")
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "3" Then C.FormulaR1C1 = "35"

Next C("B139:IV139")
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "4" Then C.FormulaR1C1 = "36"
Next C("B139:IV139")
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "5" Then C.FormulaR1C1 = "36"
End Sub
but got an error that said "Compile Error:Invalid Next control variable reference"

Does anyone know how to fix this?
 
Upvote 0
You only write 1 loop...For Each C In Range...
And repeat the command for each IF within the same loop..

Here's for a few more lines

Rich (BB code): 
Dim C As Range
For Each C In Range("B139:Z139")
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "0" Then C.FormulaR1C1 = "34"
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "1" Then C.FormulaR1C1 = "34"
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "2" Then C.FormulaR1C1 = "35"
   '...etc...
   If C.Offset(-135, 0) = "1" And C.Offset(-1, 0) = "195" Then C.FormulaR1C1 = "127"
Next C

Hope that helps.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Inventory disposal plan
Replies
3
Views
300
Spaceballs_the_fanboy
S
C
  • Solved
Automation error The Object invoked has disconnected from its clients.
Replies
7
Views
1K
cstuder
C
X
  • Question Question
Checking the product's production date by its options in the table
Replies
1
Views
75
Xen101
X
S
  • Solved
Calculate annual sales per country
Replies
5
Views
159
AhoyNC
A
T
  • Question Question
RAINFALL CESSATION DATE USING INDEX
Replies
1
Views
243
Thlama
T

Forum statistics

Threads
1,224,616
Messages
6,179,909
Members
452,949
Latest member
beartooth91

Share this page

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
Back
Top