Telefonszámunk: 1-472-0679

[Resolved] VBA képlet beillesztése

Kezdőlap › Fórumok › Excel programozás › [Resolved] VBA képlet beillesztése

Topic Resolution: Resolved
12 bejegyzés megtekintése - 1-12 / 12
  • Szerző
    Bejegyzés
  • 2021-02-11-14:42 #8249
    Bereth
    Felhasználó

      Sziasztok!

      A következő probléma megoldásában szeretném a segítségeteket kérni. Adott egy űrlap ami a táblázat utolsó üres sorába rögzíti az adatokat. Ez tökéletesen működik. Viszont az egyik oszlopcellái képletet tartalmaznak ami a termékhez tartozó adatot keressne vissza egy másik táblából. Ez pediglen a következő:

      .Cells(iRow, 9).FormulaArray = „=iferror(vlookup(&irow,9&;Segédtáblák!$I$4:$J$174;2;0);”0″)”

      Ám fordításkor a HAHIBA Függvény teljesülésekor megadott értékre „0”-ra hibát ír ki. end of statement
      Mit kell másképp beírnom hogy a fenti függvényt autómatikusan hozzáadja a következő üres cellához?

      • A témakör módosításra került: 3 years, 2 months telt el-Bereth.
      • A témakör módosításra került: 3 years, 2 months telt el-horvimi.
      2021-02-11-15:25 #8251
      horvimi
      Adminisztrátor

        Szia!

        Több dolog is problémás.
        Az első, hogy a képlet ugye egy szöveg, és ebbe szúrsz be változó részeket.
        Ezt úgy kell csinálni, hogy meg kell szakítani a változó előtt a konstans stringet, befűzni a változót, és folytatni a stringet.

        Kb így:

        .Cells(iRow, 9).FormulaArray = "=iferror(vlookup(" & irow,9 & ";Segédtáblák!$I$4:$J$174;2;0);"0")"

        Második, hogy a vlookup első paramétere egy db érték szokott lenni, én nem tudom értelmezni az irow,9 részt ott. Tulajdonképpen mit is keresel? A hol keressen rész az jól meg van adva. Ugyan fix címzéssel, ami szintén felvet jövőbeli problémát, ha a Segédtáblák lapon az I:J oszlopokban több adat lesz valaha, mint most, a 174. sorig.

        Harmadik, hogy szerintem ez nem egy tömbképlet, a formulaArray nem indokolt, elég a formula is.

        És lesz majd még egy negyedik is, hogy az iferror végén miért van a nulla idézőjelben megadva?
        Ha így akarod, akkor újabb zavar keletkezik az erőben, mert a mulla előtti macskaköröm lezárja a képlet-stringet.
        Erre van megoldás, de eőbb az előzőeket kell rendbetenned.

        Imre

        2021-02-11-16:05 #8252
        Bereth
        Felhasználó

          A teljes kód ez lenne:

          Private Sub btnRogzites_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Készlet")

Első üres sor megkeresése
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

 Ha az eszköz neve mező nincs kitöltve nem enged tovább

If Trim(Me.eszkoz_neve.Value) = "" Then
  Me.eszkoz_neve.SetFocus
  MsgBox "Minden mező kitöltése kötelező!"
  Exit Sub
End If

 táblázat kitöltése

With ws
  .Cells(iRow, 3).Value = Me.eszköz_neve.Value
  .Cells(iRow, 4).Value = Me.tbox_kod.Value
  .Cells(iRow, 6).Value = "41"
  .Cells(iRow, 7).Value = Me.tboxKezdokeszlet.Value
  
  .Cells(iRow, 9).FormulaArray = "=iferror(vlookup(&iRow,3&;Segédtáblák!$I$4:$J$174;2;0);"0")"
  
  
  .Cells(iRow, 10).Value = Me.tboxMennyisegegysege.Value
  .Cells(iRow, 12).Value = Me.tboxRendelesi_mennyiseg.Value
  .Cells(iRow, 13).Value = Me.tboxMinimum_keszlet.Value
  .Cells(iRow, 14).Value = Me.tboxMegjegyzes.Value

End With

