move the next information to the row below

S

Sandler

Board Regular
Joined
Sep 15, 2011
Messages
165
The code below shows where i want to start placing information that I get from my form.
After the information is inputted via the button click, how do i get the next information to go into
the appropriate place?

For example, when Melissa is selected the listbox value will go to A16 and the txt value will go to B16.
If Melissa continues to be selected and I pick another listbox and txt, it needs to go to A17 and B17.


Private Sub btnAdd_Click()

If opnMelissa.Value = True Then Worksheets("Our Data").Range("A16").Value = Me.lbxNutrition.Value
If opnMelissa.Value = True Then Worksheets("Our Data").Range("B16").Value = Me.txtServings.Value

If opnDaniel.Value = True Then Worksheets("Our Data").Range("P16").Value = Me.lbxNutrition.Value
If opnDaniel.Value = True Then Worksheets("Our Data").Range("Q16").Value = Me.txtServings.Value

End Sub
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Do you mean:
When Melissa is selected from the listbox the value Melissa will go to A16?
And the txt value will go to B16. What text value?
 
Upvote 0
maybe this...
Code: 
Private Sub btnAdd_Click()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
If opnMelissa.Value = True Then Worksheets("Our Data").Range("A" & lr + 1).Value = Me.lbxNutrition.Value
If opnMelissa.Value = True Then Worksheets("Our Data").Range("B" & lr + 1).Value = Me.txtServings.Value

If opnDaniel.Value = True Then Worksheets("Our Data").Range("P" & lr + 1).Value = Me.lbxNutrition.Value
If opnDaniel.Value = True Then Worksheets("Our Data").Range("Q" & lr + 1).Value = Me.txtServings.Value

End Sub
 
Upvote 0
See if following does what you want?

Code: 
Private Sub btnAdd_Click()
    Dim lr As Long, c As Long
    
    With Worksheets("Our Data")

    lr = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    If lr < 16 Then lr = 16
    
    c = IIf(opnMelissa.Value, 1, 16)
    
    .Cells(lr, c).Resize(1, 2).Value = Array(Me.lbxNutrition.Value, Me.txtServings.Value)
    
    End With


End Sub


Dave
 
Last edited:
Upvote 0
Hi dmt32, your code works perfectly for Melissa.
But I also need to add code if opnDaniel is selected. Would that be 12, 16 instead of the 1,16 for Melissa?
How can i modify the code for Daniel?

Thanks :)
 
Last edited:
Upvote 0
Sandler said:
Hi dmt32, your code works perfectly for Melissa.
But I also need to add code if opnDaniel is selected. Would that be 12, 16 instead of the 1,16 for Melissa?
How can i modify the code for Daniel?

Thanks :)
Click to expand...

Should not need any adjustment for second optionbutton Daniel - this line:

Code: 
c = IIf(opnMelissa.Value, 1, 16)

Sets Variable c to 1 (Column A) if Melissa is selected (value True) or c to 16 (Column P) if Melissa is not selected (value False)

Post back with changes you made.

Dave
 
Upvote 0
Hi Dave,
The code works perfectly for Melissa, but is a little off for Daniel.
In each instance i tried to put two different pieces of food for each individual and this was my results.
Both of Melissa's items show up, but only the last item shows up for Daniel, 3 rows down.

Our Data

*ABCDEFGHIJOPQRSTUVWXY
*
*
*
*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:309px;"><col style="width:64px;"><col style="width:64px;"><col style="width:65px;"><col style="width:19px;"><col style="width:84px;"><col style="width:19px;"><col style="width:78px;"><col style="width:19px;"><col style="width:64px;"><col style="width:37px;"><col style="width:122px;"><col style="width:64px;"><col style="width:19px;"><col style="width:61px;"><col style="width:19px;"><col style="width:64px;"><col style="width:19px;"><col style="width:70px;"><col style="width:19px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="bgcolor: #ffffcc"]Melissa[/TD]
[TD="bgcolor: #ffffcc"]servings[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc"]Protein[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc"]Carbs[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc"]Fat[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc"]Calories[/TD]

[TD="bgcolor: #ccffff"]Daniel[/TD]
[TD="bgcolor: #ccffff"]servings[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]Protein[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]Carbs[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]Fat[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]Calories[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="bgcolor: #ffffcc"]Wild Blueberry Jelly - FullCircle[/TD]
[TD="bgcolor: #ffffcc, align: right"]1[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc, align: right"]0[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc, align: right"]8[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc, align: right"]0[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc, align: right"]32[/TD]

[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="bgcolor: #ffffcc"]White Cheddar Corn Puffs - TJ's[/TD]
[TD="bgcolor: #ffffcc, align: right"]2[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc, align: right"]6[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc, align: right"]42[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc, align: right"]10[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc, align: right"]282[/TD]

[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]
[TD="bgcolor: #ffffcc"]*[/TD]

[TD="bgcolor: #ccffff"]WWCheeseString[/TD]
[TD="bgcolor: #ccffff, align: right"]3[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff, align: right"]18[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff, align: right"]0[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff, align: right"]7.5[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff, align: right"]139.5[/TD]

</tbody>
 
Upvote 0
This is the modification to the code I ended up using for it to work. I don't full understand what i did, but I guess it's right.
It is using dmt32 code and inputting some opposing items for a separate daniel part.
See code below and if someone wants to explain to me why HTML Jeanie shows asterisks in my screen code, I would appreciate it.

Private Sub btnAdd_Click()
With Worksheets("Our Data")

If Me.opnMelissa.Value Then

'you clicked on melissa....do the code for melissa

lr = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
If lr < 16 Then lr = 16

c = IIf(opnMelissa.Value, 1, 16)

.Cells(lr, c).Resize(1, 2).Value = Array(Me.lbxNutrition.Value, Me.txtServings.Value)


Else

'you clicked on daniel....do the code for daniele

dr = .Cells(.Rows.Count, "P").End(xlUp).Row + 1
If dr < 16 Then dr = 16

d = IIf(opnDaniel.Value, 16, 1)

.Cells(dr, d).Resize(1, 2).Value = Array(Me.lbxNutrition.Value, Me.txtServings.Value)


End If

End With

End Sub
 
Upvote 0
Hi,
See if this slight change to my original code does what you want:

Code: 
Private Sub btnAdd_Click()
    Dim lr As Long, c As Long
    
    With Worksheets("Our Data")
    
    c = IIf(opnMelissa.Value, 1, 16)
    
    lr = .Cells(.Rows.Count, c).End(xlUp).Row + 1
    If lr < 16 Then lr = 16
    
    .Cells(lr, c).Resize(1, 2).Value = Array(Me.lbxNutrition.Value, Me.txtServings.Value)
    
    End With


End Sub


Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
WHEN vba finds Row in header then places row number below it
Replies
5
Views
301
KDS14589
K
L
  • Question Question
VBA code for macro button not working to transfer row data to another worksheet
Replies
1
Views
279
rjwalker1973
R
M
  • Question Question
Power Query with Dynamic Data Source
Replies
4
Views
787
Alex Blakenburg
A
B
  • Question Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
552
B-radK
B
D
  • Question Question
Generating Reports in Word Using Data in Excel
Replies
1
Views
285
Dan_W
D

Forum statistics

Threads
1,223,157
Messages
6,170,419
Members
452,325
Latest member
BlahQz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top