Posts by malani2

    Re: Add "AutoSum" formula to cells in Range based on Cell Text


    Aloha,


    I think I figured it out myself. But would love to know if there is a better to perform this function as I'm trying to teach myself VBA.


    Code
    1. 'change sum in subtotal to formula
    2. Set sumrange = Range("H:Q")
    3. For Each Cell In sumrange
    4. If Cell.value = "sum" Then
    5. Cell.Select
    6. ActiveCell.Formula = "=Sum(" & Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp)).Address & ")"
    7. End If
    8. Next

    Aloha,


    I have a formula that I found that simulates the AutoSum button in VBA. ActiveCell.Formula = "=Sum(" & Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp)).Address & ")"
    and I would like a vba code to add this formula or perform the autosum function in each cell in the range that contains the word "sum".


    I'm not sure how to write the macro to do this. Any help is appreciated.

    Re: Update existing data using a UserForm


    Ger,


    I believe I'm already beyond my VBA skills. I have no idea what to do to get the info to update now that I've used your find option.


    Re: Update existing data using a UserForm


    Ger,


    thank you so much. I like your find code much better. It's much faster with a lot of info. I'm going to start working on the update code, so I may be back if I can't figure it out.

    Aloha,


    I am very new to VBA and have created a UserForm for adding data to a worksheet by basically cutting, pasting and editing codes that I have found by searching the internet. Therefore, I know that my command button codes are not very consistent. I have gotten to the point that I can add new records to the sheet, find an existing record and populating the data back in the UserForm, however I am now stuck at updating an existing record that I just found.


    Here's what I got. The cmdUpdate_Click() area is where I'm in trouble. Instead of updating the info that I just found with cmdFind_Click(), it adds a duplicate entry with the new information. (Note I deleted some code that is working fine for dropdown lists as I didn't think you needed all that info, but if it's necessary I can paste my full code. Also my actual report has over 30 columns, I reduced it down to 4 here)