Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If cell 'a' matches cell 'b' then copy cell 'd' to cell 'e'
Sounds like a simple if/then statement right? Can't get it to work for
anything. I am going from worksheet to worksheet but that should not present a problem.... I've even tried it without changing worksheets. Open to any and all suggestions.....I basically want to automate a copy/paste funtion for cells that match from worksheet to worksheet......... Marie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If cell 'a' matches cell 'b' then copy cell 'd' to cell 'e'
This worked for me... I just tested it on row 1, but it can be
adjusted for your needs Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1").Value = Range("B1").Value Then Range("D1").Copy Range("E1") End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If cell 'a' matches cell 'b' then copy cell 'd' to cell 'e'
Ok...... basically, I assume you answered my question..... problem is, I
don't know what to do with the answer. Would you please give 'idiot' proof directions on how to proceed? Do I copy/paste everything from 'private sub..........to end sub' into a cell and adjust it for my ranges? Then copy it to each cell I need it to put the answer in? Do I put it somewhere in the worksheet to execute under a macro key? I'm sorta lost here. I know just enough to be dangerous. Please help out. "GTVT06" wrote: This worked for me... I just tested it on row 1, but it can be adjusted for your needs Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1").Value = Range("B1").Value Then Range("D1").Copy Range("E1") End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If cell 'a' matches cell 'b' then copy cell 'd' to cell 'e'
Since you are working between sheets, you will have to qualify your range
object statements by specifying the sheet for each object. Sub cpynpst() Set wks1 = Worksheets("Sheet1") Set wks2 = Worksheets("Sheet2") With wks1 If .Range("A1") = .Range("B2") Then .Range("D1").Copy .Range("E1") ' <<<copies in same WS End If End With If wks1.Range("A1") = wks1.Range("B2") Then wks1.Range("D1").Copy wks2.Range("E1") '<< cpy to 2nd WS End If End Sub When working between sheets, it is a good practice to qualify all of your range objects (cell references and ranges) with their applicable worksheets. That way VBA won't have to guess which one you really want to address. "Marie" wrote: Ok...... basically, I assume you answered my question..... problem is, I don't know what to do with the answer. Would you please give 'idiot' proof directions on how to proceed? Do I copy/paste everything from 'private sub..........to end sub' into a cell and adjust it for my ranges? Then copy it to each cell I need it to put the answer in? Do I put it somewhere in the worksheet to execute under a macro key? I'm sorta lost here. I know just enough to be dangerous. Please help out. "GTVT06" wrote: This worked for me... I just tested it on row 1, but it can be adjusted for your needs Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1").Value = Range("B1").Value Then Range("D1").Copy Range("E1") End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If cell 'a' matches cell 'b' then copy cell 'd' to cell 'e'
Sorry, I should have given you the answer to your question. The code that I
provided would be pasted into the standard module. Press Alt + F11 to open the VB editor. If the code window is dark, then from the VBA menu bar InsertModule. Then copy and paste the code...But, the code I provided is only for illustration. The code that GTVT06 provided was for a Worksheet change event and would go in the code module behind the active sheet. But I don't think you were looking for either of those answers. I think the answere that you want is: =IF(Sheet1!a1 = Sheet1!b1, d1, "") and this formula would be put into cell E1 of sheet 2. It says if a1 = b1 on sheet 1 then cell e1 = cell d1 of sheet 2. If this doesn't explain what you want to know, then try explaining the problem again. "Marie" wrote: Ok...... basically, I assume you answered my question..... problem is, I don't know what to do with the answer. Would you please give 'idiot' proof directions on how to proceed? Do I copy/paste everything from 'private sub..........to end sub' into a cell and adjust it for my ranges? Then copy it to each cell I need it to put the answer in? Do I put it somewhere in the worksheet to execute under a macro key? I'm sorta lost here. I know just enough to be dangerous. Please help out. "GTVT06" wrote: This worked for me... I just tested it on row 1, but it can be adjusted for your needs Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1").Value = Range("B1").Value Then Range("D1").Copy Range("E1") End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If cell 'a' matches cell 'b' then copy cell 'd' to cell 'e'
If this works I can't thank you enough. I have 7 spreadsheets that have a
column in common but the information relating to that column is different on each sheet. I need to combine them all to one sheet and of course the colums are not exactly alike......<grin "JLGWhiz" wrote: Sorry, I should have given you the answer to your question. The code that I provided would be pasted into the standard module. Press Alt + F11 to open the VB editor. If the code window is dark, then from the VBA menu bar InsertModule. Then copy and paste the code...But, the code I provided is only for illustration. The code that GTVT06 provided was for a Worksheet change event and would go in the code module behind the active sheet. But I don't think you were looking for either of those answers. I think the answere that you want is: =IF(Sheet1!a1 = Sheet1!b1, d1, "") and this formula would be put into cell E1 of sheet 2. It says if a1 = b1 on sheet 1 then cell e1 = cell d1 of sheet 2. If this doesn't explain what you want to know, then try explaining the problem again. "Marie" wrote: Ok...... basically, I assume you answered my question..... problem is, I don't know what to do with the answer. Would you please give 'idiot' proof directions on how to proceed? Do I copy/paste everything from 'private sub..........to end sub' into a cell and adjust it for my ranges? Then copy it to each cell I need it to put the answer in? Do I put it somewhere in the worksheet to execute under a macro key? I'm sorta lost here. I know just enough to be dangerous. Please help out. "GTVT06" wrote: This worked for me... I just tested it on row 1, but it can be adjusted for your needs Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1").Value = Range("B1").Value Then Range("D1").Copy Range("E1") End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
If cell 'a' matches cell 'b' then copy cell 'd' to cell 'e'
What I am looking to do, and I don't think I made myself quite clear enough,
is on a separate worksheet from all the others.......... Have if cell sheet 1 a1 = cell sheet 9(this is the working sheet) b2, then copy cell sheet 1 c1 to cell sheet 9 d2. Now I've probably thoroughly confused you........ I want to automate matching the cell from one sheet to another sheet and if they match then take the information from a corresponding cell and place it in the matching sheet in another cell. Hope this clears it up a little. Marie "JLGWhiz" wrote: Sorry, I should have given you the answer to your question. The code that I provided would be pasted into the standard module. Press Alt + F11 to open the VB editor. If the code window is dark, then from the VBA menu bar InsertModule. Then copy and paste the code...But, the code I provided is only for illustration. The code that GTVT06 provided was for a Worksheet change event and would go in the code module behind the active sheet. But I don't think you were looking for either of those answers. I think the answere that you want is: =IF(Sheet1!a1 = Sheet1!b1, d1, "") and this formula would be put into cell E1 of sheet 2. It says if a1 = b1 on sheet 1 then cell e1 = cell d1 of sheet 2. If this doesn't explain what you want to know, then try explaining the problem again. "Marie" wrote: Ok...... basically, I assume you answered my question..... problem is, I don't know what to do with the answer. Would you please give 'idiot' proof directions on how to proceed? Do I copy/paste everything from 'private sub..........to end sub' into a cell and adjust it for my ranges? Then copy it to each cell I need it to put the answer in? Do I put it somewhere in the worksheet to execute under a macro key? I'm sorta lost here. I know just enough to be dangerous. Please help out. "GTVT06" wrote: This worked for me... I just tested it on row 1, but it can be adjusted for your needs Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1").Value = Range("B1").Value Then Range("D1").Copy Range("E1") End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning Cell Value if Matches Another cell | Excel Worksheet Functions | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing | Excel Discussion (Misc queries) | |||
How can I copy a value from a cell and paste it into another cell while adding it to the previous value in that cell | Excel Worksheet Functions | |||
if cell value matches then copy another cell | Excel Programming |