Oct 16 2020 05:39 AM
Hi All,
Another (very) newbie here!
I have a database where I want VBA to search all data in column j for a value and then column h and if both values are found in the same row, then return the words "high priority" in column k.
I have tried the below however I am getting 'type mismatch' error. Can anyone help please?
Many thanks in advance,
Rachel
StartRow = 1
LASTROW = Cells.Find("*", Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row
For Row = StartRow To LASTROW Step 1
If Cells(Row, 10) = 30 And Cells(Row, 8) = 20 Then
Cells(Row, 11).Value = "HIGH PRIORITY"
End If
Next Row
Oct 16 2020 06:21 AM
@glanza270bhp I don't know why you are using VBA for this. It would be much easier to just put this formula in cell K1 (assuming your version of excel has dynamic arrays:
=IF(AND(H:H=20,J:J=30),"HIGH PRIORITY","")
if that doesn't work you can put this in K1 and fill/copy down as needed:
=IF(AND(H1=20,J1=30),"HIGH PRIORITY","")
Oct 16 2020 06:36 AM
Hello Rachel, my experience with Excel VBA is practically non-existent but I believe I can be of help here. I debugged your sample code and I found an issue with your declaration of LASTROW. Try this code and see if it helps at all.
StartRow = 1
LASTROW = Cells(Rows.Count, 1).End(xlUp).Row
For Row = StartRow To LASTROW Step 1
If Cells(Row, 10) = 30 And Cells(Row, 8) = 20 Then
Cells(Row, 11).Value = "HIGH PRIORITY"
End If
Next Row
It worked just fine for me.
https://www.excelcampus.com/vba/find-last-row-column-cell/comment-page-1/
If you need more.
Osten
Oct 16 2020 06:38 AM
But as @mtarler states. Unless you are looking to using VBA it might be better just to formulate in-cell. I'd generally rather his way but the choice to use VBA still exists.