Replicate IF(OR("Cell"=MyRange),1,0) in VBA

[ianfreeman]

Hi all,

I need to replicate the if(or(... statement within a for next loop.
I am using this to check if a value is in a defined range.

In Column A there is a defined range acting as a database of values.
In Column B there is a range of values updated daily.
In Column C resides the if(or..statement. - this analyses if the cell value is in column A. If it is it returns 1 else 0.

The For Next loop runs down Column C and if the value is 0 the value in column B is copied and pasted in the next blank cell in Column A.

When this happens Column C updates.

This makes for slow code.

If I could house the if(or(...statement in the vba and and remove column c, I'm hoping the speed will increase.

However I do not know the syntax...Can anyone help, please.

Many thanks...

Ian

 

[ianfreeman]

Just bumping my thread...

Anyone got any advice, or do I need to post more info?

 

[VoG]

Please post your code.

 

[ianfreeman]

Thanks for taking interest....
I have modified the code to match the example I have posted, hopefully I haven't botched it. As you will see the syntax I need help with is in green...

Sub Database()

'Adds new order number to database'

Application.ScreenUpdating = False

Dim k As String, a As String, b As String, e As String

e = "Data" 'Sheet name
k = Mid(Sheets(e).Cells(65536, 1).End(xlUp).Offset(1, 0).Address, 4, 5)
b = Mid(Sheets(d).Cells(65536, 2).End(xlUp).Address, 4, 5)
a = 2

For i = a To b Step 1

Cells(a, 3).Select

If Cells(a, 3) = "0" Then 'This is where I need the if Cell(a,2).value is in MyRange (Col A) then

Range("A" & k).Value = Cells(a, 2).Value

k = k + 1

End If
a = a + 1

Next

Application.ScreenUpdating = True

End Sub

 

[VoG]

Try

If IsNumeric(Application.Match(Cells(a, 3).Value, Columns("A"), 0)) Then

 

[ianfreeman]

Yes,yes,yes...that works perfectly.

I eventually got my head around it - not sure why it took so long...this is my actual code:

Sub Database()

Application.ScreenUpdating = False

Dim endrange As String, e As String
Dim k As String

e = "Data"

endrange = Range("M65536").End(xlUp).Row

For i = 2 To endrange

k = Sheets(e).Range("G65536").End(xlUp).Offset(1, 0).Row

If IsNumeric(Application.Match(Cells(i, 13).Value, Sheets("Data").Columns("A"), 0)) Then

If IsNumeric(Application.Match(Cells(i, 11).Value, Sheets("Data").Columns("G"), 0)) Then

Else

Sheets(e).Range("G" & k).Value = Cells(i, 11).Value
End If
End If
Next i
Application.ScreenUpdating = True
End Sub

The only problem is I'm going to have to go back and rewrite some code and get rid of all my slow worksheet arrays...