Posts by sabal87

    Re: Check Macro for updates


    Yeah, I'm thinking of having a simple spreadsheet with the dates of the most up to date macro, and have the macro at their computers check and compare dates

    I am updating multiple macros at my company, and I am looking for a way to "check for updates." Sometimes people don't have the latest macro, and it causes problems that have been already been fixed, but they don't know.
    I thought about making an excel workbook that can be accessed to determine if the current macro is still working properly, or if it should be updated. Any other ideas?

    Re: Obtaining information from Listboxes


    It gives me an error "Object required"
    I think it's having trouble finding ListBox1.Listcount. I think the problem is with Listbox1. I tried renaming my list box "Listbox1" and "List Box 1" but neither one worked. Do i need to set Listbox1 as activesheet.shapes("List Box 1")?

    I have a listbox (form control) on an excel sheet, where the user can select (or deselect) multiple entries.


    Is there a way to have vba spit out which options are selected? if I choose "single" for selection type, it will tell me which option i have selected. Is there something similar for multiple?

    Re: Recurring projects in a week


    I've tried making arrays, but like I said, I get to:


    Project a (16), Project b(16), Project b(16), Project a (16), project c (8)


    I need to eliminate recurring projects, so it looks like:


    Project a (16), Project b(16), project c (8)

    I am trying to make a timesheet for my job, and have it add the hours worked for each project. I have something like the table below:[TABLE="width: 500"]

    [tr]


    [td]

    DAY

    [/td]


    [td]

    HOURS WORKED

    [/td]


    [td]

    PROJECT

    [/td]


    [/tr]


    [tr]


    [td]

    monday

    [/td]


    [td]

    8

    [/td]


    [td]

    project a

    [/td]


    [/tr]


    [tr]


    [td]

    tuesday

    [/td]


    [td]

    8

    [/td]


    [td]

    project b

    [/td]


    [/tr]


    [tr]


    [td]

    wednesday

    [/td]


    [td]

    8

    [/td]


    [td]

    project b

    [/td]


    [/tr]


    [tr]


    [td]

    thursday

    [/td]


    [td]

    8

    [/td]


    [td]

    project a

    [/td]


    [/tr]


    [tr]


    [td]

    friday

    [/td]


    [td]

    8

    [/td]


    [td]

    project c

    [/td]


    [/tr]


    [tr]


    [td]

    saturday

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    sunday

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]
    (sometimes we work saturday and sunday)
    and I'm trying to get vba to spit out in a cell below something like the following:


    [TABLE="width: 500"]

    [tr]


    [td]

    Project a (16), project b, (16), project c (8)

    [/td]


    [/tr]


    [/TABLE]


    I've tried to go down the PROJECT column and if the project name repeats, then it adds the hours worked. But when I do that, I get something like Project a (16), Project b(16), Project b(16), Project a (16), project c (8)


    Does anyone have any ideas that could help?
    I'm sorry if it sounds confusing.

    Re: Calculate number of hours worked


    One problem I'm seeing from all the answers I've gotten so far is that if I work from 8:00AM to 4:30PM, then it says that the number of hours worked is 8:30. Is there a way to make it say 8.5 instead?

    I tried looking at old threads, but I can't seem to find anything that works for my case.


    I have a time sheet similar to the one below.


    [TABLE="width: 500"]

    [tr]


    [td]

    day

    [/td]


    [td]

    time in

    [/td]


    [td]

    time out

    [/td]


    [td]

    time in

    [/td]


    [td]

    time out

    [/td]


    [td]

    hours worked

    [/td]


    [/tr]


    [tr]


    [td]

    Monday

    [/td]


    [td]

    8:30

    [/td]


    [td]

    4:30

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Tuesday

    [/td]


    [td]

    8

    [/td]


    [td]

    12:30

    [/td]


    [td]

    1:30

    [/td]


    [td]

    5

    [/td]


    [td][/td]


    [/tr]


    [/TABLE]



    I'm trying to write a code to calculate the hours worked (in both cases 8 for the example above). I've tried different number formats, but I can't find one that works.


    Any ideas or comments would be greatly appreciated

    Re: Obtaining values from a table using VBA


    I found an answer that worked for me.
    First, add

    Code
    Dim Dia(4) As Long
        Dim Length(4) As Integer


    And Change the for loop to:


    Code
    For i = 0 To 4    Dia(i) = Range("b" & i + 4)
        Length(i) = Range("c" & i + 4)
        Next

    I have the following table, and Im trying to get the different diameters and lengths so i tried to make a simple for loop to go through each row and get the corresponding number.[TABLE="class: outer_border, width: 500"]

    [tr]


    [td]

    Row and Column numbers/letters

    [/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td][/td]


    [td]

    Diameter

    [/td]


    [td]

    Length

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    Section 1

    [/td]


    [td]

    3

    [/td]


    [td]

    10

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    Section 2

    [/td]


    [td]

    6

    [/td]


    [td]

    20

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    Section 3

    [/td]


    [td]

    8

    [/td]


    [td]

    30

    [/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    Section 4

    [/td]


    [td]

    10

    [/td]


    [td]

    40

    [/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td]

    Section 5

    [/td]


    [td]

    24

    [/td]


    [td]

    50

    [/td]


    [/tr]


    [/TABLE]



    This code gives me the error "sintax error" with Dia & i. or "sub or function not defined" for Dia i.
    Does anyone have any ideas I could try?

    Re: Looping macro to populate master spreadsheet


    At the beginning of the code, you might want to create the master spreadsheet, and name it something. i.e MasterSpreadhseet (If you already have it created, just open it and run the macro from there?)
    on the loop, you can select your range, copy it, and then activate your masterspreadsheet, and paste whatever info you copied

    Re: Looping macro to populate master spreadsheet


    Something similar to this was covered in a few other threads. But anyways, here is a code that will open every excel in a specific folder, and then you can do whatever you want. See if this helps you get started


    Re: Macro to Copy from one workbook and paste values to an other workbook


    A few things I see:


    try to not name variables as just letters. give it a particular name more specific to what it is.
    on sPath, make sure you put your particular path. One line of code I like if you will run this many times on files in different folders:

    Code
    Dim WorkbookPath as String
    WorkbookPath = Activeworkbook.Path
    sPath = WorkbookPath


    codewise, the lines k = ... and n = ... should go after the "Set oWbk = ....."
    try those things and see if it gets better

    Re: Macro to Copy from one workbook and paste values to an other workbook


    If from what I understand, you need to open many files from one folder, and copy data from them into wb Mailmerge sheet1?


    try checking this out. I had a similar problem and this worked for me
    http://www.excel-it.com/Excel%202007/2007_filesearch.htm


    on the line:
    Do While sFil <> "" 'will start LOOP until all files in folder sPath have been looped through Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file


    the following part should be on a new line (just press enter right before it)
    Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file

    In cell B7, I have the number 32-338, but because of the "-" , excel thinks it's text. Is there a way to get 32-338 as a number?


    The end point is that I need it to look at cell B7, get the number (in this case 32-338) and then also give me the previous and next numbers (32-337 and 32-339)


    I tried

    Code
    Range("b7").NumberFormat = "#"

    , but that turns it into 32.


    I also can't assume that the numbers on the right will always be two digits, or that the numbers on the left will be three.

    Re: Copying tables from Excel to Word using VBA


    I finally got back to working on my macro (I was working on something else for two weeks), and finally got the reference to work. But I'm still having trouble pasting all my tables to one word document.
    My latest code is on a new thread below. Since you were able to help me before, I was wondering if you had any input now. Thanks for all your help so far

    http://www.ozgrid.com/forum/showthread.php?t=174443

    Quote from cytop;644238

    Then you need to add a reference to Word using the Tools/References menu...

    Re: VBA code to Copy a Range Into Word


    I tried to use this code because I'm trying to copy many tables into one word document. But every time the code runs, it makes a new word document. How can I make it so all the tables go into the same Word document?