Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add values from two columns into third one in another worksheet
this is the code I will use to iterate through the data in one of the
columns. I need help to come up with the code for the second column and then pasting in the third. Essentially what I am trying to do is: Column A Column B Column C (another worksheet) a e a + e b h b + h c l c + l blank p blank + p d m d + m and so on For i = 1 To lLastCol If Cells(5, i).Value = SearchAcc Then Cells(5, i).Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Copy Workbooks("workbook").Sheets("worksheet").Range("$ AV $7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next i Thank you in advance for any help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add values from two columns into third one in another worksheet
Maybe I just can't read good any more. I seem to see three different
descriptions of an objective. The illustration of data shows that values in column A is added to the values in column B and the result is posted to column C in a different sheet. The code shows that each column in the active workbook is copied to Range AV7 on a workbook named workbook. Since each iteration copies to the same range, only the last iteration will survive. You need to clearly state what you want to do with the data on the active sheet and then define how you want it to be arranged on the destination sheet. i.e. append each iteration vertically, or append each iteration horizontally, etc. Leave blank line between iteration, don't leave blank lines. If you can describe it, it can be built. "minkokiss" wrote: this is the code I will use to iterate through the data in one of the columns. I need help to come up with the code for the second column and then pasting in the third. Essentially what I am trying to do is: Column A Column B Column C (another worksheet) a e a + e b h b + h c l c + l blank p blank + p d m d + m and so on For i = 1 To lLastCol If Cells(5, i).Value = SearchAcc Then Cells(5, i).Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Copy Workbooks("workbook").Sheets("worksheet").Range("$ AV $7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next i Thank you in advance for any help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add values from two columns into third one in another worksheet
On Apr 11, 1:46 pm, JLGWhiz wrote:
Maybe I just can't read good any more. I seem to see three different descriptions of an objective. The illustration of data shows that values in column A is added to the values in column B and the result is posted to column C in a different sheet. The code shows that each column in the active workbook is copied to Range AV7 on a workbook named workbook. Since each iteration copies to the same range, only the last iteration will survive. You need to clearly state what you want to do with the data on the active sheet and then define how you want it to be arranged on the destination sheet. i.e. append each iteration vertically, or append each iteration horizontally, etc. Leave blank line between iteration, don't leave blank lines. If you can describe it, it can be built. "minkokiss" wrote: this is the code I will use to iterate through the data in one of the columns. I need help to come up with the code for the second column and then pasting in the third. Essentially what I am trying to do is: Column A Column B Column C (another worksheet) a e a + e b h b + h c l c + l blank p blank + p d m d + m and so on For i = 1 To lLastCol If Cells(5, i).Value = SearchAcc Then Cells(5, i).Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Copy Workbooks("workbook").Sheets("worksheet").Range("$ AV $7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next i Thank you in advance for any help I apologize for the confusion. The situation is the following: I get a monthly report from a cell phone company but the headers on this report are not the same each month(God knows why) so what I am trying to do is: 1) Find the first column by searching through the headers for a particular string 2) Find the second column by doing the same 3) Go through each of the two columns row by row and sum the rows of A and B and paste them into same row in C. For example sum row 1 of column a with row 1 of column B and paste in row 1 column C. Then do the same with all the rows with data in rows A and B and paste into C. Does that bring clarification to my issue or I got everybody more confused? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add values from two columns into third one in another worksheet
On Apr 11, 1:46 pm, JLGWhiz wrote:
Maybe I just can't read good any more. I seem to see three different descriptions of an objective. The illustration of data shows that values in column A is added to the values in column B and the result is posted to column C in a different sheet. The code shows that each column in the active workbook is copied to Range AV7 on a workbook named workbook. Since each iteration copies to the same range, only the last iteration will survive. You need to clearly state what you want to do with the data on the active sheet and then define how you want it to be arranged on the destination sheet. i.e. append each iteration vertically, or append each iteration horizontally, etc. Leave blank line between iteration, don't leave blank lines. If you can describe it, it can be built. "minkokiss" wrote: this is the code I will use to iterate through the data in one of the columns. I need help to come up with the code for the second column and then pasting in the third. Essentially what I am trying to do is: Column A Column B Column C (another worksheet) a e a + e b h b + h c l c + l blank p blank + p d m d + m and so on For i = 1 To lLastCol If Cells(5, i).Value = SearchAcc Then Cells(5, i).Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Copy Workbooks("workbook").Sheets("worksheet").Range("$ AV $7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next i Thank you in advance for any help I apologize for the confusion. The situation is the following: I get a monthly report from a cell phone company but the headers on this report are not the same each month(God knows why) so what I am trying to do is: 1) Find the first column by searching through the headers for a particular string 2) Find the second column by doing the same 3) Go through each of the two columns row by row and sum the rows of A and B and paste them into same row in C. For example sum row 1 of column a with row 1 of column B and paste in row 1 column C. Then do the same with all the rows with data in rows A and B and paste into C. Does that bring clarification to my issue or I got everybody more confused? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add values from two columns into third one in another worksheet
On Apr 11, 1:46 pm, JLGWhiz wrote:
Maybe I just can't read good any more. I seem to see three different descriptions of an objective. The illustration of data shows that values in column A is added to the values in column B and the result is posted to column C in a different sheet. The code shows that each column in the active workbook is copied to Range AV7 on a workbook named workbook. Since each iteration copies to the same range, only the last iteration will survive. You need to clearly state what you want to do with the data on the active sheet and then define how you want it to be arranged on the destination sheet. i.e. append each iteration vertically, or append each iteration horizontally, etc. Leave blank line between iteration, don't leave blank lines. If you can describe it, it can be built. "minkokiss" wrote: this is the code I will use to iterate through the data in one of the columns. I need help to come up with the code for the second column and then pasting in the third. Essentially what I am trying to do is: Column A Column B Column C (another worksheet) a e a + e b h b + h c l c + l blank p blank + p d m d + m and so on For i = 1 To lLastCol If Cells(5, i).Value = SearchAcc Then Cells(5, i).Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Copy Workbooks("workbook").Sheets("worksheet").Range("$ AV $7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next i Thank you in advance for any help I apologize for the confusion. The situation is the following: I get a monthly report from a cell phone company but the headers on this report are not the same each month(God knows why) so what I am trying to do is: 1) Find the first column by searching through the headers for a particular string 2) Find the second column by doing the same 3) Go through each of the two columns row by row and sum the rows of A and B and paste them into same row in C. For example sum row 1 of column a with row 1 of column B and paste in row 1 column C. Then do the same with all the rows with data in rows A and B and paste into C. Does that bring clarification to my issue or I got everybody more confused? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add values from two columns into third one in another workshee
You will need to replace Header1 and Header2 with the actual string that you
search for. You will also need to change worksheets(2) to the worksheet name or number that you want to copy the columns to. If you want them to be pasted to other than columns A and B in worksheets(2), then you will also have to change those designations. Other than that, put this in the standard module of the active workbook that contains the data to be searched and it will find your columns, copy them, add them and post the values in column C on worksheets (2). Have fun. Sub findCopyPasteAdd() lr = ActiveSheet.UsedRange.Rows.Count With ActiveSheet.UsedRange Set c = .Find("Header1", After:=ActiveSheet.Range("A" & lr)) If Not c Is Nothing Then x = c.Address Range(x).EntireColumn.Copy Worksheets(2).Range("A1") Application.CutCopyMode = False End If Set i = .Find("Header2", After:=ActiveSheet.Range("A" & lr)) If Not i Is Nothing Then y = i.Address Range(y).EntireColumn.Copy Worksheets(2).Range("B1") End If Application.CutCopyMode = False End With With Worksheets(2).Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row) .Cells(1, 3) = "Total" i = 2 Do Until .Cells(i, 1) = "" .Cells(i, 3).Value = .Cells(i, 1).Value + .Cells(i, 2).Value i = i + 1 Loop End With End Sub "minkokiss" wrote: On Apr 11, 1:46 pm, JLGWhiz wrote: Maybe I just can't read good any more. I seem to see three different descriptions of an objective. The illustration of data shows that values in column A is added to the values in column B and the result is posted to column C in a different sheet. The code shows that each column in the active workbook is copied to Range AV7 on a workbook named workbook. Since each iteration copies to the same range, only the last iteration will survive. You need to clearly state what you want to do with the data on the active sheet and then define how you want it to be arranged on the destination sheet. i.e. append each iteration vertically, or append each iteration horizontally, etc. Leave blank line between iteration, don't leave blank lines. If you can describe it, it can be built. "minkokiss" wrote: this is the code I will use to iterate through the data in one of the columns. I need help to come up with the code for the second column and then pasting in the third. Essentially what I am trying to do is: Column A Column B Column C (another worksheet) a e a + e b h b + h c l c + l blank p blank + p d m d + m and so on For i = 1 To lLastCol If Cells(5, i).Value = SearchAcc Then Cells(5, i).Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Copy Workbooks("workbook").Sheets("worksheet").Range("$ AV $7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next i Thank you in advance for any help I apologize for the confusion. The situation is the following: I get a monthly report from a cell phone company but the headers on this report are not the same each month(God knows why) so what I am trying to do is: 1) Find the first column by searching through the headers for a particular string 2) Find the second column by doing the same 3) Go through each of the two columns row by row and sum the rows of A and B and paste them into same row in C. For example sum row 1 of column a with row 1 of column B and paste in row 1 column C. Then do the same with all the rows with data in rows A and B and paste into C. Does that bring clarification to my issue or I got everybody more confused? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add values from two columns into third one in another workshee
Change the line:
Do Until Cells(i, 1) = "" To: Do Until Cells(i, 1) = lr "minkokiss" wrote: On Apr 11, 1:46 pm, JLGWhiz wrote: Maybe I just can't read good any more. I seem to see three different descriptions of an objective. The illustration of data shows that values in column A is added to the values in column B and the result is posted to column C in a different sheet. The code shows that each column in the active workbook is copied to Range AV7 on a workbook named workbook. Since each iteration copies to the same range, only the last iteration will survive. You need to clearly state what you want to do with the data on the active sheet and then define how you want it to be arranged on the destination sheet. i.e. append each iteration vertically, or append each iteration horizontally, etc. Leave blank line between iteration, don't leave blank lines. If you can describe it, it can be built. "minkokiss" wrote: this is the code I will use to iterate through the data in one of the columns. I need help to come up with the code for the second column and then pasting in the third. Essentially what I am trying to do is: Column A Column B Column C (another worksheet) a e a + e b h b + h c l c + l blank p blank + p d m d + m and so on For i = 1 To lLastCol If Cells(5, i).Value = SearchAcc Then Cells(5, i).Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Copy Workbooks("workbook").Sheets("worksheet").Range("$ AV $7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next i Thank you in advance for any help I apologize for the confusion. The situation is the following: I get a monthly report from a cell phone company but the headers on this report are not the same each month(God knows why) so what I am trying to do is: 1) Find the first column by searching through the headers for a particular string 2) Find the second column by doing the same 3) Go through each of the two columns row by row and sum the rows of A and B and paste them into same row in C. For example sum row 1 of column a with row 1 of column B and paste in row 1 column C. Then do the same with all the rows with data in rows A and B and paste into C. Does that bring clarification to my issue or I got everybody more confused? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add values from two columns into third one in another workshee
That should be Do Until i = lr
"minkokiss" wrote: On Apr 11, 1:46 pm, JLGWhiz wrote: Maybe I just can't read good any more. I seem to see three different descriptions of an objective. The illustration of data shows that values in column A is added to the values in column B and the result is posted to column C in a different sheet. The code shows that each column in the active workbook is copied to Range AV7 on a workbook named workbook. Since each iteration copies to the same range, only the last iteration will survive. You need to clearly state what you want to do with the data on the active sheet and then define how you want it to be arranged on the destination sheet. i.e. append each iteration vertically, or append each iteration horizontally, etc. Leave blank line between iteration, don't leave blank lines. If you can describe it, it can be built. "minkokiss" wrote: this is the code I will use to iterate through the data in one of the columns. I need help to come up with the code for the second column and then pasting in the third. Essentially what I am trying to do is: Column A Column B Column C (another worksheet) a e a + e b h b + h c l c + l blank p blank + p d m d + m and so on For i = 1 To lLastCol If Cells(5, i).Value = SearchAcc Then Cells(5, i).Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Copy Workbooks("workbook").Sheets("worksheet").Range("$ AV $7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next i Thank you in advance for any help I apologize for the confusion. The situation is the following: I get a monthly report from a cell phone company but the headers on this report are not the same each month(God knows why) so what I am trying to do is: 1) Find the first column by searching through the headers for a particular string 2) Find the second column by doing the same 3) Go through each of the two columns row by row and sum the rows of A and B and paste them into same row in C. For example sum row 1 of column a with row 1 of column B and paste in row 1 column C. Then do the same with all the rows with data in rows A and B and paste into C. Does that bring clarification to my issue or I got everybody more confused? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add values from two columns into third one in another workshee
On Apr 11, 10:08 pm, JLGWhiz
wrote: That should be Do Until i = lr "minkokiss" wrote: On Apr 11, 1:46 pm, JLGWhiz wrote: Maybe I just can't read good any more. I seem to see three different descriptions of an objective. The illustration of data shows that values in column A is added to the values in column B and the result is posted to column C in a different sheet. The code shows that each column in the active workbook is copied to Range AV7 on a workbook named workbook. Since each iteration copies to the same range, only the last iteration will survive. You need to clearly state what you want to do with the data on the active sheet and then define how you want it to be arranged on the destination sheet. i.e. append each iteration vertically, or append each iteration horizontally, etc. Leave blank line between iteration, don't leave blank lines. If you can describe it, it can be built. "minkokiss" wrote: this is the code I will use to iterate through the data in one of the columns. I need help to come up with the code for the second column and then pasting in the third. Essentially what I am trying to do is: Column A Column B Column C (another worksheet) a e a + e b h b + h c l c + l blank p blank + p d m d + m and so on For i = 1 To lLastCol If Cells(5, i).Value = SearchAcc Then Cells(5, i).Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Copy Workbooks("workbook").Sheets("worksheet").Range("$ AV $7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next i Thank you in advance for any help I apologize for the confusion. The situation is the following: I get a monthly report from a cell phone company but the headers on this report are not the same each month(God knows why) so what I am trying to do is: 1) Find the first column by searching through the headers for a particular string 2) Find the second column by doing the same 3) Go through each of the two columns row by row and sum the rows of A and B and paste them into same row in C. For example sum row 1 of column a with row 1 of column B and paste in row 1 column C. Then do the same with all the rows with data in rows A and B and paste into C. Does that bring clarification to my issue or I got everybody more confused? Fist of all thank you so much for the help. I need to ask you some questions and most this is because I was not clear in what I want accomplished. Your suggested solution copies columns A and B to the new worksheet and then adds the values together and pastes them into the third. I want to only use the values from A and B without copying them or moving them, add the values up and then paste in column C (all this in the program) row by row. Also in the code you select the entire column and my values are below the header row (which is a locked row) and using the entire column method will select everything including the header (I have used it before and had bad experience). Could you tell me what needs modified under these circumstances? Thank you for taking the time to educate newbies. Minko |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add values from two columns into third one in another workshee
On Apr 11, 10:08 pm, JLGWhiz
wrote: That should be Do Until i = lr "minkokiss" wrote: On Apr 11, 1:46 pm, JLGWhiz wrote: Maybe I just can't read good any more. I seem to see three different descriptions of an objective. The illustration of data shows that values in column A is added to the values in column B and the result is posted to column C in a different sheet. The code shows that each column in the active workbook is copied to Range AV7 on a workbook named workbook. Since each iteration copies to the same range, only the last iteration will survive. You need to clearly state what you want to do with the data on the active sheet and then define how you want it to be arranged on the destination sheet. i.e. append each iteration vertically, or append each iteration horizontally, etc. Leave blank line between iteration, don't leave blank lines. If you can describe it, it can be built. "minkokiss" wrote: this is the code I will use to iterate through the data in one of the columns. I need help to come up with the code for the second column and then pasting in the third. Essentially what I am trying to do is: Column A Column B Column C (another worksheet) a e a + e b h b + h c l c + l blank p blank + p d m d + m and so on For i = 1 To lLastCol If Cells(5, i).Value = SearchAcc Then Cells(5, i).Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Copy Workbooks("workbook").Sheets("worksheet").Range("$ AV $7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next i Thank you in advance for any help I apologize for the confusion. The situation is the following: I get a monthly report from a cell phone company but the headers on this report are not the same each month(God knows why) so what I am trying to do is: 1) Find the first column by searching through the headers for a particular string 2) Find the second column by doing the same 3) Go through each of the two columns row by row and sum the rows of A and B and paste them into same row in C. For example sum row 1 of column a with row 1 of column B and paste in row 1 column C. Then do the same with all the rows with data in rows A and B and paste into C. Does that bring clarification to my issue or I got everybody more confused? Thanks a lot for your help JLGWhiz. Your code helped me a little bit but it is still not what I am looking for. This is just because I don't know how to explain stuff :) So ok few issues for me: 1. I don't want the columns to be copied and pasted to the second sheet in their entirety. I just want the values from them (under the headings) since the sheet I am pasting data already has different headings and formatting in place. 2. I do not want columns A and B copied at all I just want to use the data from them and copy the result of each A+B row to the C row on the other sheet. For example I have the values 1 and 2 in A and B in sheet one and I want to write a program evaluating the total (3) and pasting ONLY the total in a row in C (on the other sheet). I would like to do this for every row in A and B and fill C with the results. Do you understand what I am looking for? If yes please let me know if there is a way I can modify the code you sent me to accomplish what I am looking for. Thank you for helping the newbie out. Your help is greatly appreciated |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add values from two columns into third one in another workshee
Working with the information you provided, I assumed a second worksheet would
be needed since you said you had to search for the column hearders with a specific string. I have no idea what your source document looks like, so I made the macro so it would find the data, capture the data and put the data in a place that could then be specifically referenced. This allowed the columns for each row to be summed. Thus the second sheet. I still don't know those things about your source document, but here is code that only copies from the second row down and pastes without headers. You can play with it from there. Sub findCopyPasteAdd() lr = ActiveSheet.UsedRange.Rows.Count With ActiveSheet.UsedRange Set c = .Find("Header1", After:=ActiveSheet.Range("A" & lr)) If Not c Is Nothing Then x = c.Address Range(Cells(Range(x).Offset(1, 0).Row, Range(x).Column), Cells(lr, Range(x).Column)).Copy Worksheets(2).Range("A2") Application.CutCopyMode = False End If Set i = .Find("Header2", After:=ActiveSheet.Range("A" & lr)) If Not i Is Nothing Then y = i.Address Range(Cells(Range(y).Offset(1, 0).Row, Range(y).Column), Cells(lr, Range(y).Column)).Copy Worksheets(2).Range("B2") End If Application.CutCopyMode = False End With With Worksheets(2).Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row) lr2 = Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Row i = 2 Do Until i = lr2 + 1 .Cells(i, 3).Value = .Cells(i, 1).Value + .Cells(i, 2).Value i = i + 1 Loop End With End Sub "minkokiss" wrote: On Apr 11, 10:08 pm, JLGWhiz wrote: That should be Do Until i = lr "minkokiss" wrote: On Apr 11, 1:46 pm, JLGWhiz wrote: Maybe I just can't read good any more. I seem to see three different descriptions of an objective. The illustration of data shows that values in column A is added to the values in column B and the result is posted to column C in a different sheet. The code shows that each column in the active workbook is copied to Range AV7 on a workbook named workbook. Since each iteration copies to the same range, only the last iteration will survive. You need to clearly state what you want to do with the data on the active sheet and then define how you want it to be arranged on the destination sheet. i.e. append each iteration vertically, or append each iteration horizontally, etc. Leave blank line between iteration, don't leave blank lines. If you can describe it, it can be built. "minkokiss" wrote: this is the code I will use to iterate through the data in one of the columns. I need help to come up with the code for the second column and then pasting in the third. Essentially what I am trying to do is: Column A Column B Column C (another worksheet) a e a + e b h b + h c l c + l blank p blank + p d m d + m and so on For i = 1 To lLastCol If Cells(5, i).Value = SearchAcc Then Cells(5, i).Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Copy Workbooks("workbook").Sheets("worksheet").Range("$ AV $7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next i Thank you in advance for any help I apologize for the confusion. The situation is the following: I get a monthly report from a cell phone company but the headers on this report are not the same each month(God knows why) so what I am trying to do is: 1) Find the first column by searching through the headers for a particular string 2) Find the second column by doing the same 3) Go through each of the two columns row by row and sum the rows of A and B and paste them into same row in C. For example sum row 1 of column a with row 1 of column B and paste in row 1 column C. Then do the same with all the rows with data in rows A and B and paste into C. Does that bring clarification to my issue or I got everybody more confused? Fist of all thank you so much for the help. I need to ask you some questions and most this is because I was not clear in what I want accomplished. Your suggested solution copies columns A and B to the new worksheet and then adds the values together and pastes them into the third. I want to only use the values from A and B without copying them or moving them, add the values up and then paste in column C (all this in the program) row by row. Also in the code you select the entire column and my values are below the header row (which is a locked row) and using the entire column method will select everything including the header (I have used it before and had bad experience). Could you tell me what needs modified under these circumstances? Thank you for taking the time to educate newbies. Minko |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add values from two columns into third one in another workshee
Here is what I suggest:
1. Make a new posting to this discussion group. 2. Post the code that I have given you. 3. Identify the sheet you have your source data on by either the sheet name or number, i.e. sheet1, sheet2, etc. as displayed on the tabs at the bottom of your screen. 4. Identify the sheet you want data to be pasted to by sheet name or number, as described above. 5. When you refer to columns A, B and C, specify which sheet you are referring to in terms of copying and again in pasting. 6. As best you can, if you have a sheet layout for your source data, describe it with which rows or columns contain data and what kind. If any rows or columns in the used range are consistently blank at the beginning of a session, then identify them and what they are used for during the session. 7. Remember that folks on this end cannot see your monitor screen and have no idea what type of data you are working with. You have to be the eyes of the code writer. 8. Again, you should start a new thread as described above. Some of us will see it and answer it. "minkokiss" wrote: On Apr 11, 10:08 pm, JLGWhiz wrote: That should be Do Until i = lr "minkokiss" wrote: On Apr 11, 1:46 pm, JLGWhiz wrote: Maybe I just can't read good any more. I seem to see three different descriptions of an objective. The illustration of data shows that values in column A is added to the values in column B and the result is posted to column C in a different sheet. The code shows that each column in the active workbook is copied to Range AV7 on a workbook named workbook. Since each iteration copies to the same range, only the last iteration will survive. You need to clearly state what you want to do with the data on the active sheet and then define how you want it to be arranged on the destination sheet. i.e. append each iteration vertically, or append each iteration horizontally, etc. Leave blank line between iteration, don't leave blank lines. If you can describe it, it can be built. "minkokiss" wrote: this is the code I will use to iterate through the data in one of the columns. I need help to come up with the code for the second column and then pasting in the third. Essentially what I am trying to do is: Column A Column B Column C (another worksheet) a e a + e b h b + h c l c + l blank p blank + p d m d + m and so on For i = 1 To lLastCol If Cells(5, i).Value = SearchAcc Then Cells(5, i).Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Copy Workbooks("workbook").Sheets("worksheet").Range("$ AV $7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next i Thank you in advance for any help I apologize for the confusion. The situation is the following: I get a monthly report from a cell phone company but the headers on this report are not the same each month(God knows why) so what I am trying to do is: 1) Find the first column by searching through the headers for a particular string 2) Find the second column by doing the same 3) Go through each of the two columns row by row and sum the rows of A and B and paste them into same row in C. For example sum row 1 of column a with row 1 of column B and paste in row 1 column C. Then do the same with all the rows with data in rows A and B and paste into C. Does that bring clarification to my issue or I got everybody more confused? Thanks a lot for your help JLGWhiz. Your code helped me a little bit but it is still not what I am looking for. This is just because I don't know how to explain stuff :) So ok few issues for me: 1. I don't want the columns to be copied and pasted to the second sheet in their entirety. I just want the values from them (under the headings) since the sheet I am pasting data already has different headings and formatting in place. 2. I do not want columns A and B copied at all I just want to use the data from them and copy the result of each A+B row to the C row on the other sheet. For example I have the values 1 and 2 in A and B in sheet one and I want to write a program evaluating the total (3) and pasting ONLY the total in a row in C (on the other sheet). I would like to do this for every row in A and B and fill C with the results. Do you understand what I am looking for? If yes please let me know if there is a way I can modify the code you sent me to accomplish what I am looking for. Thank you for helping the newbie out. Your help is greatly appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating columns in worksheet based on values from another worksh. | Excel Worksheet Functions | |||
Match Values in Rows with Partial Values in Columns | Excel Worksheet Functions | |||
Copy Values from WorkSheet back to Another Workbook Replacing Values in Worksheet | Excel Programming | |||
Comparing values in two columns and displaying missing values in n | Excel Programming | |||
Excel Compare values in columns & display missing values in a new | Excel Discussion (Misc queries) |