I am in the process of using a sub I found at http://dmcritchie.mvps.org/excel/delempty.htm to find an instance of something and then delete that row. However I plan on adapting this to add data to that row instead of deleting that row. My question is how do I find out what row 'rng' is referring to when I set it equal to what I am looking for.
Here is the sub.
Sub Delete_Events()
'Finds the 'what' and deletes that row.
Dim rng As Range
Dim what As String
what = "Motor Running"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub
The sub finds the 'what' and deletes it in this case. My question is how do I refer to what row that is and then actively select the a certain column say for example column B. Like I run this sub and in row 34 I find my first 'what' value. I want to put a certain value into column B that can vary based on user input.
I don't need anymore help then selecting column B in the same row that the rng will also be in. I appreciate your guys help with this.
Just for further clarification I'd want something like this.
Sub Add_Event_Type()
Dim rng As Range
Dim what As String
what = "user_data"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
'add frmValue.Value in ActiveCell(B & rng)
End If
Loop
End Sub
Bookmarks