How do I loop the following script

B

Beachcomber1224

New Member
Joined
May 12, 2011
Messages
18
Hello,

I have the following script, but am having trouble looping it. There are basically three parts that I have condensed. Does anyone have suggestions on looping this until there is nothing in next column's 138 cell? Below is the condensed version of my script. Thanks for any help!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Sheet1.Range("B5").Value
Case 1 To 4
Sheet1.Range("B72").Value = Sheet1.Range("B5").Value - 1
Case Else
Sheet1.Range("B72").ClearContents
End Select
Select Case Sheet1.Range("B6").Value
Case 1 To 4
Sheet1.Range("B73").Value = Sheet1.Range("B6").Value - 1
Case Else
Sheet1.Range("B73").ClearContents
End Select
Select Case Sheet1.Range("B15").Value
Case 1
Sheet1.Range("B82").Value = 3
Case 2
Sheet1.Range("B82").Value = 2
Case 3
Sheet1.Range("B82").Value = 1
Case 4
Sheet1.Range("B82").Value = 0
Case Else
Sheet1.Range("B82").ClearContents
End Select
Select Case Sheet1.Range("B16").Value
Case 1
Sheet1.Range("B83").Value = 3
Case 2
Sheet1.Range("B83").Value = 2
Case 3
Sheet1.Range("B83").Value = 1
Case 4
Sheet1.Range("B83").Value = 0
Case Else
Sheet1.Range("B83").ClearContents
End Select

Range("B138").FormulaR1C1 = "=SUM(R[-66]C:R[-2]C)"
End Sub
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Apologies as I am VERY new to all of this - below is my code. Please note that I did not include the entire script as it is the code below but repeated for many cells.

Does anyone have any suggestions as to how I can loop this?

Appreciate any help!

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Select Case Sheet1.Range("B5").Value
        Case 1 To 4
           Sheet1.Range("B72").Value = Sheet1.Range("B5").Value - 1
       Case Else
           Sheet1.Range("B72").ClearContents
   End Select
 Select Case Sheet1.Range("B6").Value
        Case 1 To 4
           Sheet1.Range("B73").Value = Sheet1.Range("B6").Value - 1
       Case Else
           Sheet1.Range("B73").ClearContents
   End Select

 
Select Case Sheet1.Range("B7").Value
     Case 1
          Sheet1.Range("B74").Value = 3
     Case 2
          Sheet1.Range("B74").Value = 2
     Case 3
          Sheet1.Range("B74").Value = 1
     Case 4
          Sheet1.Range("B74").Value = 0
     Case Else
           Sheet1.Range("B74").ClearContents
End Select
Select Case Sheet1.Range("B11").Value
     Case 1
          Sheet1.Range("B78").Value = 3
     Case 2
          Sheet1.Range("B78").Value = 2
     Case 3
          Sheet1.Range("B78").Value = 1
     Case 4
          Sheet1.Range("B78").Value = 0
     Case Else
           Sheet1.Range("B78").ClearContents
End Select
Select Case Sheet1.Range("B15").Value
     Case 1
          Sheet1.Range("B82").Value = 3
     Case 2
          Sheet1.Range("B82").Value = 2
     Case 3
          Sheet1.Range("B82").Value = 1
     Case 4
          Sheet1.Range("B82").Value = 0
     Case Else
           Sheet1.Range("B82").ClearContents
End Select
 
Range("B138").FormulaR1C1 = "=SUM(R[-66]C:R[-2]C)"
 
End Sub
 
Upvote 0
Is there a reason you are doing this in VBA and not with formulas?
 
Upvote 0
I was instructed to do this by my boss using this method. We will be using this formula to enter data (i.e. each column = 1 person)
 
Upvote 0
Can you please explain what exactly you are needing to do, and over what ranges this needs to loop? I think a written explanation will help a lot.
 
Upvote 0
No problem - happy to clarify.

