excel - Simplifying VBA Code -

i wrote working vba macro goes through inputed client id's , returns specified commission fee client.

the code 100% works, need have boss able add new clients special commission %'s list, without having go through vba code.

how can make simplified vba macro or type excel function code below does, make versitile special klient table?

the main excel table looks this:

the table special klients, commission fees, currency, markets, looks this:

option explicit  sub komisijas_calc_click()  'declare variables dim klienta_nr long dim isin string dim cena double dim skaits double dim komisija double dim vk string dim summa double dim ksheet worksheet  dim lngfirstrow long: lngfirstrow = 2 dim lnglastrow  long dim lngcol      long: lngcol = 2 dim lngcounter  long dim lngkom long: lngkom = 11     set ksheet = thisworkbook.sheets("spec_klienti")   worksheets(1)     lnglastrow = .cells(.rows.count, lngcol).end(xlup).row      lngcounter = lngfirstrow lnglastrow      klienta_nr = range("b" & lngcounter).value     isin = range("e" & lngcounter).value     cena = range("h" & lngcounter).value     skaits = range("i" & lngcounter).value     vk = range("d" & lngcounter).value     summa = cena * skaits   select case klienta_nr        case 111111                 '(vācija, francija, nīderlandes, itālija, Īrija) - 30 eur min                 if klienta_nr = 111111 , (left(isin, 2) = "de" or left(isin, 2) = "fr" or left(isin, 2) = "nl" or left(isin, 2) = "it" or left(isin, 2) = "ie")                     komisija = summa * 0.01                     .cells(lngcounter, lngkom) = komisija                         end if                 if klienta_nr = 111111 , komisija <= 30                     .cells(lngcounter, lngkom) = 30                         end if                   if klienta_nr = 1111111 , (left(isin, 2) <> "de" or left(isin, 2) <> "fr" or left(isin, 2) <> "nl" or left(isin, 2) <> "it" or left(isin, 2) <> "ie")                     komisija = summa * 0.003                     if komisija >= 40                         .cells(lngcounter, lngkom) = 40                             end if                         end if       case 2222222                 '(vācija, francija, nīderlandes, itālija, Īrija) - 30 eur min                 if klienta_nr = 222222 , (left(isin, 2) = "de" or left(isin, 2) = "fr" or left(isin, 2) = "nl" or left(isin, 2) = "it" or left(isin, 2) = "ie")                     komisija = summa * 0.01                     .cells(lngcounter, lngkom) = komisija                         end if                 'set 30 eur min                 if klienta_nr = 2222222 , komisija <= 30                     .cells(lngcounter, lngkom) = 30                         end if                  if klienta_nr = 2 , (left(isin, 2) <> "de" or left(isin, 2) <> "fr" or left(isin, 2) <> "nl" or left(isin, 2) <> "it" or left(isin, 2) <> "ie")                     goto allelsefails                         end if   case else  allelsefails:                'ip2, 0.03% komisija, 40 eur/usd max                 if right(vk, 1) = 1 or right(vk, 1) = 8                     komisija = summa * 0.003                     .cells(lngcounter, lngkom) = komisija                         end if               'ip1, 0.1% komisija, 40 eur/usd max                 if right(vk, 1) = 7                     komisija = summa * 0.01                     .cells(lngcounter, lngkom) = komisija                         end if                 'komisija max 40, >=40 equals 40                 if komisija >= 40                     .cells(lngcounter, lngkom) = 40                         end if             'end if      end select    next lngcounter   end end sub