Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    47

    Unanswered: HELP..Select Entire Row and Use FIND function

    Hi Guys and Gals(if there are any here)
    I have VB code that was running good till a new dimension was added to the project. What I want to do is when the Macro is run a meesage box pops up and asks the user for to enter a numeric value. Lets say "98". My macro is suppose to look at each Row one by one and see which column has a equal or greater than value of the entered value, Find that column and return that particualr column heading which is a size or measurment.
    So, lets say below is a row

    INDEX--ReqSize--11--22--33--44--55--66--77------
    --1------11------98--67--43--45--78--97--59------
    --2------44------45--23--45--98--33--87--17------
    --3------66------12--23--45--56--33--99--17------
    --4------11------99--23--45--56--33--87--17------
    Another problem is the all the values in Columns are % and have the % sign after them where as the user only enters numeric value instead of 98% he enters 98. Req Size is the value returned by the macro. My code is like this :

    ReportValue = Application.InputBox("Enter Report Value in XX% format")


    Range("E4").Select (just specifying a start column)
    Do While ActiveCell.Value <> "" (telling it to run till records run out)
    ActiveCell.Offset(0, 1).Select
    BeginAddress = ActiveCell.Address
    Selection (ActiveCell.Row) (i am trying to select this entire row but can't)
    Selection.Find(What:=ReportValue, After:=ActiveCell,LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    BeginColumn = ActiveCell.Column
    Range("A3").Select
    Range(ActiveCell.Row, BeginColumn).Select (Trying to slect the coresponding heading)
    Selection.Copy
    BeginAddress.Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, -1).Select
    It is due tonight any help would be appreciated thanks ..

  2. #2
    Join Date
    Feb 2009
    Posts
    47

    addition

    in the FIND command can i add "%" to the variable ReportValue that was entered by the user.
    FIND (What= ReportValue"%"....is there any way do this kind of Find..

  3. #3
    Join Date
    Apr 2009
    Posts
    14
    You line in bold should be:

    ActiveCell.Row.Select


    I would be concerned about using the ActiveCell so much, as it's easy to lose track of it! I'd recommend using a variable, eg:

    Dim C as Range

    Set C = Range("E4")
    Do Until C.Value=""
    ...
    Set C = C.Offset(1,0)
    Loop
    Last edited by Kafrin; 04-16-09 at 07:18.

Posting Permissions

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