help to convert textbox value to negative based on another textbox value

[lewisw]

Trying to figure out the else if and not even coming close.

What I am trying to accomplish if texbox1 value = "stock: then it will take textbox1 and
fill the cell with a positive number . If textbox1 = 123456 or any number that is not "stock"
it would put that cell as negative. I need to do this for textbox 2 to textbox10

code below works to put it into the worksheet until I tried to play with the else .
As you can see totaly new to this

NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        
        If UserForm1.TextBox1.Value = "stock" Then
        .Cells(NextRw, 1).Value = UserForm1.TextBox2.Value 
        Else
        .Cells(NextRw, 1).Value = UserForm1.TextBox2.Value* -1

        .Cells(NextRw, 2).Value = Date
        End With     
        .Cells(NextRw, 3).Value = UserForm1.TextBox2.Value
        .Cells(NextRw, 4).Value = UserForm1.TextBox4.Value
        .Cells(NextRw, 5).Value = UserForm1.TextBox5.Value
        .Cells(NextRw, 6).Value = UserForm1.TextBox6.Value
        .Cells(NextRw, 7).Value = UserForm1.TextBox7.Value
        .Cells(NextRw, 8).Value = UserForm1.TextBox8.Value
        .Cells(NextRw, 9).Value = UserForm1.TextBox9.Value
        .Cells(NextRw, 10).Value = UserForm1.TextBox10.Value

Thanks in advance if anyone wishes to help me.
Lewis

[ragulduy]

if strings.left(userform1.textbox1.value,5) = "stock" then
  .cells(NextRw,1).value = userform1.textbox2.value
else
  .cells(NextRw,1).value = -1
end if

[lewisw]

