Posts by rem1224

  • [Solved] VBA: Inserting Formulas Via Code

    Hi -


    When you want to embed the double quote (") in the formula, you have to use CHR(34) or VBA thinks you are trying to close the original string.


    Try something like this - -


    Code
    Cells(Cell.Row, 65).Formula = "=IF('P:\UTP\Candidate Tracking\The Candidate Records\[" & myCandidate & ".xls]Main Record'!Rnwltr2 = " & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",'P:\UTP\Candidate Tracking\The Candidate Records\[" & myCandidate & ".xls]Main Record'!Rnwltr2)"


    I think I got all the quotes in the right place, but you get the idea...:wink2:


    .....Ralph

  • buttons and pop ups

    Here's something to get you started.


    You can either have a MsgBox pop up when one of the buttons is clicked or you can have a MsgBox pop up when the command button is clicked - - it does both right now. Text for the msgbox is hard coded in the macro, but you could have it read from a cell value.


    .....Ralph

  • Formulas: Using wildcards with VLookup formula?

    Hi kersmus,


    Didn't totally understand your question -


    If you are asking if VLooKup can search more than one table array at the same time, I think the answer is no (at least not without writing your own VBA code / function)


    If the table array keeps changing size and you need to change the vlookup formulas to match, you could make it a named dynamic range and use the name in the vlookup function.


    If I missed the point completely :wink2: , post back with a few more details and/or a sample file.


    .....Ralph

  • [Solved] VBA: Weekday function

    Hi red,


    You've got a sub in Module1 that you defined with the name Weekday - the Worksheet_open module is trying to run that instead of the built-in function.


    Change the name of your sub and it should be OK


    .....Ralph

  • Formulas: Getting Current Age

    Hi,


    =YEAR(A2)-YEAR(A1) will give you the straight calc 2003 - 1956 = 47 (make sure the cell is formated to numeric not date)


    if you want to find the correct age of a person try
    =(A2-A1-1)/365.25
    1/1/1956 to 12/31/2003 would give 47.99
    1/1/56 to 1/1/2004 would give 48


    Hope this helps


    .....Ralph

  • VBA: search function

    Thanks Derk,


    Sometimes, when I pull a code string out of the macro recorder, I forget to strip out all the unnecessary garbage that excel throws in - and I had forgotten that searchformat wasn't in 97, too.


    too much to remenber - too few brain cells :wink1:

  • Sharing: excel file sharing

    Hi jdee,


    I think having 1 file would be too big-- 50 people X 6 evaluators + summary sheets + charts.


    Since each of the 6 people doing the eval have to work seperately, you could change the structure and give each of them a workbook with 50 sheets (1 for each person being evaluated). When complete, you could consolidate the info from Sheet1(person1) from each of the 6 workbooks into a master matrix workbook - either 1 for each person or 1 master for all 50 people depending on space.


    Thats probably the easiest way around the privacy issue.


    .....Ralph

  • VBA: search function

    Hi Jurgen,


    The attached might get you started. It shows how to set up a userform to get the input, run a search and highlight all found cells with the target.


    For turning off the highlighting, I showed 2 options - first using a delay timer that leaves the cells highlighted for 3 seconds then changes back to blank - or second a change_event macro that changes back to blank when the user enters the cell.


    I couldn't get it to un-hihglight only when the user leaves the cell as your post asked - maybe someone else will have an idea on that.


    Hope this helps


    .....Ralph

  • VBA: Open CSV file with a macro..

    Hi domble,


    I think you've run into Bill Gates' rule that our American date format mm/dd/yyyy is the only correct format :biggrin:


    I'm assuming that your regional settings are dd/mm/yyyy and the csv file you are opening is in the same format. Probably every date with a day value of 12 or less comes across in mm/dd/yyyy format and the dates with days greater than 12 come across in dd/mm/yyyy format (although, if you check them, they are probably just text and not actual dates)


    One workaround I found is to change the file extension from .csv to .txt and open the text file in Excel. That will start the text import wizard and you can specify comma as the delimiter, then specify the actual format for each column. If you specify "date" where needed, all the values should come over with the correct format. You can also specify text, numeric, etc for the other columns were appropriate.


    Use the macro recorder when you do this and you'll have the correct code for putting in your VBA module.


    Hope this helps


    .....Ralph

  • VBA: Variable not retaining Value

    Hi pt,


    If you want to use the same variable in more than 1 sub, you have to declare it Public in the General section of the Module code (the very top before your first sub)


    Public Temp_variable As String


    Then don't Dim the variable in either Sub code.


    Hope this helps


    .....Ralph