I am trying to extract some values from a cell.
I have figured out how to extract the value of a cell to the left of "-" but I come across an error when the cell does not have a "-" at all. How do I ignore these cells / error?.
Any help would be much appreciated or ways to improve what I have made.
Also if it helps there are roughly 20000 rows on average.
Dim r As Long
Dim Location As Long
Dim m As Long
Dim ws As Worksheet
Set ws = Worksheets("NHBRSummary")
Set ws = Worksheets("Sheet1")
m = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row
For r = 2 To m
Location = InStr(1, Cells(r, 1), "-")
Cells(r, 2).Value = Left(Cells(r, 1), Location - 1)
Next
End Sub
Set ws = Worksheets("Sheet1")
with ws
m = .Cells(.Rows.Count, "F").End(xlUp).Row
For r = 2 To m
if InStr(1, .Cells(r, 1), "-") > 0 then
.Cells(r, 2).Value = split(.Cells(r, 1).value)(0)
end if
Next
end with
You set ws to a sheet, then immediately change it, you should remove the first set if you aren't using it.
You can still use left() and location if you would like, this is just another way of doing it. The important part is the if statement using instr()
@asger Good point, a with statement would be a nice addition.
Jeez, yeah I'm doing my own work at the same time... My bad.