In the first part of the column, I will be entering "original" values. The script that I wrote converts these "original" values to "raw" values. Certain cells are converted 1 way (i.e. 1=0, 2=1, 3=2, 4=3) and other cells another way (1=3, 2=2, 3=1, 4=0). The last part sums all of the "raw" values.

The goal is to have these functions, which are formatted in column B, loop for all following columns. I was thinking of a loop - (i.e. Loop Until IsEmpty(ActiveCell.Offset(-135, 1)) - but my script didn't work.

Hope this helps!
 
Upvote 0
That does help. Now so I can better understand exactly where the loop needs to occur (what cells are going to change, and by what increment), can you please post a sizeable chunk of your code (more than one "iteration")?
 
Upvote 0
Here is the full script

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Select Case Sheet1.Range("B5").Value
        Case 1 To 4
           Sheet1.Range("B72").Value = Sheet1.Range("B5").Value - 1
       Case Else
           Sheet1.Range("B72").ClearContents
   End Select
 Select Case Sheet1.Range("B6").Value
        Case 1 To 4
           Sheet1.Range("B73").Value = Sheet1.Range("B6").Value - 1
       Case Else
           Sheet1.Range("B73").ClearContents
   End Select
 Select Case Sheet1.Range("B8").Value
        Case 1 To 4
           Sheet1.Range("B75").Value = Sheet1.Range("B8").Value - 1
       Case Else
           Sheet1.Range("B75").ClearContents
   End Select
 Select Case Sheet1.Range("B9").Value
        Case 1 To 4
           Sheet1.Range("B76").Value = Sheet1.Range("B9").Value - 1
       Case Else
           Sheet1.Range("B76").ClearContents
   End Select
 Select Case Sheet1.Range("B10").Value
        Case 1 To 4
           Sheet1.Range("B77").Value = Sheet1.Range("B10").Value - 1
       Case Else
           Sheet1.Range("B77").ClearContents
   End Select
 Select Case Sheet1.Range("B12").Value
        Case 1 To 4
           Sheet1.Range("B79").Value = Sheet1.Range("B12").Value - 1
       Case Else
           Sheet1.Range("B79").ClearContents
   End Select
 Select Case Sheet1.Range("B13").Value
        Case 1 To 4
           Sheet1.Range("B80").Value = Sheet1.Range("B13").Value - 1
       Case Else
           Sheet1.Range("B80").ClearContents
   End Select
 Select Case Sheet1.Range("B14").Value
        Case 1 To 4
           Sheet1.Range("B81").Value = Sheet1.Range("B14").Value - 1
       Case Else
           Sheet1.Range("B81").ClearContents
   End Select
 Select Case Sheet1.Range("B17").Value
        Case 1 To 4
           Sheet1.Range("B84").Value = Sheet1.Range("B17").Value - 1
       Case Else
           Sheet1.Range("B84").ClearContents
   End Select
 Select Case Sheet1.Range("B18").Value
        Case 1 To 4
           Sheet1.Range("B85").Value = Sheet1.Range("B18").Value - 1
       Case Else
           Sheet1.Range("B85").ClearContents
   End Select
 Select Case Sheet1.Range("B20").Value
        Case 1 To 4
           Sheet1.Range("B87").Value = Sheet1.Range("B20").Value - 1
       Case Else
           Sheet1.Range("B87").ClearContents
   End Select
 Select Case Sheet1.Range("B22").Value
        Case 1 To 4
           Sheet1.Range("B89").Value = Sheet1.Range("B22").Value - 1
       Case Else
           Sheet1.Range("B89").ClearContents
   End Select
 Select Case Sheet1.Range("B23").Value
        Case 1 To 4
           Sheet1.Range("B90").Value = Sheet1.Range("B23").Value - 1
       Case Else
           Sheet1.Range("B90").ClearContents
   End Select
 Select Case Sheet1.Range("B24").Value
        Case 1 To 4
           Sheet1.Range("B91").Value = Sheet1.Range("B24").Value - 1
       Case Else
           Sheet1.Range("B91").ClearContents
   End Select
 Select Case Sheet1.Range("B27").Value
        Case 1 To 4
           Sheet1.Range("B94").Value = Sheet1.Range("B27").Value - 1
       Case Else
           Sheet1.Range("B94").ClearContents
   End Select
 Select Case Sheet1.Range("B28").Value
        Case 1 To 4
           Sheet1.Range("B95").Value = Sheet1.Range("B28").Value - 1
       Case Else
           Sheet1.Range("B95").ClearContents
   End Select
 Select Case Sheet1.Range("B29").Value
        Case 1 To 4
           Sheet1.Range("B96").Value = Sheet1.Range("B29").Value - 1
       Case Else
           Sheet1.Range("B96").ClearContents
   End Select
 Select Case Sheet1.Range("B31").Value
        Case 1 To 4
           Sheet1.Range("B98").Value = Sheet1.Range("B31").Value - 1
       Case Else
           Sheet1.Range("B98").ClearContents
   End Select
 Select Case Sheet1.Range("B32").Value
        Case 1 To 4
           Sheet1.Range("B99").Value = Sheet1.Range("B32").Value - 1
       Case Else
           Sheet1.Range("B99").ClearContents
   End Select
 Select Case Sheet1.Range("B33").Value
        Case 1 To 4
           Sheet1.Range("B100").Value = Sheet1.Range("B33").Value - 1
       Case Else
           Sheet1.Range("B100").ClearContents
   End Select
 Select Case Sheet1.Range("B34").Value
        Case 1 To 4
           Sheet1.Range("B101").Value = Sheet1.Range("B34").Value - 1
       Case Else
           Sheet1.Range("B101").ClearContents
   End Select
 Select Case Sheet1.Range("B35").Value
        Case 1 To 4
           Sheet1.Range("B102").Value = Sheet1.Range("B35").Value - 1
       Case Else
           Sheet1.Range("B102").ClearContents
   End Select
 Select Case Sheet1.Range("B37").Value
        Case 1 To 4
           Sheet1.Range("B104").Value = Sheet1.Range("B37").Value - 1
       Case Else
           Sheet1.Range("B104").ClearContents
   End Select
 Select Case Sheet1.Range("B38").Value
        Case 1 To 4
           Sheet1.Range("B105").Value = Sheet1.Range("B38").Value - 1
       Case Else
           Sheet1.Range("B105").ClearContents
   End Select
 Select Case Sheet1.Range("B39").Value
        Case 1 To 4
           Sheet1.Range("B106").Value = Sheet1.Range("B39").Value - 1
       Case Else
           Sheet1.Range("B106").ClearContents
   End Select
 Select Case Sheet1.Range("B40").Value
        Case 1 To 4
           Sheet1.Range("B107").Value = Sheet1.Range("B40").Value - 1
       Case Else
           Sheet1.Range("B107").ClearContents
   End Select
 Select Case Sheet1.Range("B41").Value
        Case 1 To 4
           Sheet1.Range("B108").Value = Sheet1.Range("B41").Value - 1
       Case Else
           Sheet1.Range("B108").ClearContents
   End Select
 Select Case Sheet1.Range("B43").Value
        Case 1 To 4
           Sheet1.Range("B110").Value = Sheet1.Range("B43").Value - 1
       Case Else
           Sheet1.Range("B110").ClearContents
   End Select
 Select Case Sheet1.Range("B45").Value
        Case 1 To 4
           Sheet1.Range("B112").Value = Sheet1.Range("B45").Value - 1
       Case Else
           Sheet1.Range("B112").ClearContents
   End Select
 Select Case Sheet1.Range("B46").Value
        Case 1 To 4
           Sheet1.Range("B113").Value = Sheet1.Range("B46").Value - 1
       Case Else
           Sheet1.Range("B113").ClearContents
   End Select
 Select Case Sheet1.Range("B48").Value
        Case 1 To 4
           Sheet1.Range("B115").Value = Sheet1.Range("B48").Value - 1
       Case Else
           Sheet1.Range("B115").ClearContents
   End Select
 Select Case Sheet1.Range("B50").Value
        Case 1 To 4
           Sheet1.Range("B117").Value = Sheet1.Range("B50").Value - 1
       Case Else
           Sheet1.Range("B117").ClearContents
   End Select
 Select Case Sheet1.Range("B51").Value
        Case 1 To 4
           Sheet1.Range("B118").Value = Sheet1.Range("B51").Value - 1
       Case Else
           Sheet1.Range("B118").ClearContents
   End Select
 Select Case Sheet1.Range("B53").Value
        Case 1 To 4
           Sheet1.Range("B120").Value = Sheet1.Range("B53").Value - 1
       Case Else
           Sheet1.Range("B120").ClearContents
   End Select
 Select Case Sheet1.Range("B54").Value
        Case 1 To 4
           Sheet1.Range("B121").Value = Sheet1.Range("B54").Value - 1
       Case Else
           Sheet1.Range("B121").ClearContents
   End Select
 Select Case Sheet1.Range("B55").Value
        Case 1 To 4
           Sheet1.Range("B122").Value = Sheet1.Range("B55").Value - 1
       Case Else
           Sheet1.Range("B122").ClearContents
   End Select
 Select Case Sheet1.Range("B57").Value
        Case 1 To 4
           Sheet1.Range("B124").Value = Sheet1.Range("B57").Value - 1
       Case Else
           Sheet1.Range("B124").ClearContents
   End Select
 Select Case Sheet1.Range("B58").Value
        Case 1 To 4
           Sheet1.Range("B125").Value = Sheet1.Range("B58").Value - 1
       Case Else
           Sheet1.Range("B125").ClearContents
   End Select
 Select Case Sheet1.Range("B60").Value
        Case 1 To 4
           Sheet1.Range("B127").Value = Sheet1.Range("B60").Value - 1
       Case Else
           Sheet1.Range("B127").ClearContents
   End Select
 Select Case Sheet1.Range("B61").Value
        Case 1 To 4
           Sheet1.Range("B128").Value = Sheet1.Range("B61").Value - 1
       Case Else
           Sheet1.Range("B128").ClearContents
   End Select
 Select Case Sheet1.Range("B62").Value
        Case 1 To 4
           Sheet1.Range("B129").Value = Sheet1.Range("B62").Value - 1
       Case Else
           Sheet1.Range("B129").ClearContents
   End Select
 Select Case Sheet1.Range("B63").Value
        Case 1 To 4
           Sheet1.Range("B130").Value = Sheet1.Range("B63").Value - 1
       Case Else
           Sheet1.Range("B130").ClearContents
   End Select
 Select Case Sheet1.Range("B64").Value
        Case 1 To 4
           Sheet1.Range("B131").Value = Sheet1.Range("B64").Value - 1
       Case Else
           Sheet1.Range("B131").ClearContents
   End Select
   
 Select Case Sheet1.Range("B65").Value
        Case 1 To 4
           Sheet1.Range("B132").Value = Sheet1.Range("B65").Value - 1
       Case Else
           Sheet1.Range("B132").ClearContents
   End Select
   
  Select Case Sheet1.Range("B66").Value
        Case 1 To 4
           Sheet1.Range("B133").Value = Sheet1.Range("B66").Value - 1
       Case Else
           Sheet1.Range("B133").ClearContents
   End Select
   
 Select Case Sheet1.Range("B67").Value
        Case 1 To 4
           Sheet1.Range("B134").Value = Sheet1.Range("B67").Value - 1
       Case Else
           Sheet1.Range("B134").ClearContents
   End Select
 Select Case Sheet1.Range("B68").Value
        Case 1 To 4
           Sheet1.Range("B135").Value = Sheet1.Range("B68").Value - 1
       Case Else
           Sheet1.Range("B135").ClearContents
   End Select
 Select Case Sheet1.Range("B69").Value
        Case 1 To 4
           Sheet1.Range("B136").Value = Sheet1.Range("B69").Value - 1
       Case Else
           Sheet1.Range("B136").ClearContents
   End Select
Select Case Sheet1.Range("B7").Value
     Case 1
          Sheet1.Range("B74").Value = 3
     Case 2
          Sheet1.Range("B74").Value = 2
     Case 3
          Sheet1.Range("B74").Value = 1
     Case 4
          Sheet1.Range("B74").Value = 0
     Case Else
           Sheet1.Range("B74").ClearContents
End Select
Select Case Sheet1.Range("B11").Value
     Case 1
          Sheet1.Range("B78").Value = 3
     Case 2
          Sheet1.Range("B78").Value = 2
     Case 3
          Sheet1.Range("B78").Value = 1
     Case 4
          Sheet1.Range("B78").Value = 0
     Case Else
           Sheet1.Range("B78").ClearContents
End Select
Select Case Sheet1.Range("B15").Value
     Case 1
          Sheet1.Range("B82").Value = 3
     Case 2
          Sheet1.Range("B82").Value = 2
     Case 3
          Sheet1.Range("B82").Value = 1
     Case 4
          Sheet1.Range("B82").Value = 0
     Case Else
           Sheet1.Range("B82").ClearContents
End Select
Select Case Sheet1.Range("B16").Value
     Case 1
          Sheet1.Range("B83").Value = 3
     Case 2
          Sheet1.Range("B83").Value = 2
     Case 3
          Sheet1.Range("B83").Value = 1
     Case 4
          Sheet1.Range("B83").Value = 0
     Case Else
           Sheet1.Range("B83").ClearContents
End Select
Select Case Sheet1.Range("B19").Value
     Case 1
          Sheet1.Range("B86").Value = 3
     Case 2
          Sheet1.Range("B86").Value = 2
     Case 3
          Sheet1.Range("B86").Value = 1
     Case 4
          Sheet1.Range("B86").Value = 0
     Case Else
           Sheet1.Range("B86").ClearContents
End Select
Select Case Sheet1.Range("B21").Value
     Case 1
          Sheet1.Range("B88").Value = 3
     Case 2
          Sheet1.Range("B88").Value = 2
     Case 3
          Sheet1.Range("B88").Value = 1
     Case 4
          Sheet1.Range("B88").Value = 0
     Case Else
           Sheet1.Range("B88").ClearContents
End Select
Select Case Sheet1.Range("B25").Value
     Case 1
          Sheet1.Range("B92").Value = 3
     Case 2
          Sheet1.Range("B92").Value = 2
     Case 3
          Sheet1.Range("B92").Value = 1
     Case 4
          Sheet1.Range("B92").Value = 0
     Case Else
           Sheet1.Range("B92").ClearContents
End Select
Select Case Sheet1.Range("B26").Value
     Case 1
          Sheet1.Range("B93").Value = 3
     Case 2
          Sheet1.Range("B93").Value = 2
     Case 3
          Sheet1.Range("B93").Value = 1
     Case 4
          Sheet1.Range("B93").Value = 0
     Case Else
           Sheet1.Range("B93").ClearContents
End Select
Select Case Sheet1.Range("B30").Value
     Case 1
          Sheet1.Range("B97").Value = 3
     Case 2
          Sheet1.Range("B97").Value = 2
     Case 3
          Sheet1.Range("B97").Value = 1
     Case 4
          Sheet1.Range("B97").Value = 0
     Case Else
           Sheet1.Range("B97").ClearContents
End Select
Select Case Sheet1.Range("B36").Value
     Case 1
          Sheet1.Range("B103").Value = 3
     Case 2
          Sheet1.Range("B103").Value = 2
     Case 3
          Sheet1.Range("B103").Value = 1
     Case 4
          Sheet1.Range("B103").Value = 0
     Case Else
           Sheet1.Range("B103").ClearContents
End Select
Select Case Sheet1.Range("B42").Value
     Case 1
          Sheet1.Range("B109").Value = 3
     Case 2
          Sheet1.Range("B109").Value = 2
     Case 3
          Sheet1.Range("B109").Value = 1
     Case 4
          Sheet1.Range("B109").Value = 0
     Case Else
           Sheet1.Range("B109").ClearContents
End Select
Select Case Sheet1.Range("B44").Value
     Case 1
          Sheet1.Range("B111").Value = 3
     Case 2
          Sheet1.Range("B111").Value = 2
     Case 3
          Sheet1.Range("B111").Value = 1
     Case 4
          Sheet1.Range("B111").Value = 0
     Case Else
           Sheet1.Range("B111").ClearContents
End Select
Select Case Sheet1.Range("B47").Value
     Case 1
          Sheet1.Range("B114").Value = 3
     Case 2
          Sheet1.Range("B114").Value = 2
     Case 3
          Sheet1.Range("B114").Value = 1
     Case 4
          Sheet1.Range("B114").Value = 0
     Case Else
           Sheet1.Range("B114").ClearContents
End Select
Select Case Sheet1.Range("B49").Value
     Case 1
          Sheet1.Range("B116").Value = 3
     Case 2
          Sheet1.Range("B116").Value = 2
     Case 3
          Sheet1.Range("B116").Value = 1
     Case 4
          Sheet1.Range("B116").Value = 0
     Case Else
           Sheet1.Range("B116").ClearContents
End Select
Select Case Sheet1.Range("B52").Value
     Case 1
          Sheet1.Range("B119").Value = 3
     Case 2
          Sheet1.Range("B119").Value = 2
     Case 3
          Sheet1.Range("B119").Value = 1
     Case 4
          Sheet1.Range("B119").Value = 0
     Case Else
           Sheet1.Range("B119").ClearContents
End Select
Select Case Sheet1.Range("B56").Value
     Case 1
          Sheet1.Range("B123").Value = 3
     Case 2
          Sheet1.Range("B123").Value = 2
     Case 3
          Sheet1.Range("B123").Value = 1
     Case 4
          Sheet1.Range("B123").Value = 0
     Case Else
           Sheet1.Range("B123").ClearContents
End Select
Select Case Sheet1.Range("B59").Value
     Case 1
          Sheet1.Range("B126").Value = 3
     Case 2
          Sheet1.Range("B126").Value = 2
     Case 3
          Sheet1.Range("B126").Value = 1
     Case 4
          Sheet1.Range("B126").Value = 0
     Case Else
           Sheet1.Range("B126").ClearContents
End Select
Range("B138").FormulaR1C1 = "=SUM(R[-66]C:R[-2]C)"
End Sub

I was also hoping to loop the following once I finished this first part. This converts the "raw" scores into the "final" scores. Not sure if you had any thoughts on how to loop this? One thought I had was "Loop Until IsEmpty(ActiveCell.Offset(-135, 1))". Any help would be much appreciated!

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
 
Upvote 0
Looking at your entire script, it doesn't appear that there is any consistency in what could be looped.

For example, you are looking at rows 5, 6, 8, 9, 10, 12, 13, 14, 17, 18, 20, 22, 23, 24, 27, etc... Note that it isn't looking at rows 7, 11, 15, 16, 19, 21, 25, 26.

What we COULD do is create a separate array that houses the row numbers you want each part of the procedure to run on, and then loop through that array's values...
 
Upvote 0
I'm confused in what you're trying to loop. So right now your code is applied to column B, do you want it to loop through column C, D, E, etc until it gets to the last column w/ data?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Help with combining 2 macros into 1
Replies
8
Views
269
MARK858
MARK858
4
  • Question Question
VBA to Print Labels but Issues Selecting Printer
Replies
1
Views
297
rpaulson
rpaulson
U
  • Solved
Doing a search for text within a call, from entries in a defined name range
Replies
3
Views
127
Upex
U
R
  • Solved
Macro To Move Files - Will Not Move .msg or .csv files
Replies
4
Views
105
rachel06
R
J
  • Question Question
Userform inconsistent responses
Replies
1
Views
218
Candyman8019
C

Forum statistics

Threads
1,224,619
Messages
6,179,924
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