Hi, sorry. Those were my fault I think. This should do it though. The
reason it was going to the cell to the right was because of the "2" for
the column in the if/statement... I changed them back to "1". Also,
the error in it checking all but the last row should now be fixed as
well.
Sub Test()
Dim Screener As String
Screener = "A" 'Change this to the proper column
Dim Criteria1 As String
Dim Criteria2 As String
Dim Criteria3 As String
Dim Criteria4 As String
Dim Criteria5 As String
Criteria1 = "LSIL" 'Change as needed
Criteria2 = "ASC-US" 'Change as needed
Criteria3 = "ASC-H" 'Change as needed
Criteria4 = "HSIL" 'Change as needed
Criteria5 = "G1" 'Change as needed
'(you can add more criterias if needed)
'Note: "HS1" criteria comes AFTER "S1" not before
'Note: search is Case Sensitive
Range(Screener & "2").Select 'Assumes that you have a header in Row 1
Dim iRow As Integer
Dim iTotalRows As Integer
iRow = 0
iTotalRows = ActiveSheet.UsedRange.Rows.Count
Do
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria1 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "LG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria2 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "LG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria3 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "HG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria4 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "HG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria5 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "NEG"
'If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria6 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "HG"
iRow = iRow + 1
Loop Until iRow = iTotalRows
End Sub
ghobbit Wrote:
Hi
Many thanks for your help and explainations.
I tweaked the code a little as per the instructions you included with
it and it works more or less as I had hoped it would. Except for a
couple of things - here is the code as I have it at the moment
Sub Test()
Dim Screener As String
Screener = "A" 'Change this to the proper column
Dim Criteria1 As String
Dim Criteria2 As String
Dim Criteria3 As String
Dim Criteria4 As String
Dim Criteria5 As String
Criteria1 = "LSIL" 'Change as needed
Criteria2 = "ASC-US" 'Change as needed
Criteria3 = "ASC-H" 'Change as needed
Criteria4 = "HSIL" 'Change as needed
Criteria5 = "G1" 'Change as needed
'(you can add more criterias if needed)
'Note: "HS1" criteria comes AFTER "S1" not before
'Note: search is Case Sensitive
Range(Screener & "2").Select 'Assumes that you have a header in Row 1
Dim iRow As Integer
Dim iTotalRows As Integer
iRow = 0
iTotalRows = ActiveSheet.UsedRange.Rows.Count
Do
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria1 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "LG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria2 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "LG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria3 & "*" Then
ActiveCell.Offset(iRow, 2).Value = "HG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria4 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "HG"
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria5 & "*" Then
ActiveCell.Offset(iRow, 1).Value = "NEG"
‘If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria6 & "*" Then
ActiveCell.Offset(iRow, 2).Value = "HG"
iRow = iRow + 1
Loop Until iRow = iTotalRows - 1
End Sub
I noticed where it says
Range(Screener & "2").Select 'Assumes that you have a header in Row 1
I had a header and when I ran the macro it did its job down to all but
the last row. I had 30 rows for this test and the macro went from row 2
down to row 29. So I added some more rows so that I had 60 in all and
re-ran the macro. This time it went down to row 59 - how do I get it to
go all the way to the bottom regardless of how many rows I have?
Lastly you said that
each if/statement will overwrite the ones above it. So if the cell
contains Criteria1 and Criteria3, the results will be Criteria3 (i.e.
"HG").
It might be that I've got my wires crossed and havent got the above in
the right order but in a cell I have LSIL-CIN I/HPV:ASC-H
Going by the criteria order above - it should look at LSIL first and
therefore its a 'LG' however Criteria 3 finds ASC-H and therefore it
overwrites 'LG' and makes it a 'HG'
So far this isnt happened. What its doing is looking in A2 and finding
LSIL-CIN I/HPV:ASC-H and putting the 'LG' in B2 and then it puts 'HG'
in C2 rather overwriting 'LG' in B2. I would prefer it to overwrite it
- is this supposed to happen or have I done something wrong?
many thanks for your time and patience.
regards
Steve
--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile:
http://www.excelforum.com/member.php...o&userid=33371
View this thread:
http://www.excelforum.com/showthread...hreadid=541209