Posts by heinmiller

    I want to add 4 IF statements to an existing vlookup in my VBA code, but Im having a hard time figuring out how. I can write formulas, but I am very new to VBA
    The formulas are
    =IF(B1=B2,"Y","N")
    =IF(AND(K2="Y",J1<>J2),"NOT OK","OK")
    =IF(AND(K2="START BIN",OR(AND(B2=B3,L3="NOT OK"),(AND(B2=B4,L4="NOT OK")),(AND(B2=B5,L5="NOT OK")),(AND(B2=B6,L6="NOT OK")),(AND(B2=B7,L7="NOT OK")))),"MISMATCH"," ")
    =IF(OR(M2="MISMATCH",AND(B2=B1,N1="SELECT BIN")),"SELECT BIN"," ")


    Each formula will start on row 2 (same sheet) and I want to populate all the way down the column where data is present (each import has different # of rows)


    my vlookup code from one sheet to another below works great, but I cant figure out where to start to add the if statements. Any help would be appreciated.


    Sub formulas()
    With Sheets("Sheet1").Range("G2", Sheets("Sheet").Cells(Rows.Count, "G").End(xlUp))
    .Offset(, 3).Formula = "=VLOOKUP(G" & .Row & ",'sheet2'!$A:$D,3,FALSE)"
    .Offset(, 3).Value = .Offset(, 3).Value
    End With
    End Sub

    SOLVED - and it was a simple mistake. I set the macro to open instead of import, so it was creating a new file instead of staying in the one I wanted.


    I have a Excel workbook with multiple sheets and a macro that opens and formats at txt file, but it doesn't bring the data back into my spreadsheet - it opens a new text file. I thought I could copy the data and paste it back top my spreadsheet, but I can only get as far as coping. I cant seem to find a way to paste it back. My original file name is MAX.xlsm and the sheet I want populated is Sheet1

    I need to insert a cost based on 2 sets of criteria. If the disc type is "cd" and the qty is less than 5,000 then $1 - if its over 5,000, the $2. If the disc type is "dvd" and the qty is less than 5,000 then $3 - if its over 5,000, the $4. I cant seem to get everything right and need help!


    [TABLE="width: 192"]

    [tr]


    [TD="class: xl63, width: 64"]Quantity [/TD]
    [TD="class: xl63, width: 64"] Disc Type[/TD]
    [TD="class: xl64, width: 64"] $$ [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, align: right"]1500[/TD]
    [TD="class: xl65"] CD[/TD]
    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, align: right"]1700[/TD]
    [TD="class: xl65"] CD[/TD]
    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, align: right"]500[/TD]
    [TD="class: xl65"] CD[/TD]
    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, align: right"]7400[/TD]
    [TD="class: xl65"] dvd[/TD]
    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, align: right"]2800[/TD]
    [TD="class: xl65"] CD[/TD]
    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl65, align: right"]3100[/TD]
    [TD="class: xl65"] CD[/TD]
    [TD="class: xl66"] [/TD]

    [/tr]


    [/TABLE]

    I have a spreadsheet with over 500 Product numbers and I want to find all of the box numbers associated with each Product Number from a separate file. The file I am looking up from is set up as below with the products in the same cell separated by comma.
    [TABLE="width: 426"]

    [tr]


    [td]


    Order#

    [/td]


    [td]

    Box #

    [/td]


    [td]

    Products

    [/td]


    [/tr]


    [tr]


    [td]

    192334

    [/td]


    [td]

    -10

    [/td]


    [td]

    4098,4259,4324,4399,4434,4458,4460,4485

    [/td]


    [/tr]


    [tr]


    [td]

    194389

    [/td]


    [td]

    14

    [/td]


    [td]

    4098,4259,4328,4434,4454,4485

    [/td]


    [/tr]


    [tr]


    [td]

    195779

    [/td]


    [td]

    19

    [/td]


    [td]

    3574,4098,4259,4350,4434,4458,4460,4485

    [/td]


    [/tr]


    [tr]


    [td]

    197348

    [/td]


    [td]

    24

    [/td]


    [td]

    3200,3244,4324,4350,4399

    [/td]


    [/tr]


    [tr]


    [td]

    197911

    [/td]


    [td]

    3

    [/td]


    [td]

    4098,4259,4357,4398,4434,4485

    [/td]


    [/tr]


    [tr]


    [td]

    194747

    [/td]


    [td]

    2

    [/td]


    [td]

    4098,4259,4357,4434,4454,4485

    [/td]


    [/tr]


    [/TABLE]


    Product number 4098 should return Box Numbers -10, 14, 19, 3, and 2.
    First - do I need to put each product number in its own cell (there can be as many as 30) and second, how can I obtain multiple results? Everything I try will just give me the first instance and that is only when there is just one number in the cell.forum.ozgrid.com/index.php?attachment/64813/

    Hopefully i can explain what I need. I have a list of item numbers (A) with amounts of orders (B) and locations (C). How can I look up D1402 to return how many times column B is 1 and column C is between 6-10? For example my first search of D1402 should return 2, D1403 should return 0


    [TABLE="width: 271"]

    [tr]


    [td]

    A
    D1402

    [/td]


    [TD="align: right"]B
    1[/TD]
    [TD="align: right"]C
    7[/TD]

    [/tr]


    [tr]


    [td]

    D1402

    [/td]


    [TD="align: right"]1[/TD]
    [TD="align: right"]9[/TD]

    [/tr]


    [tr]


    [td]

    D1403

    [/td]


    [TD="align: right"]2[/TD]
    [TD="align: right"]7[/TD]

    [/tr]


    [tr]


    [td]

    D1403

    [/td]


    [TD="align: right"]1[/TD]
    [TD="align: right"]93[/TD]

    [/tr]


    [tr]


    [td]

    D1403

    [/td]


    [TD="align: right"]1[/TD]
    [TD="align: right"]54[/TD]

    [/tr]


    [tr]


    [td]

    D1403

    [/td]


    [TD="align: right"]1[/TD]
    [TD="align: right"]5[/TD]

    [/tr]


    [tr]


    [td]

    D1403

    [/td]


    [TD="align: right"]1[/TD]
    [TD="align: right"]31[/TD]

    [/tr]


    [tr]


    [td]

    D1404

    [/td]


    [TD="align: right"]1[/TD]
    [TD="align: right"]4[/TD]

    [/tr]


    [/TABLE]