If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Dave,
Ok I see whats hapening. This is not really what I wanted to do though. I simply want the writting to start at the DestRow as set in the variable above . The tehcnique of 29+ then up does not really help me here. The original idea was to try and cater for the fact that there would be totals and other data below the copied data in the detail sheet e.g. "Investments" and we wnated to avoid overwritting that. A better approach would be to check if there is anything in the row where it will be writting to and if there is to add a row to make the room for it. Great logic would be to count the number of lines to be written and if they are more than say 15 then apply an add rows routine. The 15 is the space I typically reserve in the detail sheet for the input data. I know I could just increase that, but it is a nice size to work with without having to scroll daon all the time. Yeah Yeah, I know given them an inch and they want a yard. What do you think? You want to give the coding a bash? I am sorry I last did basic programing about 20 years agao and I don't know all the new facilities of VBA in Excel. Thanks for your help, much appreciated. Stéphane "Dave Peterson" wrote: There are multiple lines of code he With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5) While you're looping through your range, you're changing firstrow, firstcol--depending on the worksheet name found in the cell on the TB worksheet. So I put Investments in E2 (I used your original code). Your code says to set these variables like: Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 so destrow = .cells(2+29,5).end(xlup).offset(1).row says to start at range E31. Then manually hit the end key and the upArrow (.end(xlup)) then drop down one row. So it really depends on what you have in E31 and above. Maybe it's as simple as looking at column G (=7) here, too???? Speedy wrote: Dear Dave (number two) I have found one more issue when experimenting with the code. The first row logic does not work as expected. I don't totally follow the logic used in the line DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5). The aim was to have been able to start writing at whatever the DestRow was set to in the logic above that. What happens it always starts at row 2 ? Can you explain that to me please? Stephane "Dave Peterson" wrote: First, I think keeping the discussion in the newsgroups is better. You get the help of lots of people. (and my wrong answers can be corrected.) if you get a "subscript out of range" error on a line like: With Sheets(i.Value) That means that the workbook that you're looking at doesn't contain a worksheet that has the same name as what's in i (i.value). So what's in i? I'd put: msgbox "****" & i.value & "****" & vblf & i.address right before this line to see if I could see any extra spaces/spelling differences. (Everything between the asterisks is the .value) I've found that when I have errors like this, it's usually a mistyped name in the cell or I'm looking at the wrong workbook. === And using strings for those variables won't be helpful. Those are initialized as empty strings (""). And then .Cells(DestRow, ThirdCol) (with destrow and thirdcol both = "") will cause trouble. Speedy wrote: Hello Dave, thanks for your interest. I agree with your comment about the $A$1 thing. (as I'd mention to Myrna in the previuous tread) but it only means that that part of the code will not run and should make no difference. what it is meant to do is to trap the error where there is no data in the sort code colum which is really E. THe module ran OK with tha code as it was though. The problem came latter when the Select Case was introduced to depending on the detail sheet name to copy the data from the input sheet to a specifc location on the detail sheet as these are not alway starting at the same co-ordinates for data input. The model gets stuck at run time with the "With Sheets(i.Value)" line. I think the option compare text is a good idea as there are in the real live example many more detail sheets and the way it is written it requires a perfect match which can be problematic. Just a question if the variables are set to zero, would it not be better to set up these as strings? If you want I can send you the spreadsheet as I can appreciate it is better to have the actual data and model together. Thanks for your help. Stéphane "Dave Peterson" wrote: Subscript 9 usually means you're refering to something that doesn't exist. worksheets("sheet99") when you don't have a sheet99. You may want to indicate which line of code is causing the problem. I think that your code is tightly tied to your data--so it would be difficult to guess anything specific--but you did get a question about this portion in your other thread: With Sheets("TB") Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp)) End With If TBCodeRng(1).Address = "$A$1" Then If TBCodeRng is in column E, how can TBCodeRng(1).address ever point to column A? And just a general comment: In your "select case" portion of code, you have this: Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 End Select I'd add "option compare text" to the top of my module--so I wouldn't have to worry about investments or INVESTMENTS or even InVeStMeNtS. My real question: What happens when a cell doesn't contain one of those strings. All your Long variables are initialized to 0. You may want to add a check: Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 Case Else 'same kind of needs to be added to the calling routine 'as you used with SetRanges CancelA = true exit sub End Select Speedy wrote: Please see the enclosed VBA code whose purpose is to populate details sheets from an input sheet. There is a subscript error 9 when run. The detail sheets are called "Investments" , "Bank" , etc. If you need the excel file please advise and I will send it. Any advise on how to shorten the code or improve the error trapping also welcomed. The code used to work OK until I added the Select Case logic which is aimed at determining the write to location in the detail sheets named Investments, Bank etc. Any ideas? Stéphane Option Explicit Dim UtilityCodeRng As Range Dim TBCodeRng As Range Dim CancelA As Boolean Sub ShuffleData() CancelA = False SetRanges If CancelA = True Then Exit Sub ShuffleAllData MsgBox "Copying of data complete.", , "Done" End Sub Sub SetRanges() With Sheets("TB") Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp)) End With If TBCodeRng(1).Address = "$A$1" Then MsgBox "There are no sort codes in the TB sheet." & Chr(13) & _ "This program will terminate.", , "No TB Sheet Data" CancelA = True Exit Sub End If End Sub Sub ShuffleAllData() Dim i As Range Dim FirstRow As Long 'The first data row Dim DestRow As Long 'The actual destination row Dim FirstCol As Long 'The actual destination Column Dim SecondCol As Long 'The actual destination Column number 2 Dim ThirdCol As Long 'The actual destination Column number 3 Sheets("TB").Activate For Each i In TBCodeRng 'Note that i.Value is the destination sheet name. Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 End Select 'Let's say the destination range is defined as 30 rows max. With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5) 'Test for Dr or Cr amount current year, Looks at colum C If i.Offset(, -4) = "" Then .Cells(DestRow, SecondCol) = i.Offset(, -3) 'Writes at Destination content of column D Else .Cells(DestRow, SecondCol) = i.Offset(, -4) * (-1) 'Writes content at destination of column C End If 'Test for Dr or Cr amount for previous year (n-1), Looks at column F If i.Offset(, -2) = "" Then .Cells(DestRow, ThirdCol) = i.Offset(, -1) 'Writes content at destination of column F Else .Cells(DestRow, ThirdCol) = i.Offset(, -2) * (-1) 'Writes content at destination of column D End If End With Next i End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
|
|||
|
|||
If you know your data, maybe you can determine the first empty cell by
starting at row 65536 of the same column and go up until you find something. Then drop down one row: With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row becomes: with sheets(i.value) destrow = .cells(.rows.count,firstcol).end(xlup).offset(1,0) .row .... if you have data sprinkled between your first cell and row 65536, then this won't work. Maybe: with sheets(i.value) do if isempty(.cells(firstrow,firstcol)) then 'found an empty cell exit do else firstrow = firstrow + 1 end if loop .... Another way is to look at the firstcell. If it's empty, use it. If it isn't, then look directly below--if that's empyt, use that. If it isn't, then you can use the .end(xldown).offset(1,0) to find the first open cell. Dim DestCell As Range Dim FirstRow As Long Dim FirstCol As Long FirstRow = 3 'for testing only FirstCol = 4 'for testing only With Sheets(i.Value) Set DestCell = .Cells(FirstRow, FirstCol) If IsEmpty(DestCell) Then 'do nothing--found an empty cell ElseIf IsEmpty(DestCell.Offset(1, 0)) Then Set DestCell = DestCell.Offset(1, 0) Else Set DestCell = DestCell.End(xlDown).Offset(1, 0) End If End With MsgBox DestCell.Row ======= so the choice is yours! good luck, Speedy wrote in message ... Dave, Ok I see whats hapening. This is not really what I wanted to do though. I simply want the writting to start at the DestRow as set in the variable above . The tehcnique of 29+ then up does not really help me here. The original idea was to try and cater for the fact that there would be totals and other data below the copied data in the detail sheet e.g. "Investments" and we wnated to avoid overwritting that. A better approach would be to check if there is anything in the row where it will be writting to and if there is to add a row to make the room for it. Great logic would be to count the number of lines to be written and if they are more than say 15 then apply an add rows routine. The 15 is the space I typically reserve in the detail sheet for the input data. I know I could just increase that, but it is a nice size to work with without having to scroll daon all the time. Yeah Yeah, I know given them an inch and they want a yard. What do you think? You want to give the coding a bash? I am sorry I last did basic programing about 20 years agao and I don't know all the new facilities of VBA in Excel. Thanks for your help, much appreciated. Stéphane "Dave Peterson" wrote: There are multiple lines of code he With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5) While you're looping through your range, you're changing firstrow, firstcol--depending on the worksheet name found in the cell on the TB worksheet. So I put Investments in E2 (I used your original code). Your code says to set these variables like: Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 so destrow = .cells(2+29,5).end(xlup).offset(1).row says to start at range E31. Then manually hit the end key and the upArrow (.end(xlup)) then drop down one row. So it really depends on what you have in E31 and above. Maybe it's as simple as looking at column G (=7) here, too???? Speedy wrote: Dear Dave (number two) I have found one more issue when experimenting with the code. The first row logic does not work as expected. I don't totally follow the logic used in the line DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5). The aim was to have been able to start writing at whatever the DestRow was set to in the logic above that. What happens it always starts at row 2 ? Can you explain that to me please? Stephane "Dave Peterson" wrote: First, I think keeping the discussion in the newsgroups is better. You get the help of lots of people. (and my wrong answers can be corrected.) if you get a "subscript out of range" error on a line like: With Sheets(i.Value) That means that the workbook that you're looking at doesn't contain a worksheet that has the same name as what's in i (i.value). So what's in i? I'd put: msgbox "****" & i.value & "****" & vblf & i.address right before this line to see if I could see any extra spaces/spelling differences. (Everything between the asterisks is the .value) I've found that when I have errors like this, it's usually a mistyped name in the cell or I'm looking at the wrong workbook. === And using strings for those variables won't be helpful. Those are initialized as empty strings (""). And then .Cells(DestRow, ThirdCol) (with destrow and thirdcol both = "") will cause trouble. Speedy wrote: Hello Dave, thanks for your interest. I agree with your comment about the $A$1 thing. (as I'd mention to Myrna in the previuous tread) but it only means that that part of the code will not run and should make no difference. what it is meant to do is to trap the error where there is no data in the sort code colum which is really E. THe module ran OK with tha code as it was though. The problem came latter when the Select Case was introduced to depending on the detail sheet name to copy the data from the input sheet to a specifc location on the detail sheet as these are not alway starting at the same co-ordinates for data input. The model gets stuck at run time with the "With Sheets(i.Value)" line. I think the option compare text is a good idea as there are in the real live example many more detail sheets and the way it is written it requires a perfect match which can be problematic. Just a question if the variables are set to zero, would it not be better to set up these as strings? If you want I can send you the spreadsheet as I can appreciate it is better to have the actual data and model together. Thanks for your help. Stéphane "Dave Peterson" wrote: Subscript 9 usually means you're refering to something that doesn't exist. worksheets("sheet99") when you don't have a sheet99. You may want to indicate which line of code is causing the problem. I think that your code is tightly tied to your data--so it would be difficult to guess anything specific--but you did get a question about this portion in your other thread: With Sheets("TB") Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp)) End With If TBCodeRng(1).Address = "$A$1" Then If TBCodeRng is in column E, how can TBCodeRng(1).address ever point to column A? And just a general comment: In your "select case" portion of code, you have this: Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 End Select I'd add "option compare text" to the top of my module--so I wouldn't have to worry about investments or INVESTMENTS or even InVeStMeNtS. My real question: What happens when a cell doesn't contain one of those strings. All your Long variables are initialized to 0. You may want to add a check: Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 Case Else 'same kind of needs to be added to the calling routine 'as you used with SetRanges CancelA = true exit sub End Select Speedy wrote: Please see the enclosed VBA code whose purpose is to populate details sheets from an input sheet. There is a subscript error 9 when run. The detail sheets are called "Investments" , "Bank" , etc. If you need the excel file please advise and I will send it. Any advise on how to shorten the code or improve the error trapping also welcomed. The code used to work OK until I added the Select Case logic which is aimed at determining the write to location in the detail sheets named Investments, Bank etc. Any ideas? Stéphane Option Explicit Dim UtilityCodeRng As Range Dim TBCodeRng As Range Dim CancelA As Boolean Sub ShuffleData() CancelA = False SetRanges If CancelA = True Then Exit Sub ShuffleAllData MsgBox "Copying of data complete.", , "Done" End Sub Sub SetRanges() With Sheets("TB") Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp)) End With If TBCodeRng(1).Address = "$A$1" Then MsgBox "There are no sort codes in the TB sheet." & Chr(13) & _ "This program will terminate.", , "No TB Sheet Data" CancelA = True Exit Sub End If End Sub Sub ShuffleAllData() Dim i As Range Dim FirstRow As Long 'The first data row Dim DestRow As Long 'The actual destination row Dim FirstCol As Long 'The actual destination Column Dim SecondCol As Long 'The actual destination Column number 2 Dim ThirdCol As Long 'The actual destination Column number 3 Sheets("TB").Activate For Each i In TBCodeRng 'Note that i.Value is the destination sheet name. Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 End Select 'Let's say the destination range is defined as 30 rows max. With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5) 'Test for Dr or Cr amount current year, Looks at colum C If i.Offset(, -4) = "" Then .Cells(DestRow, SecondCol) = i.Offset(, -3) 'Writes at Destination content of column D Else .Cells(DestRow, SecondCol) = i.Offset(, -4) * (-1) 'Writes content at destination of column C End If 'Test for Dr or Cr amount for previous year (n-1), Looks at column F If i.Offset(, -2) = "" Then .Cells(DestRow, ThirdCol) = i.Offset(, -1) 'Writes content at destination of column F Else .Cells(DestRow, ThirdCol) = i.Offset(, -2) * (-1) 'Writes content at destination of column D End If End With Next i End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
|
|||
|
|||
Dear Dave, thanks for your valuable input.
The code you have suggested is interesting and I will use some of that logic. There is however a precision I would like to make. The problem is not so much at the start row/Cell to beginin to wite as that is in fact determined by the variable in the Select logic. In other words, I know where to start wrinting the data in the detail sheets as this is a reserved pre-set area in each sheet. It is sometimes different from one detail sheet to another because the headers etc. are longer or a bit different. But that part is hard coded as such via the variables and it will be OK. We therefore need to change the : With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row to With something like: Sheets(i.Value) DestRow = .Cells(FirstRow, FirstCol).??? What I am really trying to cover is once we have begun writting from the start row I only have (reserved) 15 blank lines below that in the details sheets templates before there are sub totals and various other data at the end (e.g. "done by:", "Date:") in boxes. So what I really need to handle is to see whether the data to be written will exceed the 15 reserved lines and would it overwrite the data below. I am not sure whether the copying as we currently do it does an insert or overwrite? I suppose it it inserts then there is really no problem with the data below it will be pushed down? I would still like to look at some example of logic to cater for the identification of whether the area below 15 lines will be written to. Perhaps we could include a counter as to the number on lines which will be written for each sort code i.e. "investments" etc. and possible an insert row logic along the lines IF InsertRow is 15 then insert Rows (X-15) , where X is the number of lines to be written as calculated. What do you think? Thanks, Stéphane "Dave Peterson" wrote: If you know your data, maybe you can determine the first empty cell by starting at row 65536 of the same column and go up until you find something. Then drop down one row: With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row becomes: with sheets(i.value) destrow = .cells(.rows.count,firstcol).end(xlup).offset(1,0) .row .... if you have data sprinkled between your first cell and row 65536, then this won't work. Maybe: with sheets(i.value) do if isempty(.cells(firstrow,firstcol)) then 'found an empty cell exit do else firstrow = firstrow + 1 end if loop .... Another way is to look at the firstcell. If it's empty, use it. If it isn't, then look directly below--if that's empyt, use that. If it isn't, then you can use the .end(xldown).offset(1,0) to find the first open cell. Dim DestCell As Range Dim FirstRow As Long Dim FirstCol As Long FirstRow = 3 'for testing only FirstCol = 4 'for testing only With Sheets(i.Value) Set DestCell = .Cells(FirstRow, FirstCol) If IsEmpty(DestCell) Then 'do nothing--found an empty cell ElseIf IsEmpty(DestCell.Offset(1, 0)) Then Set DestCell = DestCell.Offset(1, 0) Else Set DestCell = DestCell.End(xlDown).Offset(1, 0) End If End With MsgBox DestCell.Row ======= so the choice is yours! good luck, Speedy wrote in message ... Dave, Ok I see whats hapening. This is not really what I wanted to do though. I simply want the writting to start at the DestRow as set in the variable above . The tehcnique of 29+ then up does not really help me here. The original idea was to try and cater for the fact that there would be totals and other data below the copied data in the detail sheet e.g. "Investments" and we wnated to avoid overwritting that. A better approach would be to check if there is anything in the row where it will be writting to and if there is to add a row to make the room for it. Great logic would be to count the number of lines to be written and if they are more than say 15 then apply an add rows routine. The 15 is the space I typically reserve in the detail sheet for the input data. I know I could just increase that, but it is a nice size to work with without having to scroll daon all the time. Yeah Yeah, I know given them an inch and they want a yard. What do you think? You want to give the coding a bash? I am sorry I last did basic programing about 20 years agao and I don't know all the new facilities of VBA in Excel. Thanks for your help, much appreciated. Stéphane "Dave Peterson" wrote: There are multiple lines of code he With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5) While you're looping through your range, you're changing firstrow, firstcol--depending on the worksheet name found in the cell on the TB worksheet. So I put Investments in E2 (I used your original code). Your code says to set these variables like: Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 so destrow = .cells(2+29,5).end(xlup).offset(1).row says to start at range E31. Then manually hit the end key and the upArrow (.end(xlup)) then drop down one row. So it really depends on what you have in E31 and above. Maybe it's as simple as looking at column G (=7) here, too???? Speedy wrote: Dear Dave (number two) I have found one more issue when experimenting with the code. The first row logic does not work as expected. I don't totally follow the logic used in the line DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5). The aim was to have been able to start writing at whatever the DestRow was set to in the logic above that. What happens it always starts at row 2 ? Can you explain that to me please? Stephane "Dave Peterson" wrote: First, I think keeping the discussion in the newsgroups is better. You get the help of lots of people. (and my wrong answers can be corrected.) if you get a "subscript out of range" error on a line like: With Sheets(i.Value) That means that the workbook that you're looking at doesn't contain a worksheet that has the same name as what's in i (i.value). So what's in i? I'd put: msgbox "****" & i.value & "****" & vblf & i.address right before this line to see if I could see any extra spaces/spelling differences. (Everything between the asterisks is the .value) I've found that when I have errors like this, it's usually a mistyped name in the cell or I'm looking at the wrong workbook. === And using strings for those variables won't be helpful. Those are initialized as empty strings (""). And then .Cells(DestRow, ThirdCol) (with destrow and thirdcol both = "") will cause trouble. Speedy wrote: Hello Dave, thanks for your interest. I agree with your comment about the $A$1 thing. (as I'd mention to Myrna in the previuous tread) but it only means that that part of the code will not run and should make no difference. what it is meant to do is to trap the error where there is no data in the sort code colum which is really E. THe module ran OK with tha code as it was though. The problem came latter when the Select Case was introduced to depending on the detail sheet name to copy the data from the input sheet to a specifc location on the detail sheet as these are not alway starting at the same co-ordinates for data input. The model gets stuck at run time with the "With Sheets(i.Value)" line. I think the option compare text is a good idea as there are in the real live example many more detail sheets and the way it is written it requires a perfect match which can be problematic. Just a question if the variables are set to zero, would it not be better to set up these as strings? If you want I can send you the spreadsheet as I can appreciate it is better to have the actual data and model together. Thanks for your help. Stéphane "Dave Peterson" wrote: Subscript 9 usually means you're refering to something that doesn't exist. worksheets("sheet99") when you don't have a sheet99. You may want to indicate which line of code is causing the problem. I think that your code is tightly tied to your data--so it would be difficult to guess anything specific--but you did get a question about this portion in your other thread: With Sheets("TB") Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp)) End With If TBCodeRng(1).Address = "$A$1" Then If TBCodeRng is in column E, how can TBCodeRng(1).address ever point to column A? And just a general comment: In your "select case" portion of code, you have this: Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 End Select I'd add "option compare text" to the top of my module--so I wouldn't have to worry about investments or INVESTMENTS or even InVeStMeNtS. My real question: What happens when a cell doesn't contain one of those strings. All your Long variables are initialized to 0. You may want to add a check: Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 Case Else 'same kind of needs to be added to the calling routine 'as you used with SetRanges CancelA = true exit sub End Select Speedy wrote: Please see the enclosed VBA code whose purpose is to populate details sheets from an input sheet. There is a subscript error 9 when run. The detail sheets are called "Investments" , "Bank" , etc. If you need the excel file please advise and I will send it. Any advise on how to shorten the code or improve the error trapping also welcomed. The code used to work OK until I added the Select Case logic which is aimed at determining the write to location in the detail sheets named Investments, Bank etc. Any ideas? Stéphane Option Explicit Dim UtilityCodeRng As Range Dim TBCodeRng As Range Dim CancelA As Boolean Sub ShuffleData() CancelA = False SetRanges If CancelA = True Then Exit Sub ShuffleAllData MsgBox "Copying of data complete.", , "Done" End Sub Sub SetRanges() With Sheets("TB") Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp)) End With If TBCodeRng(1).Address = "$A$1" Then MsgBox "There are no sort codes in the TB sheet." & Chr(13) & _ "This program will terminate.", , "No TB Sheet Data" CancelA = True Exit Sub End If End Sub Sub ShuffleAllData() Dim i As Range Dim FirstRow As Long 'The first data row Dim DestRow As Long 'The actual destination row Dim FirstCol As Long 'The actual destination Column Dim SecondCol As Long 'The actual destination Column number 2 Dim ThirdCol As Long 'The actual destination Column number 3 Sheets("TB").Activate For Each i In TBCodeRng 'Note that i.Value is the destination sheet name. Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 End Select 'Let's say the destination range is defined as 30 rows max. With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5) 'Test for Dr or Cr amount current year, Looks at colum C If i.Offset(, -4) = "" Then .Cells(DestRow, SecondCol) = i.Offset(, -3) 'Writes at Destination content of column D Else .Cells(DestRow, SecondCol) = i.Offset(, -4) * (-1) 'Writes content at destination of column C End If 'Test for Dr or Cr amount for previous year (n-1), Looks at column F If i.Offset(, -2) = "" Then .Cells(DestRow, ThirdCol) = i.Offset(, -1) 'Writes content at destination of column F Else .Cells(DestRow, ThirdCol) = i.Offset(, -2) * (-1) 'Writes content at destination of column D End If End With Next i End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
|
|||
|
|||
First, is FirstRow the real first row to write to or do you have to add 29 to
get your starting point. If you need to, you can add the +29. And if you reserve 15 cells for logging your data, what happens on the 16th cell? How can you tell the difference between the reserved for other use 16th cell and if you inserted a new 16th cell. Especially when you insert a new row? Say you have data like this: reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved empty reserved empty reserved empty reserved empty reserved empty other uses other uses other uses other uses other uses other uses other uses When you write 5 more cells, it looks like: reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled other use other use other use other use other use other use other use And unless there's some indicator somewhere, it looks a lot like: reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled Where you just added a bunch more entries. If you have some key value in the adjacent column for the first cell of "other use", you could check that. I checked column A of the current row for some unique string ("other use" for me) that shows up on that first row of the "reserved for other use" section. If I didn't find an empty cell before that, then when I find it, I just insert a new row. Option Explicit Sub testme() Dim FirstRow As Long Dim FirstCol As Long 'for testing FirstRow = 5 FirstCol = 4 With ActiveSheet 'for testing 'With Sheets(i.Value) Do If IsEmpty(.Cells(FirstRow, FirstCol)) Then 'found an empty cell Exit Do Else FirstRow = FirstRow + 1 'look for indicator in column A??? If LCase(.Cells(FirstRow, "A").Value) = LCase("other use") Then .Rows(FirstRow).Insert Exit Do End If End If Loop End With MsgBox FirstRow End Sub I used column A, but you could use any cell on that row--as long as you can check something unique about it. Speedy wrote: Dear Dave, thanks for your valuable input. The code you have suggested is interesting and I will use some of that logic. There is however a precision I would like to make. The problem is not so much at the start row/Cell to beginin to wite as that is in fact determined by the variable in the Select logic. In other words, I know where to start wrinting the data in the detail sheets as this is a reserved pre-set area in each sheet. It is sometimes different from one detail sheet to another because the headers etc. are longer or a bit different. But that part is hard coded as such via the variables and it will be OK. We therefore need to change the : With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row to With something like: Sheets(i.Value) DestRow = .Cells(FirstRow, FirstCol).??? What I am really trying to cover is once we have begun writting from the start row I only have (reserved) 15 blank lines below that in the details sheets templates before there are sub totals and various other data at the end (e.g. "done by:", "Date:") in boxes. So what I really need to handle is to see whether the data to be written will exceed the 15 reserved lines and would it overwrite the data below. I am not sure whether the copying as we currently do it does an insert or overwrite? I suppose it it inserts then there is really no problem with the data below it will be pushed down? I would still like to look at some example of logic to cater for the identification of whether the area below 15 lines will be written to. Perhaps we could include a counter as to the number on lines which will be written for each sort code i.e. "investments" etc. and possible an insert row logic along the lines IF InsertRow is 15 then insert Rows (X-15) , where X is the number of lines to be written as calculated. What do you think? Thanks, Stéphane "Dave Peterson" wrote: If you know your data, maybe you can determine the first empty cell by starting at row 65536 of the same column and go up until you find something. Then drop down one row: With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row becomes: with sheets(i.value) destrow = .cells(.rows.count,firstcol).end(xlup).offset(1,0) .row .... if you have data sprinkled between your first cell and row 65536, then this won't work. Maybe: with sheets(i.value) do if isempty(.cells(firstrow,firstcol)) then 'found an empty cell exit do else firstrow = firstrow + 1 end if loop .... Another way is to look at the firstcell. If it's empty, use it. If it isn't, then look directly below--if that's empyt, use that. If it isn't, then you can use the .end(xldown).offset(1,0) to find the first open cell. Dim DestCell As Range Dim FirstRow As Long Dim FirstCol As Long FirstRow = 3 'for testing only FirstCol = 4 'for testing only With Sheets(i.Value) Set DestCell = .Cells(FirstRow, FirstCol) If IsEmpty(DestCell) Then 'do nothing--found an empty cell ElseIf IsEmpty(DestCell.Offset(1, 0)) Then Set DestCell = DestCell.Offset(1, 0) Else Set DestCell = DestCell.End(xlDown).Offset(1, 0) End If End With MsgBox DestCell.Row ======= so the choice is yours! good luck, Speedy wrote in message ... Dave, Ok I see whats hapening. This is not really what I wanted to do though. I simply want the writting to start at the DestRow as set in the variable above . The tehcnique of 29+ then up does not really help me here. The original idea was to try and cater for the fact that there would be totals and other data below the copied data in the detail sheet e.g. "Investments" and we wnated to avoid overwritting that. A better approach would be to check if there is anything in the row where it will be writting to and if there is to add a row to make the room for it. Great logic would be to count the number of lines to be written and if they are more than say 15 then apply an add rows routine. The 15 is the space I typically reserve in the detail sheet for the input data. I know I could just increase that, but it is a nice size to work with without having to scroll daon all the time. Yeah Yeah, I know given them an inch and they want a yard. What do you think? You want to give the coding a bash? I am sorry I last did basic programing about 20 years agao and I don't know all the new facilities of VBA in Excel. Thanks for your help, much appreciated. Stéphane "Dave Peterson" wrote: There are multiple lines of code he With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5) While you're looping through your range, you're changing firstrow, firstcol--depending on the worksheet name found in the cell on the TB worksheet. So I put Investments in E2 (I used your original code). Your code says to set these variables like: Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 so destrow = .cells(2+29,5).end(xlup).offset(1).row says to start at range E31. Then manually hit the end key and the upArrow (.end(xlup)) then drop down one row. So it really depends on what you have in E31 and above. Maybe it's as simple as looking at column G (=7) here, too???? Speedy wrote: Dear Dave (number two) I have found one more issue when experimenting with the code. The first row logic does not work as expected. I don't totally follow the logic used in the line DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5). The aim was to have been able to start writing at whatever the DestRow was set to in the logic above that. What happens it always starts at row 2 ? Can you explain that to me please? Stephane "Dave Peterson" wrote: First, I think keeping the discussion in the newsgroups is better. You get the help of lots of people. (and my wrong answers can be corrected.) if you get a "subscript out of range" error on a line like: With Sheets(i.Value) That means that the workbook that you're looking at doesn't contain a worksheet that has the same name as what's in i (i.value). So what's in i? I'd put: msgbox "****" & i.value & "****" & vblf & i.address right before this line to see if I could see any extra spaces/spelling differences. (Everything between the asterisks is the .value) I've found that when I have errors like this, it's usually a mistyped name in the cell or I'm looking at the wrong workbook. === And using strings for those variables won't be helpful. Those are initialized as empty strings (""). And then .Cells(DestRow, ThirdCol) (with destrow and thirdcol both = "") will cause trouble. Speedy wrote: Hello Dave, thanks for your interest. I agree with your comment about the $A$1 thing. (as I'd mention to Myrna in the previuous tread) but it only means that that part of the code will not run and should make no difference. what it is meant to do is to trap the error where there is no data in the sort code colum which is really E. THe module ran OK with tha code as it was though. The problem came latter when the Select Case was introduced to depending on the detail sheet name to copy the data from the input sheet to a specifc location on the detail sheet as these are not alway starting at the same co-ordinates for data input. The model gets stuck at run time with the "With Sheets(i.Value)" line. I think the option compare text is a good idea as there are in the real live example many more detail sheets and the way it is written it requires a perfect match which can be problematic. Just a question if the variables are set to zero, would it not be better to set up these as strings? If you want I can send you the spreadsheet as I can appreciate it is better to have the actual data and model together. Thanks for your help. Stéphane "Dave Peterson" wrote: Subscript 9 usually means you're refering to something that doesn't exist. worksheets("sheet99") when you don't have a sheet99. You may want to indicate which line of code is causing the problem. I think that your code is tightly tied to your data--so it would be difficult to guess anything specific--but you did get a question about this portion in your other thread: With Sheets("TB") Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp)) End With If TBCodeRng(1).Address = "$A$1" Then If TBCodeRng is in column E, how can TBCodeRng(1).address ever point to column A? And just a general comment: In your "select case" portion of code, you have this: Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 End Select I'd add "option compare text" to the top of my module--so I wouldn't have to worry about investments or INVESTMENTS or even InVeStMeNtS. My real question: What happens when a cell doesn't contain one of those strings. All your Long variables are initialized to 0. You may want to add a check: Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 Case Else 'same kind of needs to be added to the calling routine 'as you used with SetRanges CancelA = true exit sub End Select Speedy wrote: Please see the enclosed VBA code whose purpose is to populate details sheets from an input sheet. There is a subscript error 9 when run. The detail sheets are called "Investments" , "Bank" , etc. If you need the excel file please advise and I will send it. Any advise on how to shorten the code or improve the error trapping also welcomed. The code used to work OK until I added the Select Case logic which is aimed at determining the write to location in the detail sheets named Investments, Bank etc. Any ideas? Stéphane Option Explicit Dim UtilityCodeRng As Range Dim TBCodeRng As Range Dim CancelA As Boolean Sub ShuffleData() CancelA = False SetRanges If CancelA = True Then Exit Sub ShuffleAllData MsgBox "Copying of data complete.", , "Done" End Sub Sub SetRanges() With Sheets("TB") Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp)) End With If TBCodeRng(1).Address = "$A$1" Then MsgBox "There are no sort codes in the TB sheet." & Chr(13) & _ "This program will terminate.", , "No TB Sheet Data" CancelA = True Exit Sub End If End Sub Sub ShuffleAllData() Dim i As Range Dim FirstRow As Long 'The first data row Dim DestRow As Long 'The actual destination row Dim FirstCol As Long 'The actual destination Column Dim SecondCol As Long 'The actual destination Column number 2 Dim ThirdCol As Long 'The actual destination Column number 3 Sheets("TB").Activate For Each i In TBCodeRng 'Note that i.Value is the destination sheet name. Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 End Select 'Let's say the destination range is defined as 30 rows max. With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5) 'Test for Dr or Cr amount current year, Looks at colum C If i.Offset(, -4) = "" Then .Cells(DestRow, SecondCol) = i.Offset(, -3) 'Writes at Destination content of column D Else .Cells(DestRow, SecondCol) = i.Offset(, -4) * (-1) 'Writes content at destination of column C End If 'Test for Dr or Cr amount for previous year (n-1), Looks at column F If i.Offset(, -2) = "" Then .Cells(DestRow, ThirdCol) = i.Offset(, -1) 'Writes content at destination of column F Else .Cells(DestRow, ThirdCol) = i.Offset(, -2) * (-1) 'Writes content at destination of column D End If End With Next i End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
|
|||
|
|||
Dear Dave,
We are not quite there yet. The +29 is a false problem. This has no bearing anymore and we can ignore it. (it was another way of trying to deal with the problem but is not what I require). Using your example data, what we have in fact is a number of reserved filled rows (Say row 1 to 10 )as you have have . Then we have 15 reserved empty row for us to copy data into at the locations (Columns) specified in the variables. At row 10+15+1 down, we have reserved filled rows with total and footers for another say 10 rows below. Assume that the same 15 rows are reserved in each detail sheets (only thing that changes is the start write row) but that is handled by the variables for each detail sheet) . These detail sheets , just for the record, exist before the model is run and are populated with the data from the so called input sheet. i.e. they are not created as such and that's why I use the term populate. Thinking about it, I would rather use counters to keep track of things ie if write is than 10+15+1 i.e. it would now encroach in the other use area, the model should do an insert row before writing so that all the data below is just pushed down and the totalling will still work as its range is set to Row 11 to row 15 and the total in row 16. Adding a row should automatically adjust the range. SO it is not necessary to test if the FirstRow and FirstCol is available as per your last logic as this is so defined in the detail sheet. What we need to test for is when we go over the 15 resevred row space below that so as to not wipe out the data beleo (if I understand the logic of the way the data is copied across properly). So as long as we write in the 15 reseverd row area there is not problem everything will be OK. When we go over the 15 lines we risk to wipe out the data below and need to add space to write in. SO I think the logic should be oriented towards counting rows and if 15 then add row. What do you think? Dave again thanks for your input and patience. Stéphane "Dave Peterson" wrote: First, is FirstRow the real first row to write to or do you have to add 29 to get your starting point. If you need to, you can add the +29. And if you reserve 15 cells for logging your data, what happens on the 16th cell? How can you tell the difference between the reserved for other use 16th cell and if you inserted a new 16th cell. Especially when you insert a new row? Say you have data like this: reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved empty reserved empty reserved empty reserved empty reserved empty other uses other uses other uses other uses other uses other uses other uses When you write 5 more cells, it looks like: reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled other use other use other use other use other use other use other use And unless there's some indicator somewhere, it looks a lot like: reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled Where you just added a bunch more entries. If you have some key value in the adjacent column for the first cell of "other use", you could check that. I checked column A of the current row for some unique string ("other use" for me) that shows up on that first row of the "reserved for other use" section. If I didn't find an empty cell before that, then when I find it, I just insert a new row. Option Explicit Sub testme() Dim FirstRow As Long Dim FirstCol As Long 'for testing FirstRow = 5 FirstCol = 4 With ActiveSheet 'for testing 'With Sheets(i.Value) Do If IsEmpty(.Cells(FirstRow, FirstCol)) Then 'found an empty cell Exit Do Else FirstRow = FirstRow + 1 'look for indicator in column A??? If LCase(.Cells(FirstRow, "A").Value) = LCase("other use") Then .Rows(FirstRow).Insert Exit Do End If End If Loop End With MsgBox FirstRow End Sub I used column A, but you could use any cell on that row--as long as you can check something unique about it. Speedy wrote: Dear Dave, thanks for your valuable input. The code you have suggested is interesting and I will use some of that logic. There is however a precision I would like to make. The problem is not so much at the start row/Cell to beginin to wite as that is in fact determined by the variable in the Select logic. In other words, I know where to start wrinting the data in the detail sheets as this is a reserved pre-set area in each sheet. It is sometimes different from one detail sheet to another because the headers etc. are longer or a bit different. But that part is hard coded as such via the variables and it will be OK. We therefore need to change the : With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row to With something like: Sheets(i.Value) DestRow = .Cells(FirstRow, FirstCol).??? What I am really trying to cover is once we have begun writting from the start row I only have (reserved) 15 blank lines below that in the details sheets templates before there are sub totals and various other data at the end (e.g. "done by:", "Date:") in boxes. So what I really need to handle is to see whether the data to be written will exceed the 15 reserved lines and would it overwrite the data below. I am not sure whether the copying as we currently do it does an insert or overwrite? I suppose it it inserts then there is really no problem with the data below it will be pushed down? I would still like to look at some example of logic to cater for the identification of whether the area below 15 lines will be written to. Perhaps we could include a counter as to the number on lines which will be written for each sort code i.e. "investments" etc. and possible an insert row logic along the lines IF InsertRow is 15 then insert Rows (X-15) , where X is the number of lines to be written as calculated. What do you think? Thanks, Stéphane "Dave Peterson" wrote: If you know your data, maybe you can determine the first empty cell by starting at row 65536 of the same column and go up until you find something. Then drop down one row: With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row becomes: with sheets(i.value) destrow = .cells(.rows.count,firstcol).end(xlup).offset(1,0) .row .... if you have data sprinkled between your first cell and row 65536, then this won't work. Maybe: with sheets(i.value) do if isempty(.cells(firstrow,firstcol)) then 'found an empty cell exit do else firstrow = firstrow + 1 end if loop .... Another way is to look at the firstcell. If it's empty, use it. If it isn't, then look directly below--if that's empyt, use that. If it isn't, then you can use the .end(xldown).offset(1,0) to find the first open cell. Dim DestCell As Range Dim FirstRow As Long Dim FirstCol As Long FirstRow = 3 'for testing only FirstCol = 4 'for testing only With Sheets(i.Value) Set DestCell = .Cells(FirstRow, FirstCol) If IsEmpty(DestCell) Then 'do nothing--found an empty cell ElseIf IsEmpty(DestCell.Offset(1, 0)) Then Set DestCell = DestCell.Offset(1, 0) Else Set DestCell = DestCell.End(xlDown).Offset(1, 0) End If End With MsgBox DestCell.Row ======= so the choice is yours! good luck, Speedy wrote in message ... Dave, Ok I see whats hapening. This is not really what I wanted to do though. I simply want the writting to start at the DestRow as set in the variable above . The tehcnique of 29+ then up does not really help me here. The original idea was to try and cater for the fact that there would be totals and other data below the copied data in the detail sheet e.g. "Investments" and we wnated to avoid overwritting that. A better approach would be to check if there is anything in the row where it will be writting to and if there is to add a row to make the room for it. Great logic would be to count the number of lines to be written and if they are more than say 15 then apply an add rows routine. The 15 is the space I typically reserve in the detail sheet for the input data. I know I could just increase that, but it is a nice size to work with without having to scroll daon all the time. Yeah Yeah, I know given them an inch and they want a yard. What do you think? You want to give the coding a bash? I am sorry I last did basic programing about 20 years agao and I don't know all the new facilities of VBA in Excel. Thanks for your help, much appreciated. Stéphane "Dave Peterson" wrote: There are multiple lines of code he With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5) While you're looping through your range, you're changing firstrow, firstcol--depending on the worksheet name found in the cell on the TB worksheet. So I put Investments in E2 (I used your original code). Your code says to set these variables like: Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 so destrow = .cells(2+29,5).end(xlup).offset(1).row says to start at range E31. Then manually hit the end key and the upArrow (.end(xlup)) then drop down one row. So it really depends on what you have in E31 and above. Maybe it's as simple as looking at column G (=7) here, too???? Speedy wrote: Dear Dave (number two) I have found one more issue when experimenting with the code. The first row logic does not work as expected. I don't totally follow the logic used in the line DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5). The aim was to have been able to start writing at whatever the DestRow was set to in the logic above that. What happens it always starts at row 2 ? Can you explain that to me please? Stephane "Dave Peterson" wrote: First, I think keeping the discussion in the newsgroups is better. You get the help of lots of people. (and my wrong answers can be corrected.) if you get a "subscript out of range" error on a line like: With Sheets(i.Value) That means that the workbook that you're looking at doesn't contain a worksheet that has the same name as what's in i (i.value). So what's in i? I'd put: msgbox "****" & i.value & "****" & vblf & i.address right before this line to see if I could see any extra spaces/spelling differences. (Everything between the asterisks is the .value) I've found that when I have errors like this, it's usually a mistyped name in the cell or I'm looking at the wrong workbook. === And using strings for those variables won't be helpful. Those are initialized as empty strings (""). And then .Cells(DestRow, ThirdCol) (with destrow and thirdcol both = "") will cause trouble. Speedy wrote: Hello Dave, thanks for your interest. I agree with your comment about the $A$1 thing. (as I'd mention to Myrna in the previuous tread) but it only means that that part of the code will not run and should make no difference. what it is meant to do is to trap the error where there is no data in the sort code colum which is really E. THe module ran OK with tha code as it was though. The problem came latter when the Select Case was introduced to depending on the detail sheet name to copy the data from the input sheet to a specifc location on the detail sheet as these are not alway starting at the same co-ordinates for data input. The model gets stuck at run time with the "With Sheets(i.Value)" line. I think the option compare text is a good idea as there are in the real live example many more detail sheets and the way it is written it requires a perfect match which can be problematic. Just a question if the variables are set to zero, would it not be better to set up these as strings? If you want I can send you the spreadsheet as I can appreciate it is better to have the actual data and model together. Thanks for your help. Stéphane "Dave Peterson" wrote: Subscript 9 usually means you're refering to something that doesn't exist. worksheets("sheet99") when you don't have a sheet99. You may want to indicate which line of code is causing the problem. I think that your code is tightly tied to your data--so it would be difficult to guess anything specific--but you did get a question about this portion in your other thread: With Sheets("TB") Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp)) End With If TBCodeRng(1).Address = "$A$1" Then If TBCodeRng is in column E, how can TBCodeRng(1).address ever point to column A? And just a general comment: In your "select case" portion of code, you have this: Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 End Select I'd add "option compare text" to the top of my module--so I wouldn't have to worry about investments or INVESTMENTS or even InVeStMeNtS. My real question: What happens when a cell doesn't contain one of those strings. All your Long variables are initialized to 0. You may want to add a check: Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 Case Else 'same kind of needs to be added to the calling routine 'as you used with SetRanges CancelA = true exit sub End Select Speedy wrote: Please see the enclosed VBA code whose purpose is to populate details sheets from an input sheet. There is a subscript error 9 when run. The detail sheets are called "Investments" , "Bank" , etc. If you need the excel file please advise and I will send it. Any advise on how to shorten the code or improve the error trapping also welcomed. The code used to work OK until I added the Select Case logic which is aimed at determining the write to location in the detail sheets named Investments, Bank etc. Any ideas? StÃÃâ€*’©phane Option Explicit Dim UtilityCodeRng As Range Dim TBCodeRng As Range Dim CancelA As Boolean Sub ShuffleData() CancelA = False SetRanges If CancelA = True Then Exit Sub ShuffleAllData MsgBox "Copying of data complete.", , "Done" End Sub Sub SetRanges() With Sheets("TB") Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp)) End With If TBCodeRng(1).Address = "$A$1" Then MsgBox "There are no sort codes in the TB sheet." & Chr(13) & _ "This program will terminate.", , "No TB Sheet Data" CancelA = True Exit Sub End If End Sub Sub ShuffleAllData() Dim i As Range Dim FirstRow As Long 'The first data row Dim DestRow As Long 'The actual destination row Dim FirstCol As Long 'The actual destination Column Dim SecondCol As Long 'The actual destination Column number 2 Dim ThirdCol As Long 'The actual destination Column number 3 Sheets("TB").Activate For Each i In TBCodeRng 'Note that i.Value is the destination sheet name. Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 End Select 'Let's say the destination range is defined as 30 rows max. With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5) 'Test for Dr or Cr amount current year, Looks at colum C If i.Offset(, -4) = "" Then .Cells(DestRow, SecondCol) = i.Offset(, -3) 'Writes at Destination content of column D Else .Cells(DestRow, SecondCol) = i.Offset(, -4) * (-1) 'Writes content at destination of column C End If 'Test for Dr or Cr amount for previous year (n-1), Looks at column F If i.Offset(, -2) = "" Then .Cells(DestRow, ThirdCol) = i.Offset(, -1) 'Writes content at destination of column F Else .Cells(DestRow, ThirdCol) = i.Offset(, -2) * (-1) 'Writes content at destination of column D End If End With Next i End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#16
|
|||
|
|||
I think you have to test for empty cells. And the first possible cell that
could be empty is on the firstrow, isn't it? But it's not just a matter of counting. If I count to 15 and find no empty cells, do I insert right there. I would have guessed that you wanted the new stuff to be after all the values that were stored. And you might be on row 2325 by then. I would just loop through the possible (15) reserved cells. Then if I didn't find a spot to put the data, I'd keep looking until I found that indicator saying that I was on the first row after that "group". Speedy wrote: Dear Dave, We are not quite there yet. The +29 is a false problem. This has no bearing anymore and we can ignore it. (it was another way of trying to deal with the problem but is not what I require). Using your example data, what we have in fact is a number of reserved filled rows (Say row 1 to 10 )as you have have . Then we have 15 reserved empty row for us to copy data into at the locations (Columns) specified in the variables. At row 10+15+1 down, we have reserved filled rows with total and footers for another say 10 rows below. Assume that the same 15 rows are reserved in each detail sheets (only thing that changes is the start write row) but that is handled by the variables for each detail sheet) . These detail sheets , just for the record, exist before the model is run and are populated with the data from the so called input sheet. i.e. they are not created as such and that's why I use the term populate. Thinking about it, I would rather use counters to keep track of things ie if write is than 10+15+1 i.e. it would now encroach in the other use area, the model should do an insert row before writing so that all the data below is just pushed down and the totalling will still work as its range is set to Row 11 to row 15 and the total in row 16. Adding a row should automatically adjust the range. SO it is not necessary to test if the FirstRow and FirstCol is available as per your last logic as this is so defined in the detail sheet. What we need to test for is when we go over the 15 resevred row space below that so as to not wipe out the data beleo (if I understand the logic of the way the data is copied across properly). So as long as we write in the 15 reseverd row area there is not problem everything will be OK. When we go over the 15 lines we risk to wipe out the data below and need to add space to write in. SO I think the logic should be oriented towards counting rows and if 15 then add row. What do you think? Dave again thanks for your input and patience. Stéphane "Dave Peterson" wrote: First, is FirstRow the real first row to write to or do you have to add 29 to get your starting point. If you need to, you can add the +29. And if you reserve 15 cells for logging your data, what happens on the 16th cell? How can you tell the difference between the reserved for other use 16th cell and if you inserted a new 16th cell. Especially when you insert a new row? Say you have data like this: reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved empty reserved empty reserved empty reserved empty reserved empty other uses other uses other uses other uses other uses other uses other uses When you write 5 more cells, it looks like: reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled other use other use other use other use other use other use other use And unless there's some indicator somewhere, it looks a lot like: reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled Where you just added a bunch more entries. If you have some key value in the adjacent column for the first cell of "other use", you could check that. I checked column A of the current row for some unique string ("other use" for me) that shows up on that first row of the "reserved for other use" section. If I didn't find an empty cell before that, then when I find it, I just insert a new row. Option Explicit Sub testme() Dim FirstRow As Long Dim FirstCol As Long 'for testing FirstRow = 5 FirstCol = 4 With ActiveSheet 'for testing 'With Sheets(i.Value) Do If IsEmpty(.Cells(FirstRow, FirstCol)) Then 'found an empty cell Exit Do Else FirstRow = FirstRow + 1 'look for indicator in column A??? If LCase(.Cells(FirstRow, "A").Value) = LCase("other use") Then .Rows(FirstRow).Insert Exit Do End If End If Loop End With MsgBox FirstRow End Sub I used column A, but you could use any cell on that row--as long as you can check something unique about it. Speedy wrote: Dear Dave, thanks for your valuable input. The code you have suggested is interesting and I will use some of that logic. There is however a precision I would like to make. The problem is not so much at the start row/Cell to beginin to wite as that is in fact determined by the variable in the Select logic. In other words, I know where to start wrinting the data in the detail sheets as this is a reserved pre-set area in each sheet. It is sometimes different from one detail sheet to another because the headers etc. are longer or a bit different. But that part is hard coded as such via the variables and it will be OK. We therefore need to change the : With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row to With something like: Sheets(i.Value) DestRow = .Cells(FirstRow, FirstCol).??? What I am really trying to cover is once we have begun writting from the start row I only have (reserved) 15 blank lines below that in the details sheets templates before there are sub totals and various other data at the end (e.g. "done by:", "Date:") in boxes. So what I really need to handle is to see whether the data to be written will exceed the 15 reserved lines and would it overwrite the data below. I am not sure whether the copying as we currently do it does an insert or overwrite? I suppose it it inserts then there is really no problem with the data below it will be pushed down? I would still like to look at some example of logic to cater for the identification of whether the area below 15 lines will be written to. Perhaps we could include a counter as to the number on lines which will be written for each sort code i.e. "investments" etc. and possible an insert row logic along the lines IF InsertRow is 15 then insert Rows (X-15) , where X is the number of lines to be written as calculated. What do you think? Thanks, Stéphane "Dave Peterson" wrote: If you know your data, maybe you can determine the first empty cell by starting at row 65536 of the same column and go up until you find something. Then drop down one row: With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row becomes: with sheets(i.value) destrow = .cells(.rows.count,firstcol).end(xlup).offset(1,0) .row .... if you have data sprinkled between your first cell and row 65536, then this won't work. Maybe: with sheets(i.value) do if isempty(.cells(firstrow,firstcol)) then 'found an empty cell exit do else firstrow = firstrow + 1 end if loop .... Another way is to look at the firstcell. If it's empty, use it. If it isn't, then look directly below--if that's empyt, use that. If it isn't, then you can use the .end(xldown).offset(1,0) to find the first open cell. Dim DestCell As Range Dim FirstRow As Long Dim FirstCol As Long FirstRow = 3 'for testing only FirstCol = 4 'for testing only With Sheets(i.Value) Set DestCell = .Cells(FirstRow, FirstCol) If IsEmpty(DestCell) Then 'do nothing--found an empty cell ElseIf IsEmpty(DestCell.Offset(1, 0)) Then Set DestCell = DestCell.Offset(1, 0) Else Set DestCell = DestCell.End(xlDown).Offset(1, 0) End If End With MsgBox DestCell.Row ======= so the choice is yours! good luck, Speedy wrote in message ... Dave, Ok I see whats hapening. This is not really what I wanted to do though. I simply want the writting to start at the DestRow as set in the variable above . The tehcnique of 29+ then up does not really help me here. The original idea was to try and cater for the fact that there would be totals and other data below the copied data in the detail sheet e.g. "Investments" and we wnated to avoid overwritting that. A better approach would be to check if there is anything in the row where it will be writting to and if there is to add a row to make the room for it. Great logic would be to count the number of lines to be written and if they are more than say 15 then apply an add rows routine. The 15 is the space I typically reserve in the detail sheet for the input data. I know I could just increase that, but it is a nice size to work with without having to scroll daon all the time. Yeah Yeah, I know given them an inch and they want a yard. What do you think? You want to give the coding a bash? I am sorry I last did basic programing about 20 years agao and I don't know all the new facilities of VBA in Excel. Thanks for your help, much appreciated. Stéphane "Dave Peterson" wrote: There are multiple lines of code he With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5) While you're looping through your range, you're changing firstrow, firstcol--depending on the worksheet name found in the cell on the TB worksheet. So I put Investments in E2 (I used your original code). Your code says to set these variables like: Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 so destrow = .cells(2+29,5).end(xlup).offset(1).row says to start at range E31. Then manually hit the end key and the upArrow (.end(xlup)) then drop down one row. So it really depends on what you have in E31 and above. Maybe it's as simple as looking at column G (=7) here, too???? Speedy wrote: Dear Dave (number two) I have found one more issue when experimenting with the code. The first row logic does not work as expected. I don't totally follow the logic used in the line DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5). The aim was to have been able to start writing at whatever the DestRow was set to in the logic above that. What happens it always starts at row 2 ? Can you explain that to me please? Stephane "Dave Peterson" wrote: First, I think keeping the discussion in the newsgroups is better. You get the help of lots of people. (and my wrong answers can be corrected.) if you get a "subscript out of range" error on a line like: With Sheets(i.Value) That means that the workbook that you're looking at doesn't contain a worksheet that has the same name as what's in i (i.value). So what's in i? I'd put: msgbox "****" & i.value & "****" & vblf & i.address right before this line to see if I could see any extra spaces/spelling differences. (Everything between the asterisks is the .value) I've found that when I have errors like this, it's usually a mistyped name in the cell or I'm looking at the wrong workbook. === And using strings for those variables won't be helpful. Those are initialized as empty strings (""). And then .Cells(DestRow, ThirdCol) (with destrow and thirdcol both = "") will cause trouble. Speedy wrote: Hello Dave, thanks for your interest. I agree with your comment about the $A$1 thing. (as I'd mention to Myrna in the previuous tread) but it only means that that part of the code will not run and should make no difference. what it is meant to do is to trap the error where there is no data in the sort code colum which is really E. THe module ran OK with tha code as it was though. The problem came latter when the Select Case was introduced to depending on the detail sheet name to copy the data from the input sheet to a specifc location on the detail sheet as these are not alway starting at the same co-ordinates for data input. The model gets stuck at run time with the "With Sheets(i.Value)" line. I think the option compare text is a good idea as there are in the real live example many more detail sheets and the way it is written it requires a perfect match which can be problematic. Just a question if the variables are set to zero, would it not be better to set up these as strings? If you want I can send you the spreadsheet as I can appreciate it is better to have the actual data and model together. Thanks for your help. Stéphane "Dave Peterson" wrote: Subscript 9 usually means you're refering to something that doesn't exist. worksheets("sheet99") when you don't have a sheet99. You may want to indicate which line of code is causing the problem. I think that your code is tightly tied to your data--so it would be difficult to guess anything specific--but you did get a question about this portion in your other thread: With Sheets("TB") Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp)) End With If TBCodeRng(1).Address = "$A$1" Then If TBCodeRng is in column E, how can TBCodeRng(1).address ever point to column A? And just a general comment: In your "select case" portion of code, you have this: Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 End Select I'd add "option compare text" to the top of my module--so I wouldn't have to worry about investments or INVESTMENTS or even InVeStMeNtS. My real question: What happens when a cell doesn't contain one of those strings. All your Long variables are initialized to 0. You may want to add a check: Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 Case Else 'same kind of needs to be added to the calling routine 'as you used with SetRanges CancelA = true exit sub End Select Speedy wrote: Please see the enclosed VBA code whose purpose is to populate details sheets from an input sheet. There is a subscript error 9 when run. The detail sheets are called "Investments" , "Bank" , etc. If you need the excel file please advise and I will send it. Any advise on how to shorten the code or improve the error trapping also welcomed. The code used to work OK until I added the Select Case logic which is aimed at determining the write to location in the detail sheets named Investments, Bank etc. Any ideas? Stéphane Option Explicit Dim UtilityCodeRng As Range Dim TBCodeRng As Range Dim CancelA As Boolean Sub ShuffleData() CancelA = False SetRanges If CancelA = True Then Exit Sub ShuffleAllData MsgBox "Copying of data complete.", , "Done" End Sub Sub SetRanges() With Sheets("TB") Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp)) End With If TBCodeRng(1).Address = "$A$1" Then MsgBox "There are no sort codes in the TB sheet." & Chr(13) & _ "This program will terminate.", , "No TB Sheet Data" CancelA = True Exit Sub End If End Sub Sub ShuffleAllData() Dim i As Range Dim FirstRow As Long 'The first data row Dim DestRow As Long 'The actual destination row Dim FirstCol As Long 'The actual destination Column Dim SecondCol As Long 'The actual destination Column number 2 Dim ThirdCol As Long 'The actual destination Column number 3 Sheets("TB").Activate For Each i In TBCodeRng 'Note that i.Value is the destination sheet name. Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 End Select 'Let's say the destination range is defined as 30 rows max. With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5) 'Test for Dr or Cr amount current year, Looks at colum C If i.Offset(, -4) = "" Then .Cells(DestRow, SecondCol) = i.Offset(, -3) 'Writes at Destination content of column D Else .Cells(DestRow, SecondCol) = i.Offset(, -4) * (-1) 'Writes content at destination of column C End If 'Test for Dr or Cr amount for previous year (n-1), Looks at column F If i.Offset(, -2) = "" Then .Cells(DestRow, ThirdCol) = i.Offset(, -1) 'Writes content at destination of column F Else .Cells(DestRow, ThirdCol) = i.Offset(, -2) * (-1) 'Writes content at destination of column D End If End With Next i End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#17
|
|||
|
|||
Hi Dave,
See Below. "Dave Peterson" wrote: I think you have to test for empty cells. And the first possible cell that could be empty is on the firstrow, isn't it? REPLY: Yes, but I know it is available for populating as I have set it up as such when the detail sheet were created. So i do not see why I need to do this? In setting up the variables for FIrstRow I have set up that location based on each detail sheet and the space below is set up for copying into. But it's not just a matter of counting. If I count to 15 and find no empty cells, do I insert right there. I would have guessed that you wanted the new stuff to be after all the values that were stored. And you might be on row 2325 by then. REPLY: It is no so complex. I have 15 pre set rows to write in. If the data from the input sheet exceeds that then we will overwrite the total row below. So this is why I say we need to determine whtehr there will be more than 15 rows of data coming form the input sheet. This is not known in advance and depends on the data we receive and can change each time we receive new data. What it means in reality is that the accountant will have added or even deleted an account. This happens all the time with late adjustemnts and corrections to the original balances. I would just loop through the possible (15) reserved cells. Then if I didn't find a spot to put the data, I'd keep looking until I found that indicator saying that I was on the first row after that "group". REPLY: Yes that's what we need to code together with the extra logic for the above if we have more than the 15 rows pre set space reservation to write into and we need to provide for an insert row to take place before the next write. Can I bother you to code that for me one more time? Thanks for your input. Stéphane Speedy wrote: Dear Dave, We are not quite there yet. The +29 is a false problem. This has no bearing anymore and we can ignore it. (it was another way of trying to deal with the problem but is not what I require). Using your example data, what we have in fact is a number of reserved filled rows (Say row 1 to 10 )as you have have . Then we have 15 reserved empty row for us to copy data into at the locations (Columns) specified in the variables. At row 10+15+1 down, we have reserved filled rows with total and footers for another say 10 rows below. Assume that the same 15 rows are reserved in each detail sheets (only thing that changes is the start write row) but that is handled by the variables for each detail sheet) . These detail sheets , just for the record, exist before the model is run and are populated with the data from the so called input sheet. i.e. they are not created as such and that's why I use the term populate. Thinking about it, I would rather use counters to keep track of things ie if write is than 10+15+1 i.e. it would now encroach in the other use area, the model should do an insert row before writing so that all the data below is just pushed down and the totalling will still work as its range is set to Row 11 to row 15 and the total in row 16. Adding a row should automatically adjust the range. SO it is not necessary to test if the FirstRow and FirstCol is available as per your last logic as this is so defined in the detail sheet. What we need to test for is when we go over the 15 resevred row space below that so as to not wipe out the data beleo (if I understand the logic of the way the data is copied across properly). So as long as we write in the 15 reseverd row area there is not problem everything will be OK. When we go over the 15 lines we risk to wipe out the data below and need to add space to write in. SO I think the logic should be oriented towards counting rows and if 15 then add row. What do you think? Dave again thanks for your input and patience. Stéphane "Dave Peterson" wrote: First, is FirstRow the real first row to write to or do you have to add 29 to get your starting point. If you need to, you can add the +29. And if you reserve 15 cells for logging your data, what happens on the 16th cell? How can you tell the difference between the reserved for other use 16th cell and if you inserted a new 16th cell. Especially when you insert a new row? Say you have data like this: reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved empty reserved empty reserved empty reserved empty reserved empty other uses other uses other uses other uses other uses other uses other uses When you write 5 more cells, it looks like: reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled other use other use other use other use other use other use other use And unless there's some indicator somewhere, it looks a lot like: reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled reserved filled Where you just added a bunch more entries. If you have some key value in the adjacent column for the first cell of "other use", you could check that. I checked column A of the current row for some unique string ("other use" for me) that shows up on that first row of the "reserved for other use" section. If I didn't find an empty cell before that, then when I find it, I just insert a new row. Option Explicit Sub testme() Dim FirstRow As Long Dim FirstCol As Long 'for testing FirstRow = 5 FirstCol = 4 With ActiveSheet 'for testing 'With Sheets(i.Value) Do If IsEmpty(.Cells(FirstRow, FirstCol)) Then 'found an empty cell Exit Do Else FirstRow = FirstRow + 1 'look for indicator in column A??? If LCase(.Cells(FirstRow, "A").Value) = LCase("other use") Then .Rows(FirstRow).Insert Exit Do End If End If Loop End With MsgBox FirstRow End Sub I used column A, but you could use any cell on that row--as long as you can check something unique about it. Speedy wrote: Dear Dave, thanks for your valuable input. The code you have suggested is interesting and I will use some of that logic. There is however a precision I would like to make. The problem is not so much at the start row/Cell to beginin to wite as that is in fact determined by the variable in the Select logic. In other words, I know where to start wrinting the data in the detail sheets as this is a reserved pre-set area in each sheet. It is sometimes different from one detail sheet to another because the headers etc. are longer or a bit different. But that part is hard coded as such via the variables and it will be OK. We therefore need to change the : With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row to With something like: Sheets(i.Value) DestRow = .Cells(FirstRow, FirstCol).??? What I am really trying to cover is once we have begun writting from the start row I only have (reserved) 15 blank lines below that in the details sheets templates before there are sub totals and various other data at the end (e.g. "done by:", "Date:") in boxes. So what I really need to handle is to see whether the data to be written will exceed the 15 reserved lines and would it overwrite the data below. I am not sure whether the copying as we currently do it does an insert or overwrite? I suppose it it inserts then there is really no problem with the data below it will be pushed down? I would still like to look at some example of logic to cater for the identification of whether the area below 15 lines will be written to. Perhaps we could include a counter as to the number on lines which will be written for each sort code i.e. "investments" etc. and possible an insert row logic along the lines IF InsertRow is 15 then insert Rows (X-15) , where X is the number of lines to be written as calculated. What do you think? Thanks, Stéphane "Dave Peterson" wrote: If you know your data, maybe you can determine the first empty cell by starting at row 65536 of the same column and go up until you find something. Then drop down one row: With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row becomes: with sheets(i.value) destrow = .cells(.rows.count,firstcol).end(xlup).offset(1,0) .row .... if you have data sprinkled between your first cell and row 65536, then this won't work. Maybe: with sheets(i.value) do if isempty(.cells(firstrow,firstcol)) then 'found an empty cell exit do else firstrow = firstrow + 1 end if loop .... Another way is to look at the firstcell. If it's empty, use it. If it isn't, then look directly below--if that's empyt, use that. If it isn't, then you can use the .end(xldown).offset(1,0) to find the first open cell. Dim DestCell As Range Dim FirstRow As Long Dim FirstCol As Long FirstRow = 3 'for testing only FirstCol = 4 'for testing only With Sheets(i.Value) Set DestCell = .Cells(FirstRow, FirstCol) If IsEmpty(DestCell) Then 'do nothing--found an empty cell ElseIf IsEmpty(DestCell.Offset(1, 0)) Then Set DestCell = DestCell.Offset(1, 0) Else Set DestCell = DestCell.End(xlDown).Offset(1, 0) End If End With MsgBox DestCell.Row ======= so the choice is yours! good luck, Speedy wrote in message ... Dave, Ok I see whats hapening. This is not really what I wanted to do though. I simply want the writting to start at the DestRow as set in the variable above . The tehcnique of 29+ then up does not really help me here. The original idea was to try and cater for the fact that there would be totals and other data below the copied data in the detail sheet e.g. "Investments" and we wnated to avoid overwritting that. A better approach would be to check if there is anything in the row where it will be writting to and if there is to add a row to make the room for it. Great logic would be to count the number of lines to be written and if they are more than say 15 then apply an add rows routine. The 15 is the space I typically reserve in the detail sheet for the input data. I know I could just increase that, but it is a nice size to work with without having to scroll daon all the time. Yeah Yeah, I know given them an inch and they want a yard. What do you think? You want to give the coding a bash? I am sorry I last did basic programing about 20 years agao and I don't know all the new facilities of VBA in Excel. Thanks for your help, much appreciated. Stéphane "Dave Peterson" wrote: There are multiple lines of code he With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5) While you're looping through your range, you're changing firstrow, firstcol--depending on the worksheet name found in the cell on the TB worksheet. So I put Investments in E2 (I used your original code). Your code says to set these variables like: Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 so destrow = .cells(2+29,5).end(xlup).offset(1).row says to start at range E31. Then manually hit the end key and the upArrow (.end(xlup)) then drop down one row. So it really depends on what you have in E31 and above. Maybe it's as simple as looking at column G (=7) here, too???? Speedy wrote: Dear Dave (number two) I have found one more issue when experimenting with the code. The first row logic does not work as expected. I don't totally follow the logic used in the line DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5). The aim was to have been able to start writing at whatever the DestRow was set to in the logic above that. What happens it always starts at row 2 ? Can you explain that to me please? Stephane "Dave Peterson" wrote: First, I think keeping the discussion in the newsgroups is better. You get the help of lots of people. (and my wrong answers can be corrected.) if you get a "subscript out of range" error on a line like: With Sheets(i.Value) That means that the workbook that you're looking at doesn't contain a worksheet that has the same name as what's in i (i.value). So what's in i? I'd put: msgbox "****" & i.value & "****" & vblf & i.address right before this line to see if I could see any extra spaces/spelling differences. (Everything between the asterisks is the .value) I've found that when I have errors like this, it's usually a mistyped name in the cell or I'm looking at the wrong workbook. === And using strings for those variables won't be helpful. Those are initialized as empty strings (""). And then .Cells(DestRow, ThirdCol) (with destrow and thirdcol both = "") will cause trouble. Speedy wrote: Hello Dave, thanks for your interest. I agree with your comment about the $A$1 thing. (as I'd mention to Myrna in the previuous tread) but it only means that that part of the code will not run and should make no difference. what it is meant to do is to trap the error where there is no data in the sort code colum which is really E. THe module ran OK with tha code as it was though. The problem came latter when the Select Case was introduced to depending on the detail sheet name to copy the data from the input sheet to a specifc location on the detail sheet as these are not alway starting at the same co-ordinates for data input. The model gets stuck at run time with the "With Sheets(i.Value)" line. I think the option compare text is a good idea as there are in the real live example many more detail sheets and the way it is written it requires a perfect match which can be problematic. Just a question if the variables are set to zero, would it not be better to set up these as strings? If you want I can send you the spreadsheet as I can appreciate it is better to have the actual data and model together. Thanks for your help. StÃÃâ€*’©phane "Dave Peterson" wrote: Subscript 9 usually means you're refering to something that doesn't exist. worksheets("sheet99") when you don't have a sheet99. You may want to indicate which line of code is causing the problem. I think that your code is tightly tied to your data--so it would be difficult to guess anything specific--but you did get a question about this portion in your other thread: With Sheets("TB") Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp)) End With If TBCodeRng(1).Address = "$A$1" Then If TBCodeRng is in column E, how can TBCodeRng(1).address ever point to column A? And just a general comment: In your "select case" portion of code, you have this: Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 End Select I'd add "option compare text" to the top of my module--so I wouldn't have to worry about investments or INVESTMENTS or even InVeStMeNtS. My real question: What happens when a cell doesn't contain one of those strings. All your Long variables are initialized to 0. You may want to add a check: Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 Case Else 'same kind of needs to be added to the calling routine 'as you used with SetRanges CancelA = true exit sub End Select Speedy wrote: Please see the enclosed VBA code whose purpose is to populate details sheets from an input sheet. There is a subscript error 9 when run. The detail sheets are called "Investments" , "Bank" , etc. If you need the excel file please advise and I will send it. Any advise on how to shorten the code or improve the error trapping also welcomed. The code used to work OK until I added the Select Case logic which is aimed at determining the write to location in the detail sheets named Investments, Bank etc. Any ideas? StÃÃâ€*’Æ’ÂÃâ₠¬Å¡Ãƒâ€šÃ‚©phane Option Explicit Dim UtilityCodeRng As Range Dim TBCodeRng As Range Dim CancelA As Boolean Sub ShuffleData() CancelA = False SetRanges If CancelA = True Then Exit Sub ShuffleAllData MsgBox "Copying of data complete.", , "Done" End Sub Sub SetRanges() With Sheets("TB") Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp)) End With If TBCodeRng(1).Address = "$A$1" Then MsgBox "There are no sort codes in the TB sheet." & Chr(13) & _ "This program will terminate.", , "No TB Sheet Data" CancelA = True Exit Sub End If End Sub Sub ShuffleAllData() Dim i As Range Dim FirstRow As Long 'The first data row Dim DestRow As Long 'The actual destination row Dim FirstCol As Long 'The actual destination Column Dim SecondCol As Long 'The actual destination Column number 2 Dim ThirdCol As Long 'The actual destination Column number 3 Sheets("TB").Activate For Each i In TBCodeRng 'Note that i.Value is the destination sheet name. Select Case i.Value Case "Investments": FirstRow = 2 FirstCol = 5 SecondCol = 7 ThirdCol = 10 Case "Bank Balances": FirstRow = 2 FirstCol = 3 SecondCol = 4 ThirdCol = 7 Case "Share Capital & Reserves": FirstRow = 2 FirstCol = 6 SecondCol = 10 ThirdCol = 12 End Select 'Let's say the destination range is defined as 30 rows max. With Sheets(i.Value) DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5) 'Test for Dr or Cr amount current year, Looks at colum C If i.Offset(, -4) = "" Then .Cells(DestRow, SecondCol) = i.Offset(, -3) 'Writes at Destination content of column D Else .Cells(DestRow, SecondCol) = i.Offset(, -4) * (-1) 'Writes content at destination of column C End If 'Test for Dr or Cr amount for previous year (n-1), Looks at column F If i.Offset(, -2) = "" Then .Cells(DestRow, ThirdCol) = i.Offset(, -1) 'Writes content at destination of column F Else .Cells(DestRow, ThirdCol) = i.Offset(, -2) * (-1) 'Writes content at destination of column D End If End With Next i End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#18
|
|||
|
|||
I suggested this in a previous post:
Option Explicit Sub testme() Dim FirstRow As Long Dim FirstCol As Long 'for testing FirstRow = 5 FirstCol = 4 With ActiveSheet 'for testing 'With Sheets(i.Value) Do If IsEmpty(.Cells(FirstRow, FirstCol)) Then 'found an empty cell Exit Do Else FirstRow = FirstRow + 1 'look for indicator in column A??? If LCase(.Cells(FirstRow, "A").Value) = LCase("other use") Then .Rows(FirstRow).Insert Exit Do End If End If Loop End With MsgBox FirstRow End Sub I used column A, but you could use any cell on that row--as long as you can check something unique about it. Speedy wrote in message ... Hi Dave, See Below. "Dave Peterson" wrote: I think you have to test for empty cells. And the first possible cell that could be empty is on the firstrow, isn't it? REPLY: Yes, but I know it is available for populating as I have set it up as such when the detail sheet were created. So i do not see why I need to do this? In setting up the variables for FIrstRow I have set up that location based on each detail sheet and the space below is set up for copying into. But it's not just a matter of counting. If I count to 15 and find no empty cells, do I insert right there. I would have guessed that you wanted the new stuff to be after all the values that were stored. And you might be on row 2325 by then. REPLY: It is no so complex. I have 15 pre set rows to write in. If the data from the input sheet exceeds that then we will overwrite the total row below. So this is why I say we need to determine whtehr there will be more than 15 rows of data coming form the input sheet. This is not known in advance and depends on the data we receive and can change each time we receive new data. What it means in reality is that the accountant will have added or even deleted an account. This happens all the time with late adjustemnts and corrections to the original balances. I would just loop through the possible (15) reserved cells. Then if I didn't find a spot to put the data, I'd keep looking until I found that indicator saying that I was on the first row after that "group". REPLY: Yes that's what we need to code together with the extra logic for the above if we have more than the 15 rows pre set space reservation to write into and we need to provide for an insert row to take place before the next write. Can I bother you to code that for me one more time? Thanks for your input. Stéphane snipped |
#19
|
|||
|
|||
Yes, I get it. This should about do it now.
Thanks for your assistance. Stéphane "Dave Peterson" wrote: I suggested this in a previous post: Option Explicit Sub testme() Dim FirstRow As Long Dim FirstCol As Long 'for testing FirstRow = 5 FirstCol = 4 With ActiveSheet 'for testing 'With Sheets(i.Value) Do If IsEmpty(.Cells(FirstRow, FirstCol)) Then 'found an empty cell Exit Do Else FirstRow = FirstRow + 1 'look for indicator in column A??? If LCase(.Cells(FirstRow, "A").Value) = LCase("other use") Then .Rows(FirstRow).Insert Exit Do End If End If Loop End With MsgBox FirstRow End Sub I used column A, but you could use any cell on that row--as long as you can check something unique about it. Speedy wrote in message ... Hi Dave, See Below. "Dave Peterson" wrote: I think you have to test for empty cells. And the first possible cell that could be empty is on the firstrow, isn't it? REPLY: Yes, but I know it is available for populating as I have set it up as such when the detail sheet were created. So i do not see why I need to do this? In setting up the variables for FIrstRow I have set up that location based on each detail sheet and the space below is set up for copying into. But it's not just a matter of counting. If I count to 15 and find no empty cells, do I insert right there. I would have guessed that you wanted the new stuff to be after all the values that were stored. And you might be on row 2325 by then. REPLY: It is no so complex. I have 15 pre set rows to write in. If the data from the input sheet exceeds that then we will overwrite the total row below. So this is why I say we need to determine whtehr there will be more than 15 rows of data coming form the input sheet. This is not known in advance and depends on the data we receive and can change each time we receive new data. What it means in reality is that the accountant will have added or even deleted an account. This happens all the time with late adjustemnts and corrections to the original balances. I would just loop through the possible (15) reserved cells. Then if I didn't find a spot to put the data, I'd keep looking until I found that indicator saying that I was on the first row after that "group". REPLY: Yes that's what we need to code together with the extra logic for the above if we have more than the 15 rows pre set space reservation to write into and we need to provide for an insert row to take place before the next write. Can I bother you to code that for me one more time? Thanks for your input. Stéphane snipped |
#20
|
|||
|
|||
Hope it works!
Speedy wrote: Yes, I get it. This should about do it now. Thanks for your assistance. Stéphane |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
code to correct leap yr problem in February 2004 | ron | General Discussion | 4 | October 2nd, 2004 05:03 PM |
Mailmerge zip code problem | SamFortMyers | Mailmerge | 10 | September 8th, 2004 12:08 AM |
Problem with my TOC Field Code | Jed Sheckler | New Users | 1 | August 31st, 2004 10:59 PM |
Strange annoying problem - long chunk of code | John | Using Forms | 1 | June 10th, 2004 03:37 AM |