Posts by jarko28

    Re: vlookup or if function to autofill help


    Try the attached.

    It needs the helper columns on both sheets as I can't think of another way to do it without the helper columns

    You can make copies of the sheet for all your LGE types, then type in the appropriate LGE number in cell L1 on the LGE specific sheets.

    You can hide the columns if you don't want to view them.

    Let me know if it works.

    Hi all,

    This is a conditional formatting problem I have not been able to resolve:

    I have a range, say A1:N30

    each cell contains a text/number combination.

    I would like to highlight each cell on a specific row if the vale exists within the row above - the issue is that the cell values are not in the same columns.

    I have attached a sample workbook with the desired output.

    Any help would be appreciated.


    I have been searching for hours trying to find a solution to no avail. What I need to do is make sure that when I press a button on a form, the value in a combo-box should be populated as "text format" not as a number. The reason for this is that when my numbers start with a 0, the leading zero gets dropped and I can't run the rest of my code as the items don't match...

    I have tried substituting the "Value" to "text" in : Me.cboItem.value and have tried for hours trying to name dim variables as a String and pass this into the cboItem...with no luck...Any help would be very much appreciated.

    Thank you.

    The below populated the combo box cboItem:

    Private Sub OptionButton1_Click()
        With Me.cboItem
            .List = Application.Transpose(Worksheets("Item List").Range("List1"))
        End With
    End Sub


    Really need some VBA help - I am trying to launch Excel Solver automatically by defining a range of cells that need to change in order for the solver to run.

    The problem is that the range of cells changes depending on another macro that runs.

    The range of cells is ALWAYS in cells H2:H30 but the number of items that are populated are different each time.

    For example, if only 4 items are present, the solver should run ByChange:=($H$2:H$5)

    if 7 items are present, the solver should run ByChange:=($H$2:$H$8)

    I have tried to substitute the range with a dynamic range formula, but it's not working.

    Does any one know how to resolve this issue? also, How would one clear the prior restraints solver had in the first place? i know it would be with the solver delete line, but since the range changes, it poses the same issue, you need to have it dynamic so you can delete it once.

    Thanks in advance.

    SolverOk SetCell:="$J$31", MaxMinVal:=2, ValueOf:=0, ByChange:="=OFFSET($H$2,0,0,COUNTA($H$2:$H$30),1)", _
            Engine:=3, EngineDesc:="Evolutionary"
        SolverDelete CellRef:="=OFFSET($H$2,0,0,COUNTA($H$2:$H$30),1)", Relation:=6
        SolverAdd CellRef:="=OFFSET($H$2,0,0,COUNTA($H$2:$H$30),1)", Relation:=6, FormulaText:="AllDifferent"
        SolverOk SetCell:="$J$31", MaxMinVal:=2, ValueOf:=0, ByChange:="=OFFSET($H$2,0,0,COUNTA($H$2:$H$30),1)", _
            Engine:=3, EngineDesc:="Evolutionary"

    Re: VBA transpose macro ignoring items that start with a 0


    this is great! thank you so much - you saved me tons of time a day.

    one quick question - is it possible to transpose the items without actually changing their number format...for example my item is 02333 but the code will run and on sheet 2 it will become 2333 losing the leading zero as I assume excel thinks it is a number not text...

    Thanks again!!!

    Dear VBA Gurus,

    I have adapted some code and it works almost perfectly in that it transposes data for each item in column A and details in subsequent columns on the same row and puts that in column format with the item number repeated, however, it does not work when the item starts with a 0 for example:

    Column A Column B Column C Column D
    12345 a-100 Blue a-233

    will result in:

    Column A Column B
    12345 a-100
    12345 Blue
    12345 a-233

    but if the item is 02345 or 03445 the code below won't pick it ignores it and moves to the next item in the list. Also if in the example above, column B is blank but columns C, D, E, F etc are not, the code will also ignore the item and move to transpose the next items.

    How would the code below need to be modified in order to address those two issues?


    Re: determine stock out day per item based on value in a cell


    This is a VBA generated sheet which spits out an array with thousands of items by day for about 2 years, so this cannot change and there are hundreds of thousands of rows...

    this seems like it should be simple I am overwhelmed by it..I'm thinking it needs to be a combination of an array formula/and or index match?

    Hi guys,

    I am stuck, and have no idea how to approach this:

    i have a few columns of data:

    column A: item
    Column B: date
    Column F: remaining inventory

    What I need is a formula to tell me the day I run out of inventory, the dates are sequentially sorted already and the balance on that day in column F. For example if for item 1 the quantity is 20 on March 15/13, and -20 on March 16/13, i really run out of inventory as of Mar 15th because I can't cover demand on the 16th. Now I have a list of hundreds of items sorted like that in the same columns...i need to know when I run out for each item. If the item does not run to a negative balance, i want to know the last day it is covered until

    Any ideas would be very much appreciated.

    Thanks in advance!

    Hi guys,

    i have a problem always getting an error when I am send an automated email from excel through Lotus Notes:

    Notes Error 7000: Database already contains a document with this ID (UNID)

    I see lots of queries online about this exact issue with no resolution. In my case, the email actually always sends, but I have a weak understanding of VBA, so i don't know how to do error handling. Does anyone know how to either fix this issue, or if not, how do I put an error statement in to ignore the pop up error message?

    Thanks in advance!

    Option Explicit
    Const EMBED_ATTACHMENT As Long = 1454
    Const stPath As String = "C:\Documents and Settings\user\Desktop"
    Const stSubject As String = "my report"
    Const vaMsg As Variant = "bla bla bla" & vbCrLf & _
                                              "Kind regards," & vbCrLf & _
    Const vaCopyTo As Variant = [EMAIL=[EMAIL="[email protected]"][email protected][/EMAIL]][/EMAIL]

    Public Function GetLotusUID() As String
        GetLotusUID = Replace(Mid(CreateObject("Scriptlet.TypeLib").GUID, 2, 36), "-", "")
    End Function