Makro für Preisübernahme

Hallo, ich hoffe es gibt hier ein paar Excel-Asse die mir weiterhelfen können.
Also ich habe ein kfm. Programm zur Auftragsbearbeitung. Nun möchte ich in dieses ein paar Artikel einpflegen und zwar habe ich hierfür viele Excelpreislisten mit einer Preismatrix. Wenn ich nun einen Preis suche und hierfür die Breite und Höhe in meinem kfm. Programm eingebe sollte das Programm auf die hinterlegte Excel-Preisliste springen und dann sich den Preis herraussuchen.

kurzes Beispiel.
ich brauche eine Tür die ist 1000 x 2000 mm groß, das gebe ich in mein kfm. Programm ein. Jetzt sollte das Programm auf die Excel Tabelle zugreifen, die man lt. Hersteller hinterlegen kann und sich den Preis herraussuchen. Der PC muss jetzt den Preis finden der bis zum jeweiligen Rastermass passt. Also in diesem Fall gilt das Raster Breite bis 1010 mm und Höhe bis 2010 mm. Der Preis wäre in diesem Fall also 443,-

Hoffe mir kann jemand helfen....

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Sub test() Dim i As Integer Dim j As Integer i = 9 Do Until Cells(i, 2).Value > Range("B1").Value i = i + 1 Loop j = 3 Do Until Cells(8, j).Value > Range("A1").Value j = j + 1 Loop Range("A2").Value = Cells(8, j).Value Range("B2").Value = Cells(i, 2).Value End Sub

Hallo Daniel,

danke für deine Schnelle Antwort, aber mein Problem ist damit noch nicht gelöst.
Ich versuche dir die Problematik noch mal genauers zu schildern.

Mein Kfm. Programm hat eine Eingabemaske wo ich meine Breite und Höhe eingeben muss.
So der nächste Schritt wäre dass ich meinem Programm sagen muss durch irgendein makro er soll ein bestimmte Excel Preisliste dazu aussuchen und mit den Werten die ich in mein Progamm eingegeben habe den dazugehörigen Preis aus meiner Excel Tabelle auslesen, und dann natürlich auch in mein Programm übergen.

Das Programm bezeichnet die Breite als P_Value1 und die Höhe als P_Value 2. Der Hersteller hat mir ein Makro gegeben, aber bei diesem muss ich die Beträge direkt in das Makro eingeben, da ich aber mehrere Preislisten habe, sollte das Makro direkt auf die entsprechende Excel-Preisliste zugreifen. Da unser Hersteller aber keine Ahnung vom Excel hat muss ich mich eben an euch wenden.

Das Makro das er mir geschickt hat sieht so aus.

Das Programm bezeichnet die Breite als P_Value1 und die Höhe als P_Value 2.WS_LP ist der gesuchte Preis.

IF P_VALUE1 <= 1635 AND P_VALUE2 <= 1885 THEN WS_LP = 0
IF P_VALUE1 <= 1635 AND P_VALUE2 <= 1760 THEN WS_LP = 0
IF P_VALUE1 <= 1635 AND P_VALUE2 <= 1635 THEN WS_LP = 0
IF P_VALUE1 <= 1635 AND P_VALUE2 <= 1510 THEN WS_LP = 451
IF P_VALUE1 <= 1635 AND P_VALUE2 <= 1385 THEN WS_LP = 433
IF P_VALUE1 <= 1635 AND P_VALUE2 <= 1260 THEN WS_LP = 412
IF P_VALUE1 <= 1635 AND P_VALUE2 <= 1135 THEN WS_LP = 393
IF P_VALUE1 <= 1635 AND P_VALUE2 <= 1010 THEN WS_LP = 375
IF P_VALUE1 <= 1635 AND P_VALUE2 <= 885 THEN WS_LP = 358
IF P_VALUE1 <= 1635 AND P_VALUE2 <= 760 THEN WS_LP = 343
IF P_VALUE1 <= 1635 AND P_VALUE2 <= 635 THEN WS_LP = 327
IF P_VALUE1 <= 1635 AND P_VALUE2 <= 510 THEN WS_LP = 315

