alexnkc
11-27-2013, 08:13 PM
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.
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.