PDA

View Full Version : [SOLVED] Shorten the If statement and Capturing New Row Number



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.

alexnkc
11-28-2013, 01:32 AM
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
11-28-2013, 01:49 AM
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
11-28-2013, 03:48 AM
Thanks p45cal.
Work Great