IF P_VALUE1 <= 1510 AND P_VALUE2 <= 1885 THEN WS_LP = 0
IF P_VALUE1 <= 1510 AND P_VALUE2 <= 1760 THEN WS_LP = 0
IF P_VALUE1 <= 1510 AND P_VALUE2 <= 1635 THEN WS_LP = 447
IF P_VALUE1 <= 1510 AND P_VALUE2 <= 1510 THEN WS_LP = 432
IF P_VALUE1 <= 1510 AND P_VALUE2 <= 1385 THEN WS_LP = 415
IF P_VALUE1 <= 1510 AND P_VALUE2 <= 1260 THEN WS_LP = 395
IF P_VALUE1 <= 1510 AND P_VALUE2 <= 1135 THEN WS_LP = 375
IF P_VALUE1 <= 1510 AND P_VALUE2 <= 1010 THEN WS_LP = 359
IF P_VALUE1 <= 1510 AND P_VALUE2 <= 885 THEN WS_LP = 344
IF P_VALUE1 <= 1510 AND P_VALUE2 <= 760 THEN WS_LP = 330
IF P_VALUE1 <= 1510 AND P_VALUE2 <= 635 THEN WS_LP = 314
IF P_VALUE1 <= 1510 AND P_VALUE2 <= 510 THEN WS_LP = 301

IF P_VALUE1 <= 1385 AND P_VALUE2 <= 1885 THEN WS_LP = 452
IF P_VALUE1 <= 1385 AND P_VALUE2 <= 1760 THEN WS_LP = 0
IF P_VALUE1 <= 1385 AND P_VALUE2 <= 1635 THEN WS_LP = 429
IF P_VALUE1 <= 1385 AND P_VALUE2 <= 1510 THEN WS_LP = 413
IF P_VALUE1 <= 1385 AND P_VALUE2 <= 1385 THEN WS_LP = 394
IF P_VALUE1 <= 1385 AND P_VALUE2 <= 1260 THEN WS_LP = 376
IF P_VALUE1 <= 1385 AND P_VALUE2 <= 1135 THEN WS_LP = 358
IF P_VALUE1 <= 1385 AND P_VALUE2 <= 1010 THEN WS_LP = 339
IF P_VALUE1 <= 1385 AND P_VALUE2 <= 885 THEN WS_LP = 328
IF P_VALUE1 <= 1385 AND P_VALUE2 <= 760 THEN WS_LP = 316
IF P_VALUE1 <= 1385 AND P_VALUE2 <= 635 THEN WS_LP = 302
IF P_VALUE1 <= 1385 AND P_VALUE2 <= 510 THEN WS_LP = 289

IF P_VALUE1 <= 1260 AND P_VALUE2 <= 1885 THEN WS_LP = 432
IF P_VALUE1 <= 1260 AND P_VALUE2 <= 1760 THEN WS_LP = 423
IF P_VALUE1 <= 1260 AND P_VALUE2 <= 1635 THEN WS_LP = 404
IF P_VALUE1 <= 1260 AND P_VALUE2 <= 1510 THEN WS_LP = 391
IF P_VALUE1 <= 1260 AND P_VALUE2 <= 1385 THEN WS_LP = 374
IF P_VALUE1 <= 1260 AND P_VALUE2 <= 1260 THEN WS_LP = 353
IF P_VALUE1 <= 1260 AND P_VALUE2 <= 1135 THEN WS_LP = 337
IF P_VALUE1 <= 1260 AND P_VALUE2 <= 1010 THEN WS_LP = 321
IF P_VALUE1 <= 1260 AND P_VALUE2 <= 885 THEN WS_LP = 311
IF P_VALUE1 <= 1260 AND P_VALUE2 <= 760 THEN WS_LP = 301
IF P_VALUE1 <= 1260 AND P_VALUE2 <= 635 THEN WS_LP = 288
IF P_VALUE1 <= 1260 AND P_VALUE2 <= 510 THEN WS_LP = 277

