excel-VBA button another code

Question

I'm working on this excel project and I wanted to ask you about this part of the VBA button procedure. I'm totally beginner with VBA, I just learned about variables. Can someone find any other procedure for this button something like loop procedure. Button is doing its jobs well but I did not know this procedure well established or there is any other like loop.

Here is my VBA button code and my excel file

OK here is my code

Sub Button2_Click()
Dim emri As String, nje As Integer, dy As Integer, tre As Integer
Dim kater As Integer, pese As Integer, gjashte As Integer
Dim shtate As Integer, tete As Integer, nente As Integer


  emri = Range("B5").Value
  nje = Range("B6").Value
  dy = Range("F6").Value
  tre = Range("F7").Value
  kater = Range("F8").Value
  pese = Range("F9").Value

  gjashte = nje + dy
  shtate = nje + tre
  tete = nje + kater
  nente = nje + pese

If UCase(Range("B5").Value) = UCase(Range("D6").Value) Then

  Range("F6").Value = gjashte

ElseIf UCase(Range("B5").Value) = UCase(Range("D7").Value) Then

  Range("F7").Value = shtate

ElseIf UCase(Range("B5").Value) = UCase(Range("D8").Value) Then

  Range("F8").Value = tete

ElseIf UCase(Range("B5").Value) = UCase(Range("D9").Value) Then

  Range("F9").Value = nente

End If

Range("A12").Value = emri
Range("A13").Value = nje

Range("B5", "B6").ClearContents


End Sub

Could you add the code as actual code in the question? That way people could copy it to test or amend your solution. — EWit
– EWit, Commented Aug 7, 2014 at 9:27
I have removed the picture which showed the code. Please add the code and not the picture showing the code. — Siddharth Rout
– Siddharth Rout, Commented Aug 7, 2014 at 9:33
lowak if i sold today a BMW at sold section i choose BMW and quantity sold e.g. 2 or 3 etc and if i click sold button i would like to add range("B6") and range("F6") and put the result at the range("F6"). Or if i choose ferari at product description and for quantity sold e.g. 4 and after i click sold button i also like to add range("B6") and range("F9") and put the result on range("F9"). And so on. :) — Mongol-genius
– Mongol-genius, Commented Aug 7, 2014 at 10:13
Okay. Is there any reason behind coping entered data to Cells A12 and A13? And how is this possible like you sold more Ferraris than you actually had? :) — lowak
– lowak, Commented Aug 7, 2014 at 10:21

lowak · Accepted Answer · 2014-08-07 14:44:09Z

1

Okay, so lets try this code. It loops through columns D from 6th to 16th row. If entered value (changed to Uppercase) matches value from list it makes plus to sold cars and minus to quantity left (you may have a function there so it's just an optional thing). Everything else stays almost the same.

Sub Button2_Click()

Dim emri As String, nje As Integer


  emri = UCase(Range("B5").Value)
  nje = Range("B6").Value


For i = 6 To 16 'you can also set variable for checking lastrow
    If emri = Cells(i, 4).Value Then

        Cells(i, 6).Value = Cells(i, 6).Value + nje 'plus in sold cars column
        Cells(i, 7).Value = Cells(i, 7).Value - nje 'minus in quantity left
        Exit For

    End If

Next i


Range("A12").Value = emri
Range("A13").Value = nje

Range("B5", "B6").ClearContents


End Sub

answered Aug 7, 2014 at 14:44

lowak

1,2942 gold badges19 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mongol-genius Over a year ago

thank lowak :) I just tested the code and it works :)

lowak Over a year ago

So i guess it does work :) Mark this as and anwser, it may help others in future.

Mongol-genius Over a year ago

I tried such a thing, but I got such a message " Vote up requires 15 reputation " sorry :(

lowak Over a year ago

Because you tried to vote up my anwser. Try this: next to anwser there is a grey "tick". If you click it, it will become green and this means you accepted awnser.

Collectives™ on Stack Overflow

excel-VBA button another code

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related