Simple Excel VB problem

[elkw]

Hi, im very new to Visual Basic and am using it for a university
project im doing.

i want to be able to check if an integer within a cell is in a range
of cells on a different sheet also containing integers. If it is in
that range of cells then i want it to publish to a list box i have.

Im guessing this is a real simple bit of syntax so if anyone could
give me some example ccode, i would be very much appreciative.

Many thanks,

Kieran

 

[Bob Phillips]

The easiest way that I can think of is to use a worksheet function to check
if that value is found in the other range.

COUNTIF will count how many times it appears. You can use a worksheet
function in VBA using the WorksheetFunction method, and if the value is
greater than 0 you know its there, so add it to the listbox.

As it is a uni project I will leave you to look up the details, I have given
you a method.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[elkw]

Thank you very much Bob, that has helped greatly, problem solved.

Kieran

 

[Guest]

Sub elkw()
v = Sheets("s1").Range("A1").Value
Set r2 = Sheets("s2").Range("A1:B9")
k = Application.CountIf(r2, v)
If k > 0 Then
MsgBox (v & " is there")
Else
MsgBox (v & " is not there")
End If
End Sub

Gets a value from sheet s1 and looks for it in a range of cells in sheet s2