Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's wrong with my NextRow code?
I am new to VBA for Excel and hopefully some experts on the Forum can
help me solve the problem. I try to use NextRow to insert data to the next available row and it is not working. The workbook has 4 worksheets. The User Form is on sheet REPORT (the active sheet) and when user selects the “No” option button on one of the questions in the UwerForm, a combo box appears and the data in the combo box will be transferred to sheet RECAP starting from row D54. I was able to transfer the data to the D column in sheet RECAP but instead of D54, thge data was put in D55; the subsequent No answers put the data in the wrong row as well. Here is the code I wrote: 'Hide the combo box and the OK button after transferring respective concern to "Recap" Private Sub cmdCargoEnter_Click() 'Hide the frame FrameConcernCargo.Visible = False 'Check for completeness If cmbConcernCargo.Text = "" Then MsgBox "You forgot to select the security concern" FrameConcernCargo.Visible = True End If ' Find next available row NextRow = Sheets("Recap").Range("a53").Row + _ Sheets("Recap").Range("a53").CurrentRegion.Rows.Co unt ' Transfer the concern Sheets("Recap").Cells(NextRow, 4) = cmbConcernCargo ' Reset the Userform for the next row cmbConcernCargo = "" End Sub I attached two screenshots for reference --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's wrong with my NextRow code?
inbound03,
For your NextRow, you could use the following: NextRow = Sheets("Recap").Range("a53").end(xlup).row + 1 That should return the last blank row underneath row 53. Also...in your code: 'Check for completeness If cmbConcernCargo.Text = "" Then MsgBox "You forgot to select the security concern" FrameConcernCargo.Visible = True End If You might want to add (just after the MsgBox) Exit Sub ' so that the rest of your code won't run John "inbound03" wrote in message ... I am new to VBA for Excel and hopefully some experts on the Forum can help me solve the problem. I try to use NextRow to insert data to the next available row and it is not working. The workbook has 4 worksheets. The User Form is on sheet REPORT (the active sheet) and when user selects the “No” option button on one of the questions in the UwerForm, a combo box appears and the data in the combo box will be transferred to sheet RECAP starting from row D54. I was able to transfer the data to the D column in sheet RECAP but instead of D54, thge data was put in D55; the subsequent No answers put the data in the wrong row as well. Here is the code I wrote: 'Hide the combo box and the OK button after transferring respective concern to "Recap" Private Sub cmdCargoEnter_Click() 'Hide the frame FrameConcernCargo.Visible = False 'Check for completeness If cmbConcernCargo.Text = "" Then MsgBox "You forgot to select the security concern" FrameConcernCargo.Visible = True End If ' Find next available row NextRow = Sheets("Recap").Range("a53").Row + _ Sheets("Recap").Range("a53").CurrentRegion.Rows.Co unt ' Transfer the concern Sheets("Recap").Cells(NextRow, 4) = cmbConcernCargo ' Reset the Userform for the next row cmbConcernCargo = "" End Sub I attached two screenshots for reference --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's wrong with my NextRow code?
Hi John:
Thanks very much for your advice and the code! The "NewRow" work perfectly now but I noticed that the text (relatively long) I put i column D always move 1 line down. I believe it is the length of th text that cause the problem. I wonder how can I format both the cel and the text so that they will fit nicely together. Thanks in advance. Ale -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's wrong with my NextRow code?
Alex,
column D always move 1 line down I'm not sure what you mean by this. If you put text into an active or selected cell, it should be there (not one line down). Text, under 256 characters, shouldn't cause any problems. Over 256, it gets truncated (but there are workarounds). If you want to send the workbook directly to me, I'll take a look at it for you. John "inbound03" wrote in message ... Hi John: Thanks very much for your advice and the code! The "NewRow" works perfectly now but I noticed that the text (relatively long) I put in column D always move 1 line down. I believe it is the length of the text that cause the problem. I wonder how can I format both the cell and the text so that they will fit nicely together. Thanks in advance. Alex --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's wrong with my NextRow code?
HI John:
I got it figured out and it works nicely. I posted the full code her to solicit your comment. Also, I hope you can help me answer th following two questions: 1. Is there anyway I can make the the cells that contain the data t adjust automatically to fit the information? 2. How can I work around the text being truncated if I have more tha 256 characters in a cell. Thanks in advance and HAPPY NEW YEAR! Alex User clicks the checkbox to activate the frame that contains the "YES" "NO", and 'N/A" option buttons: 'Select to make the options appear; disslect to hide the options Private Sub chk27_Click() If chk27 = True Then Frame27.Visible = True Else Frame27.Visible = False chk27 = False End If End Sub When user select the "NO" button: 'Action to be performed when the "No" button is clicked Private Sub opt27No_Click() Sheets("Report").Range("H177") = "No" 'Show combo box in UserForm If opt27No = True Then FrameConcernCargo.Visible = True 'Select the corresponding question number, category Corrective Action Qnum = Sheets("RiskMatrix").Range("A20") Cat = Sheets("RiskMatrix").Range("b20") Action = Sheets("RiskMatrix").Range("c20") 'Select and show the corresponding concern in the combo box i UserForm cmbConcernCargo.RowSource = "RiskMatrix!$w$2:$w$3" Else FrameConcernCargo.Visible = False End If End Sub User will choose a concern statement and all related informatio (question number, category, cocnern, and corrective action) will poste on the next sheet starting A54 'Hide the combo box and the OK button after transferring respectiv concern to "Recap" Private Sub cmdCargoEnter_Click() 'Hide the frame FrameConcernCargo.Visible = False 'Check for completeness If cmbConcernCargo.Text = "" Then MsgBox "Please select the security concern" FrameConcernCargo.Visible = True Exit Sub End If ' Find next available row NextRow = Sheets("Recap").Range("a5000").End(xlUp).Row + 1 ' Transfer the data Sheets("Recap").Cells(NextRow, 1) = Qnum Sheets("RiskMatrix").Range(Qnum) Sheets("Recap").Cells(NextRow, 2) = Cat Sheets("RiskMatrix").Range(Cat) Sheets("Recap").Cells(NextRow, 4) = cmbConcernCargo.Text & Chr(15) Sheets("Recap").Cells(NextRow, 7) = Action Sheets("RiskMatrix").Range(Action) ' Reset the Userform for the next row cmbConcernCargo = "" End Su -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is wrong with this code? | Excel Discussion (Misc queries) | |||
What is wrong with the code? | Excel Discussion (Misc queries) | |||
Can someone tell me what is wrong with this code? | Excel Discussion (Misc queries) | |||
What's wrong with my code ? | Excel Worksheet Functions | |||
Is something wrong with the code | Excel Programming |