What's wrong with my NextRow code?

  • Thread starter Thread starter inbound03
  • Start date Start date
I

inbound03

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.Count

' 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
 
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
 
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
 
Alex,
column D always move 1 line down
Click to expand...
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
 
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
 
Back
Top