End Sub

          a cellában ez szerepel eredetileg, amit minden egyes alkalommal a következő üres sorba kellene beírnia, tehát a c41+i. sorba

          =HAHIBA(FKERES(C41;Segédtáblák!$I$4:$J$174;2;0);"0")
          A C41 mezőben lévő értéket megkeresi a segédtáblában és az ahhoz tartozó 2. cellában lévő értéket adja vissza. ha az üres akkor „0” értéket adja vissza.
          ugyan is a kapott értéket később hozzáadjuk egy másikhoz.
          iRaw, 3 ezt helyettesíti (viszont ezt véletlenül 9-re írtam).

          • A hozzászólás módosításra került: 3 years, 2 months telt el-Bereth.
          • A hozzászólás módosításra került: 3 years, 2 months telt el-Bereth.
          2021-02-11-16:13 #8255
          horvimi
          Adminisztrátor

            A Vlookup-ban is kell a .Cells(iRow,3), ez már csak egy értéket fog oda tenni.

            Tehát helyesen (szerintem, nem teszteltem)

            .Cells(iRow, 9).Formula = "=iferror(vlookup(" & .Cells(iRow,3) & ";Segédtáblák!$I$4:$J$174;2;0);0)"

            Az iferror nulláját nem tesszük idézőjelbe!

            Imre

            2021-02-11-16:18 #8256
            verax
            Felhasználó

              Sziasztok!
              Ajánlom az alábbi képletet szíves felhasználásra: „=IFERROR(VLOOKUP(INDIRECT(ADDRESS(irow,9,1)),Segédtáblák!R4C9:R174C10,2),””0″”)”

              üdvözlettel
              verax

              2021-02-11-16:21 #8257
              verax
              Felhasználó

                csak a lényeg maradt ki 🙂 a változó nevét & jelekkel kell a két szövegrészlet közé befűzni:
                „=IFERROR(VLOOKUP(INDIRECT(ADDRESS(„&irow&”,9,1)),Segéd!R4C9:R174C10,2),””0″”)”

                2021-02-11-16:25 #8258
                verax
                Felhasználó

                  … és még a szóközökről is illik gondoskodni … 🙂
                  „=IFERROR(VLOOKUP(INDIRECT(ADDRESS(” & irow & „,9,1)),Segéd!R4C9:R174C10,2),””0″”)”

                  és ez végleges 🙂

                  üdv
                  verax

                  2021-02-11-18:32 #8259
                  horvimi
                  Adminisztrátor

                    Verax!

                    ha kódot teszel fel, lszi tedd „code” tagek közé, hogy nem formázott szövegként (preformatted) jelenjen meg, és akkor copy/paste-el lehet másolni, nem lesznek rosszak pl. az idézőjelek. Ez egy kezdőnek megfejthetetlen probléma.

                    Csak kijelölöd a kódrészletet, és megnyomod fent a code gombot.

                    Köszi!

                    Imre

                    2021-02-11-18:34 #8260
                    horvimi
                    Adminisztrátor

                      Az INDIREKT meg Volatile, úgyhogy csak óvatosan vele!

                      2021-02-11-22:51 #8261
                      verax
                      Felhasználó

                        🙂 Mi végleges a világon? 🙂

                        Itt egy másik megoldás INDIREKT() nélkül – hiszen a „C” oszlop a Cells(iRow,3) kifejezésben tulajdonképpen állandó.

                        .Cells(iRow, 9).Formula = "=IFERROR(VLOOKUP($C" & iRow & ",Segédtáblák!$I$4:$J$174,2,0),0)"

                        illetve, ha ragaszkodsz a „0” hiba esetén kimeneti értékhez, akkor kettőzött idézőjelet kell használni – amint az a korábbi javaslatomban is látszik:

                        .Cells(iRow, 9).Formula = "=IFERROR(VLOOKUP($C" & iRow & ",Segédtáblák!$I$4:$J$174,2,0),""0"")"

                        Ami fontos, hogy tagoló karakterekként itt vesszőt(,) kell használni és nem pontosvesszőt(;)!

                        üdv
                        verax

                        2021-02-12-00:20 #8262
                        horvimi
                        Adminisztrátor

                          2013-as Exceltől létezik a FormulaLocal, amiben már lehet használni pontosvesszőt is, ha magyar rendszeren dolgozik.
                          Én nem szoktam ezt javasolni…

                          Imre

                          2021-02-12-01:41 #8263
                          verax
                          Felhasználó

                            Ez számomra is egy értékes információ! Erről sem tudtam eddig. (Köszönöm! 🙂 )
                            Még jobb, hogy már a 2007-es változatban is elérhető.
                            Ha valaki magyar nyelvű Excel környezetben a képletmegadás ezen módját szeretné használni, akkor viszont célszerű az alábbiak szerint fogalmazni:

                            .Cells(iRow, 9).FormulaLocal = "=HAHIBA(FKERES($C" & iRow & ";Segédtáblák!$I$4:$J$174;2;0);""0"")"

                            üdv
                            verax

                          • Szerző
                            Bejegyzés
                          12 bejegyzés megtekintése - 1-12 / 12
                          • Be kell jelentkezni a hozzászóláshoz.