IF P_VALUE1 <= 1135 AND P_VALUE2 <= 1885 THEN WS_LP = 412
IF P_VALUE1 <= 1135 AND P_VALUE2 <= 1760 THEN WS_LP = 406
IF P_VALUE1 <= 1135 AND P_VALUE2 <= 1635 THEN WS_LP = 387
IF P_VALUE1 <= 1135 AND P_VALUE2 <= 1510 THEN WS_LP = 372
IF P_VALUE1 <= 1135 AND P_VALUE2 <= 1385 THEN WS_LP = 356
IF P_VALUE1 <= 1135 AND P_VALUE2 <= 1260 THEN WS_LP = 333
IF P_VALUE1 <= 1135 AND P_VALUE2 <= 1135 THEN WS_LP = 312
IF P_VALUE1 <= 1135 AND P_VALUE2 <= 1010 THEN WS_LP = 300
IF P_VALUE1 <= 1135 AND P_VALUE2 <= 885 THEN WS_LP = 291
IF P_VALUE1 <= 1135 AND P_VALUE2 <= 760 THEN WS_LP = 285
IF P_VALUE1 <= 1135 AND P_VALUE2 <= 635 THEN WS_LP = 275
IF P_VALUE1 <= 1135 AND P_VALUE2 <= 510 THEN WS_LP = 266

IF P_VALUE1 <= 1010 AND P_VALUE2 <= 1885 THEN WS_LP = 391
IF P_VALUE1 <= 1010 AND P_VALUE2 <= 1760 THEN WS_LP = 378
IF P_VALUE1 <= 1010 AND P_VALUE2 <= 1635 THEN WS_LP = 361
IF P_VALUE1 <= 1010 AND P_VALUE2 <= 1510 THEN WS_LP = 350
IF P_VALUE1 <= 1010 AND P_VALUE2 <= 1385 THEN WS_LP = 332
IF P_VALUE1 <= 1010 AND P_VALUE2 <= 1260 THEN WS_LP = 318
IF P_VALUE1 <= 1010 AND P_VALUE2 <= 1135 THEN WS_LP = 297
IF P_VALUE1 <= 1010 AND P_VALUE2 <= 1010 THEN WS_LP = 281
IF P_VALUE1 <= 1010 AND P_VALUE2 <= 885 THEN WS_LP = 272
IF P_VALUE1 <= 1010 AND P_VALUE2 <= 760 THEN WS_LP = 266
IF P_VALUE1 <= 1010 AND P_VALUE2 <= 635 THEN WS_LP = 256
IF P_VALUE1 <= 1010 AND P_VALUE2 <= 510 THEN WS_LP = 248

IF P_VALUE1 <= 885 AND P_VALUE2 <= 1885 THEN WS_LP = 373
IF P_VALUE1 <= 885 AND P_VALUE2 <= 1760 THEN WS_LP = 360
IF P_VALUE1 <= 885 AND P_VALUE2 <= 1635 THEN WS_LP = 347
IF P_VALUE1 <= 885 AND P_VALUE2 <= 1510 THEN WS_LP = 334
IF P_VALUE1 <= 885 AND P_VALUE2 <= 1385 THEN WS_LP = 319
IF P_VALUE1 <= 885 AND P_VALUE2 <= 1260 THEN WS_LP = 305
IF P_VALUE1 <= 885 AND P_VALUE2 <= 1135 THEN WS_LP = 286
IF P_VALUE1 <= 885 AND P_VALUE2 <= 1010 THEN WS_LP = 270
IF P_VALUE1 <= 885 AND P_VALUE2 <= 885 THEN WS_LP = 262
IF P_VALUE1 <= 885 AND P_VALUE2 <= 760 THEN WS_LP = 256
IF P_VALUE1 <= 885 AND P_VALUE2 <= 635 THEN WS_LP = 246
IF P_VALUE1 <= 885 AND P_VALUE2 <= 510 THEN WS_LP = 239

