Posts by jarko28

    Re: Error handling in vba or change to " if condition "


    Gentlemen,


    thanks for your help thus far...


    i am getting an error when trying to adopt the code...i want the value of "y" variable to be the item that I click on, so if I click on cell A2, and the value is "12345", i would like that to be the search value...when i do that, i get a variable not defined error:



    what am i doing wrong?

    I have the below code but I need a bit of help changing it:


    Currently, it looks for a string in column A in sheet 2, if it finds it, it puts a value in column B on that same row....but it also puts some random values at the end of the list because my error handler is wrong...


    What I need is:


    IF you find it, then put a value in the adjacent cell, then exit macro and
    IF the item doesn't exist, then put the item at the end of the list


    Any help would be appreciated.


    Hello,


    I am hoping someone is able to help me solve the following:


    Sheet1
    In Column A, there is a list of items.
    In Column B, there are comments relating to this item.



    I have userform1 with textbox1 where a user can enter new notes.


    On Sheet2
    userform1 is initialized when an item clicked. What I would like it to add text into textbox1 and pass the value of the textbox to Sheet1 column B next to the same item as clicked on sheet2...


    If the item is not found, then the code should insert the item at the end of the list along with the comment.
    If it is found, it should override the comment


    The default value of the textbox should be the comment that already exists when the userform shows up...if not found, it would be blank...


    Thank you very much for the help...

    Hi all,


    The below code autofilters a spreadsheet based on a value I click on, and displays columns 1,2,5 of all records based on the filter in a userform. How would I change the code in order to do the same thing but the autofilter should be applied to a worksheet in a closed workbook:



    Code
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)      If Target.Column > 2 Then Exit Sub      With Cells(1).CurrentRegion          .AutoFilter Target.Column, Target.Value      End With      UserForm1.Show  End Sub





    In your Userform..

    Code
    Private Sub UserForm_Initialize()      Dim a As Range, n As Long, Val As Variant      Set a = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(12)      With Me.ListBox1          .ColumnCount = 3          For Each Val In a              .AddItem              .List(n, 0) = Val: .List(n, 1) = Val.Offset(0, 1): .List(n, 2) = Val.Offset(0, 5)              n = n + 1          Next      End With      ActiveSheet.AutoFilterMode = False  End Sub



    thanks in advance,

    Re: Reference Range in a closed workbook


    Jindon,


    thanks for the quick response...this is actually code you gave me in another post that i am trying to edit...i am trying to edit the below in order to search in another workbook ...when i click a cell, it looks for that value in a closed workbook and shows the offset columns...i tried to edit it with wht you have above but it's not working too well...


    How would i alter the below to look on another sheet, and ideally in another closed workbook ....i would like to understand both ways if you would be kind enough to provide....

    Hi all,


    I have been searching posts on here but all potential solution which I thought would work seem to give me VBA errors ...


    what i am trying to do is reference a range to a closed workbook...the below works great when I try to use it on a workbook that is open, but I cannot figure out how to alter the code in order to reference a range in a closed workbook...



    Any help would be appreciated.


    thanks.

    Re: show all related items in column B based on a list in column A, then show in user


    jindon...


    this is lightning fast....WOW...so much to learn.


    thank you so much!


    just one quick question...if i wanted to show the different attributes in columns b and f on the same line in the listbox, how would I alter the code? I know i would need to use the multi column property in the listbox, just cant figure out how to make the spaces between columns a uniform length in the listbox...


    thanks again!


    Amazing.

    Hi all,


    I have a list of items in column A, say:
    Column A:
    1
    1
    1
    2
    2
    3
    1


    in column B,C,D,etc.., I have attributes of those items..


    Column B
    A
    B
    C
    D
    E
    A
    F


    So now, what I am struggling with is some VBA code when I click on the item, say a cell containing a "1", I would like a userform listbox to pop up and show:


    Item 1
    A
    B
    C
    F



    and also the inverse, when I click on attribute in column B, I would like to see all items related to that attribute...so if I click on a cell containing an "A", i would see


    Attribute A:
    1
    3



    I have array formulas for this, but they are dragging me to a halt completely, was hoping VBA solution could speed things up...my tables have about 100,000 rows of data...


    any help would be great,


    Thanks in advance.

    Re: Store result of each "for loop" calculation and use it to calculate running total


    Hi,


    I have attached it, but it would not let me attach it as a macro book...the idea is that i click on cell on sheet 2 in column B on the item number...it takes a look at the quantity...then it looks at how much is to be consumed on sheet 1 by date, and gives me a running total as a list...


    thanks for looking at this, and thanks for giving me the first part of the code, really really appreciate it!

    Hi,


    Having major issues with trying to calculate a running total...




    This is the starting total:

    Code
    Application.WorksheetFunction.VLookup(ActiveCell, rng3, 7, False)


    I would like to store the looked up value in each lookup of

    Code
    Usage is looked up:
    Format(Int(.Cells(r, Cell.Column)), "##,##0")


    Code
    So the formula would be: Application.WorksheetFunction.VLookup(ActiveCell, rng3, 7, False) - Format(Int(.Cells(r, Cell.Column)), "##,##0")


    this then gets stored as a variable to be used as the number, i tried to name it variable "y". I want to then take away the next looked up usage in the loop...


    Does anyone know how to adjust the code below to get this result?



    So if the starting total is 10
    the usage looked up in the loop is 1,2,3,4....


    I want the running balance to negate and display each negation....9,7,4,0


    Hi All,


    I want to display part of my table in a userform, but the subgroubs of any given items (sometimes they are duplicates so I don't want to display it, and sometimes in other column they are not, so i do want to display it):


    I have attached a sample... also the userform referencing the table will be in a different workbook..


    please help if you are able, thank you in advance.

    Hi all,


    I have 2 sheets. On the first sheet is a list of items...


    Column A
    ITEM A
    ITEM B
    ITEM C
    ITEM D


    On the second sheet, I have a master list of those items and maybe some others which have relevant information broken down by date for that item...
    ______DAY 1__DAY 2___DAY 3___DAY 4
    ITEM A 4______1 _______________1
    ITEM B _________5_______6_________
    ITEM C_ 1_______1_______1_______1
    ITEM D 10 ______________30


    What I am trying to accomplish is:


    When I click in sheet1 on the cell containing the specific item number, I want a message box to pop up and show me a list of quantities by day for the item selected, the data in the msgbox to be populated from sheet2 but skipping the blanks if the cells are blank or 0 quantity


    so on sheet 1, if I click on item D, message box appears with:
    -------------------
    ITEM D
    DAY 1 10
    DAY 3 30


    -------------------


    Any help would be very much appreciated.


    Thanks in advance!

    Re: Transpose cells based on type


    Hi NBVC,


    Thanks so far...


    Almost exactly as required.That seems to work except that i am not sure how to get the item headers over to appear twice in the column so that they could be indexed with your formula, any ideas?


    Thanks again.