copy value to corresponding cell/column

Topic

New Reply

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

Reply | Quote

Replies

skipro,

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

Copy across to I2 then copy all those cells down

39022-Skipro1

HTH,
Maud

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Hi Maud

..I would use an ‘onEntry’ trigger that, when an entry is made in column , 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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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)
[COLOR=”#008000″]’——————————-
‘DECLARE AND SET VARIABLES[/COLOR]
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
[COLOR=”#008000”]’——————————-
‘VALIDATE CHANGED VALUE IN COL A[/COLOR]
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:
[COLOR=”#008000″]’——————————-
‘VALIDATE CHANGED VALUE IN COL B[/COLOR]
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:
[COLOR=”#008000″]’——————————-
‘PLACE VALUE IN RIGHT COLUMN[/COLOR]
    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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 ). 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

Reply | Quote

Good point Zeddy!

Adding the line (in blue) should resolve that issue of allowing changes to be made directly in the grid.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   [COLOR=”#0000FF”] If Not Intersect(Target, Range(“D2:I13”)) Is Nothing Then Range(“A1”).Select
[/COLOR]    Data = Target
End Sub

Reply | Quote