Posts by homegrownandy

    I'm not very good at VBA. Through examples online I have put together this.

    I'm getting type mismatch on this line:

    ActiveDocument.TablesOfContents.Add rangeWord, _

    any ideas why?

    This thread is a continuation of the one here:

    Re: Move file based on file name.

    Hello. I have done some testing. I've moved a test file to my desktop and set up a similar folder structure.

    Now. this is the line with a problem:

    objFSO.MoveFile (strMyFolder & "\" & objMyFile.Name), strMyFolder & "\" & Left(objMyFile.Name, WorksheetFunc

    IF there is no folder in there which shares the name with the document it does tell me the folder doesn't exist. However if the folder exists and is empty (or contains something) then it says file already exists.

    I am no expert on VBA (obviously haha) and although I can understand most VBA I am clueless here.


    Edit: I've only pasted in part of this line here for some reason. Just wanted to make it clear the whole line exists as it does in the code provided.

    Re: Move file based on file name.

    Hi Robert. Thanks for your time. I am getting runtime error 58. file already exists. I have attempted this using an empty folder called test. I'm jut trying to work out why, but I can only guess at the moment.

    I'm sure I have all the parts to this code in one form or another. I just cant get anything together that will work.

    I want to move all PDF files in a folder based on its name. Specifically it will be the text before "-". These files are generated invoices. They need to be emailed and printed (this is done via vba). but at the end of that process I want them all moved to the correct locaton.


    123561143-99999-99999.pdf <-- would need to go into the 123561143 folder
    test-999-9999.pdf. < --- goes into the test folder.

    All the folders will be in the same location so.. It should be more than possible to extract that first part of the string and use that variable in the directory string for where its moving to. This would ideally loop through all the files in that folder and move them. Or error if the folder doesn't exist.

    If anyone could help me with any part of this I would appreciate it.

    Thanks, Andy.

    Re: Conditional formatting vs colorindex

    Update: an easier solution would be to apply an iif statement to the formatting then use a value in that row to define the colour.

    the range to highlight is names "plan". The current rule is =plan
    I want an if statement that looks in the F column and sees if its value 1/2/3 then ill apply the appropriate colour.

    im trying: =$F9=1+Plan and its not liking it... its highlighting the whole row.

    I have a gannt chart for project planning. I want the ability to select a cell and change the colour of it.

    Conditional formatting takes priotity over cell highlight. How can i avoid this?

    Public Sub test()
    Selection.Interior.ColorIndex = 37
    End Sub

    when I highlight certain cells i want to update as required for completions ect.

    Re: runtime error '13': type mismatch

    I'm able to pinpoint some of the cells causing error, and with time ill be able to get them all. So it appears to me I have two options.

    Hopefully I can apply this code to a range inside the workbook. Failing that ill have to run other code before it to delete the cells causing issue. (we don't actually need them as these are referencing external documents.) Ill investigate further and attempt to put this in a range, but I might not get back to this until early next week.

    Thanks so far!

    Re: Links to other spredsheets.

    the error is where I've highlighted in bold. "argument not optional" is what I get. I've tried putting set = before but the error is the same. Also I'm not 100% its in the right place. I don't know if its obvious but I'm no VBA expert haha.

    Re: runtime error '13': type mismatch

    Im a bit confused here, but I did try the:

    "If InStr(vCell.Value, "ram")>0 Then"

    With no success.

    All I want to do is look for the word RAM then highlight the row. Maybe you guys have a suggestion on how to do this differently?

    As the title suggests I'm getting the following error:

    runtime error '13': type mismatch

    On this code:

    specifically this line:

    If InStr(vCell.Value, "ram") Then

    But this code works fine on any spreadsheet I've created, just not on the one we receive weekly from our clients. I've done a bit of googling and I'm at a loss (and its nearly home time) so I thought I would ask here.

    Hopefully someone can help. Thanks.

    We receive a lot of spreadsheets from our clients that contain links to their internal servers. Obviously we can not update these links and we get the following error:

    "This workbook contains one or more links that cannot be updated"

    I'm trying to run some VBA on these as they come in and I'm having a few problems. This being one of them.

    Is it possible to disable all linking with VBA or maybe another way so it doesn't interrupt the script?


    Re: outlook window popup


    AppActivate oMail.Subject

    Works perfectly. I'm sure if I added more code it would do as you suggested so ill bare that in mind.

    Thanks again.

    I'm using the below code to send emails from a form. Everything is working as expected but its not obvious that its working. The outlook window that opens remains in the windows taskbar. This does flash to indicate its open but I want it to popup on screen. Is there anything I can add to the code for this to happen?

    Thanks, Andy

    Re: Copy associated cells for not blank cells.

    I couldnt actually get to the error at the time, It was locking up my computer totally. I had some other code in that document which i think was stopping it. I've copied the data to a new document and run the code and it worked perfectly. Thanks a lot for that. Really appreciate it.

    Re: Copy associated cells for not blank cells.

    Runtime Error 1004

    I think you were right earlier when you said there was too much data. Is it possible to make this loop or something? I appreciate what you have done though, you've freed up days of time for me.. even if i spend a day or two trying to make this run in a loop.