Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to direct a value to a empty cell

    I would like to write a formula in d4 and have the value show in c4 without have a formula in c4. How can I do this? than you for your help.

    Roco

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Roco

    Place this code in the sheet's module. In this example cell D4 has the formula =A1/B1. If you make a change to either precedent (A1 or B1), Cell C4 will be updated with no formula in its cell. If you do not want a value showing in Cell D4 then change the font color to white.

    HTH,
    Maud

    hidden formula.png

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Range("C4") = Range("D4").Value
    Application.EnableEvents = True
    End Sub
    You could also eliminate the formula in D4 altogether and just have C4 update directly form changes made in A1 and/or B1

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    [c4] = [a1] / [b1]
    Application.EnableEvents = True
    End Sub

  3. #3
    New Lounger
    Join Date
    Sep 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Red face Delete Rows

    Thank You so much it was what I was looking for...maybe you can help me with one other thing.

    I have a table on sheet1 using columns C7 thru G196 that transfer information to sheet2. By me using "Sheet1!Cell#" All the rows have the same information on sheet 2 as sheet1 except for the cell in row D which has no formula. The information that I enter is not always in the same rows in sheet1. So On Sheet2 I would end up having Some blank cells in column D. I would like to create a bottom to click to delete all rows if the cell is blank in Column D even if there is still information in the same row. Can you help me with this? I thank you in advance for your help on this.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Roco,

    Here is the code that you requested. If the cell is blank on sheet 1 then the corresponding cell on sheet 2 will have a zero unless you have turned it off in Options or you have written a conditional formula to deal with it, ex =IF(Sheet1!C7="","",Sheet1!C7). I am not sure if you did so I have placed in the code to delete the row if col D in that row is blank or zero. if they do transfer to sheet 2 as blank only, then remove the segment in Blue.

    Place in a standard module:
    Code:
    Public Sub RemoveColumnD()
    Dim Rng As Range
    Dim cell As Range
    Set Rng = Worksheets("Sheet2").Range("D7:D196")
    For Each cell In Rng
        If cell = "" Or cell = 0 Then cell.EntireRow.Delete
    Next cell
    End Sub
    Assign the macro to a form button on sheet 2.

    HTH,
    Maud

  5. #5
    New Lounger
    Join Date
    Sep 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing Blank Cells in Column D

    Thank you so much for the code. It works with some quirks...I have to run it about 6 times. Each time I run it does delete rows. If you have another way of doing it with one click that would be great, but if not the one that you gave me will do. Again thank you so much.

    Roco

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Roco,

    Sorry, I had to cycle backwards throught the rows.

    Code:
    Public Sub RemoveColumnD()
    Application.ScreenUpdating = False
    With Worksheets("Sheet2")
    For I = 196 To 7 Step -1
        If .Cells(I, 4) = "" Or .Cells(I, 4) = 0 Then .Cells(I, 4).EntireRow.Delete
    Next I
    End With
    Application.ScreenUpdating = True
    End Sub
    Last edited by Maudibe; 2014-12-07 at 13:56.

  7. #7
    New Lounger
    Join Date
    Sep 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up Thank you so much

    Thank You so much....it works great....well done.

Posting Permissions

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