Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Apr 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selective Cell input (97)

    Hi All,

    I'm having trouble with this macro. I want to in a selected range (say a1 to d5000) to have a formula keyed into the cell only if the cell has an existing formula in it. The formulas to be keyed in each of the 4 columns abcd are different, but the formulas down each column is the same except for the row numbers.

    Thanks.

  2. #2
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selective Cell input (97)

    Hi kingming. Try this code:
    Sub SelectiveCell()
    Dim MyRangeA, MyRangeB, MyRangeC, MyRangeD As Range
    Set MyRangeA = Worksheets("Sheet3").Range("A1:A18")
    For x = 1 To 18
    If MyRangeA.Cells(x).Value = "" Then _
    GoTo LastLine Else GoTo Line1
    Line1:
    MyRangeA.Cells(x).Formula = "=3*2"
    LastLine:
    Range("A1").Select
    Next x
    End Sub

    You would need to insert your own ranges and formulae in the appropriate places in the code. You would also need to add similar code for your other ranges.

    Regards
    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  3. #3
    Lounger
    Join Date
    Apr 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selective Cell input (97)

    Thanks for your advice.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Selective Cell input (97)

    HI,
    Do you mean you want a formula added to the cell only if it already has a formula in it or only if it already has something in it? If it's the former then try something like:
    <pre>Dim rngCell as range
    for each rngcell in selection
    with rngCell
    if left$(.formula,1) = "=" then
    select case .column
    case 1
    .formula = "formula for column A here"
    case 2
    .formula = "formula for column B here"
    case 3
    .formula = "formula for column C here"
    case 4
    .formula = "formula for column D here"
    case else
    end select
    end if
    end with
    next rngCell
    </pre>

    You can adjust this as necessary. Note: this is 'air code' so you may need to check it!
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Selective Cell input (97)

    "Air code"? Is that what Elvis called it? <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selective Cell input (97)

    Hi Rory

    I was interested in the solution you posted re: Selective Cell input. It is certainly more elegant than my effort. However, I've tried out the code but it doesn't quite work. It only puts a formula (the correct one) into the active cell. It doesn't loop through the for/next section of the code. I cannot figure out the cause. I have set rngCell to the appropriate range on the worksheet.
    I'd be very greatful for your time and expertise.
    I could see many useful applications of the code.

    Regards

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selective Cell input (97)

    Your code will replace constants as well as formula.
    Legare Coleman

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selective Cell input (97)

    With Rory's code you need to select all of the cells where you want the macro to operate (in this case A15000) before running the macro. If you want to fix the cells in the macro, you could replace the For statement with:

    [pre]
    For Each rngcell in Worksheets("Sheet1").Range("A15000")
    [pre]
    Legare Coleman

  9. #9
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selective Cell input (97)

    Thanks Legare

    It works a treat.

    Regards

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

Posting Permissions

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