Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts

    copy value to corresponding cell/column

    See attached sample file.
    Column "A" & "B" will be added/changed manually.
    Row 1 does not change
    I need to copy value of "B" to corresponding column [D to I]which matches the value in same row as "A".
    Translated:
    A2="Q", B2=$10, I need to fill D["Q"]2 with $10
    A3="R", B3=$20, I need to fill E["R"]3 with $20
    etc
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    skipro,

    in cell D2 place the formula =IF($A2=D$1,$B2,"")

    Copy across to I2 then copy all those cells down

    Skipro1.png

    HTH,
    Maud
    Attached Files Attached Files

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Seems like you are processing receipts and amounts to their respective columns.
    I would recommend using a dropdown for your entries in column [A], to make sure they are not 'mistyped'. For example, if you had an entry in column [A] as "Hotell", it will not be posted to any the [Hotel] column.
    You can create a named list of valid entries and use this for the dropdown.

    zeddy

  4. #4
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Maud,
    Thanks. Easy enough [when you know what you are doing].
    Since I cannot predict the number of row entries, and other formulas will probably be entered below this section, such as subtotals and other analysis modes [see attached file], can this be done without specifying the actual cells by embedding a formula in each as you suggested. I am thinking of a script that would work in the specific columns with undefined number of rows so the "added" formulas could just be moved down increasing the number of entries that could be entered in the future?
    I will not be readily available to modify [add formulas to cells] as needed.
    Attached Files Attached Files

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Skipro,

    Consider the following code. As long as you start on row 2 and you maintain a blank row between the last pair (in Col A and B) and the Subtotals row, you can add as many rows of data as you like. No formulas needed.

    HTH,
    Maud

    Code:
    Public Sub MatchColums()
    Dim AvailableRow As Long
    Dim col As Integer
    AvailableRow = Range("A2").End(xlDown).Row
    For I = 2 To AvailableRow
        col = WorksheetFunction.Match(Cells(I, 1), Range("D1:I1"), 0) + 3
        Cells(I, col) = Cells(I, 2)
    Next I
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2015-01-21 at 19:43.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    ..I would use an 'onEntry' trigger that, when an entry is made in column [B], it would match the corresponding column for the adjacent cell (in col [A] ) and directly 'post' the entered value to the correct column. No formulas would then be required (making the spreadsheet smaller and faster)

    zeddy

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    ..and you could easily add a warning message if the entry in the col [A] cell did not match a corresponding 'posting-to' column (or even have a 'new' posting column created automatically)

    zeddy

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Zeddy,

    I agree that a Worksheet_Change event is the way to go instead of a button. With the code, however, no formulas are required as I stated in post #5. Your logic of validating the match would certainly be the next step to evolve the code. Two good points!

    Maud

  9. #9
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Maud,
    Again thank you.

    Using your last revised sample, if I change a value in "A", it adds "B" in "D-I" appropriately, great. But it leaves the old value in "D-I", not so great. I do not know if this was intended due to the idea that the entire column "A" was fixed. It is not and can vary [QQ,R,TTT,UU,VVV] while still using only those already stated options [Q,R,S,T,U,V]. Can "D-I" reflect the current "A,B", that is only 1 entry per row?
    Also, Q-V may change, easy fix, but the user may need to add choices, such as Q-Z. I can change macro as needed, but again I may not be available to do so. Can this be "written" in?

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi skipro

    see attached file.
    I modified Maud's code to clear the data first, before running the code via the button [Run code]
    see sheet named [maud] in attached file.

    On sheet named [zeddy] in attached file, I use the dropdown method (discussed in earlier post) together with event trapping to place values in the relevant column.

    zeddy
    Attached Files Attached Files

  11. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2015-01-25)

  12. #11
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Zeddy,
    Column A works fine, but column B does not update D-I unless I update/refresh col A. Can this extra step be eliminated so if I change value in col B it updates D-I spontaneously? It appears the macro needs to be run again for col B to update, but not col A.
    Last edited by skipro; 2015-01-23 at 17:53.

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Skipro,

    I Think I have resolved the issues while taking Zeddy's 2 points into consideration.

    The following code removes the button to run the code but instead runs when a change is made in columns A or B. It also performs validation on the changed value that you made.

    One question: Suppose you entered a q instead of a Q, would you want the code to change it to uppercase and then match or would you rather have it case sensitive?

    HTH,
    Maud

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    '-------------------------------
    'DECLARE AND SET VARIABLES
    Dim AvailableRow As Long, LastCol As Long
    Dim col As Integer
    LastCol = ActiveSheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column
    If Intersect(Target, Range("A2:B13")) Is Nothing Then Exit Sub
    '-------------------------------
    'VALIDATE CHANGED VALUE IN COL A
    If Not Intersect(Target, Range("A2:A13")) Is Nothing Then
        For I = 4 To LastCol
            If Target = Cells(1, I) Then GoTo Continue1
        Next I
        MsgBox "You have entered " & Chr(34) & Target & Chr(34) & " which is not a value that matches a column header"
        Application.EnableEvents = False
        Target = ""
        Application.EnableEvents = True
        Target.Select
        Exit Sub
    End If
    Continue1:
    '-------------------------------
    'VALIDATE CHANGED VALUE IN COL B
    If Not Intersect(Target, Range("B2:B13")) Is Nothing Then
        If WorksheetFunction.IsNumber(Target) Then GoTo Continue2
        MsgBox "You have entered " & Chr(34) & Target & Chr(34) & " which a non numeric value in column B"
        Application.EnableEvents = False
        Target = ""
        Application.EnableEvents = True
        Target.Select
        Exit Sub
    End If
    Continue2:
    '-------------------------------
    'PLACE VALUE IN RIGHT COLUMN
        Range("D2:I13").ClearContents
        AvailableRow = Range("A2").End(xlDown).Row
        For I = 2 To AvailableRow
            col = WorksheetFunction.Match(Cells(I, 1), Range("D1:I1"), 0) + 3
            Cells(I, col) = Cells(I, 2)
        Next I
    End Sub
    Attached Files Attached Files

  14. #13
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Maud,
    Thanks.
    I would like it case insensitive.
    Are you aware that if an invaild entry is placed in A your message appears and if oked, or if an entry in A is deleted, the cell goes blank. Then if a cell in B is also changed, all entries in D-I below the blank A also go blank. They reappear when a valid entry is placed in the blank cell.

  15. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Skipro,

    Fixed those issues. You will notice that after a blank value or invalid value is corrected, the active cell becomes A1. This is necessary and expected.

    Maud
    Attached Files Attached Files

  16. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    ..in an ideal world, I would have the range [D2:V13] protected (with or without a password).
    Then, in your code, you could add the line to 'unprotect' this range prior to making changes, e.g. add
    [D2:I13].unprotect
    ..prior to your
    'PLACE VALUE IN RIGHT COLUMN
    Range("D2:I13").ClearContents

    ..and then re-protect after changes are made.

    The reason for doing this is that at present, a User can 'manually' re-type a numeric value
    in the block [D2:I13] e.g. 'post' a value to a different column than that specified in corresponding column [A] (and even a different amount to that specified in column [B] ). You can even have 2 (or more) values entered in the same row under different columns.
    Of course, one answer to the User is 'don't do that'.
    But see attached example to show what could happen.

    zeddy
    Attached Files Attached Files

  17. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2015-01-25)

Page 1 of 2 12 LastLast

Posting Permissions

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