Desperate Help

[mrinal saha]

Hi Folks,

This is something i have written and got confused badly. Problem area is in RED. I have stored a value in trans from a cell and later in code if trans matches to the options it should act accordingly. But just after getting into first IF condition pasting cell value = " " gets out of the condition.

Sub Test()
lr = Sheets("Rough").Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To lr
For Each ws In ThisWorkbook.Worksheets
'For i = 4 To lr
If ws.Name <> Sheets("Rough").Cells(i, 1).Value Then
GoTo y:
ElseIf ws.Name = Sheets("Rough").Cells(i, 1).Value Then
Deal = Sheets("Rough").Cells(i, 4).Value
myplan = Left(Sheets("Rough").Cells(i, 7), 11 - 2)
member = Sheets("Rough").Cells(i, 5).Value
trans = Sheets("Rough").Cells(i, 3).Value
Sheets("Rough").Activate
Sheets("Rough").Range("a3:i3").Select
Cost = Sheets("Rough").Cells(i, 8).Value
Proceeds = Sheets("Rough").Cells(i, 9).Value
Selection.AutoFilter
ActiveSheet.Range("$A$3:A" & lr).AutoFilter Field:=1, Criteria1:= _
ws.Name
ActiveSheet.Range("$A$3:A" & lr).AutoFilter Field:=4, Criteria1:= _
"Deal"
ActiveSheet.Range("$A$3:A" & lr).AutoFilter Field:=7, Criteria1:= _
"myplan"

ws.Activate

Cells.Select
On Error Resume Next
If Selection.Find(What:=Deal, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
ActiveCell.Offset(1, -2).Select
Range(Selection, Selection.End(xlDown)).Select
On Error Resume Next
If Selection.Find(What:=myplan, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
ActiveCell.Offset(1, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
If Selection.Find(What:=member, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
ActiveCell.Offset(0, 2).Select
If trans = "Adjusting Entry-Proceeds" Or "Adjusting Entry-Cost" Then
ActiveCell.Value = " "
ElseIf trans = "LOC Interest" Or "LOC Borrowing" Or "LOC Loan Repayment" Or "LOC Interest Repayment" _
Or "Purchase" Or "Expense-In" Or "Subsequent Funding" Or "Capitalized Expense" Or "Write Off" Then
ActiveCell.Value = Cost.Value
ElseIf trans = "Escrow Proceeds" Or "Dividend Income" Or "Interest Income" Or "Sale" Or "RCD (Non-Recallable)" _
Or "RCD (Recallable)" Or "PDROC (Recallable)" Or "PDROC (Non-Recallable)" Then
ActiveCell.Value = Proceeds.Value
ElseIf trans = " " Then
ActiveCell.Value = " "
End If
End If
End If
End If
Sheets("Rough").Activate
Selection.AutoFilter

' If Sheets("Rough").Cells(i, 4).Value <> Sheets("Rough").Cells(i - 1, 4).Value _
' Or Sheets("Rough").Cells(i, 4).Value = Sheets("Rough").Cells(i - 1, 4).Value _
' And Sheets("Rough").Cells(i, 3).Value <> Sheets("Rough").Cells(i - 1, 3).Value Then
'
' End If
'End If
End If

y: Next ws
Next i
End Sub

Thanks,
Mrinal

 

[VoG]

Your If statements need to be written like this

If trans = "Adjusting Entry-Proceeds" Or trans = "Adjusting Entry-Cost" Then