none
VBA Paste as Text on Condition RRS feed

  • Question

  • Hello all

    We have something like this:


    - In Column "B" there are empty cells
    - In Column "C" there are formulas
    - In Column "D" there are empty cells

    As soon as any cell in column "B" (range B4:B22) is not empty,
    we want to paste as text column "C" in column "D".

    Can you please help?

    Here´s the sample file just in case:
    http://www.mediafire.com/file/xs00fq89609lh7y
    Sunday, December 4, 2016 6:29 PM

Answers

  • ok good !!

    Here you go https://1drv.ms/x/s!AlawUlaW5DROgSBFNY4U3LCT0vZ0


    Vish Mishra

    • Marked as answer by NicoPer Wednesday, December 7, 2016 1:21 PM
    Wednesday, December 7, 2016 4:27 AM

All replies

  • Hi,

    You can use this formula in Column D, Row no 4 and drag it till wherever you have the data in it. I think this would solve your problem.

    =IF(B4<>"",C4,"")

    Let me know if this is exactly you are expecting


    Vish Mishra

    Sunday, December 4, 2016 6:44 PM
  • Hi Vishwamitra


    The thing is that this must be done using VBA as the column "C" data is constantly changing and we need to "record" (freeze) the text value on that given trigger.


    Sunday, December 4, 2016 8:31 PM
  • Hi

    try this:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Application.EnableEvents = False
        If Not Intersect(Target, Range("B4:B22")) Is Nothing Then
            Target.Offset(0, 1).Copy
            Target.Offset(0, 2).PasteSpecial xlPasteValues
        End If
        Application.CutCopyMode = False
        Application.EnableEvents = True
    End Sub


    Cimjet

    • Marked as answer by NicoPer Sunday, December 4, 2016 10:27 PM
    • Unmarked as answer by NicoPer Sunday, December 4, 2016 11:22 PM
    Sunday, December 4, 2016 9:46 PM
  • Hi Cimjet and thank you very much.

    I´ve tried it in the sample and it works if I type something directly in the range.
    But data is appearing automatically in that range, and the code doesn´t seem to work with it.

    I´ve updated the sample for you to check:
    http://www.mediafire.com/file/xs00fq89609lh7y

    It looks like this:

    In B5 we got the formula:
    =F3

    So if you go to the noted F3 and enter something, then the B5 cell updates, but not the D5 cell.

    Is there any way around this?

    • Edited by NicoPer Sunday, December 4, 2016 11:34 PM
    Sunday, December 4, 2016 10:29 PM
  • May be you can still try the formula in column D in order to do the jab along with Cimjet VBA code:

    download it and see ... https://1drv.ms/x/s!AlawUlaW5DROgR-EC-lJ8Lz8cJBK


    Vish Mishra

    Monday, December 5, 2016 6:26 AM
  • The column "B" s RTD data changing by the second. I´m very sorry, I should have said it at the beginning.
    We only want to record the first time that the cell change from empty to something.

    All this happens automatically, we don´t entry anything in column "B".

    Vish, what you suggest will keep updating the value and we don´t want that. We want to grab only the first one.

    Monday, December 5, 2016 2:14 PM
  • Hi

    I don't know if I can help but for starter, I need more information.

    F3 will fill B5 so tell me what are the cells that will fill the rest of column B.


    Cimjet

    Edit : Also the information coming from F3, are they typed in.

    The change Event macro is executed when you press the Enter key.

    • Edited by Cimjet Monday, December 5, 2016 2:57 PM
    Monday, December 5, 2016 2:34 PM
  • Hi,

    Not an issue.. but this one of the very important aspect of your expected result and you did not mention it before. People of putting lot of effort to understand your question and trying to provide you the solution but such ambiguity in your question leads to loss of lot of effort of the answerer of this community. 

    Here is my understanding based on your questions so far:

    1. Column B be will be updated frequently 

    2. Column C : Is auto generated (Random) IDs (No change required there)

    3. Column D: Any empty column

    What is needed:

    As soon as a new value added in Column B in any row, value of column C of that row should be pasted in Column D in the same row - Provided that the OLD Value of the cell in Column B was BLANK. Once a cell in Column B has a data in it.. after that no matter how many times it gets updated.... Column C data should not be copied to Column D for that Row.

    Is my understanding correct?

    I will be able to help you, if you can specify like how Column is going to be updated? By formula or by VBA or how?


    Vish Mishra

    Monday, December 5, 2016 3:27 PM
  • I´ll try to explain it better (English is not my main language)


    Column "B" has RTD data, streaming real time quotes once an event is triggered (is blank before the event is triggered).
    These values change, and with them the column "C" values changes too.

    In the example I added a dummy cell in B5 controlled by the F3 cell content.
    I found that this simulates the RTD change process.

    To test if the code work, delete everything in columns "B" and "D" and type something in F3.
    What you type there should appear in B5 also, and that should trigger the VBA, pasting the text from column "C" in column "D".
    Now, that text pasted in column "D" should not change anymore. Even if the value in column "B" changes.


    I hope it´s clear enough. Very sorry for the confusion.

    Monday, December 5, 2016 7:35 PM
  • Thanks for explaining it.

    Though it was not clear how Column B will get updated. Like which event you mean.

    Anyway based on your given example how column B was getting updated based on a value in Cell F3, I have modified the code and added a helper column. This will do the trick as expected.

    You can find your workbook here as an attachment in one drive:

    https://1drv.ms/x/s!AlawUlaW5DROgR-EC-lJ8Lz8cJBK


    Vish Mishra

    Tuesday, December 6, 2016 6:01 AM
  • Hi Vish

    Thank you very much and sorry for the confusion. We´re getting there.

    The column "B" has RTD data, meaning that it has a formula. (Is blank when it starts, meaning that there are no values/text, but there is a formula there).

    I´ve checked your code and it does record the first value which is cool, but if the column "B" has a formula it shows 1 automatically in all the helper rows.

    Do not touch the column "B" now. I´ve added a tester column ("F") for you to check if it works.
     If you enter anything there it should trigger.





    Can you make it work like this? Here´s the updated sample:
    http://www.mediafire.com/file/93j6ahv4gchzt3y

    Tuesday, December 6, 2016 6:28 PM
  • ok good !!

    Here you go https://1drv.ms/x/s!AlawUlaW5DROgSBFNY4U3LCT0vZ0


    Vish Mishra

    • Marked as answer by NicoPer Wednesday, December 7, 2016 1:21 PM
    Wednesday, December 7, 2016 4:27 AM
  • Is working. Thanks so much Vish.

    I highly appreciate it.

    Wednesday, December 7, 2016 1:22 PM