move the next information to the row below

[Sandler]

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

 

[My Aswer Is This]

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?

 

[Michael M]

maybe this...

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

 

[dmt32]

See if following does what you want?

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

 

[Sandler]

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

 

[dmt32]

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

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

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

 

[Sandler]

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

*

A

B

C

D

E

F

G

H

I

J

O

P

Q

R

S

T

U

V

W

X

Y

15

Melissa

servings

*

Protein

*

Carbs

*

Fat

*

Calories

*

Daniel

servings

*

Protein

*

Carbs

*

Fat

*

Calories

16

Wild Blueberry Jelly - FullCircle

1

*

0

*

8

*

0

*

32

*

*

*

*

*

*

*

*

*

*

*

17

White Cheddar Corn Puffs - TJ's

2

*

6

*

42

*

10

*

282

*

*

*

*

*

*

*

*

*

*

*

18

*

*

*

*

*

*

*

*

*

*

*

WWCheeseString

3

*

18

*

0

*

7.5

*

139.5

<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>
</tbody>

 

[Sandler]

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

 

[dmt32]

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

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

 

[Sandler]

Thanks Dave, this is perfect.

I appreciate it