Shorten the If statement and Capturing New Row Number

[alexnkc]

Hi,

Code below (If statement) will help me to identify the L column from row 90 backward until the cell is not "Pending...".

Sub test()


Dim strLocation As String
strLocation = "C:\Users\user\Desktop\[aaa.xls]Sheet1"


Dim strRowNum As Long
strRowNum = 90




With Range("E11")
    .Formula = _
            "='" & strLocation & "'!L" & strRowNum
    .Value = .Value


        If .Value = "Pending..." Then
            .Formula = _
            "='" & strLocation & "'!L90" & -1  '(L89)
        End If
            If .Value = "Pending..." Then
                .Formula = "='" & strLocation & "'!L" & strRowNum & -2  '(L88)
            End If
                
                If .Value = "Pending..." Then
                    .Formula = "='" & strLocation & "'!L" & strRowNum& - 3  '(L87)
                End If
                
                    If .Value = "Pending..." Then
                        .Formula = "='" & strLocation & "'!L" & strRowNum & -4  '(L86)
                    End If
                    
                        If .Value = "Pending..." Then
                            .Formula = "='" & strLocation & "'!L" & strRowNum & -5  '(L85)
                        End If
                        
                            If .Value = "Pending..." Then
                                .Formula = "='" & strLocation & "'!L" & strRowNum & -6  '(L84)
                            End If
                            
    .NumberFormat = "0.0 %"
End Sub

1) Is there anyway to reduce the (If statement) to shorter version? (I come across Do Until ... Loop, but I do not know how to apply on it)

2) Cell L86 is value (non "Pending..." wording), so what is the code that can capture new Row Number in wording such as strRowNumUpdated

Thanks for the kind help.

[alexnkc]

Hi,

I think below Code is working. Is there anyway better way to perform similar job?

Sub test()     
    Dim strLocation As String
    strLocation = "C:\Users\user\Desktop\[aaa.xls]Sheet1"
     
     
    Dim strRowNum As Long
    strRowNum = 90
    
    Dim i As String
    i = 1
      
    Dim strRowNumUpdated As Long '(NewRowCarrier)
     
    With Range("E11")
        .Formula = _
        "='" & strLocation & "'!L" & strRowNum
        .Value = .Value
         
    Do
        If .Value = "Pending..." Then
            .Formula = _
            "='" & strLocation & "'!L90" & -i
        End If
        i = 1 + i
    Loop Until .Value <> "Pending..."




    strRowNumUpdated = strRowNum - i + 1
    
        .NumberFormat = "0.0 %"
    
End Sub

[p45cal]

Try:

Sub test2()
Dim strLocation As String, RowNum As Long
strLocation = "C:\Users\user\Desktop\[aaa.xls]Sheet1"
RowNum = 91
With Range("E11")
  Do
  RowNum = RowNum - 1
    .Formula = "='" & strLocation & "'!L" & RowNum
  Loop Until .Value <> "Pending..."
  .Value = .Value
  .NumberFormat = "0.0 %"
End With
End Sub

[alexnkc]

Thanks p45cal.
Work Great