Posts by PeterLz

    Its not an actual table, what I showed in the image was just a depiction of how the information is stored in a plain sheet. I cannot attach any files since they are sensitive information. Basically what I would like to achieve is to count the columns in Sheet1, and on the last one that has data, use a rows. Count downwards to the last non-blank row and then copy that range to the new Workbook. The move part I can manage, but I cannot achieve the range selection/copy. Also, my headers don't have the same information so I cannot use that as reference.

    Hi,


    Actually I do need the selection part since the data will be copied and moved to a new workbook. And I want only the last used column (highlighted in yellow), which can be in any column since my data is dynamic both in columns and in rows. That's why I tried to use the last column and last row but I cant seem to make it work. I always end up selecting the last cell only, not the range.


    Hello,


    I have an excel file that has data in a dynamic range, ranging from Column to column. I have some filters on column A. What I would like is to select the last used column, ranging from first visible cell in that column to the very last cell that has data in the filtered range. I hope its understandable.

    Hi there,


    I am trying to use WScript.Shell to search a specific path for certain files. Problems is the path has over 1800 folders, and it takes a crazy amount of time to search each and every one. My question is, how could I search certain folders, for specific excel files. The folders I'm interested in all start with JOB-0 in the naming, going up to 0700 and more. And in each of these folders I want to search in the subfolders for .xls file and open it to collect some data.


    So lets break it down:


    Path: C:\Test\
    FolderSrchString = "JOB-0"
    SubFolderSearch = whatever the name is
    flExtension = *.xls*
    Open and then I run my own macro to extract the data, close and next folder/file loop.


    Any help would be appreciated. Thanks!

    Hi guys,


    I was hoping that you can help me out a bit with an issue I keep having with Excel VBA/Access database.
    I am trying to fetch a certain data with the following statement:


    sQRY = ("SELECT DBobs.[dbObserv] FROM DBobs WHERE DBobs.[dbLine]='" & UFmain.ComboBox2.Value & "'" & " AND DBobs.[dbFAM]= '" & UFmain.ComboBox1.Value & "'" & " AND DBobs.[dbDate]='" & rng50.Value & "'")


    But whenever I run the macro, I keep getting the data type mismatch error.
    The dbObserv is set as date/time, and the rest are short texts. The problem is with my range value (rng50.value). The cell contains the date and time in this format: 25.07.2018 13:54:12 just like in the Access database. I want to compare the 3 datas to retrieve dbObserv's value but I'm missing something with the rng50 value, since the code works if I take that out. Im guessing the format or something is missing.


    Hope it all makes sense. I just started working with Access database through Excel and I'm still learning.


    Any advice would be helpful.

    Hi,


    Sorry for the late reply, I had other topics to attend to. So, the excel file is doing exactly what I wanted. There is a single problem, if i clear the cell on the Form sheet (H16:I16) it gives a runtime error, and the line code highlighted is the following:


    End If
    End If
    Sheets("Data").Range("J" & foundName.Row & ":K" & fNonEmptyRow - 1).Copy Cells(Rows.Count, "J").End(xlUp).Offset(1, 0)
    If Sheets("Data").AutoFilterMode = True Then Sheets("Data").AutoFilterMode = False
    End If


    Also, if you cold help me with keeping the formatting (borders) on the Form sheet after retrieving the data would help.


    Many thanks!

    Update,


    I have forgotten a crucial detail regarding all of this information gathering. On the Form sheet, there is a cell in which the user first enters a specific number (cell F15:I15), which you can see on the Data sheet now. I want to get the information with this as a reference, because my Data has multiple entries with the same Name, but the locations differ. So, checking what storage nr it has, checking for the Name and then retrieving the Color and Location. Here is the new attachment.

    Hi Mumps,


    First of all, thank you for helping me out with code. The code you gave me worked perfectly, with the file I gave you, with the examples. But sadly it wont work with my Database, so I have uploaded a new file containing my Database. I encountered multiple errors with this code. If the target cell is empty (H16), it gives the error: Run-Time error '1004': Copy method of Range class failed, and i cannot start the UserForm. If I fill OR clear the cell, it gives the following error: Automation Error -2147417848 (80010108) The object invoked has disconnected from its clients; rendering the excel file inoperable and need to close it and reopen it for use.

    So as you requested, i attached the file, and colored the cells accordingly. The userform is going to be the main interface with what the user will complete the form. At this moment they are filling in the form manually, hand written. We have the database for the required information, but it takes a lot of time to fill in each form. This is the only part that i can't get to work. I kept the sheets format exactly the same. Basically when the user selects ex: apple, on the Data sheet it has an ID number, and it has multiple entries in column J and K. Those columns need to be inputted automatically on the Form sheet accordingly. Hope its enough for understanding. Thank you in advance anyways!

    Hello all,


    First of all, I want to thank you guys, for this forum, and let you know that it helped me out quite a few times regarding VBA and macros. Since I am fairly new to VBA, and there are a lot of things I still need to learn, today I am stuck with issue. Straight to the topic:


    I have an excel file that contains 2 sheets. Sheet1 called Data and Sheet 2 Called Form. On sheet2(Form) there is a Form that the User needs to fill in the required cells with information. I am trying to make this as automated as possible. I have a Userform in which he can manually input the datas. When a specific cell is filled in, I want excel too look for that value on the Data sheet, lookup its unique ID, and return all of the values with the same unique ID into the Form sheet.
    Because of company policy i can't show the exact datas, so I created a similar one for reference.


    My columns are from A to D, A being ID number, B name, C color and D box amount.
    On the Form Sheet, the user input data is on cell D25, and the returned value starts from H25 downwards, depending how many unique ID's the item has.


    If the User types in Baloon type 2, I need excel to check the ID number,and return all of the values from collumn C and D. Note that these rows are dynamic, it can have 5 rows with the same ID or 25 or even more.


    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 260"]

    [tr]


    [td]

    ID

    [/td]


    [td]

    Name

    [/td]


    [td]

    Color

    [/td]


    [td]

    Box Amount

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Baloon type 2

    [/td]


    [td]

    red

    [/td]


    [td]

    5

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td]

    green

    [/td]


    [td]

    10

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td]

    blue

    [/td]


    [td]

    20

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td]

    white

    [/td]


    [td]

    15

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td]

    yellow

    [/td]


    [td]

    30

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td]

    orange

    [/td]


    [td]

    8

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Ribbon type 4

    [/td]


    [td]

    black

    [/td]


    [td]

    5

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    yellow

    [/td]


    [td]

    10

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    red

    [/td]


    [td]

    15

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    white

    [/td]


    [td]

    8

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    purple

    [/td]


    [td]

    9

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    grey

    [/td]


    [td]

    12

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    orange

    [/td]


    [td]

    10

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    green

    [/td]


    [td]

    7

    [/td]


    [/tr]


    [/TABLE]


    I hope it is understandable, and thank you in advance!