1

I am trying to return the row number of cells that match two criteria taken from a different worksheet than those of the ranges.

Dim WS As Worksheet
Dim WP As Worksheet
Dim namerange As Range
Dim frange As Range
Dim LRP As Integer
Dim p As Integer
Dim place As Integer

Set WP = Sheets("Pacients")
Set WS = Sheets("Agenda")
Set namerange = WS.Range("C2:C5000")
Set frange = WS.Range("G2:G5000")
LRP = WP.Cells(Rows.Count, 1).End(xlUp).Row
p = 1

For p = 1 To LRP
    WP.Activate
    pacient = Range("B" & p)
    If Range("O" & p).Value = True Then
    
    
    Do While Range("O" & p).Value = True
        WP.Activate
        place = WorksheetFunction.Match(1, (namerange = Range("B" & p)) * (frange <> "F"), 0)
        WS.Activate
        Range("J" & place).Select
        ActiveCell.FormulaR1C1 = "1"
        Range("J" & place + 1).Select
        ActiveCell.FormulaR1C1 = "=IF(RC[-7] =" & pacient & " & MONTH(RC[-8])=MONTH(R" & place & "C2),1,"""")"
        Range("J" & place + 1).Select
        Selection.Copy
        Range("J" & place + 1 & ":J5000").Select
        ActiveSheet.Paste

       WP.Activate
        Range("O" & p).Calculate
        Loop
    
    End If
    Next p

Specifically, the part of the code that gives a mismatch error is place = WorksheetFunction.Match(1, (namerange = Range("B" & p)) * (frange <> "F"), 0)

Note that namerange and frange are from WS worksheet and Range("B" & p) comes from WP worksheet.

I have searched online and found an alternative using WS.Evaluate("MATCH(,,))but then I don't know how to change worksheet from criteria to range.

Also, given the if statement If Range("O" & p).Value = True the match function should return a number always (I did this to avoid type mismatches between integers and error).

Any help would be very much appreciated!

Foucault
  • 39
  • 5
  • 1
    What is LRP? From where does it come? How do you expect `For p = 1 To LRP` to run if it is unknown? – Tom Brunberg Aug 30 '22 at 11:26
  • 1
    I can only assume `If Range("O" & p).Value = True` should be replaced by `If IsNumeric(Range("O" & p).Value) = True`. Also, you should [qualify your ranges](https://stackoverflow.com/questions/41860149/vba-dot-notation) and [avoid activate/select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) where possible. – Cyril Aug 30 '22 at 13:43
  • @TomBrunberg Sorry I forgot to copy the line of code. It is the number of nonempty rows in the worksheet. I have now edited my question, thank you for raising that up. – Foucault Aug 31 '22 at 08:12
  • @Cyril Could you please extend why I should avoid activate/select? Thank you! – Foucault Aug 31 '22 at 08:13
  • 3
    @Foucault The link provided has the second answer with an explanation of reasons **why** activate/select should be avoided - worth a read. – Solar Mike Aug 31 '22 at 08:19

0 Answers0