CODEWITHSUNDEEP
excelvba
Mongol-genius
Mongol-genius

Reputation: 103

excel-VBA button another code

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

http://i57.tinypic.com/16a7h9i.png

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

Upvotes: 0

Views: 199

Answers (1)

lowak
lowak

Reputation: 1284

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

Upvotes: 1

Related Questions