+ Reply to Thread
Results 1 to 4 of 4

How do I find out what row 'rng' refers to?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-11-2011
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    325

    How do I find out what row 'rng' refers to?

    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
    Last edited by 111StepsAhead; 05-10-2011 at 08:31 AM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How do I find out what row 'rng' refers to?

    cells(rng.row, "B") = frmValue.Value
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How do I find out what row 'rng' refers to?

    Hi

    In the Else condition
    Range("B" & rng.Row) = 'your value'
    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: How do I find out what row 'rng' refers to?

    Think of rng like a cell. Lets say it finds cell A34 first. The Row property of A34 is 34. The column property would be 1 (A = 1, B = 2, etc.) So the line of code you may be looking for would be something like:

    Activesheet.Cells(rng.Row, "B").Value = frmValue
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1