Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Headers down the rows...
Hi
I have for example 10 columns From Say column D to K there is thousands of rows below corresponding to a particular column, Each row will have a figure in one of the columns. I am trying to have this in a format to enter into our system. I need the column headers located in Row 1 to be inserted into a new column for the corresponding row where the amount has been placed into. For example if I put in Column H a 100.00 I want the column H header to be inserted into a new column A (for example) and if it was Column J then the header from Column J to be inserted into a new column A. Hope this makes sense, I appreciate all your help, Andrea |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Headers down the rows...
Hi Andrea
Is this related to the previous problem, or a new one? I am not clear on what you are asking here. Could you give another explanation with some examples of what is appearing where, and what you want to output and where. -- Regards Roger Govier wrote in message oups.com... Hi I have for example 10 columns From Say column D to K there is thousands of rows below corresponding to a particular column, Each row will have a figure in one of the columns. I am trying to have this in a format to enter into our system. I need the column headers located in Row 1 to be inserted into a new column for the corresponding row where the amount has been placed into. For example if I put in Column H a 100.00 I want the column H header to be inserted into a new column A (for example) and if it was Column J then the header from Column J to be inserted into a new column A. Hope this makes sense, I appreciate all your help, Andrea |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Headers down the rows...
On 8 May, 11:33, "Roger Govier" wrote:
Hi Andrea Is this related to the previous problem, or a new one? I am not clear on what you are asking here. Could you give another explanation with some examples of what is appearing where, and what you want to output and where. -- Regards Roger Govier wrote in message oups.com... Hi I have for example 10 columns From Say column D to K there is thousands of rows below corresponding to a particular column, Each row will have a figure in one of the columns. I am trying to have this in a format to enter into our system. I need the column headers located in Row 1 to be inserted into a new column for the corresponding row where the amount has been placed into. For example if I put in Column H a 100.00 I want the column H header to be inserted into a new column A (for example) and if it was Column J then the header from Column J to be inserted into a new column A. Hope this makes sense, I appreciate all your help, Andrea- Hide quoted text - - Show quoted text - Hi Roger It's all related to the same spreadsheet, I thought by breaking it down into steps would be easier to make sense of. You helped put the correct labelling to input into both systems. It's difficult to explain but I have rows of data - say 10 accounts along columns. If I payment from account 10 then I was the header for account 10 placed in a cell next to the row. If the payment was made from account 10 then all the other cells would be blank. I want all the data in one column but I want to identify each account which I think can be done by having the column headers put in a cell next to the row. Does any of this make sense? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Headers down the rows...
wrote in message
ups.com... On 8 May, 11:33, "Roger Govier" wrote: Hi Andrea Is this related to the previous problem, or a new one? I am not clear on what you are asking here. Could you give another explanation with some examples of what is appearing where, and what you want to output and where. -- Regards Roger Govier wrote in message oups.com... Hi I have for example 10 columns From Say column D to K there is thousands of rows below corresponding to a particular column, Each row will have a figure in one of the columns. I am trying to have this in a format to enter into our system. I need the column headers located in Row 1 to be inserted into a new column for the corresponding row where the amount has been placed into. For example if I put in Column H a 100.00 I want the column H header to be inserted into a new column A (for example) and if it was Column J then the header from Column J to be inserted into a new column A. Hope this makes sense, I appreciate all your help, Andrea- Hide quoted text - - Show quoted text - Hi Roger It's all related to the same spreadsheet, I thought by breaking it down into steps would be easier to make sense of. You helped put the correct labelling to input into both systems. It's difficult to explain but I have rows of data - say 10 accounts along columns. If I payment from account 10 then I was the header for account 10 placed in a cell next to the row. If the payment was made from account 10 then all the other cells would be blank. I want all the data in one column but I want to identify each account which I think can be done by having the column headers put in a cell next to the row. Does any of this make sense? Hi Andrea I think I am beginning to understand. It would help, if you could indicate what the column headers are. In the previous problem, this was a routine being ran against a set of data, after information had been input, and was only looking at values in column D. What you are talking of now, sounds more like you want event code to input the column header of the column you have made an entry, into some other column on the same row as that entry. Is this the case? It might be easier if you could mail me direct with a copy of the workbook, and what you are trying to do. To send direct, remove NOSPAM from my email address. -- Regards Roger Govier |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Headers down the rows...
On 8 May, 11:58, "Roger Govier" wrote:
wrote in message ups.com... On 8 May, 11:33, "Roger Govier" wrote: Hi Andrea Is this related to the previous problem, or a new one? I am not clear on what you are asking here. Could you give another explanation with some examples of what is appearing where, and what you want to output and where. -- Regards Roger Govier wrote in message groups.com... Hi I have for example 10 columns From Say column D to K there is thousands of rows below corresponding to a particular column, Each row will have a figure in one of the columns. I am trying to have this in a format to enter into our system. I need the column headers located in Row 1 to be inserted into a new column for the corresponding row where the amount has been placed into. For example if I put in Column H a 100.00 I want the column H header to be inserted into a new column A (for example) and if it was Column J then the header from Column J to be inserted into a new column A. Hope this makes sense, I appreciate all your help, Andrea- Hide quoted text - - Show quoted text - Hi Roger It's all related to the same spreadsheet, I thought by breaking it down into steps would be easier to make sense of. You helped put the correct labelling to input into both systems. It's difficult to explain but I have rows of data - say 10 accounts along columns. If I payment from account 10 then I was the header for account 10 placed in a cell next to the row. If the payment was made from account 10 then all the other cells would be blank. I want all the data in one column but I want to identify each account which I think can be done by having the column headers put in a cell next to the row. Does any of this make sense? Hi Andrea I think I am beginning to understand. It would help, if you could indicate what the column headers are. In the previous problem, this was a routine being ran against a set of data, after information had been input, and was only looking at values in column D. What you are talking of now, sounds more like you want event code to input the column header of the column you have made an entry, into some other column on the same row as that entry. Is this the case? It might be easier if you could mail me direct with a copy of the workbook, and what you are trying to do. To send direct, remove NOSPAM from my email address. -- Regards Roger Govier- Hide quoted text - - Show quoted text - Hi Roger Lets say my column headers are for example BankAcc1 through to BankAcc10 (more accounts may be added) If I had a withdrawal from account 10 then there would be nothing in all the cells in the rows until column 10, If I had a withdrawl or deposit from BankAcc5 then there would be blanks cells for all cells in the row except BankAcc5. I have no access to my email right now to send this to you, Once all the entry amounts are in one column with a reference to which Bank account they relate to they are then duplicated and then have the correct code placed in column E being "CashW" or "CashD". I duplicate them for use in the other system with the value in column E being "SecW" or "SecD". Hope this makes sense, Please let me know, Andrea |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Headers down the rows...
wrote in message ps.com... On 8 May, 11:58, "Roger Govier" wrote: wrote in message ups.com... On 8 May, 11:33, "Roger Govier" wrote: Hi Andrea Is this related to the previous problem, or a new one? I am not clear on what you are asking here. Could you give another explanation with some examples of what is appearing where, and what you want to output and where. -- Regards Roger Govier wrote in message groups.com... Hi I have for example 10 columns From Say column D to K there is thousands of rows below corresponding to a particular column, Each row will have a figure in one of the columns. I am trying to have this in a format to enter into our system. I need the column headers located in Row 1 to be inserted into a new column for the corresponding row where the amount has been placed into. For example if I put in Column H a 100.00 I want the column H header to be inserted into a new column A (for example) and if it was Column J then the header from Column J to be inserted into a new column A. Hope this makes sense, I appreciate all your help, Andrea- Hide quoted text - - Show quoted text - Hi Roger It's all related to the same spreadsheet, I thought by breaking it down into steps would be easier to make sense of. You helped put the correct labelling to input into both systems. It's difficult to explain but I have rows of data - say 10 accounts along columns. If I payment from account 10 then I was the header for account 10 placed in a cell next to the row. If the payment was made from account 10 then all the other cells would be blank. I want all the data in one column but I want to identify each account which I think can be done by having the column headers put in a cell next to the row. Does any of this make sense? Hi Andrea I think I am beginning to understand. It would help, if you could indicate what the column headers are. In the previous problem, this was a routine being ran against a set of data, after information had been input, and was only looking at values in column D. What you are talking of now, sounds more like you want event code to input the column header of the column you have made an entry, into some other column on the same row as that entry. Is this the case? It might be easier if you could mail me direct with a copy of the workbook, and what you are trying to do. To send direct, remove NOSPAM from my email address. -- Regards Roger Govier- Hide quoted text - - Show quoted text - Hi Roger Lets say my column headers are for example BankAcc1 through to BankAcc10 (more accounts may be added) If I had a withdrawal from account 10 then there would be nothing in all the cells in the rows until column 10, If I had a withdrawl or deposit from BankAcc5 then there would be blanks cells for all cells in the row except BankAcc5. I have no access to my email right now to send this to you, Once all the entry amounts are in one column with a reference to which Bank account they relate to they are then duplicated and then have the correct code placed in column E being "CashW" or "CashD". I duplicate them for use in the other system with the value in column E being "SecW" or "SecD". Hope this makes sense, Please let me know, Andrea Hi Andrea The following code assumes that column A is blank, and will receive the Bank names. Data is entered in columns B onward Sub addBank() Dim r As Range, lr As Long, lc As Long, i As Long, j As Long Set r = ActiveSheet.UsedRange lr = r.Rows.Count lc = r.Columns.Count For i = 2 To lr For j = 2 To lc + 1 If Cells(i, j) < "" Then Cells(i, 1) = Cells(1, j).Value Exit For End If Next j Next i End Sub Hope this helps. -- Regards Roger Govier |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Headers down the rows...
On 8 May, 12:44, "Roger Govier" wrote:
wrote in message ps.com... On 8 May, 11:58, "Roger Govier" wrote: wrote in message roups.com... On 8 May, 11:33, "Roger Govier" wrote: Hi Andrea Is this related to the previous problem, or a new one? I am not clear on what you are asking here. Could you give another explanation with some examples of what is appearing where, and what you want to output and where. -- Regards Roger Govier wrote in message groups.com... Hi I have for example 10 columns From Say column D to K there is thousands of rows below corresponding to a particular column, Each row will have a figure in one of the columns. I am trying to have this in a format to enter into our system. I need the column headers located in Row 1 to be inserted into a new column for the corresponding row where the amount has been placed into. For example if I put in Column H a 100.00 I want the column H header to be inserted into a new column A (for example) and if it was Column J then the header from Column J to be inserted into a new column A. Hope this makes sense, I appreciate all your help, Andrea- Hide quoted text - - Show quoted text - Hi Roger It's all related to the same spreadsheet, I thought by breaking it down into steps would be easier to make sense of. You helped put the correct labelling to input into both systems. It's difficult to explain but I have rows of data - say 10 accounts along columns. If I payment from account 10 then I was the header for account 10 placed in a cell next to the row. If the payment was made from account 10 then all the other cells would be blank. I want all the data in one column but I want to identify each account which I think can be done by having the column headers put in a cell next to the row. Does any of this make sense? Hi Andrea I think I am beginning to understand. It would help, if you could indicate what the column headers are. In the previous problem, this was a routine being ran against a set of data, after information had been input, and was only looking at values in column D. What you are talking of now, sounds more like you want event code to input the column header of the column you have made an entry, into some other column on the same row as that entry. Is this the case? It might be easier if you could mail me direct with a copy of the workbook, and what you are trying to do. To send direct, remove NOSPAM from my email address. -- Regards Roger Govier- Hide quoted text - - Show quoted text - Hi Roger Lets say my column headers are for example BankAcc1 through to BankAcc10 (more accounts may be added) If I had a withdrawal from account 10 then there would be nothing in all the cells in the rows until column 10, If I had a withdrawl or deposit from BankAcc5 then there would be blanks cells for all cells in the row except BankAcc5. I have no access to my email right now to send this to you, Once all the entry amounts are in one column with a reference to which Bank account they relate to they are then duplicated and then have the correct code placed in column E being "CashW" or "CashD". I duplicate them for use in the other system with the value in column E being "SecW" or "SecD". Hope this makes sense, Please let me know, Andrea Hi Andrea The following code assumes that column A is blank, and will receive the Bank names. Data is entered in columns B onward Sub addBank() Dim r As Range, lr As Long, lc As Long, i As Long, j As Long Set r = ActiveSheet.UsedRange lr = r.Rows.Count lc = r.Columns.Count For i = 2 To lr For j = 2 To lc + 1 If Cells(i, j) < "" Then Cells(i, 1) = Cells(1, j).Value Exit For End If Next j Next i End Sub Hope this helps. -- Regards Roger Govier- Hide quoted text - - Show quoted text - Hi Roger Here is a quick example of what I needed - the macro you gave me doesnt appear to work with my data; Col A Col B Col C Col D Col E 1(Blank) Name1 Name2 BankAcc1 BankAcc2 2 John Smith -100.00 3 Adam Watson 100.00 Row 1 contains the field names and rows 2 onwards contains the data. I want the data to appear in this format; Col A Col B Col C Col D Col E BankAcc2 John Smith -100.00 BankAcc1 Adam Watson 100.00 This may make more sense to you, I used two bank accounts as an example - there are actually 21 (I think) but more may be added; THanks again for all your help; Andrea |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Headers down the rows...
wrote in message
ps.com... On 8 May, 12:44, "Roger Govier" wrote: wrote in message ps.com... On 8 May, 11:58, "Roger Govier" wrote: wrote in message roups.com... On 8 May, 11:33, "Roger Govier" wrote: Hi Andrea Is this related to the previous problem, or a new one? I am not clear on what you are asking here. Could you give another explanation with some examples of what is appearing where, and what you want to output and where. -- Regards Roger Govier wrote in message groups.com... Hi I have for example 10 columns From Say column D to K there is thousands of rows below corresponding to a particular column, Each row will have a figure in one of the columns. I am trying to have this in a format to enter into our system. I need the column headers located in Row 1 to be inserted into a new column for the corresponding row where the amount has been placed into. For example if I put in Column H a 100.00 I want the column H header to be inserted into a new column A (for example) and if it was Column J then the header from Column J to be inserted into a new column A. Hope this makes sense, I appreciate all your help, Andrea- Hide quoted text - - Show quoted text - Hi Roger It's all related to the same spreadsheet, I thought by breaking it down into steps would be easier to make sense of. You helped put the correct labelling to input into both systems. It's difficult to explain but I have rows of data - say 10 accounts along columns. If I payment from account 10 then I was the header for account 10 placed in a cell next to the row. If the payment was made from account 10 then all the other cells would be blank. I want all the data in one column but I want to identify each account which I think can be done by having the column headers put in a cell next to the row. Does any of this make sense? Hi Andrea I think I am beginning to understand. It would help, if you could indicate what the column headers are. In the previous problem, this was a routine being ran against a set of data, after information had been input, and was only looking at values in column D. What you are talking of now, sounds more like you want event code to input the column header of the column you have made an entry, into some other column on the same row as that entry. Is this the case? It might be easier if you could mail me direct with a copy of the workbook, and what you are trying to do. To send direct, remove NOSPAM from my email address. -- Regards Roger Govier- Hide quoted text - - Show quoted text - Hi Roger Lets say my column headers are for example BankAcc1 through to BankAcc10 (more accounts may be added) If I had a withdrawal from account 10 then there would be nothing in all the cells in the rows until column 10, If I had a withdrawl or deposit from BankAcc5 then there would be blanks cells for all cells in the row except BankAcc5. I have no access to my email right now to send this to you, Once all the entry amounts are in one column with a reference to which Bank account they relate to they are then duplicated and then have the correct code placed in column E being "CashW" or "CashD". I duplicate them for use in the other system with the value in column E being "SecW" or "SecD". Hope this makes sense, Please let me know, Andrea Hi Andrea The following code assumes that column A is blank, and will receive the Bank names. Data is entered in columns B onward Sub addBank() Dim r As Range, lr As Long, lc As Long, i As Long, j As Long Set r = ActiveSheet.UsedRange lr = r.Rows.Count lc = r.Columns.Count For i = 2 To lr For j = 2 To lc + 1 If Cells(i, j) < "" Then Cells(i, 1) = Cells(1, j).Value Exit For End If Next j Next i End Sub Hope this helps. -- Regards Roger Govier- Hide quoted text - - Show quoted text - Hi Roger Here is a quick example of what I needed - the macro you gave me doesnt appear to work with my data; Col A Col B Col C Col D Col E 1(Blank) Name1 Name2 BankAcc1 BankAcc2 2 John Smith -100.00 3 Adam Watson 100.00 Row 1 contains the field names and rows 2 onwards contains the data. I want the data to appear in this format; Col A Col B Col C Col D Col E BankAcc2 John Smith -100.00 BankAcc1 Adam Watson 100.00 This may make more sense to you, I used two bank accounts as an example - there are actually 21 (I think) but more may be added; THanks again for all your help; Andrea Hi Andrea Oh to have the full specification at the outset <bg The following will do what you ask. I am now assuming there are no blank columns at the outset. The code, looks to see if there are blank columns, and if not inserts them automatically Sub addBank() Dim r As Range, lr As Long, lc As Long, i As Long, j As Long If Cells(1, 1) < "" Then Columns("A:A").Insert Shift:=xlToRight End If If Cells(1, 4) < "" Then Columns("D:E").Insert Shift:=xlToRight End If Set r = ActiveSheet.UsedRange lr = r.Rows.Count lc = r.Columns.Count For i = 2 To lr For j = 6 To lc + 1 If Cells(i, j) < "" Then Cells(i, 1) = Cells(1, j).Value Cells(i, 4) = Cells(i, j).Value Exit For End If Next j Next i End Sub Now, anticipating your next request, the following code does both tasks, including duplicating the rows for you. Sub addBankandLabels() Dim r As Range, lr As Long, lc As Long, i As Long, j As Long If Cells(1, 1) < "" Then Columns("A:A").Insert Shift:=xlToRight End If If Cells(1, 4) < "" Then Columns("D:E").Insert Shift:=xlToRight End If Set r = ActiveSheet.UsedRange lr = r.Rows.Count lc = r.Columns.Count For i = 2 To lr * 2 For j = 6 To lc + 1 If Cells(i, j) < "" Then Cells(i, 1) = Cells(1, j).Value: Cells(i, 4) = Cells(i, j).Value i = i + 1 Rows(i).Insert Shift:=xlDown Cells(i, 1) = Cells(1, j).Value: Cells(i, 4) = Cells(i - 1, j).Value Cells(i, 2) = Cells(i - 1, 2).Value: Cells(i, 3) = Cells(i - 1, 3).Value If Cells(i - 1, 4) < 0 Then Cells(i, 5) = "CashW": Cells(i - 1, 5) = "SecW" Else Cells(i, 5) = "CashD": Cells(i - 1, 5) = "SecD" End If Exit For End If Next j Next i End Sub Now, what are you going to do with all that saved time??<vbg -- Regards Roger Govier |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Headers down the rows...
On 8 May, 13:48, "Roger Govier" wrote:
wrote in message ps.com... On 8 May, 12:44, "Roger Govier" wrote: wrote in message oups.com... On 8 May, 11:58, "Roger Govier" wrote: wrote in message roups.com... On 8 May, 11:33, "Roger Govier" wrote: Hi Andrea Is this related to the previous problem, or a new one? I am not clear on what you are asking here. Could you give another explanation with some examples of what is appearing where, and what you want to output and where. -- Regards Roger Govier wrote in message groups.com... Hi I have for example 10 columns From Say column D to K there is thousands of rows below corresponding to a particular column, Each row will have a figure in one of the columns. I am trying to have this in a format to enter into our system. I need the column headers located in Row 1 to be inserted into a new column for the corresponding row where the amount has been placed into. For example if I put in Column H a 100.00 I want the column H header to be inserted into a new column A (for example) and if it was Column J then the header from Column J to be inserted into a new column A. Hope this makes sense, I appreciate all your help, Andrea- Hide quoted text - - Show quoted text - Hi Roger It's all related to the same spreadsheet, I thought by breaking it down into steps would be easier to make sense of. You helped put the correct labelling to input into both systems. It's difficult to explain but I have rows of data - say 10 accounts along columns. If I payment from account 10 then I was the header for account 10 placed in a cell next to the row. If the payment was made from account 10 then all the other cells would be blank. I want all the data in one column but I want to identify each account which I think can be done by having the column headers put in a cell next to the row. Does any of this make sense? Hi Andrea I think I am beginning to understand. It would help, if you could indicate what the column headers are. In the previous problem, this was a routine being ran against a set of data, after information had been input, and was only looking at values in column D. What you are talking of now, sounds more like you want event code to input the column header of the column you have made an entry, into some other column on the same row as that entry. Is this the case? It might be easier if you could mail me direct with a copy of the workbook, and what you are trying to do. To send direct, remove NOSPAM from my email address. -- Regards Roger Govier- Hide quoted text - - Show quoted text - Hi Roger Lets say my column headers are for example BankAcc1 through to BankAcc10 (more accounts may be added) If I had a withdrawal from account 10 then there would be nothing in all the cells in the rows until column 10, If I had a withdrawl or deposit from BankAcc5 then there would be blanks cells for all cells in the row except BankAcc5. I have no access to my email right now to send this to you, Once all the entry amounts are in one column with a reference to which Bank account they relate to they are then duplicated and then have the correct code placed in column E being "CashW" or "CashD". I duplicate them for use in the other system with the value in column E being "SecW" or "SecD". Hope this makes sense, Please let me know, Andrea Hi Andrea The following code assumes that column A is blank, and will receive the Bank names. Data is entered in columns B onward Sub addBank() Dim r As Range, lr As Long, lc As Long, i As Long, j As Long Set r = ActiveSheet.UsedRange lr = r.Rows.Count lc = r.Columns.Count For i = 2 To lr For j = 2 To lc + 1 If Cells(i, j) < "" Then Cells(i, 1) = Cells(1, j).Value Exit For End If Next j Next i End Sub Hope this helps. -- Regards Roger Govier- Hide quoted text - - Show quoted text - Hi Roger Here is a quick example of what I needed - the macro you gave me doesnt appear to work with my data; Col A Col B Col C Col D Col E 1(Blank) Name1 Name2 BankAcc1 BankAcc2 2 John Smith -100.00 3 Adam Watson 100.00 Row 1 contains the field names and rows 2 onwards contains the data. I want the data to appear in this format; Col A Col B Col C Col D Col E BankAcc2 John Smith -100.00 BankAcc1 Adam Watson 100.00 This may make more sense to you, I used two bank accounts as an example - there are actually 21 (I think) but more may be added; THanks again for all your help; Andrea Hi Andrea Oh to have the full specification at the outset <bg The following will do what you ask. I am now assuming there are no blank columns at the outset. The code, looks to see if there are blank columns, and if not inserts them automatically Sub addBank() Dim r As Range, lr As Long, lc As Long, i As Long, j As Long If Cells(1, 1) < "" Then Columns("A:A").Insert Shift:=xlToRight End If If Cells(1, 4) < "" Then Columns("D:E").Insert Shift:=xlToRight End If Set r = ActiveSheet.UsedRange lr = r.Rows.Count lc = r.Columns.Count For i = 2 To lr For j = 6 To lc + 1 If Cells(i, j) < "" Then Cells(i, 1) = Cells(1, j).Value Cells(i, 4) = Cells(i, j).Value Exit For End If Next j Next i End Sub Now, anticipating your next request, the following code does both tasks, including duplicating the rows for you. Sub addBankandLabels() Dim r As Range, lr As Long, lc As Long, i As Long, j As Long If Cells(1, 1) < "" Then Columns("A:A").Insert Shift:=xlToRight End If If Cells(1, 4) < "" Then Columns("D:E").Insert Shift:=xlToRight End If Set r = ActiveSheet.UsedRange lr = r.Rows.Count lc = r.Columns.Count For i = 2 To lr * 2 For j = 6 To lc + 1 If Cells(i, j) < "" Then Cells(i, 1) = Cells(1, j).Value: Cells(i, 4) = Cells(i, j).Value i = i + 1 Rows(i).Insert Shift:=xlDown Cells(i, 1) = Cells(1, j).Value: Cells(i, 4) = Cells(i - 1, j).Value Cells(i, 2) = Cells(i - 1, 2).Value: Cells(i, 3) = Cells(i - 1, 3).Value If Cells(i - 1, 4) < 0 Then Cells(i, 5) = "CashW": Cells(i - 1, 5) = "SecW" Else Cells(i, 5) = "CashD": Cells(i - 1, 5) = "SecD" End If Exit For End If Next j Next i End Sub Now, what are you going to do with all that saved time??<vbg -- Regards Roger Govier- Hide quoted text - - Show quoted text - You didnt get my previous message, Thank you for all your help, Much appreciated..... Andrea |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Headers down the rows...
wrote in message
ps.com... Hi Roger Here is a quick example of what I needed - the macro you gave me doesnt appear to work with my data; Col A Col B Col C Col D Col E 1(Blank) Name1 Name2 BankAcc1 BankAcc2 2 John Smith -100.00 3 Adam Watson 100.00 Row 1 contains the field names and rows 2 onwards contains the data. I want the data to appear in this format; Col A Col B Col C Col D Col E BankAcc2 John Smith -100.00 BankAcc1 Adam Watson 100.00 This may make more sense to you, I used two bank accounts as an example - there are actually 21 (I think) but more may be added; THanks again for all your help; Andrea Hi Andrea Oh to have the full specification at the outset <bg The following will do what you ask. I am now assuming there are no blank columns at the outset. The code, looks to see if there are blank columns, and if not inserts them automatically Sub addBank() Dim r As Range, lr As Long, lc As Long, i As Long, j As Long If Cells(1, 1) < "" Then Columns("A:A").Insert Shift:=xlToRight End If If Cells(1, 4) < "" Then Columns("D:E").Insert Shift:=xlToRight End If Set r = ActiveSheet.UsedRange lr = r.Rows.Count lc = r.Columns.Count For i = 2 To lr For j = 6 To lc + 1 If Cells(i, j) < "" Then Cells(i, 1) = Cells(1, j).Value Cells(i, 4) = Cells(i, j).Value Exit For End If Next j Next i End Sub Now, anticipating your next request, the following code does both tasks, including duplicating the rows for you. Sub addBankandLabels() Dim r As Range, lr As Long, lc As Long, i As Long, j As Long If Cells(1, 1) < "" Then Columns("A:A").Insert Shift:=xlToRight End If If Cells(1, 4) < "" Then Columns("D:E").Insert Shift:=xlToRight End If Set r = ActiveSheet.UsedRange lr = r.Rows.Count lc = r.Columns.Count For i = 2 To lr * 2 For j = 6 To lc + 1 If Cells(i, j) < "" Then Cells(i, 1) = Cells(1, j).Value: Cells(i, 4) = Cells(i, j).Value i = i + 1 Rows(i).Insert Shift:=xlDown Cells(i, 1) = Cells(1, j).Value: Cells(i, 4) = Cells(i - 1, j).Value Cells(i, 2) = Cells(i - 1, 2).Value: Cells(i, 3) = Cells(i - 1, 3).Value If Cells(i - 1, 4) < 0 Then Cells(i, 5) = "CashW": Cells(i - 1, 5) = "SecW" Else Cells(i, 5) = "CashD": Cells(i - 1, 5) = "SecD" End If Exit For End If Next j Next i End Sub Now, what are you going to do with all that saved time??<vbg -- Regards Roger Govier- Hide quoted text - - Show quoted text - You didnt get my previous message, Thank you for all your help, Much appreciated..... Andrea You're very welcome. Thanks for the feedback -- Regards Roger Govier |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display index of column headers in column A | Excel Discussion (Misc queries) | |||
lookup using column headers and row headers | Excel Discussion (Misc queries) | |||
Hide Column Headers but not row (Headers) | Excel Programming | |||
Excel - returning column headers in a seperate column | Excel Discussion (Misc queries) | |||
Loop through column headers to search from column name and get cell range | Excel Programming |