Checking two or more cells

Hello Everyone,

I have been lurking the forums for a while to help with several of my VB issues however this one has me scratching my head to the point I need assistance.

Here is what I am looking to do;
I need a loop that will check two ranges and if both are true add to a variable and output to another sheet.

Here is what I have so far;

For Each cell In Worksheets("sheet1").Range("C2:C351")
If Left((cell.Value), 8) = "10.24.2." Then
IPADDRESS = IPADDRESS + 1
Worksheets("IP Counts").Range("B12") = IPADDRESS
End If
NEXT


The above works great for a single range however when I try to check another range it will give random errors during debug.

Below is what my recent attempt was which doesn't work. Same for statement as above however different IF.

If (Left((cell.Value), 7) = "10.7.2.") And (MODCheck(Left((cell.Value)), 5) = "RICOH") Then
IPMOD = IPMOD + 1
Worksheets("IP Counts").Range("F12") = IPMOD
End If


I have the feeling I need to do something different with my For loop but again it works fine with a single range.

Thank you for any help you can provide, until then back to the books to figure out a solution.

Hi Bijiont,
You dont need to use parenthesis for the cel value

Left(cell.Value, 7) = "10.7.2." ' this is enough


If you check your second macro, you'll see that you closed one parenthesis too soon, making the "Left()" command incomplete

'instead of
(MODCheck(Left((cell.Value)), 5) = "RICOH") Then

'try
(MODCheck(Left(cell.Value, 5)) = "RICOH") Then

Thanks for the reply CaBieberach however that code doesn't work.

When using what you had below it gives off a Syntax Error.

I know this has to be possible however I am not sure how I am currently doing it is the correct way or not.

If it makes it easier the end result is I need to check to large ranges (C2:C390) & (D3:D390) and if they equal whatever my variables are they add to another. Problem I am seeing is that just doing the range doesn't cut it I need to also take the LEFT 5~15 characters to verify their IP address go to the proper variable.

All of this is occuring on the same sheet except the output variable which is taking a total of what comes back as a true statement is on another sheet but that portion is not a problem.

Thanks in advance to anyone who replies.

Hi Bijiont,
How many parameters do you need for the "MODCheck" procedure?
In which line exactly is the error coming?

In your procedure, you are testing (C2:C390) to be "10.7.2." and again testing the same range (C2:C390) to be MODCheck(Left((cell.Value)), 5) = "RICOH". Is that right so?

Give me a ScreenShot or upload your worksheet to help you more.

From what you wrote i could only asume the following:

If (Left(cell.Value, 7) = "10.7.2.") And (MODCheck(Left(cell.Offset(0,1).Value, 15),5) = "RICOH") Then
IPMOD = IPMOD + 1

End If

Worksheets("IP Counts").Range("F12") = IPMOD 'Write this line outside the loop to speed Up your code.

'where:
' Cell.value is a cell in the C column
' Cell.Offset(0,1).value is the cell in the same row as Cell.Value, but in column D.
' Im also assuming you want to pass to the procedure MODCheck the 15 leftmost characters and the number 5 .

Thank you CaBieberach!!

You put me on the right track I completely forgot I can use the same range and just use Offset on the cell selection.

It's now working exactly how I need it to.

Thank you again for all your help!


Just in the event anyone else was curious here is what is the end result code:

If (Left(cell.Value, 8) = "10.24.2.") And (Left(cell.Offset(0, 1).Value, 5) = "RICOH") Then
TestMFP = TestMFP + 1
Worksheets("Total Counts").Range("F3") = TestMFP
End If


It's in no way "pretty" right now but it does what I need short term.

Nice to hear that Bijiont.
It was a pleasure.