IF P_VALUE1 <= 760 AND P_VALUE2 <= 1885 THEN WS_LP = 354
IF P_VALUE1 <= 760 AND P_VALUE2 <= 1760 THEN WS_LP = 343
IF P_VALUE1 <= 760 AND P_VALUE2 <= 1635 THEN WS_LP = 329
IF P_VALUE1 <= 760 AND P_VALUE2 <= 1510 THEN WS_LP = 319
IF P_VALUE1 <= 760 AND P_VALUE2 <= 1385 THEN WS_LP = 306
IF P_VALUE1 <= 760 AND P_VALUE2 <= 1260 THEN WS_LP = 293
IF P_VALUE1 <= 760 AND P_VALUE2 <= 1135 THEN WS_LP = 283
IF P_VALUE1 <= 760 AND P_VALUE2 <= 1010 THEN WS_LP = 263
IF P_VALUE1 <= 760 AND P_VALUE2 <= 885 THEN WS_LP = 254
IF P_VALUE1 <= 760 AND P_VALUE2 <= 760 THEN WS_LP = 245
IF P_VALUE1 <= 760 AND P_VALUE2 <= 635 THEN WS_LP = 237
IF P_VALUE1 <= 760 AND P_VALUE2 <= 510 THEN WS_LP = 230

IF P_VALUE1 <= 635 AND P_VALUE2 <= 1885 THEN WS_LP = 335
IF P_VALUE1 <= 635 AND P_VALUE2 <= 1760 THEN WS_LP = 325
IF P_VALUE1 <= 635 AND P_VALUE2 <= 1635 THEN WS_LP = 312
IF P_VALUE1 <= 635 AND P_VALUE2 <= 1510 THEN WS_LP = 301
IF P_VALUE1 <= 635 AND P_VALUE2 <= 1385 THEN WS_LP = 290
IF P_VALUE1 <= 635 AND P_VALUE2 <= 1260 THEN WS_LP = 278
IF P_VALUE1 <= 635 AND P_VALUE2 <= 1135 THEN WS_LP = 269
IF P_VALUE1 <= 635 AND P_VALUE2 <= 1010 THEN WS_LP = 251
IF P_VALUE1 <= 635 AND P_VALUE2 <= 885 THEN WS_LP = 242
IF P_VALUE1 <= 635 AND P_VALUE2 <= 760 THEN WS_LP = 235
IF P_VALUE1 <= 635 AND P_VALUE2 <= 635 THEN WS_LP = 226
IF P_VALUE1 <= 635 AND P_VALUE2 <= 510 THEN WS_LP = 219

IF P_VALUE1 <= 510 AND P_VALUE2 <= 1885 THEN WS_LP = 317
IF P_VALUE1 <= 510 AND P_VALUE2 <= 1760 THEN WS_LP = 307
IF P_VALUE1 <= 510 AND P_VALUE2 <= 1635 THEN WS_LP = 297
IF P_VALUE1 <= 510 AND P_VALUE2 <= 1510 THEN WS_LP = 286
IF P_VALUE1 <= 510 AND P_VALUE2 <= 1385 THEN WS_LP = 275
IF P_VALUE1 <= 510 AND P_VALUE2 <= 1260 THEN WS_LP = 266
IF P_VALUE1 <= 510 AND P_VALUE2 <= 1135 THEN WS_LP = 258
IF P_VALUE1 <= 510 AND P_VALUE2 <= 1010 THEN WS_LP = 242
IF P_VALUE1 <= 510 AND P_VALUE2 <= 885 THEN WS_LP = 235
IF P_VALUE1 <= 510 AND P_VALUE2 <= 760 THEN WS_LP = 227
IF P_VALUE1 <= 510 AND P_VALUE2 <= 635 THEN WS_LP = 217
IF P_VALUE1 <= 510 AND P_VALUE2 <= 510 THEN WS_LP = 207

if P_Value1 = 0 or p_Value2 = 0 then begin
WS_LP = 0
message "Bei Breite und Höhe müssen Werte eingegeben werden!"
endif
if P_Value1 > 1635 then message "Die Breite darf maximal 1635 mm betragen!"
if P_Value2 > 1885 then message "Die Höhe darf maximal 1885 mm betragen!"
if WS_LP = 0 then message "Es konnte kein Preis ermittelt werden!"

end

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

300 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Makro für Preisübernahme

2 Beiträge zum Forumthread Beiträge zu diesem Forumthread

300 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread