shortening if statement [SOLVED]

[asafraz81]

hi,

i have code with 'for' loop 6-138

i want the loop to ignore 10 numbers.

i know that i can write if not statement to ignore one number.

how can i ignore 10 numbers in one if statement?

i attach part of the code to explain my question.

the code runs on column with loop on the cells

[pre]

Sub expances_sheet2()
Dim i As Integer
On Error Resume Next
For i = 6 To 138
If Not i = 30 Then
If Not i = 36 Then
If Not i = 45 Then
If Not i = 50 Then
If Not i = 54 Then
If Not i = 63 Then
If Not i = 69 Then
If Not i = 91 Then
If Not i = 94 Then
If Not i = 133 Then

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
Next i

[/pre]
i want to fix this code and to use one if statement

can it be?


thanks

asaf

 

[Smallman]

Hi Asafraz81


In order to provide appropriate advice we need to see what is in between each of those if Statements. That will be the key to shortening the code. I would suggest a loop is not appropriate if you are performing the same action continuously for all criteria.


Take care


Soon to be Asleep


Smallman

 

[asafraz81]

my code runs with vlookup see below:

[pre]

Sub expances_sheet2()
Dim cell As Range
Dim i As Integer
' Workbooks(".xlsx").Sheets("").Activate
Workbooks(".xlsm").Sheets("").Activate
On Error Resume Next
For i = 6 To 138
If Not i = 30 Then
If Not i = 36 Then
If Not i = 45 Then
If Not i = 50 Then
If Not i = 54 Then
If Not i = 63 Then
If Not i = 69 Then
If Not i = 91 Then
If Not i = 94 Then
If Not i = 133 Then

Workbooks(".xlsm").Sheets("").Cells(i, 4) = _
WorksheetFunction.VLookup(Workbooks(".xlsm").Sheets("").Cells(i, 2), _
Workbooks(".xlsx").Sheets("").Range("expances9"), 3, False) / 1000
Workbooks(".xlsm").Sheets("").Cells(i, 11) = _
WorksheetFunction.VLookup(Workbooks(".xlsm").Sheets("").Cells(i, 2), _
Workbooks(".xlsx").Sheets("").Range("expances9"), 2, False) / 1000
Workbooks(".xlsm").Sheets("").Cells(i, 5) = _
WorksheetFunction.VLookup(Workbooks(".xlsm").Sheets("").Cells(i, 2), _
Workbooks("  .xlsx").Sheets("").Range("expances8"), 3, False) / 1000
Workbooks(".xlsm").Sheets("").Cells(i, 12) = _
WorksheetFunction.VLookup(Workbooks(".xlsm").Sheets("").Cells(i, 2), _
Workbooks("  .xlsx").Sheets("").Range("expances8"), 2, False) / 1000

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
Next i
End Sub

[/pre]
i hope now it will be better to understand.

thanks

 

[NARAYANK991]

Hi ,


I am sure others will respond , but in the meantime , you can try this :

[pre]

Sub expances_sheet2()
Dim Process_It As Boolean
Dim j As Integer
Dim x() As Integer
x = Array(30, 36, 45, 50, 54, 63, 69, 91, 94, 133)

Dim i As Integer
On Error Resume Next
For i = 6 To 138
Process_It = True
For j = LBound(x) To UBound(x)
If i < x(j) Then Exit For
If i = x(j) Then Process_It = False
Next
If Process_It Then
Your existing code comes here
End If
Next i
End Sub

[/pre]
Narayan

 

[lohithsriram]

Hi,

Here is another version to try on..

[pre]

Sub expances_sheet2()

Dim i As Integer
On Error Resume Next
Workbooks(".xlsm").Sheets("").Activate
For i = 6 To 138
Select Case i
Case 30, 36, 45, 50, 54, 63, 69, 91, 94, 133

Case Else
Workbooks(".xlsm").Sheets("").Cells(i, 4) = _
WorksheetFunction.VLookup(Workbooks(".xlsm").Sheets("").Cells(i, 2), _
Workbooks(".xlsx").Sheets("").Range("expances9"), 3, False) / 1000
Workbooks(".xlsm").Sheets("").Cells(i, 11) = _
WorksheetFunction.VLookup(Workbooks(".xlsm").Sheets("").Cells(i, 2), _
Workbooks(".xlsx").Sheets("").Range("expances9"), 2, False) / 1000
Workbooks(".xlsm").Sheets("").Cells(i, 5) = _
WorksheetFunction.VLookup(Workbooks(".xlsm").Sheets("").Cells(i, 2), _
Workbooks(" .xlsx").Sheets("").Range("expances8"), 3, False) / 1000
Workbooks(".xlsm").Sheets("").Cells(i, 12) = _
WorksheetFunction.VLookup(Workbooks(".xlsm").Sheets("").Cells(i, 2), _
Workbooks(" .xlsx").Sheets("").Range("expances8"), 2, False) / 1000
End Select
Next i
End Sub

[/pre]

 

[asafraz81]

hi narayank

the code you suggest make an error "type mismatch" on this line:

x = Array(30, 36, 45, 50, 54, 63, 69, 91, 94, 133)

 

[asafraz81]

lohith,

your idea work very good.

thanks to everybody...

 

[NARAYANK991]

Hi ,


Sorry , my mistake ; the following declaration is to be changed as given below :

[pre]

Dim x As Variant

[/pre]
Narayan