+ Reply to Thread
Results 1 to 4 of 4

Loop if one column value next column?

Hybrid View

  1. #1
    Registered User
    Join Date
    09-10-2004
    Posts
    18

    Loop if one column value next column?

    I am new to looping and am hoping someone might be able to help out. I have constructed a loop on column H. The problem is that I don't know how to loop the value into column I. Can anyone please help? I am posting the code here, but you will see that I have I2 hard coded, when the I cell should be the I on the same row as the H cell that is being evaluated.

    
    Dim varCounter As Integer
    
     Range("h2").Select
          
       Do Until Selection.Value = ""
                    
       Loop
       If Selection.Value = 65536 Then
       i2.Value = "Read"
       ElseIf Selection.Value = 131072 Then
       i2.Value = "Write"
       ElseIf Selection.Value = 196608 Then
       i2.Value = "Modify"
       ElseIf Selection.Value = 262144 Then
       i2.Value = "Execute"
       ElseIf Selection.Value = 524288 Then
       i2.Value = "Delete"
       ElseIf Selection.Value = 1048576 Then
       i2.Value = "Change Permissions"
       ElseIf Selection.Value = 2097152 Then
       i2.Value = "Take Ownership"
       ElseIf Selection.Value = 1 Then
       i2.Value = " Read Contents"
       ElseIf Selection.Value = 2 Then
       i2.Value = "Write Contents"
       ElseIf Selection.Value = 8 Then
       i2.Value = "Delete"
       ElseIf Selection.Value = 1677216 Then
       i2.Value = "Search"
       ElseIf Selection.Value = 1769472 Then
       i2.Value = "Full"
       Else: Selection.Value = "No Writes Assigned"
     
     End If
     
     Selection.Value = Selection.Value + 1
     Selection.Offset(1, 0).Select
    
    End Sub
    Thanks~!

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606
    Not sure if this you what you want. It loops through each cell in column H to the last one with something in it.
    Sub x()
    
    Dim varCounter As Integer
    Dim rng As Range
    
    With Sheet1
       For Each rng In .Range("H2:H" & Cells(Rows.Count, "H").End(xlUp).Row)
          Select Case rng
             Case 65536
             rng.Offset(, 1) = "Read"
             Case 131072
             rng.Offset(, 1) = "Write"
             Case 196608
             rng.Offset(, 1) = "Modify"
             Case 262144
             rng.Offset(, 1) = "Execute"
             Case 524288
             rng.Offset(, 1) = "Delete"
             Case 1048576
             rng.Offset(, 1) = "Change Permissions"
             Case 2097152
             rng.Offset(, 1) = "Take Ownership"
             Case 1
             rng.Offset(, 1) = "Read Contents"
             Case 2
             rng.Offset(, 1) = "Write Contents"
             Case 8
             rng.Offset(, 1) = "Delete"
             Case 1677216
             rng.Offset(, 1) = "Search"
             Case 1769472
             rng.Offset(, 1) = "Full"
             Case Else
             rng = "No Writes Assigned"
         End Select
        Next rng
    End With
    
    End Sub

  3. #3
    Registered User
    Join Date
    09-10-2004
    Posts
    18
    Thank you for the reply. My only question is where the code is putting the result or answer. It needs to go into I for each row that there is an H value. It also needs to start at h2.

    Make sense?

    Thanks!

  4. #4
    Registered User
    Join Date
    09-10-2004
    Posts
    18

    Talking

    That did it! Thanks! I just had to change the last one ("No Writes Assigned") to also have that offset -1, because that was going into H.

    Thank you so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1