Must have done something wrong as all I am getting on anything other than "stock"
is negative 1 in column 3 (instead of the negative value of what was inputted into
the userform textbox2 (But I'm a step closer it think lol.

Thanks again Ragulduy but can you see what I did wrong. Code snippet below .

Sub AddData()
    
    With Sheet1
        NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(NextRw, 1).Value = UserForm1.TextBox1.Value
        .Cells(NextRw, 2).Value = Date

       If Strings.Left(UserForm1.TextBox1.Value, 3) = "stock" Then
  .Cells(NextRw, 3).Value = UserForm1.TextBox2.Value
Else
  .Cells(NextRw, 3).Value = -1
End If
       
        .Cells(NextRw, 4).Value = UserForm1.TextBox3.Value
        .Cells(NextRw, 5).Value = UserForm1.TextBox4.Value
        .Cells(NextRw, 6).Value = UserForm1.TextBox5.Value
        .Cells(NextRw, 7).Value = UserForm1.TextBox6.Value
        .Cells(NextRw, 8).Value = UserForm1.TextBox7.Value
        .Cells(NextRw, 9).Value = UserForm1.TextBox8.Value
        .Cells(NextRw, 10).Value = UserForm1.TextBox9.Value
        .Cells(NextRw, 11).Value = UserForm1.TextBox10.Value
        
       
 MsgBox "Entry Added"
      End With
    
    End Sub

[ragulduy]

Sorry, I misread what you wanted, change:

  .Cells(NextRw, 3).Value = -1

to

  .Cells(NextRw, 3).Value = -UserForm1.TextBox2.Value

[lewisw]

oops spoke to fast just tested one with the text stock and it also came negative lol.

this is what I have now did I muck it up ?

       If Strings.Left(UserForm1.TextBox1.Value, 3) = "stock" Then
  .Cells(NextRw, 3).Value = UserForm1.TextBox2.Value
Else
  .Cells(NextRw, 3).Value = -UserForm1.TextBox2.Value
End If

[lewisw]

AMAZING thank you so much.
Now just need to copy this loop for 10 textboxes lol.

[ragulduy]

you changed:

if strings.left(userform1.textbox1.value,5) = "stock" then

to

 If Strings.Left(UserForm1.TextBox1.Value, 3) = "stock" Then

[lewisw]

only ask because when I tried to do the next set and put the value,5 it didn't work.
The first column works to be positive if "stock" appears but second column always goes negative when it says "stock"

Sub AddData()
    
    With Sheet1
        NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(NextRw, 1).Value = UserForm1.TextBox1.Value
        .Cells(NextRw, 2).Value = Date

       If Strings.Left(UserForm1.TextBox1.Value, 5) = "stock" Then
  .Cells(NextRw, 3).Value = UserForm1.TextBox2.Value
Else
  .Cells(NextRw, 3).Value = -UserForm1.TextBox2.Value
End If

If Strings.Left(UserForm1.TextBox2.Value, 5) = "stock" Then
  .Cells(NextRw, 4).Value = UserForm1.TextBox3.Value
Else
  .Cells(NextRw, 4).Value = -UserForm1.TextBox3.Value
End If

[lewisw]

oops sorry see my error. with the second set

[lewisw]

your right and it works when I put it back to 5.
Can you tell me what the value,5 means I thought it was in reference to the column number which was 3 and
of course I am wrong.

Thanks again for your time

[lewisw]

Can I ask you another question since you have helped me so much.

What if I want to have a checkbox for Stock and when you click
on the checkbox for stock it would fill in .Cells(NextRw, 1).Value with the word stock (instead of having to type it in) and continue your loop with cells being positive.




Is this complicated to do ?

[ragulduy]

Hi - the LEFT(a,b) function takes the string a and returns the first b letters. So it returns the first 5 letters of whatever is in the textbox and compares it to "stock".

I'd suggest posting another thread for your new question.

[lewisw]

btw I only get the error if I do not enter "stock" . If I enter "stock there is no error when clicking add"

[lewisw]

sorry I have some typos causing my problems. Again thanks for the help.

[lewisw]

your right I will post another thread for the other question.

I do have something happening weird now. the positive based on stock or negative based on anything else is working perfect but the code you gave me affected code elsewhere on my userform.

The userform1 was totaling the textboxes and it works fine still but as soon as I click add I get an error.

the userform code that works on userform display but mucks up your code is .

Private Sub TextBox2_Change()
TextBox11 = Val(TextBox2)
End Sub
Private Sub TextBox3_Change()
TextBox11 = Val(TextBox2) + Val(TextBox3)
End Sub
Private Sub TextBox4_Change()
TextBox11 = Val(TextBox2) + Val(TextBox3) + Val(TextBox4)
End Sub

and the error on submit goes to this line with a type mismatch.

.Cells(NextRw, 5).Value = -UserForm1.TextBox4.Value

Is there something I need to change in the userform subtotal to match your new code ?

[lewisw]

I'm still having an issue even though I double checked I have no typos.

code works as long as I start from the first column and do not skip any.
but the minute I skip a column I get a type mismatch error.

to explain :
column 1 is either a po number or the term stock (adjust numbers positive or negative)
column 2 is the date
column 3 is first size
column 4 is second size
etc

so as long as I do not skip column 1,2,3,4 it works perfect but if I add
to column 1,2 skip 3 and put a qty in column 4 it bombs.


can anyone see what I did wrong.
code below...

Dim NextRw As Long
Sub AddData()
    
    With ActiveSheet
        NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(NextRw, 1).Value = UserForm1.TextBox1.Value
        .Cells(NextRw, 2).Value = Date

If Strings.Left(UserForm1.TextBox1.Value, 5) = "stock" Then
  .Cells(NextRw, 3).Value = UserForm1.TextBox2.Value
Else
  .Cells(NextRw, 3).Value = -UserForm1.TextBox2.Value
End If

If Strings.Left(UserForm1.TextBox1.Value, 5) = "stock" Then
  .Cells(NextRw, 4).Value = UserForm1.TextBox3.Value
Else
  .Cells(NextRw, 4).Value = -UserForm1.TextBox3.Value
End If

If Strings.Left(UserForm1.TextBox1.Value, 5) = "stock" Then
   .Cells(NextRw, 5).Value = UserForm1.TextBox4.Value
Else
  .Cells(NextRw, 5).Value = -UserForm1.TextBox4.Value
End If