Welcome back Roobarb !
Guess he got hungry and finally decided to come home to eat.
Welcome back Roobarb !
Guess he got hungry and finally decided to come home to eat.
Hi,
Are you using some "illegal" file name in your Save As? Is something set to ReadOnly? Is the directory you are trying to write to protected or access restricted?
All I can think of at the moment.
.....Ralph
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 - -
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
Hi Cronina,
A couple of ways will work.
A1 = First Name B1=Last Name C1 = combination
C1 is =CONCATENATE(A1," ",B1)
or
C1 is =A1 & " " & B1
Hope this helps
.....Ralph
Give this a try....
=IF(OR(C7="Correct",C7="Received"),B6+E7,IF(C4="Issued",B6-E7,B6))
.....Ralph
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
Hi demok,
Do you have any .xls files in this directory - -
Program Files/Microsoft Office/Office10/XLStart
If so, they will open whenever Excel is started.
.....Ralph
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
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
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
Hi red,
I couldn't get your code to give that error. Can you post a copy of the workbook that is giving you the error?
.....Ralph
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:
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
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
Hi,
Sounds like you still have a link hanging around somewhere.
Check the Edit / Links menu item and see if there are still links defined there.
.....Ralph
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
Hi Alan,
If you're using Excel 2000 or XP, the VBA has funcitons to read from and write to the clipboard.
PutInClipboard Method
GetFromClipboard Method
The VBA help text has some good examples.
.....Ralph
Hi J,
Have you tried using Data / Validation on the cell? You can specify text length, or numeric values, or specific item from a list and give your own custom error message.
(it's on the Data menu).
Post back if this doesn't give you what you need.
.....Ralph
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
Hi Les,
Have you tried adding
Application.DisplayAlerts = False
in your code before the close statement.
.....Ralph